PL/SQL :: ROLES And Table Access

May 23, 2013

I am using Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production.I have a table D_GEOGRAPHY_25729 in RDS schema and I have created a role ACC_D_TABLES logging as ADMIN.

Now I have given GRANT SELECT access for table D_GEOGRAPHY_25729 to role ACC_D_TABLES.

There is another schema TRANS in the same database which has the role ACC_D_TABLES. But after logging in this schema, I am not able to query directly from table D_GEOGRAPHY_25729. I need to use like below to access the table.

select * from RDS.D_GEOGRAPHY_25729 Having the correct role ACC_D_TABLES to TRANS schema, why should I use RDS.D_GEOGRAPHY_25729 ? Is it not possible without prefixing the RDS schema name?

It is possible when I create a public synonym for D_GEOGRAPHY_25729 in RDS schema, but if i do so everyone can access this table.

View 7 Replies


ADVERTISEMENT

Server Administration :: ORA-28031 Maximum Of 148 Roles Enabled Roles Exceeded

Sep 14, 2010

SQL> connect USER/Pass
ERROR:
ORA-28031: maximum of 148 enabled roles exceeded

Getting the error like this..!

SQL> show parameter max_enable
NAME TYPE VALUE
-----------
max_enabled_roles integer 150
SQL> alter system set max_enabled_roles=200 scope=spfile;

[code]....

Still am not able to connect to the db

View 8 Replies View Related

PL/SQL :: Index Range Scan And Table Access By Index Rowid Versus Table Access Full

Oct 5, 2013

Let's consider such table that all rows fit into single block:

SQL> create table test as select rownum id, '$'||rownum name from dual connect by level <= 530;
Table created.
SQL> create index i_test on test(id);
Index created.
SQL>
SQL> begin

[code].....

why does approach with full scan take longer even if table occupies only one data block? PS. 11gR2

View 8 Replies View Related

Application Express :: ORA-24247 / Network Access Denied By Access Control List

Apr 5, 2013

When I invoke SOAP Web-Service using APEX_WEB_SERVICE.MAKE_REQUEST, then I'm able to get response from web-service. However all German character are replaced by JUNK data. However data is coming fine when I test web-services using SOAP UI.

I tried to invoke web-service using UTL_HTTP. However when I use UTL_HTTP, then I'm getting following error.

ORA-24247: network access denied by access control list

<li> Why German characters are replaced by Junk data while invoking web-service from APEX, while it's working fine from SOAP UI

<li> When I can access web-service successfully using APEX_WEB_SERVICE, then why it's throwing ORA-24247 error when I call using UTL_HTTP?

DB: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
APEX: 4.0.2.00.07
Web-Server: EPG
SELECT * FROM NLS_DATABASE_PARAMETERS;

[code]...

View 4 Replies View Related

ORA-24247 / Network Access Denied By Access Control List (ACL)

Dec 12, 2012

I am having oracle database version 11.2.0.3.0 standard edition, where one of my users requirement is that he wanted to send mails from oracle database but he is getting below error

ORA-24247: network access denied by access control list (ACL)
ORA-06512: at "SYS.UTL_TCP", line 17
ORA-06512: at "SYS.UTL_TCP", line 267
ORA-06512: at "SYS.UTL_SMTP", line 161
ORA-06512: at "SYS.UTL_SMTP", line 197
ORA-06512: at "HF_REPORTING.SEND_MAIL", line 12
ORA-06512: at line 18

is it possible to send mails from oracle database in STANDARD Edition?

View 1 Replies View Related

SQL & PL/SQL :: ORA-24247 / Network Access Denied By Access Control List (ACL)

Nov 15, 2011

While Running the proc,it's raising the below error.

Error report:

ORA-24247: network access denied by access control list (ACL)
ORA-06512: at "SYS.UTL_TCP", line 17
ORA-06512: at "SYS.UTL_TCP", line 246
ORA-06512: at "SYS.UTL_SMTP", line 115
ORA-06512: at "SYS.UTL_SMTP", line 138
ORA-06512: at "LISTER_SHIFT.SOLN_GENERIC_SENDMAIL", line 33
ORA-06512: at line 1

[code]....

View 12 Replies View Related

PL/SQL :: Table Access Full

Aug 11, 2012

i have query lie below.

select
(SELECT MIC.MICR_BANK_NAME FROM M_ECS_MICR_MST MIC WHERE MIC.PK_MICR_ID = BANK.ECS_MICR_CODE) AS BANKNAME,
, (SELECT PAY.PAYMENTCODE FROM M_ECS_PAYMENT_MODE PAY WHERE TO_CHAR(PAY.ID) = BANK.ECS_FACTORING_HOUSE) AS
[code].....

i have used composite index below column which used in the tbl_bank_statement table.like column name tbl_bank_ statement (policy_ no,ecs_ micr_code,ecs_factoring_house,ecs_mandate_status)

but still this table giving me TABLE ACCESS FULL.

View 3 Replies View Related

PL/SQL :: Grant Table Access

Aug 15, 2013

I have a schema TEST1 and it has a view 'TEST_VIEW'. I granted read only permission on the view to TEST2 schema. grant select on TEST_VIEW to TEST2. 

But TEST2 is used  by an application which requires to read view metadata and then sync with application afterwords start reading. Is there any special permission to grant to access metadata of the view / table.

View 4 Replies View Related

SQL & PL/SQL :: Roles Given To Objects

Feb 27, 2011

I have granted execute, select, insert, update, delete privilege on objects to roles. Now i want to check status. we have around 2000 objects.

Require out put should be like this.

Object_name Object_Type Role_Granted
----------- ----------- ------------
Table1 Table ABC_ROLE
Table2 Table CDE_ROLE
PROCE1 PROCEDURE PROC_ROLE
Func1 FUNCTION FUN_ROLE

View 3 Replies View Related

PL/SQL :: Cross Environment Table Access

Mar 13, 2013

I am working in all 3 environments: development , Test and Production. How can we access the tables created in one environment in the other environment. Also, request to share the SQL code for the same.

I am using Oracle Sql Developer tool.

View 4 Replies View Related

SQL & PL/SQL :: Access Table From One To Another Database On Same Server

Jun 22, 2010

How can we access a table from one database to another database on the same server.

Let us say One database is "A" and the Other database is "B" both databases are running on one server "ABCD".

My question is how can we access a table from database B to database A.

View 21 Replies View Related

SQL & PL/SQL :: Packages - Roles And Privileges?

Aug 9, 2005

I'm not sure if this belong to this place. move it to the proper place.

I'm having an annoying problem: (I'm not sure if it is a problem)
- In a package body I'm trying to delete/update/insert/select the contents of a table in other schema.
- The respective synonym exists.
- I had created a role with the respective privileges over the synonym.
- I granted the role to the package's owner.
- I try to compile the package, but it keeps returning compilation errors. (Not table found)
- In standard SQL, I can delete/insert/update/select over the table.
- The only way to compile the package, without errors, is to grant the privileges directly to the package's owner.

Is it supposed to work in this way?

View 7 Replies View Related

What Is Meant By Roles And Privileges

Jun 27, 2012

What is meant by Roles and privileges?

View 4 Replies View Related

Server Administration :: How To Track The Last Access To A Table

Oct 11, 2011

is it possible to track the last access to a table?

View 2 Replies View Related

Security :: Ensuring Read Only Access To Any Table

Jul 27, 2012

I am an auditor. I need to ensure that a perticular user at any cost should not be able to update contents of a table.

Privileges granted:

GRANT SELECT ANY TABLE TO "ABC"
GRANT ALTER SESSION TO "ABC"
GRANT CREATE SESSION TO "ABC"

Can abc modify, alter contents of a table?

View 7 Replies View Related

Security :: How To Get The Data Access For A Temp Table

Jul 9, 2013

How to get the data access for a temp table ?

View 5 Replies View Related

Access - Sql Statements Being Fired On Table (Test)?

Oct 4, 2012

Oracle 11gR2

I have on table 'Test' in database and I want to know currently which sql statements are being fired on table 'Test' ?

View 8 Replies View Related

Forms :: Dynamic Value (get Roles) In Combo Box

Feb 12, 2010

i want to get user (dba roles in combo box runt time).i have write down this code in when new form instance.

declare
rg_id recordgroup := find_group('RG') ;
ln_error number ;
begin
if not id_null(rg_id) then
ln_error := populate_group(rg_id) ;
if ln_error = 0 then
populate_list('BLOCK3.T' , rg_id) ;
else
message('Error in Populating Group') ;
end if ;
end if ;
end ;

attached file.when i run it gives the follwoing error. frm-41334-invalid record group.

record group query is: SELECT GRANTED_ROLE FROM DBA_ROLE_PRIVS WHERE GRANTEE='MTSYS'

View 6 Replies View Related

SQL & PL/SQL :: Grant All Roles Of One User To Another Through Script

Nov 24, 2010

I am trying to create a script where a user can be granted all the roles that another user has. I created a script:

create user yyyyy identified by zzzz default tablespace ahspace temporary tablespace temp_data;

grant connect to yyyyy;

declare
cursor grantpriv is
select granted_role from dba_role_privs where grantee='xxxxx'; -- existing user
cursor_name integer;
statement varchar2(200);
Begin
cursor_name :=dbms_sql.open_cursor;

[Code]....

View 3 Replies View Related

SQL & PL/SQL :: What Kind Of Privileges Can't Be Assigned To Roles

May 2, 2010

I read that few privilegs can not be granted to roles like 'UNLIMITED TABLESPACE ' and they need to granted to user directly.

1. Is there any reason behind that few privileges cant be granted to roles or is it just oracle design.
2. what kind of privileges cant be assigned to roles.

View 7 Replies View Related

Security :: Grant Privs To ROLES?

Feb 18, 2013

I am working on the roles and privs. I want to provide the privs on any object to any user through the roles. not a direct grant.I am having 150 users and 50 roles, I want to do this using the script is few strokes. for this I have tried to create a script but I am not able to make that.

For example There is 2 user scott and test

A. test is having select privs on scott.emp.
B. Test is having insert,delete,update privs on scott.emp;
c. DB is having 2 roles
scott_edit - insert,delete,update privs on tables
scott_read - select privs on tables

I want to revoke separate privs from test and want to given only scott_edit and scott_read (which(roles) is having all privs)

View 2 Replies View Related

Server Administration :: SQL For Generating Roles

Dec 18, 2012

I am looking for the below one.SQL for generating roles needs to be spooled by automated script.

View 4 Replies View Related

SQL & PL/SQL :: Access Individual Item From A Table Of Type ROWTYPE

Jun 1, 2010

Script for test:

CREATE TABLE TEST(empno VARCHAR2(4), empname VARCHAR2(50), empstd NUMBER(2))
insert into test values(0001,'A',2);
insert into test values(0002,'B',5);
insert into test values(0003,'C',2);
insert into test values(0004,'D',7);
insert into test values(0005,'E',9);

Now I want to get empno for the particular employees based upon subscript and I have written below

DECLARE
CURSOR cur_rec
IS
SELECT *
FROM TEST;
TYPE cur_type IS TABLE OF cur_rec%ROWTYPE;
v_cur_rec cur_type;
BEGIN
OPEN cur_rec;
FETCH cur_rec
BULK COLLECT INTO v_cur_rec;
[code]....

how to access individial item froma table of rowtype data.

CM: Added [code] tags, please do so yourself next time, see the orafaq forum.

View 5 Replies View Related

SQL & PL/SQL :: Very Slow Access And Updation Of Table Data Of Another Schema

Jun 3, 2010

I am trying to access and modify data of a table of another schema which contains 80,000-90,000 records. My procedure is taking near about 30 mins to complete the operation. faster access and updation of table data.

Details:
I have two schema: TEST and PROD
I am running the below code from TEST Schema.
/* CODE START HERE*/
DECLARE
exc_bulk_errors EXCEPTION;
PRAGMA EXCEPTION_INIT (exc_bulk_errors, -24381);
v_block_count NUMBER := 1000;

[Code]....

The above code is taking near about 30mins to process.

I have also tried another approch: Creating a procedure in PROD schema to update COMPONENT_MASTER table and by calling the procedure from above code by passing component code.

/* PROCEDURE CALL FROM ABOCE CODE INTEST SCHEMA*/
PROD.PROCEDURE_TO_UPDATE(v_comp_code);

But still it is taking same time.

View 12 Replies View Related

SQL & PL/SQL :: Queries Returning Same Result With Different Access Path To Table?

Feb 7, 2012

Following are 2 queries, which return same results as far as the input parameter is NOT NULL

select /*+ gather_plan_statistics */ * from PX_CJQ where decode(:x,null,1,object_id) = NVL(:x,object_id);
select /*+ gather_plan_statistics */ * from PX_CJQ where object_id = NVL(:x,object_id);

However the execution plan differs a lot The FTS cost or rows accessed count also varies what could be the reason? The PX_CJQ is simply select * from dba_objects and has index on object_id which anyway is not being used in this case

variable x number
exec :x:= 28
Query - 1
***************
***************
SQL> >select /*+ gather_plan_statistics */ * from PX_CJQ where decode(:x,null,1,object_id) = NVL(:x,object_id);
SQL> >select * from table(dbms_xplan.display_cursor(NULL,NULL,'ALLSTATS LAST'));

[code]...

Query - 2
***************
***************
SQL> >select /*+ gather_plan_statistics */ * from PX_CJQ where object_id = NVL(:x,object_id);
SQL> >select * from table(dbms_xplan.display_cursor(NULL,NULL,'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT

[code]...

29 rows selected.
SQL> >

View 8 Replies View Related

SQL & PL/SQL :: Open Oracle Table In MS Access Frontend - Gives Error?

Oct 26, 2011

I have a Database that has a frontend in MS Access and a backend in Oracle. When I try to open the Oracle Table/s in the MS Access Frontend, it gives me this error message:

Reserved error (-7711); there is no message for this error.

There is some stuff on the internet about this error but none of it fixes my issue. It seems to be an MS Access error rather than an Oracle error. Only thing is this issue is only on Machines that are running on XP. I have no problem on mine which is Windows 7.

View 7 Replies View Related

Security :: How To Restrict Access To Table (Database Vault)

Nov 12, 2012

I configured a simple security configuration for the HR sample database schema. URL....

Now system user can't select data from table hr.employee, but HR user still can. How to restrict access to table for table owner?

View 1 Replies View Related

PL/SQL :: Table Access By Index Rowid / Taking More Time

Sep 13, 2012

I've a query like

update tab1
  set col1 = ( select col2 from
                       tab2 
                where tab1.id = tab2.id) table 1 has arnd 10,000 rows

table 2 has arnd 1,700,000 rows and has a primary key on column id.This query is taking around 20 secs to execute. I checked the x-plan and most of time taken for table access by index rowid.I checked the stats for the tab2, its just three days old.

View 17 Replies View Related

ORA-00600 - Application Unable To Access Specific Table

Dec 3, 2010

Our oracle database has been running great. This past wendesday the application was unable to access a specific table. Sutting down and restarting the database seemed to have fixed the issue. Last night the database has been working since that restart on wednesday morning. Suddenly stopped working. We stopped and restarted the database again this time getting ORA-00600 error as shown below. We are able to make a connection to the database and can even access the tables in question. The application is complaining about the connection to the database. I understand that ORA-00600 is an internal error what I need to know is what the internal error code 549 actually is. I have tried googling to no avail.

SQL*Plus: Release 9.2.0.1.0 - Production on Fri Dec 3 09:47:20 2010
Copyright © 1982, 2002, Oracle Corporation. All rights reserved.

Connected.
ORA-00600: internal error code, arguments: [549], [], [], [], [], [], [], []
ORA-03113: end-of-file on communication channel
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production

C:\Documents and Settings\tieoduser>sqlplus /nolog
SQL*Plus: Release 9.2.0.1.0 - Production on Fri Dec 3 09:49:31 2010
Copyright © 1982, 2002, Oracle Corporation. All rights reserved.

SQL> connect sys/as sysdba
Enter password:
Connected to an idle instance.
SQL> startup
ORACLE instance started.
[code]....

View 2 Replies View Related

Generating Script For Roles For Schema Import?

May 19, 2011

I want to import schema1 from sourcedb to targetdb. For this I need to generate create sripts and grant scripts for roles related to schema. For example schema1 has role1; rol1 has rol2; rol2 has rol3 and some system privileges and object privileges. How can I generate this script from sourcedb?

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved