Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Dusan.

Asked: November 21, 2000 - 12:01 pm UTC

Last updated: July 07, 2017 - 2:26 am UTC

Version: 8.1.6

Viewed 100K+ times! This question is

You Asked

Hi Tom,

We are using 2 schemas, user1, user2.
User1 has user1_table, and created role role1. The following was issued:
grant select, insert, update, delete on user1_table to role1;
grant role1 to user2
User2 created private synonym user1_table for user1.user1_table
and tried to create :

create or replace procedure aa as
aa user1_table.column%TYPE;
begin
null;
end;
/
Warning: Procedure created with compilation errors.

SQL> show err
Errors for PROCEDURE AA:

LINE/COL ERROR
-------- -----------------------------------------------------------------
2/4 PLS-00201: identifier 'USER1.USER1_TABLE' must be declared
2/4 PL/SQL: Item ignored

Why user2 cannot access user1_table in his procedure, if he has rights on the this table via role1?
We do not want to grant user1_table to user2 directly.

Thanks,

Dusan



and Tom said...

See
</code> http://asktom.oracle.com/Misc/RolesAndProcedures.html <code>

You have no choice but to grant select on user1_table to user2 directly. It is the only way to make it work.

Why is it that roles are not used during the compilation of a procedure? It has to do with the dependency model. Oracle is not storing exactly WHY you are allowed to access T ¡V only that you are (directly able to -- not via a role). Any change to your privileges that might cause access to T to go away will cause the procedure to become invalid and necessitate its recompilation. Without roles ¡V that means only ¡§REVOKE SELECT ANY TABLE¡¨ or ¡§REVOKE SELECT ON T¡¨ from the definers account or from PUBLIC. With roles ¡V it greatly expands the number of times we would invalidate this procedure. If some role that was granted to some role that was granted to this user was modified, this procedure might go invalid, even if we did not rely on that privilege from that role. ROLES are designed to be very fluid when compared to GRANTS given to users as far as privilege sets go. For a minute, let¡¦s say that roles did give us privileges in stored objects. Now, most any time anything was revoked from ANY ROLE we had, or any role any role we have has (and so on -- roles can and are granted to roles) -- many of our objects would become invalid. Think about that -- REVOKE some privilege from a ROLE and suddenly your entire database must be recompiled!

Consider the impact of revoking some system privilege from a ROLE, it would be like doing that to PUBLIC now -- don't do it, just think about it (if you do revoke some powerful system privilege from PUBLIC, do it on a test database). Revoking SELECT ANY TABLE from PUBLIC for example would cause virtually every procedure in the database to go invalid. If procedures relied on roles ¡V virtually every procedure in the database would constantly become invalid due to small changes in permissions. Since one of the major benefits of procedures is the ¡§compile once, run many¡¨ model ¡V this would be disastrous for performance.

Also consider that roles may be

ć Non-default: If I have a non-default role and I enable it and I compile a procedure that relies on those privileges, when I log out I no longer have that role -- should my procedure become invalid -- why? Why not? I could easily argue both sides.

ƒá Password Protected: if someone changes the password on a ROLE, should everything that might need that role be recompiled? I might be granted that role but not knowing the new password ¡V I can no longer enable it. Should the privileges still be available? Why or Why not? Again, arguing either side of this is easy. There are cases for and against each.

The bottom line with respect to roles in procedures with definers rights are:

ć You have thousands or tens of thousands of end users. They don't create stored objects (they should not). We need roles to manage these people. Roles are designed for these people (end users).

ć You have far fewer application schema's (things that hold stored objects). For these we want to be explicit as to exactly what privileges we need and why. In security terms this is called the concept of 'least privileges' -- you want to specifically say what privilege you need and why you need it. If you inherit lots of privileges from roles you cannot do that effectively. We can manage to be explicit since the number of development schemas is SMALL (but the number of end users is large)...

ć Having the direct relationship between the definer and the procedure makes for a much more efficient database. We recompile objects only when we need to, not when we might need to. It is a large efficiency enhancement.


Rating

  (99 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

A reader, March 28, 2003 - 11:06 am UTC


But, how can you tell?

Robert, April 02, 2003 - 12:22 pm UTC

Tom,

Good info on *why* explicit privs must be granted.

... But is there a way to tell if a privilege has been granted to a user explicitly vs. via a role?

From what I can tell it looks like dba_sys_privs, dba_tab_privs, etc. show the same info whether or not a privilige has been granted explicitly or via a role.

This is an important issue, because of the way Oracle handles privileges with procedures, views, etc.

How can we tell if a user's privilege has been granted explicitly or via a role?

Thanks,

Robert.

Tom Kyte
April 02, 2003 - 12:56 pm UTC

select * from dba_sys_privs where grantee in ( 'PUBLIC', <SOMEONE> );
select * from dba_tab_privs where grantee in ( 'PUBLIC', <SOMEONE> );

shows you what you have directly granted to you (or to public, which counts as well)

Thanks....and....Sorry about that!

Robert, April 02, 2003 - 2:04 pm UTC

Tom,

Sorry for wasting your time.
I was seeing unusual behaviour from privileges after having done a database refresh (prod to test) from a hot backup, and I *assumed* that role privileges were showing up in dba_sys_privs.
A quick test/demo ala AskTom proved otherwise, as you said.
Should have done this before asking you.
Thanks for all your help.

Robert.

application roles

Reader, July 18, 2003 - 8:55 am UTC

Could you please help me understand how one can enable roles through package. I was reading the doc and the syntax for doing this is,

create role role_name
identified using schema.package;

In the package do i have to assign privileges to the role? I would appreciate if you have an example. Thanks.

Tom Kyte
July 19, 2003 - 10:40 am UTC

That just makes a role that can only be enabled via the stored procedure -- meaning the end user (client application) has to run your piece of code -- which can validate if it wants to enable the role or not -- at runtime.

You grant privs to the role as normal -- via grant.

that means ...

Reader, July 19, 2003 - 10:55 am UTC

So, does it mean that I can use SET ROLE statement in the procedure to enable and disable roles? Thanks.

Tom Kyte
July 19, 2003 - 11:41 am UTC

you would use dbms_session.set_role

Grant Issues

Yogesh, August 28, 2003 - 5:12 pm UTC

I have 2 schemas X and Y, I have a procedure p1 in schema X
which creates some tables dynamically based on logic. User Y have execute grants on procedure p1.

Do Y also need to have grants on all objects which procedure p1 is referening to ? I feel no ... as procedure is going be executed in X schema ... but called from Y.

But when I tried executing it from Y, it starts giving insufficient privileges .. or table not found errors. When I issed grants to user Y,then it started working fine ... I could not understand why ...



Tom Kyte
August 29, 2003 - 8:42 am UTC

Y only needs execute on P1.

P1 will execute with the base privs of X (no roles).


You would actually need to provide "an example" showing your issue.

How about the public role ??

Bart Verstegen, March 04, 2004 - 4:20 am UTC

Hello Tom,

I'm having fun reading many topics.

About this topic, I'm lost a little.

In schema A I create a table 't':
create table t (id varchar2(2));
Table created.

grant the rights of table 't' to the ROLE (??) PUBLIC:
grant select on t to PUBLIC;
Grant succeeded.

In schema B I create a procedure:

create or replace procedure p
is
l_id a.t.id%type;
begin
null;
end;

Procedure created.
(Heuh ?)

Is there something special about the PUBLIC role ? Because with self-defined roles all works as expected (like the primary question of this topic). I came to this by accident because I was revoking rights from our own role and could still compile procedures (because of grants to PUBLIC, I was thrown back in my chair quite heavily).


Btw:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production

Best regards.


Tom Kyte
March 04, 2004 - 11:45 am UTC

PUBLIC acts like a role but is really a schema -- and a schema that everyone "inherits"


A grant to public is just like granting that grant to each individual user.

Be careful with PUBLIC grants -- everyone has them, regardless.

Repost on my question

Bart Verstegen, March 04, 2004 - 5:19 am UTC

This says it all isn't it ? (...which count as well phrase)

(sorry for bothering then !)

<QUOTE>
Followup:
select * from dba_sys_privs where grantee in ( 'PUBLIC', <SOMEONE> );
select * from dba_tab_privs where grantee in ( 'PUBLIC', <SOMEONE> );

shows you what you have directly granted to you (or to public, which counts as
well)
</QUOTE>


OK

Sue, April 02, 2004 - 1:43 am UTC

Dear Tom,
1)How to find out 'what are the privileges that make up a role'?
2)Is it possible to disable a privilege(either system or
object privilege) for a particular user with out revoking
the privilege from him or her?
Please do reply.
Bye!


Tom Kyte
April 02, 2004 - 10:04 am UTC

1) query dba_sys_privs and dba_tab_privs where grantee = 'THAT ROLE'

2) no, the way to disable that priv is to revoke it.

how about compiling?

Susan, September 22, 2004 - 4:38 pm UTC

We have packages that are owned by the application schema. Our users login as themselves (for auditing) and call the packages as themselves. The users have rights to the package through a role. If the package becomes invalid, and is called by the user, shouldn't it recompile automagically? Or, does the user need a direct execute privilege? Thanks

Tom Kyte
September 22, 2004 - 6:02 pm UTC

it should compile automagically.


ops$tkyte@ORA9IR2> create role app_role;
 
Role created.
 
ops$tkyte@ORA9IR2> create user a identified by a;
 
User created.
 
ops$tkyte@ORA9IR2> create user b identified by b;
 
User created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> grant create session, app_role to a;
 
Grant succeeded.
 
ops$tkyte@ORA9IR2> grant create session, create procedure, select any table,
  2        execute any procedure, app_role to b;
 
Grant succeeded.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> @connect b/b
ops$tkyte@ORA9IR2> set termout off
b@ORA9IR2> set termout on
b@ORA9IR2>
b@ORA9IR2> create or replace package demo_pkg
  2  as
  3          procedure p;
  4
  5          x dual%rowtype;
  6  end;
  7  /
 
Package created.
 
b@ORA9IR2>
b@ORA9IR2> create or replace package body demo_pkg
  2  as
  3
  4  procedure p
  5  is
  6  begin
  7          for x in (select * from dual)
  8          loop
  9                  null;
 10          end loop;
 11  end;
 12
 13  end;
 14  /
 
Package body created.
 
b@ORA9IR2>
b@ORA9IR2> grant execute on demo_pkg to app_role;
 
Grant succeeded.
 
b@ORA9IR2>
b@ORA9IR2> @connect a/a
b@ORA9IR2> set termout off
a@ORA9IR2> set termout on
a@ORA9IR2>
a@ORA9IR2> exec b.demo_pkg.p
 
PL/SQL procedure successfully completed.
 
a@ORA9IR2>
a@ORA9IR2> @connect /
a@ORA9IR2> set termout off
ops$tkyte@ORA9IR2> set termout on
ops$tkyte@ORA9IR2> revoke select any table from b;
 
Revoke succeeded.
 
ops$tkyte@ORA9IR2> revoke execute any procedure from b;
 
Revoke succeeded.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select object_name, object_type, status
  2    from dba_objects
  3   where owner = 'B';
 
OBJECT_NAME                    OBJECT_TYPE        STATUS
------------------------------ ------------------ ----------
DEMO_PKG                       PACKAGE            INVALID
 
DEMO_PKG                       PACKAGE BODY       VALID
 
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> @connect a/a
ops$tkyte@ORA9IR2> set termout off
a@ORA9IR2> set termout on
a@ORA9IR2> exec b.demo_pkg.p
 
PL/SQL procedure successfully completed.
 
 

Thanks

Susan, September 23, 2004 - 1:00 pm UTC

We're getting ORA-20003 error messages when the application (ASP) tries to call a package that has become invalid, but the same user can call from the backend. I assume this is a problem w/respect to how the ASP page is calling the procedure? I've seen some threads on this w/PowerBuilder and ASP, but no solutions. As an interim solution, for this particular package, I was thinking of implementing a schema trigger that comipiled the trigger after an alter table. Overkill?

Tom Kyte
September 24, 2004 - 9:15 am UTC

ora-20003 is a USER DEFINED (eg: their programers are raising that error)...

Ahh, perhaps they are calling dbms_describe.describe_procedure -- which returns ora-20003 when the procedure is invalid -- meaning the invoker should have caught that exception and dealt with it by ALTER procedure P compile; and then retrying.  Here is what is probably happening:


ops$tkyte@ORA9IR2> create table t ( x int );
 
Table created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create or replace procedure p( x int )
  2  as
  3  begin
  4          for x in ( select * from t ) loop null; end loop;
  5  end;
  6  /
 
Procedure created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> declare
  2          n  dbms_describe.number_table;
  3          v  dbms_describe.varchar2_table;
  4  begin
  5          dbms_describe.describe_procedure( 'P', null, null, n, n, n, v, n, n,n,n,n,n,n,n );
  6  end;
  7  /
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2> alter table t add y number;
 
Table altered.
 
ops$tkyte@ORA9IR2> declare
  2          n  dbms_describe.number_table;
  3          v  dbms_describe.varchar2_table;
  4  begin
  5          dbms_describe.describe_procedure( 'P', null, null, n, n, n, v, n, n,n,n,n,n,n,n );
  6  end;
  7  /
declare
*
ERROR at line 1:
ORA-20003: ORU-10036: object P is invalid and cannot be described
ORA-06512: at "SYS.DBMS_DESCRIBE", line 115
ORA-06512: at line 5
 
<b>that is what you are getting, in my own code that used dbms_describe, I simply parsed a block like this dbms_sql block is:</b>

 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> declare
  2      l_theCursor     integer default dbms_sql.open_cursor;
  3  begin
  4      dbms_sql.parse(  l_theCursor,
  5                      'begin p; end;',
  6                       dbms_sql.native );
  7      dbms_sql.close_cursor( l_theCursor );
  8  exception
  9          when others then
 10                  if ( dbms_sql.is_open(l_theCursor) )
 11                  then
 12                  dbms_sql.close_cursor( l_theCursor );
 13                  end if;
 14                  dbms_output.put_line( 'we actually failed but it is OK, we parsed, therefore compiled! ' || sqlerrm );
 15  end;
 16  /
we actually failed but it is OK, we parsed, therefore compiled! ORA-06550: line
1, column 7:
PLS-00306: wrong number or types of arguments in call to
'P'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
 
PL/SQL procedure successfully completed.

<b>I would just parse "begin p; end;", ignoring any errors.  if P could be compiled it would be (avoids the need to have the ALTER priv on this procedure, just need EXECUTE - the parse will compile)</b>

 
ops$tkyte@ORA9IR2> declare
  2          n  dbms_describe.number_table;
  3          v  dbms_describe.varchar2_table;
  4  begin
  5          dbms_describe.describe_procedure( 'P', null, null, n, n, n, v, n, n,n,n,n,n,n,n );
  6  end;
  7  /
 
PL/SQL procedure successfully completed.



<b>perhaps you can ask the developers using dbms_describe to use that logic?</b>
 

Thanks Tom!

Susan, September 24, 2004 - 10:38 am UTC

As usual, very useful. I appreciate your help.

secure schema

A reader, October 15, 2004 - 11:08 am UTC

hello tom
following is what I think the way one should create schema
for applications.
1. have one schema main1 which will have all the tables that
store data.
2. another schema code1 that has all packaged
procedures/functions that select,
delete, insert etc on the tables in main1 schema.
Naturally you have to grant the privileges to select,
insert etc. on the tables that you want to use in your
packages in code1. The reason you dont want to combine
main1 and code1 is because this allows you to create
different sets of packages in multiple schemas (
(e.g. code1 for application1, code2 for application2)
with appropriate minimum sets of privileges in each
schema.

3. For each user have a schema (user1, user2 etc.)
4. create sets of roles role1, role2 etc. Each role would
simply grant the privilege to execute the correct
package to user1, user2 etc.

Any comments?



Tom Kyte
October 15, 2004 - 12:04 pm UTC

2) would be "create package procedures/function that perform the necessary transactions -- an API"

if you ask me. you don't create a procedure "to insert", you create a procedure to "hire an employee", "fire an employee", "transfer an employee"

thanx!

A reader, October 15, 2004 - 12:12 pm UTC

yeah, that is what I meant actually - an API that uses
privileges to insert, delete etc. to get the job
done.


SELECT ANY TABLE

A reader, October 19, 2004 - 3:14 pm UTC

SQL> show parameter o7

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
O7_DICTIONARY_ACCESSIBILITY          boolean     FALSE
SQL> grant connect,resource to a identified by a;               

Grant succeeded.

SQL> grant connect,resource,select any table to b identified by b;

Grant succeeded.

SQL> connect a/a
Connected.
SQL> create table t(i int);

Table created.

SQL> connect b/b
Connected.
SQL> create table t as select * from a.t;

Table created.

SQL> drop table t;

Table dropped.

SQL> create or replace procedure p as begin execute immediate 'create table t as select * from a.t';end;
  2  /

Procedure created.

SQL> exec p
BEGIN p; END;

*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "B.P", line 1
ORA-06512: at line 1

Why does SELECT ANY TABLE not work for B when running the procedure p above?

Thanks 

Tom Kyte
October 19, 2004 - 4:19 pm UTC

</code> http://asktom.oracle.com/Misc/RolesAndProcedures.html <code>

you are trying to CREATE a table, not select a table -- roles, all about roles. see that link.

USER_* views

A reader, November 01, 2004 - 12:15 pm UTC

In the default 'definer-rights' mode of running stored procs, if I refer to some USER_* view in a stored procedure, it will point to the view for the definer/owner of the stored proc, NO MATTER who executes it, right?

Thanks

Tom Kyte
November 02, 2004 - 6:08 am UTC

since those views use userenv('schemaid') in their predicates to filter the data -- the USER_ views will return information for the OWNER of the procedure, correct.

As always very good stuff.

Orlando Reyes, November 23, 2004 - 1:47 pm UTC

Tom,

I have a question that might be related to this whole issue.

I have a procedure: That calls another procedure inside a package. Se below.

CREATE OR REPLACE PROCEDURE SP_PROCESS_IMS_ORDERS IS
v_success VARCHAR2(1);
v_message VARCHAR2(512);

BEGIN
ORDER_PROCESS.PROCESS_IMS_ORDERS(v_success, v_message);
COMMIT;
END;

Problem is:

If I go into TOAD, logging in as the owner of the procedure and run the procedure, it executes just fine. However, if I create a job using dbms_job from TOAD still under the procedure owner and try to execute the same procedure, it starts running, it does the first step (move data from table A to table B) and then it just stops processing and keeps running for ever until I kill the session (job/unix process), or in some cases it process extremely slow, one record per minute instead of one record per second as when running manually.

The same thing happens if I schedule a cron job to run it from Unix, it starts but does not process any data and takes 98% of the CPU doing nothing.

Question is am I missing something related to privileges and/or synonyms?

For this process there are two users involved, TIBCO, the owner and DB_OWNER who owns some of the tables being updated.

Both the procedure and package have public synonyms and TIBCO granted execution rights to both PUBLIC and DB_OWNER.

So where can I look for the problem, how can I check if the procedure is actually doing something or just spinning its wheels?

Would this problems have anything to do with permissions?

If it is the same code, how come behaves so different when called from different places?

Any ideas would be appreciated.

Thanks and have a great Thanks giving weekend.

Orlando


Tom Kyte
November 23, 2004 - 2:48 pm UTC

this would have nothing to do with grants, privileges or anything (unless you have a bug in your code and use dynamic sql and that is flinging you into a self induced infinite loop or something)


can you show us

a) a tkprof of this running from SQLPLUS (real programmers don't do toad :)
b) a tkprof of it running as a job.

Vary Nice

Orlando Reyes, November 23, 2004 - 4:57 pm UTC

Okay,

You got me there, I am not doing the doing the coding in this case though, I am just the DBA, and as you said I will need to run the tkprof to see more details, I guess I was trying to see if something jumped at you right away that could point me on the right direction.

I was not being lazy; I just don't have much time.

Thanks again and even though TOAD is your competition it comes very handy sometimes se we combine OEM and TOAD :-)

Have a great day.

Orlando


Tom Kyte
November 23, 2004 - 7:29 pm UTC

toad, jdev, whatever -- sqlplus rocks, it rolls...

I've a feeling the thing you submit in toad is 100% entirely different from what you schedule as a job.

Grant execute on individual procedure on package

Prasad, January 05, 2005 - 11:03 pm UTC

Is it possible to grant execute rights (to a different user) on individual procedure in a package, without granting execute rights to the entire package or creating a standalone procedure?

I tried but returns error..

Oracle Version 9iR2

example:
U:\>sqlplus system/xxx@orcl

SYSTEM@ORCL>create user u1 identified by u1;

User created.

SYSTEM@ORCL>create user u2 identified by u2;

User created.

SYSTEM@ORCL>grant connect to u1;

Grant succeeded.

SYSTEM@ORCL>grant connect to u2;

Grant succeeded.

SYSTEM@ORCL>grant resource to u1;

Grant succeeded.

SYSTEM@ORCL>grant resource to u2;

Grant succeeded.

SYSTEM@ORCL>exit

U:\>sqlplus u1/u1@orcl

U1@ORCL>create or replace package p1 as
2 procedure proc1;
3 end p1;
4 /

Package created.

U1@ORCL>create or replace package body p1 as
2 procedure proc1 as
3 begin
4 dbms_output.put_line('Hello');
5 end proc1;
6 end p1;
7 /

Package body created.

U1@ORCL>create or replace procedure proc2 as
2 begin
3 dbms_output.put_line('Hello world');
4 end;
5 /

Procedure created.

U1@ORCL>exit

U:\>sqlplus system/xxx@orcl


SYSTEM@ORCL>grant execute on u1.p1.proc1 to u2;
grant execute on u1.p1.proc1 to u2
*
ERROR at line 1:
ORA-00905: missing keyword


SYSTEM@ORCL>grant execute on u1.p1 to u2;

Grant succeeded.

SYSTEM@ORCL>grant execute on u1.proc2 to u2;

Grant succeeded.


Tom Kyte
January 06, 2005 - 10:29 am UTC

the schema object is the package.

all public procedures/fuctions (things in the spec) are accessible (or not)

if you have a specific break down like that, p1, p2, p3 should be grantable, p4, p5, p6 should be grantable separately -- you want two packages (eg: a package for all users, another package just for admin users)

Link not working

raghavendra, February 16, 2005 - 4:00 pm UTC

</code> http://asktom.oracle.com/~tkyte/Misc/RolesAndProcedures.html <code>mentioned at the beginning of this chain is not working. Looks like the article is moved or they have stopped giving the access to public.

Tom Kyte
February 16, 2005 - 5:22 pm UTC

change osi to asktom in the url please

Confusion with use of granting roles

Adolph Dsouza, February 26, 2005 - 12:37 am UTC

I have 2 users, 'ABC' and 'OAP'.

create user abc identified by abc;

CREATE TABLE SHTEXPMAST(
ID NUMBER(10),
DESCRIPTION VARCHAR2(50) NOT NULL,
ACTHEAD NUMBER(10),
CONSTRAINT PK_SHTEXPMAST_EXPID PRIMARY KEY ( ID ));

insert into SHTEXPMAST values (1,'test',1);
insert into SHTEXPMAST values (2,'test1',1);
insert into SHTEXPMAST values (3,'test2',1);
insert into SHTEXPMAST values (4,'test3',1);


create user oap identified by oap;
grant create session to oap;
alter user oap default role none;

conn abc/abc@

create role oaprole identified using abc.check_oap;

create or replace procedure check_oap
authid current_user
as
x varchar2(10);
begin
select user into x from dual;
if x = 'OAP' then
dbms_session.set_role('OAPROLE');
end if;
end;
/

grant execute on check_oap to public;

Create public synonym SHTexpmast for SHTexpmast ;

grant select on shtexpmast to oaprOLE;


conn oap/oap

exec abc.check_oap;

select count(*) from shtexpmast;

when I connect through sql and execute the procedure 'check_oap', the select statement executes fine. I have a entry form in Forms 6i for the 'shtexpmast' table, in the pre-form trigger i call the procedure. The select statement retrives the count correctly.

But when i go to my base table block and execute query. nothing is retreived. The base table for the block is 'shtexpmast'.
If i execute the same form connected as ABC. it works perfectly fine.

I know that you that you normally do not answer Forms related questions, but i was hoping you would be able to throw some light as to what i might be missing.

thanking you in advance

Adolph

Tom Kyte
February 26, 2005 - 7:55 am UTC

'nothing is returned' or "an error is returned"

for if nothing is returned -- something funky is happening

if an error is returned, something else is happening.

it is not clear who owns the above mentioned table.

also, what is the use of 'abc/abc@' -- are remote databases involved, are you sure you are always in the right database when testing?

solved the above

Adolph Dsouza, February 26, 2005 - 1:20 am UTC

Have managed to figure out what went wrong. sorry to take up your time

Regards
Adolph

some clarification needed

Adolph, February 28, 2005 - 12:25 am UTC

Sorry for not being clear. No remote databases are involved.

There was an error being returned 'ORA-00942: table or view does not exist'. The 'On-Error' on the form was supressing it.

The problem was occuring because the "check_oap" procedure was being called in the initial password verification form and not the entry form. Calling the procedure in the entry form solved the problem.

Tom, Does this procedure need to be called by every form accessing the table or Is there a way I can call it only once at the start when executing the module.

Regards

Adolph



Tom Kyte
February 28, 2005 - 7:34 am UTC

check_oap would have to be called at least once in every session you create that needs the role in order to enable the role.

Grants on Packages

Michael, June 06, 2005 - 12:02 pm UTC

I granted execute on all packages in one schema to a read only user. However, this user can not see the package bodies. It can see the packages, the functions, procedures, views, etc. What else is needed for the ro user to be able to see the package body (code) (8i)?? For the ro user, all_source has no package bodies. Thanks

Tom Kyte
June 06, 2005 - 12:41 pm UTC

well, why would a RO user need to read the package body...

but anyway, it does this by DESIGN, the ALL_* view hide bodies on purpose.

You could (as someone with select on dba_source and the grant option)

create view bobs_code
as
select * from dba_source where owner = 'BOB';

grant select on bobs_code to ro_user;

grants on Packages

Michael, June 07, 2005 - 12:29 pm UTC

Thanks for the help. The reason for the RO user to have access to the source is as follows: the DB is very sensitive in that the data is time critical. They want new Developers to be able to see and study the source code without changing it so that they can learn the DB, tables, source code, etc., and all the interactions before they start changing it. This, to me, is very normal. Why Oracle wants to make package bodies special I can not understand.

Tom Kyte
June 07, 2005 - 1:15 pm UTC

because the ability to EXECUTE source code should not always convey the right to READ the source code.

you can grant these developers access to DBA_SOURCE.
you can create a special view of DBA_SOURCE and grant it to these developers.




Package accessing different objects based on Grant

Ishan Bansal, June 14, 2005 - 7:45 am UTC

Hi Tom.
I am a regular reader of your column, but this is the first time I am posting a question here.
I have a very strange but interesting requirement.
I have a table tab_A in schema A
I have a procedure proc_1 in schema B.
This procedure fetches data from tab_A.

Now I need to create another table with tab_B with the same structure as tab_A.
User will pass a particular value based on which proc_1 will do select on tab_A or tab_B. So, one option is to change the procedure proc_1 for this parameter, so that it does select on the right table based on this new parameter.
However, there are hundreds of procedures like proc_1 that would need to be changed and tested. Thats a big change. There is an intelligent workaround, if possible. We can create the new table tab_B with the same name tab_A but in a different schema C. So, we have two exactly similar tables with same names but in 2 different schemas. Now, what I need is some way so that procedure can chose which schema it needs to look up for data. I can call the procedure from 2 seperate schemas based on the user's parameter. However, I dont want to make any change in procedure itself.
Just to summarise again, I need a way so that a proc called from schema A will access objects of schema A, and the same proc called from schema B access objects of schema B. Please lemme know if this is possible.
Thanks,
Ishan.

Tom Kyte
June 14, 2005 - 10:04 am UTC

this doesn't sound like an interesting problem, it sounds "funky" to me. (like a bad idea)

what is the underlying reasoning/goal here? You are driving down a road that will lead you to lots of dynamic sql, an utterly bad idea.

So, what is the logic behind doing this?

grant privs to public and new users

Jean, June 15, 2005 - 4:54 pm UTC

Hi Tom
I'm kind of confuse here. I issue a GRANT ALL ON some_object TO PUBLIC today and tomorrow I'll create a new user X. Will this user get all privs granted by the GRANT ALL.. of yesterday?? This user is created without any explicit grants.
If so, is there a way to revoke privs from this user only?

Thanks!

Tom Kyte
June 16, 2005 - 3:36 am UTC

anything granted to PUBLIC is granted to every user that exists or will exist.

PUBLIC is like a "special role" that everyone has.


If you grant to public, EVERYONE has it, period.

Why can't we see the package body from all_source

Curtis Farthing, June 16, 2005 - 5:02 pm UTC

This is in regards to the last part of the Q&A found at asktom.oracle.com/pls/ask/f?p=4950:8:7525829015338918478::NO::F4950_P8_DISPLAYID:1065832643319 (just before the "funky" comment). Our developers use TOAD, and they wanted to see the source code of packages owned by our application schema. I accomplished this by granting them "Create Any Procedure" and "select" access to the sys.dba_objects table. Luckily TOAD uses DBA_Source.

Curtis

P.S. I enjoyed your presentation at the NEOOUG in Cleveland last month.

Tom Kyte
June 16, 2005 - 10:01 pm UTC

ouch!!!!!!

ouch ouch ouch.


"create any procedure", think about that - just think about that.


create or replace package sys.xxxxxxxxx


ouch ouch ouch.


if you want them to see the body, just grant select on dba_source.

all_source is designed to hide the bodies, all source shows you the interface, not the implementation of the package. by design.

Help!!

Kumar, June 27, 2005 - 10:10 pm UTC

Hi Tom,
I have some three procedures stored in a table and I want to
execute them simultaneously and concatenate their output and
send them back to the client.

SQL> create or replace procedure p1
  2  as
  3  begin
  4  dbms_output.put_line('Hello World');
  5  end;
  6  /

Procedure created.

SQL> create or replace procedure p2
  2  as
  3  begin
  4  dbms_output.put_line('Oracle Programming');
  5  end;
  6  /

Procedure created.

SQL> create or replace procedure p3
  2  as
  3  begin
  4  dbms_output.put_line('How now brown cow');
  5  end;
  6  /

Procedure created.

SQL> create table procs(proc_id number,proc_name varchar2(20))
  2  /

Table created.

SQL>insert into procs values(1,'P1')
  2  /

1 row created.

SQL> insert into procs values(2,'P2')

1 row created.

SQL> insert into procs values(3,'P3')

1 row created.

How to achieve this??
Please do reply.
Bye!! 

Tom Kyte
June 28, 2005 - 7:44 am UTC

you cannot, you would need three sessions to execute them simultaneously, so the client would have to create three sessions on three connections in order to do this.

the keyword "simultaneous" isn't truly achievable in this sense.

Roles & Privs

David Prabhakar, June 28, 2005 - 3:33 am UTC

Hi Tom,

We are using 9iR2 Release Database. It was working fine till Last Friday. On Monday I find that users are not able to create any table in thier schema. I checked the SGA sige, Buffer_cache Size and Thats all seems very fine. Database is running on a Sun FireV250 box, single Instance, with plenty of space in each Mount points.

Actual Problem:
**************************
1. when I connect in as a a user and look at
>select * from session_privs view.....
I find only Create session and alter session.
Although I granted the user with Connect, Resource roles which is sufficient to work with .

what happened to other privileges? But I can see that the user has been granted with Connect, Resource Roles in the
DBA_ROLE_PRIVS view.

2. I testing purpose, created one dummy user, assigned him Default, temporary tablespace with unlimited tablespace quotas.

Granted him the Connect, Resource roles to him.

Again, when i connect as this dummy user and look at his SESSION_PRIVS ..i find nothing except CREATE SESSION AND ALTER SESSION.


3. I Tried creating a Table, the error i got was
ORA-01031 -Insufficient Privilege (ofcourse..since i dont have create table privilege)

4. I connected as Sysdba and tried to drop the User..The Error that I got was
ORA-006004 -Error at Recursive SQL Level 1
ORA-01031 -Insufficient Privileges


WHATS WRONG ? WHAT COULD HAVE GONE WRONG?

PLS HELP.



Thanks and regards,
David.


Tom Kyte
June 28, 2005 - 7:51 am UTC

you'll have to answer that. Privileges do not just magically disappear. They go away in response to a direct command. Someone revoked these privileges.

who else has DBA in your office, you need to have a talk with them. It could be that they played with the connect and resource roles -- what are granted to these roles?

Roles and Privileges

David Prabhakar, June 29, 2005 - 1:53 am UTC

Hi Tom,

I read ur response. Since I am the only DBA around here, the possibility of Other users tampering the Connect, Resource Role is ruled out.
But please tell me, In a situation like this, where I grant Connect, Resource to a User and the user doest have even the Create Table privileges....What is the next step that can be taken?
The worst case here is, As a SYS DBA, I am neither able to drop that User now. Says ORA_01031:Insufficient Privilege and ORA-00604: Error at Recursive Sql Level 1.

I tried running the SQL.BSQ script(Which actually creates these roles) even after that, the user still doesnt have create table privileges.

Is this a BUG?

Thanks in Advance for ur valuable response. This is a Great Site and helps all of us in many ways technically.

David.

Tom Kyte
June 29, 2005 - 8:51 am UTC

sorry, but nothing is ruled out.

Ok, since you are running random scripts now as the only dba, I'm opting out. Please contact support and work with them in a controlled fashion. This is where it gets "dangerous".







Stored procedure permissions

John Baughman, August 11, 2005 - 12:21 pm UTC

Tom,

This could have also gone in the article about DBMS_METADATA vs. EXP ;).

I have a stored procedure that I created to "archive" the DDL of selected schemas and this procedure will need to run as a scheduled job on a regular basis (i.e. 1x daily, 2x week, or other schedule).

The problem I am having is when I run this code as a script (in SQL*Plus :), it runs fine and can generate the DDL. However, when I run it as a stored procedure, it only generates the DDL for the logged in schema.

What privileges would I need? I've granted SELECT ANY TABLE and EXECUTE ANY PROCEDURE as system object to the user thinking it might be one of those. Do I need direct object privileges granted to this user for all the other schema's objects?

I need this script to be able to catch all new and old objects created/modified/stable in the selected schemas.

(I'm still getting used to "proper" techniques for PL/SQL coming from VB/VB.NET so if you have any other comments about my code... :)

Here's the code:

CREATE OR REPLACE PROCEDURE Archive_Schema( fileDirectory IN VARCHAR2, schemaList IN VARCHAR2, delimiter IN VARCHAR2 DEFAULT ',' ) IS
mCLOB CLOB;
mOutputFile UTL_FILE.FILE_TYPE;
mOwner VARCHAR2(30);
mObjectType VARCHAR2(18);
mObjectName VARCHAR2(30);
mDDLCursorID INTEGER;
mReturnValue INTEGER;

mDBHostName VARCHAR(64);
mDBInstanceName VARCHAR(16);
mDBInstanceNumber NUMBER;
mDBVersion VARCHAR2(17);
mDBStartupTime DATE;

mTypeSchemaArchive typeSchemaArchive := typeSchemaArchive();

PROCEDURE GetDBInfo IS
BEGIN
DBMS_SQL.PARSE( mDDLCursorID, 'SELECT instance_number, instance_name, host_name, version, startup_time FROM v$instance', DBMS_SQL.NATIVE );

DBMS_SQL.DEFINE_COLUMN( mDDLCursorID, 1, mDBInstanceNumber );
DBMS_SQL.DEFINE_COLUMN( mDDLCursorID, 2, mDBInstanceName, 16 );
DBMS_SQL.DEFINE_COLUMN( mDDLCursorID, 3, mDBHostName, 64 );
DBMS_SQL.DEFINE_COLUMN( mDDLCursorID, 4, mDBVersion, 17 );
DBMS_SQL.DEFINE_COLUMN( mDDLCursorID, 5, mDBStartupTime );

mReturnValue := DBMS_SQL.EXECUTE_AND_FETCH( mDDLCursorID );

DBMS_SQL.COLUMN_VALUE( mDDLCursorID, 1, mDBInstanceNumber );
DBMS_SQL.COLUMN_VALUE( mDDLCursorID, 2, mDBInstanceName );
DBMS_SQL.COLUMN_VALUE( mDDLCursorID, 3, mDBHostName );
DBMS_SQL.COLUMN_VALUE( mDDLCursorID, 4, mDBVersion );
DBMS_SQL.COLUMN_VALUE( mDDLCursorID, 5, mDBStartupTime );
END;

PROCEDURE OpenOutputFile( fileDirectory IN VARCHAR2, schemaList IN VARCHAR2, delimiter IN VARCHAR2 DEFAULT ',' ) IS
filename VARCHAR2(255);

BEGIN
filename := mDBInstancename || '_' || REPLACE( schemaList, delimiter, '_' ) || '_' || TO_CHAR( SYSDATE, 'yyyymmddhh24miss' ) || '.sql';
mOutputFile := UTL_FILE.FOPEN( fileDirectory, filename, 'w', 32767 );
END;

PROCEDURE ParseSchemaList( schemaList IN VARCHAR2, delimiter IN VARCHAR2 DEFAULT ',' ) IS
-- declare and initialize PL/SQL table based on pre-created type
-- variables needed for parsing
offset NUMBER DEFAULT 1;
overallLength NUMBER DEFAULT LENGTH( schemaList ); -- The length of the input string.
stringLength NUMBER; -- The length of the current chunk.

BEGIN
-- load temp table type from delimited list

WHILE offset < overallLength LOOP
stringLength := INSTR( schemaList, delimiter, offset );
IF stringLength = 0 THEN
stringLength := overallLength + 1;
END IF;

mTypeSchemaArchive.EXTEND;
mTypeSchemaArchive( mTypeSchemaArchive.COUNT ) := SUBSTR( schemaList, offset, stringLength - offset );

offset := stringLength + 1;
END LOOP;
END;
--END ParseSchemaList;

PROCEDURE PrintOwnerHeader( p_Owner IN VARCHAR2 ) IS
BEGIN
UTL_FILE.NEW_LINE( mOutputFile, 2 );
UTL_FILE.PUT_LINE( mOutputFile, '/*==================================' );
UTL_FILE.PUTF( mOutputFile, 'SCHEMA: %s', p_Owner );
UTL_FILE.NEW_LINE( mOutputFile, 1 );
UTL_FILE.PUT_LINE( mOutputFile, '==================================*/' );
END;
-- END PrintOwnerHeader;

PROCEDURE PrintObjectHeader( p_owner IN VARCHAR2, p_objectType IN VARCHAR2 ) IS
BEGIN
UTL_FILE.NEW_LINE( mOutputFile, 2 );
UTL_FILE.PUT_LINE( mOutputFile, '/*==================================' );
UTL_FILE.PUTF( mOutputFile, '%s SCHEMA OBJECT: %s', p_owner, p_objectType );
UTL_FILE.NEW_LINE( mOutputFile, 1 );
UTL_FILE.PUT_LINE( mOutputFile, '==================================*/' );
END;
-- END PrintObjectHeader;

PROCEDURE PrintFileHeader IS
BEGIN
UTL_FILE.PUT_LINE( mOutputFile, '/*==================================' );
UTL_FILE.PUTF( mOutputFile, 'Select database schemas as of: %s', TO_CHAR( SYSDATE, 'dd-mon-yyyy hh24:mi:ss' ) );
UTL_FILE.NEW_LINE( mOutputFile, 1 );
UTL_FILE.PUTF( mOutputFile, 'HOST NAME: %s', mDBHostName );
UTL_FILE.NEW_LINE( mOutputFile, 1 );
UTL_FILE.PUTF( mOutputFile, 'INSTANCE NAME: %s', mDBInstanceName );
UTL_FILE.NEW_LINE( mOutputFile, 1 );
UTL_FILE.PUTF( mOutputFile, 'INSTANCE NUMBER: %s', TO_CHAR( mDBInstanceNumber ) );
UTL_FILE.NEW_LINE( mOutputFile, 1 );
UTL_FILE.PUTF( mOutputFile, 'DB VERSION: %s', mDBVersion );
UTL_FILE.NEW_LINE( mOutputFile, 1 );
UTL_FILE.PUTF( mOutputFile, 'DB STARTUP TIME: %s', TO_CHAR( mDBStartupTime, 'dd-mon-yyyy hh24:mi:ss' ) );
UTL_FILE.NEW_LINE( mOutputFile, 2 );

UTL_FILE.PUT_LINE( mOutputFile, 'Selected Schemas:' );

FOR schemas IN ( SELECT COLUMN_VALUE FROM TABLE(CAST(mTypeSchemaArchive AS typeSchemaArchive)) ) LOOP
UTL_FILE.PUTF( mOutputFile, ' %s', schemas.COLUMN_VALUE );
UTL_FILE.NEW_LINE( mOutputFile, 1 );
END LOOP;

UTL_FILE.NEW_LINE( mOutputFile, 1 );
UTL_FILE.PUT_LINE( mOutputFile, '==================================*/' );
UTL_FILE.NEW_LINE( mOutputFile, 2 );
END;
-- END PrintFileHeader;

BEGIN
mDDLCursorID := DBMS_SQL.OPEN_CURSOR;

GetDBInfo;

OpenOutputFile( fileDirectory, schemaList, delimiter );

ParseSchemaList( schemaList, delimiter );

PrintFileHeader;

DBMS_METADATA.SET_TRANSFORM_PARAM( DBMS_METADATA.SESSION_TRANSFORM, 'STORAGE', FALSE );

FOR schemas IN ( SELECT owner, object_type, object_name FROM ALL_OBJECTS WHERE owner IN ( SELECT COLUMN_VALUE FROM TABLE(CAST(mTypeSchemaArchive AS typeSchemaArchive)) ) ORDER BY owner, object_type, object_name ) LOOP
BEGIN
IF mOwner IS NULL THEN
mOwner := schemas.owner;
mObjectType := schemas.object_type;

PrintOwnerHeader( mOwner );
PrintObjectHeader( mOwner, mObjectType );
ELSE
IF mOwner <> schemas.owner THEN
mOwner := schemas.owner;
mObjectType := schemas.object_type;

PrintOwnerHeader( mOwner );
PrintObjectHeader( mOwner, mObjectType );

ELSIF mObjectType <> schemas.object_type THEN
mObjectType := schemas.object_type;

PrintObjectHeader( mOwner, mObjectType );

END IF;
END IF;

mCLOB := DBMS_METADATA.GET_DDL( mObjectType, schemas.object_name, mOwner );

UTL_FILE.PUT( mOutputFile, DBMS_LOB.SUBSTR( mCLOB, DBMS_LOB.GETLENGTH( mCLOB ), 1 ) );
UTL_FILE.FFLUSH( mOutputFile );
EXCEPTION
WHEN OTHERS THEN
UTL_FILE.PUTF( mOutputFile, 'Error on %s: %s.%s ', mObjectType, mOwner, schemas.object_name );
END;
END LOOP;

UTL_FILE.FCLOSE( mOutputFile );

DBMS_METADATA.SET_TRANSFORM_PARAM( DBMS_METADATA.SESSION_TRANSFORM, 'DEFAULT', TRUE );

DBMS_SQL.CLOSE_CURSOR( mDDLCursorID );
END;
/


Tom Kyte
August 12, 2005 - 8:07 am UTC

root cause:

</code> http://asktom.oracle.com/Misc/RolesAndProcedures.html <code>

If you have Expert one on one Oracle, read the chapter about invokers vs definers rights.

If your goal was to run this in each schema as if the person owning the schema was submitting an anonymous block with sql in it - then you want to use

authid current_user

on the procedure definition. That way, the code (the plsql) will compile much like a pro*c or VB application OUTSIDE of the database (the code and what is calls are compile once) but the SQL is parsed and executed using the credentials of whomever called the procedure.



Figured it out...

John Baughman, August 11, 2005 - 5:54 pm UTC

The user needs the role SELECT_CATALOG_ROLE granted then in the CREATE OR REPLACE PROCEDURE line, add AUTHID CURRENT_USER.

In my case it would look like this:

CREATE OR REPLACE PROCEDURE Archive_Schema( fileDirectory IN VARCHAR2, schemaList IN VARCHAR2, delimiter IN VARCHAR2 DEFAULT ',' ) AUTHID CURRENT_USER IS

Thanks and I hope I didn't waste any of your time. I just need to use a few debugging tools that I was unaware of. SQLERRM and SQL_CODE are your friends...

Select privilege on all objects in a schema

User, October 05, 2005 - 11:26 am UTC

Hi,

I am creating a role that will have select privilege on all objects in a particular schema. Is there a command (like SELECT ANY TABLE) that I can use to grant select privileges on objects in ONLY one schema, or do I have to explicitly grant select to each of the tables/view? I am on version 9.2.0.5.

Thanks.

Tom Kyte
October 05, 2005 - 11:48 am UTC

No, there is no "grant select on anything in this schema"

Getting strange problem with Oracle 9.2.0.6

Dilip, October 25, 2005 - 12:08 pm UTC

Hi

We have a strange problem in oracle 9i database.

We create a user - Create user test identified by test;

We grant him only create session - grant create session to test;

When we connect as Test and query - select * from session_privs;

We get the following
DROP ANY TABLE
UNLIMITED TABLESPACE
SELECT ANY TABLE
EXECUTE ANY PROCEDURE.

We tried with 3 test users and the same problem is getting repeated. Not sure how the user "TEST" is getting the above privs without granting. Can anyone help ASAP.


This is happening only in one instance, can't get from where it is taking ? Can u suggest some debugging.



system@CDCIRND> create user TEST identified by TEST default tablespace USERS temporary tablespace TEMP;

User created.

system@CDCIRND> grant create session to TEST;

Grant succeeded.

system@CDCIRND> @connect TEST/TEST@CDCIRND

test@CDCIRND>
test@CDCIRND> SELECT * FROM SESSION_PRIVS;


PRIVILEGE
----------------------------------------
CREATE SESSION
UNLIMITED TABLESPACE
DROP ANY TABLE
SELECT ANY TABLE
EXECUTE ANY PROCEDURE

test@CDCIRND> SELECT * FROM SESSION_ROLES;

no rows selected

test@CDCIRND>

Tom Kyte
October 26, 2005 - 8:46 am UTC

someone has granted those privileges to PUBLIC.


select * FROM DBA_SYS_PRIVS WHERE GRANTEE = 'PUBLIC';



Insufficient Privileges

Yet another Oracle developer, November 21, 2005 - 12:07 pm UTC

I'm trying to create a table on my own schema from a stored procedure and I get the ORA-01031: insufficient privileges error. I have CREATE ANY TABLE session privilege. I can create the table from sqlplus. How come I can't create the table from the procedure?

Tom Kyte
November 21, 2005 - 12:29 pm UTC

You have these privs from a role.

suggestion 1: get rid of the ANY priv, use the least priv you need which is simply "create table"


</code> http://asktom.oracle.com/Misc/RolesAndProcedures.html <code>

Compile of Procedure and Packages

A reader, December 10, 2005 - 3:36 pm UTC

Tom,

Suppose there is a schema named "abc"

"abc" creates a package "pack1" and procedure "pro1" with "create or replace ....."

Another schema user "xyz" have insert, update and delete priviliges on all tables of "abc".

If I give CREATE ANY PROCEDURE system privilege to create a procedure or package in "abc" to "xyz".

And "xyz" edit a package named "pack1" as above and compile it then who will be the owner of the "pack1"

"abc" or "xyz"

because package "pack1" is with "create or replace......"

Tom Kyte
December 11, 2005 - 3:01 am UTC

xyz would create the package in their own schema, unless xyz issued "create or replace package ABC.pack1 ...." or used current_schema (alter session) to set their current schema to abc.

A reader, December 22, 2005 - 6:49 pm UTC

Hi tom

IN grant connect ,resource to mark what does this "resource" do..would you please explain this in little detail....or tell me location where i can find that

Thanks
sachin

Tom Kyte
December 22, 2005 - 8:34 pm UTC

select * from dba_sys_privs where grantee = 'RESOURCE';

that'll show you want the role resource gets system wise - in addition - anyone with RESOURCE will have unlimited tablespace (no quota)

To clarify whether to use roles or not

A reader, January 16, 2006 - 1:13 pm UTC

Hi Tom,

I am about to start developing my first application using actual, real database accounts (can you tell I work with Java?).

If all user functionality is given via stored procs is there any point in defining roles since they don't work for packages and just give the necessary EXECUTE privileges directly to the user accounts?

Thanks

RP

Tom Kyte
January 16, 2006 - 1:49 pm UTC

you use direct grants for the OWNER of the package.

You grant execute on the package to the end users via roles.


End users => roles. Roles (are for) End Users.

A reader, January 19, 2006 - 6:35 am UTC

Thanks for that clarification Tom.

On a related note, I've been looking into using VPD in my app.
Say that app users can only see records in the table CUSTOMERS where the client id matches the username (ie records they created).

I have 2 questions:

1. If the user has EXECUTE privilege on UPDATE_CUSTOMER proc but not the underlying table will the SQL in the proc automatically change to take account of the user's privilgies? Or does this auto-predicate adding only apply to direct DML/Select?

2. Does Oracle provide have built in auditing where it can record that User SCOTT called proc CUSTOMER_FUNC.UPDATE_CUSTOMER with the params and date?

Thanks

R

Tom Kyte
January 19, 2006 - 1:19 pm UTC

1) it applies to ALL SQL regardless of what issues said SQL. That is the "magic" here. All SQL - sql in procedures, sql not in procedures...

2) no, it does not.

Stored procedure wrapping

A reader, January 20, 2006 - 7:21 am UTC

Hi,

my java app needs an administrative web front-end to manage database users.

Can you see any inherent problems just calling the corresponding SQL statements(CREATE USER.../DROP USER...) from Java???

I'll probably wrap the SQL with stored procs and give the user administrator EXECUTE privilege on them.

Thanks

RP

Tom Kyte
January 20, 2006 - 10:30 am UTC

It is what we do in our own management tools (invoke those sorts of DDL commands from a client application written in java)

Sporitic 1031

marc, May 09, 2006 - 11:53 am UTC

I have an application that we are upgrading from 8i to 10Gr2. For some reason the app server will get an ora-1030 error. We refresh page over and over we get the same error. We wait 10 minutes, refresh, it will go away. Over the weekend the server encountered the same error 4am, no users connected, and 5 minutes later, error is gone. Any ides? Is there a way to capture invalid sqls in the database?

The error happens at different places with different sqls. Most calls are wrapped in packages. I try the same SQL in sqlplus and no issue. So I would expect the issue would be in the app server, but my app server coders tell me the query I am running in sqlplus is the same query with the error. I ask them about the metadata calls, they say it is not the issue.

I am using roles for privileges, but I cannot see that being the issue since the error it sporadic.

This is 1 query we my team has received the error:
Select P_ UTILS.GET_DATE from dual

This is the function:
FUNCTION get_date
RETURN TIMESTAMP AS
BEGIN
RETURN SYSTIMESTAMP;
EXCEPTION
WHEN OTHERS THEN
P_Log.add_log('FX-1000', NULL, 'FAILED', 'get_date function');
RAISE;
END;

I created a test in sqlplus and have a continuous loop running the above statement for 1 day, no issues.

Is there a way to capture invalid sqls in the database?



Tom Kyte
May 09, 2006 - 12:22 pm UTC

1030
1031

very different.

But I assume you mean 1031.

If the query is failing in sqlplus, I feel very very certain SOMEONE IS MESSING with the privileges.


What a "useful" function :)

Sorry - but someone is messing with the privileges here.

yes 1031

marc, May 09, 2006 - 5:57 pm UTC

opps, yes the error is 1031

(The function was created because the oracle 8i db was built in EST timezone, and I did not want to store dates in EST and I could not change the server timezone( and there was issues with new_time). so I created a function to shift time zones and daylight savings time. Now in 10g, the db is set up correctly. )

I am the only person who changes the permissions and I am not touching these packages when the error occurs. This is why this bug is such a mystery to me. I need a way to trace the sqls to find out which sql is the issue.

I would start a trace(tkprof) but the only sqls' in a trace are valid sql’s. So a call to a procedure that has this error will never get into the trace file or any table I can think of, like v$sql_text.

I think the code is some rouge code that the java guys forgot about.

Next time I get this error, I will try:
1. starts a trace
2. grant dba,
3. hopefully the error goes away
4. stop trace
5. revoke dba
6. review the file for the offending code.

But it would be nice to now every call, good or bad, that is hitting the db.



Tom Kyte
May 10, 2006 - 7:31 am UTC

...
I am the only person who changes the permissions and I am not touching these
packages when the error occurs.
......

You might be surprised what goes on in your own system sometimes. Might be time to enable some heavy duty auditing to see what is *really* happening.



do NOT GRANT DBA TO ANYONE - I don't get where you were going with that.


If the java guys had, well, some error handling? You would know what is failing precisely. Are you saying they just sort of ignore errors?

why DBA Grant

marc, May 10, 2006 - 10:35 am UTC

1031 error is a call to some object that is there but the user has no access to, right? So if I grant DBA, the user will be able to successfully call the object.

The idea of the grant DBA was to allow the user to successfully call the object, do whatever to the object but I would have successfully logged the call in the tkprof logs. Afterwards check the logs for some call that I do not like and scolded the developer. J This was a way to find out what call is the offending call.

The user owns all objects this user is calling. They do not access any object from any other user. Some objects inside the package are owned by sys and the package compiles fine, so I do not see that as the problem. Also everyone of my package has an error handler which any error happens inside my procedures will be logged. There are no errors in my logs. I see the call to the users own packages as the issue.

This morning my developer started to get the error, and I granted dba but the grant to not help, still the same error. Or the permission was fix again during my debugging.

What auditing to recommend?


Tom Kyte
May 10, 2006 - 10:43 am UTC

maybe yes, maybe no.

Why the HECK would you give someone DBA - even if it "fixed" the problem. Tell you what - just have everyone log in as "sysdba", that'll fix ALL ACCESS issues.


Tell the developers to build some logging into their application so they can report to you "this is what we actually need access to"

Something else is going wrong here, your developers need to employ their "debugging 101 skills" (something that appears to be lacking more and more often with developers, sigh).

How to run grant script

Sara, May 11, 2006 - 3:21 pm UTC

I have two users, user1 and user2. User1 has create any table. It creates a table user2.table1. It cannot select from that table. User2 has to grant select back to user1. There are also other users that need to be able to select from the table.

The table is being created with dynamic sql in a package scheduled with dbms_jobs.

How can I run the grant script? I was planning on creating another procedure with the grant statements, but user2 doesn't know when the job finished, so it doesn't know when to run the grants.

Do you have any suggestions?

Tom Kyte
May 11, 2006 - 8:11 pm UTC

the ANY privileges should be avoided like the plague.

There is an "any" privilege that would let user1 grant it to themselves - and since user1 can pretty much TOTALLY DESTROY YOUR DATABASE (i would not let user1 in my databases), it is probably OK to grant them this :)


Seriously, I'd be looking at entirely rethinking the approach here. When I see an "any" privilege - I get concerned.

not the applications fault, but the database

marc, May 16, 2006 - 2:54 pm UTC

I think I have found the issue. I must wait and see if the issue appears again. When we released to QA we found a similar issue but a different oracle error and a message “Got Minus One From A Read Call.” Anther team I work with said they had similar error but not exactly. The other team needed to install a patch, 4518443. We installed the patch last Wednesday and have not seen any issue yet. After googling the error, I found some more developers with similar issues.

I must apologize to the Java developers and thinking it was their issue.



Tom Kyte
May 16, 2006 - 3:14 pm UTC

sorry, but if the java developers cannot tell you whence errors are happening, it is their issue.

Java Developers

marc, May 16, 2006 - 3:33 pm UTC

The java developer WAS telling me where the error was. I did not believe them since the call they were making was so simple.

The simplest call they claimed they were calling was:
FUNCTION get_date
RETURN TIMESTAMP AS
BEGIN
RETURN SYSTIMESTAMP;
EXCEPTION
WHEN OTHERS THEN
P_Log.add_log('FX-1000', NULL, 'FAILED', 'get_date function');
RAISE;
END;

The call was so simple I thought it could not be the oracle db, but some metadata or hidden query.

Tom Kyte
May 16, 2006 - 4:21 pm UTC

well 4518443 is not a patch, it is a bug#, but the bug is wholly unrelated to anything here. Not sure what you did, but I'm not aware of anything that would intermittently through a 1031.

Did nothing

marc, May 22, 2006 - 5:20 pm UTC

The current release of my app was in code freeze in QA. We did not change any code. The other changes was the bug fix.

Tom Kyte
May 23, 2006 - 6:53 am UTC

and like I said - that bug has no relevance, so either

a) you are mistaken
b) you have given the wrong number.

I don't see how a bug relating to the listener, which isn't involved here at all, would have any relevance to the discussion on a 1031.

Next step

marc, May 23, 2006 - 5:01 pm UTC

I jumped the gun a little. I got the error last night, again. But this time I had a network sniffer watching the app servers. We noticed there are responses of ora-1031 with no request. Then the listener crashed with ora-12519 error. i do not know if they both relate. But we are looking into it.

We open a second tar with oracle and will keep u informed.

thanks

Hopefully I did not jump the gun again.

marc, June 01, 2006 - 11:00 am UTC

Well another week with no issues. After the last 1031 issue, we found the dev server "PROCESSES" was set to 150 which crashed the listener. We bumped up the "PROCESSES" to 500 and will wait for the problem again.

As of today we never have seen this issue in QA.

Tom Kyte
June 01, 2006 - 11:22 am UTC

I cannot imagine any link between an ORA-1031 (insufficient privs) and PROCESSES.

me too...

marc, June 01, 2006 - 11:37 am UTC

me too...

Different Behavior

Mita, August 08, 2006 - 6:25 pm UTC

is there a reason why my procedure is giving error when I am able to run same thing as stand alone sql or pl/sql block ??


Connected to:
Oracle8i Enterprise Edition Release 8.1.7.4.0 - Production
With the Partitioning option
JServer Release 8.1.7.4.0 - Production

PACE_MASTERDBO@eqpaceod> set serveroutput on
PACE_MASTERDBO@eqpaceod> select min(src_intfc_inst)
2 from RULESDBO.SOURCE
3 where ltrim(rtrim(source_code)) = 'APMEOD';

MIN(SRC_INTFC_INST)
-------------------
104

PACE_MASTERDBO@eqpaceod> declare
nbr_mlm_inst number ;
BEGIN

select min(src_intfc_inst)
into nbr_mlm_inst
from RULESDBO.SOURCE
where ltrim(rtrim(source_code)) = 'APMEOD' ;

dbms_output.put_line(' Instanct No '||nbr_mlm_inst);

end;
/ 2 3 4 5 6 7 8 9 10 11 12 13
Instanct No 104

PL/SQL procedure successfully completed.

PACE_MASTERDBO@eqpaceod> CREATE OR REPLACE PROCEDURE PACE_MASTERDBO.test AS
2 nbr_mlm_inst number ;
3 BEGIN
4
5 select min(src_intfc_inst)
6 into nbr_mlm_inst
7 from RULESDBO.SOURCE
8 where ltrim(rtrim(source_code)) = 'APMEOD' ;
9
10 dbms_output.put_line('Date ' ||vc2_as_of_date||' '||nbr_mlm_inst||' '||nbr_mrc_inst);
11
12 end;
13 /

Warning: Procedure created with compilation errors.

LINE/COL ERROR
-------- -----------------------------------------------------------------
5/5 PL/SQL: SQL Statement ignored
7/10 PLS-00201: identifier 'RULESDBO.SOURCE' must be declared
10/5 PL/SQL: Statement ignored
10/36 PLS-00201: identifier 'VC2_AS_OF_DATE' must be declared


Tom Kyte
August 09, 2006 - 10:20 am UTC

Grant to a Role with 'select in select' Problem

Pasko, August 09, 2006 - 11:21 am UTC

Hi Tom,

take a look at the following: the last select statement
at the end of this script should have worked, but it is giving an Error: ORA-01031.
Is that expected ?


------------------------------------------
define DB = 'your_db_name'

spool test_grant_on_view_select_in_select.log

connect system/&&system_password@&&DB

--drop user user1 cascade;
--drop user user2 cascade;
--drop user user3 cascade;

grant connect , resource to user1 identified by user1 ;
grant connect , resource to user2 identified by user2 ;
grant create session to user3 identified by user3 ;

connect user1/user1@&&DB

drop table t ;
create table t as
select
rownum rn , object_name
from all_objects
where rownum < 10;

connect user2/user2@&&DB

drop table lookup;
create table lookup as
select
rownum rn , object_name
from all_objects
where rownum < 10;

connect system/&&system_password@&&DB

grant select on user2.lookup to user1 ;

connect user1/user1@&&DB

create or replace view vw_test
as
select rn, object_name ,
(select object_name from user2.lookup l
where l.object_name = t.object_name)
as name
from
( select rn, object_name
from t
) t
/

select count(*) from vw_test ;

connect system/&&system_password@&&DB

grant select on user1.vw_test to user3 ;

connect user3/user3@&&DB

select count(*) from user1.vw_test ;

spool off;

------------------------------------------


Tom Kyte
August 09, 2006 - 12:47 pm UTC

user1 is not allowed to convey access to user2.lookup given the privileges in place.

user1 would need "with grant option" granted on the select against lookup.

continued from the previous post ( Pasko )

Pasko, August 09, 2006 - 11:31 am UTC

--Possible Solution:===>>>>

propmt If we build a Wrapper View on top of the Old View, then it works, for example

connect user1/user1@&&DB

create or replace view vw_select_from_vw_test
as
select * from user1.vw_test
/


connect system/&&system_password@&&DB
grant select on user1.vw_select_from_vw_test to user3 ;


connect user3/user3@&&DB

prompt This works now after we built another view ,granted on it and select

select count(*) from user1.vw_select_from_vw_test ;

Another Grant Problem with Views with Subquery Factoring

Pasko, August 09, 2006 - 11:52 am UTC

Hi Tom,

it seems there is also a Problem when granting on Views with Subquery Factoring:

Table script Continued from previous poster( Pasko ):



prompt ---------------------------------------------
prompt Another Grant Problem with Subquery Factoring
prompt ---------------------------------------------

connect user1/user1@&&DB

create or replace view vw_grant_on_factoring_sql
as
with factored_sql as
(select rn, object_name from t )
select * from factored_sql
/

create or replace view vw_without_factoring_sql
as
select *
from
(select rn, object_name from t)
/


prompt user1 select from Own View with Subquery Factoring
select count(*) from vw_grant_on_factoring_sql
/


prompt user1 select from Own View without Subquery Factoring
select count(*) from vw_without_factoring_sql
/

connect system/&&system_password@&&DB

prompt grant select on user1.vw_grant_on_factoring_sql to user3
grant select on user1.vw_grant_on_factoring_sql to user3 ;


prompt grant select on user1.vw_without_factoring_sql to user3
grant select on user1.vw_without_factoring_sql to user3 ;


connect user3/user3@&&DB


prompt user3 select from a Granted View with Subquery Factoring
select count(*) from user1.vw_grant_on_factoring_sql
/


prompt user3 select from a Granted View without Subquery Factoring
select count(*) from user1.vw_without_factoring_sql
/

exit;


Thanks for the Tip on "with grant option"

Pascal, August 10, 2006 - 4:12 am UTC

Hi Tom,

thanks for your Response regarding the Grant with
"with grant option" Option. it works perfectly now.
I just learned something new today!

How do you explain the fact that it works if we create another view from the Original View, even without the "with grant option" ?


Did you get time to investigate on the second post from me above, about the Grant on a View which was created using the Subquery factoring Clause?

Thanks very much.

Pascal(pasko)


Tom Kyte
August 10, 2006 - 9:15 am UTC

I did not - I just found the way to correct the issue.

grant allowed by database link

A reader, September 19, 2006 - 2:31 pm UTC

I have stumbled upon this after an user ask me question about grants to objects. I realized that through database link and a synonym the user can create a procedure without to be granted directly. My question is: Is it either feature or bug?

Test case:
ops$mportes@FCCUAT9I> drop table t;

Table dropped.

ops$mportes@FCCUAT9I> drop role r_test;

Role dropped.

ops$mportes@FCCUAT9I>
ops$mportes@FCCUAT9I> create table t as
2 select username
3 from all_users
4 where rownum <= 10;

Table created.

ops$mportes@FCCUAT9I>
ops$mportes@FCCUAT9I> create role r_test;

Role created.

ops$mportes@FCCUAT9I>
ops$mportes@FCCUAT9I> grant select on t to r_test;

Grant succeeded.

ops$mportes@FCCUAT9I>
ops$mportes@FCCUAT9I> grant create session,
2 create synonym,
3 create procedure,
4 create database link,
5 r_test
6 to a identified by a;

Grant succeeded.

ops$mportes@FCCUAT9I>
ops$mportes@FCCUAT9I> connect a/a
Connected.
ops$mportes@FCCUAT9I> show user
USER is "A"
ops$mportes@FCCUAT9I> @login
a@FCCUAT9I> create synonym t for ops$mportes.t;

Synonym created.

a@FCCUAT9I>
a@FCCUAT9I> create database link a_link
2 connect to a identified by a
3 using 'fccuat9i';

Database link created.

a@FCCUAT9I>
a@FCCUAT9I> create or replace procedure read_t as
2 begin
3 for x in ( select username
4 from t )
5 loop
6 dbms_output.put_line('User: '|| x.username );
7 end loop;
8 end;
9 /

Warning: Procedure created with compilation errors.

a@FCCUAT9I> -- however
a@FCCUAT9I>
a@FCCUAT9I> create or replace procedure read_t as
2 begin
3 for x in ( select username
4 from t@a_link )
5 loop
6 dbms_output.put_line('User: '|| x.username );
7 end loop;
8 end;
9 /

Procedure created.

a@FCCUAT9I>
a@FCCUAT9I> set echo off
a@FCCUAT9I> exec read_t
User: SYS
User: SYSTEM
User: OUTLN
User: DBSNMP
User: BOUAT
User: BOUATUSR
User: BCPS_INTERFACE
User: MR95673
User: OPS$TRACKER
User: SA82094

PL/SQL procedure successfully completed.



Tom Kyte
September 19, 2006 - 3:25 pm UTC

interesting, filed away for when I return home (but please feel free to file with support, if they create a bug, please update so I do not do the same)

[re] grant allowed by database link

Marcio Portes, September 19, 2006 - 3:59 pm UTC

Right, I've filed an SR, here is the number: New SR 5796428.993, let's wait for their answer. I'll keep you posted.

Regards,
Marcio Portes.


grant allowed by database link

Michel Cadot, September 20, 2006 - 3:14 am UTC

Reproduced in 8.1.7.4, 9.2.0.6, 10.1.0.4 and 10.2.0.2.
But if you slighty modified the test with:

MICHEL> alter user a default role none;

User altered.

Then you have:

MICHEL> connect a/a
Connected.
A> create or replace procedure read_t as
2 begin
3 for x in ( select username from t@a_link ) loop
4 dbms_output.put_line('User: '|| x.username );
5 end loop;
6 end;
7 /

Warning: Procedure created with compilation errors.

I think the rationale is that when checking the privilege on the object there is a real connection through the database link and so default roles are enabled allowing the access to the table and so the procedure creation.

Regards
Michel

grant allowed by database link

Marcio Portes, September 25, 2006 - 9:59 am UTC

As promised. Here is the answer from Oracle Support.

ANSWER
=======
Hi Marcio,

This is not a bug, it's expected behavior. Please refer to the following bugs closed as not a bug:

Bug 3408559 - Abstract: CREATE VIEW ON OTHER SCHEMA IS POSSIBLE WITHOUT PRIVILEGES BY USING DBLINK
Bug 4460766 - Abstract: ABLE TO CREATE STORED PROCEDURE WHEN ACCESSING OBJECTS VIA ROLE THROUGH DB LINK


Roles don't work from PL/SQL because roles exist in sessions only, therefore, in general they don't work from a stored procedure, but with a dblink, you create a session. The dblink is making a new connection, so the rules of PL/SQL in the local database don't apply.
When dblink is used, only SELECT part of the procedure is sent to remote database. Here, Oracle doesn't know wheather this SELECT is part of procedure or view etc. It only applies privilege
required for SELECT and since user "a" is granted SELECT on table via role, SELECT completes and procedure is created.


grant allowed by database link

Michel Cadot, September 27, 2006 - 3:29 pm UTC

Thanks Marcio.

This is true only if your role is a default one.
This is a reason to use non default roles above all if your user can use some db links.

Regards
Michel


grant allowed by database link

Marcio Portes, September 27, 2006 - 5:25 pm UTC

Yes Michel Cadot even though I went far little bit to them. This was my last conversation with them.

25-SEP-06 20:24:13 GMT

New info : marcio.portes@: Ok, I just opened this because of
documentation.
</code> http://download-east.oracle.com/docs/cd/B10501_01/appdev.920/a96595/dci05pls.htm <code>


"quote"
Privileges Required to Create Procedures and Functions To create a standalone procedure or function, or package specification or body, you must have the CREATE PROCEDURE system privilege to create a procedure or package in your schema, or the CREATE ANY PROCEDURE system privilege to create a procedure or package in another user's schema.
For the compilation of the procedure or package, the owner of the procedure or package must have been explicitly granted the necessary object privileges for all objects referenced within the body of the code. The owner cannot have obtained required privileges through roles.
"/quote"

It looks clear to me that the development user MUST have be granted explicitly
to work through...

Any thought before I closed it?

Thanks and regards,
Marcio Portes.


26-SEP-06 07:08:28 GMT

.
UPDATE
=======
Hi Marcio,

This is true but it applies to objects in the local database. For remote objects there are other rules. When CREATE PROCEDURE statement is issued, a session is created in the remote database and the procedure is created with all the privileges from that session. In this case doesn't matter if SELECT privilege is granted directly to the user or via a role.

Best Regards,



creating procedures - development environment

Adrian Mos, November 08, 2006 - 5:08 pm UTC

I have an 10gR2 development database. There are 5 apps being developed at the same time by a number of developers. Each app it's own schema. How do I grant permission to a developer's database user to do everything in three of those schemas (like CREATE PROCEDURE in all three schemas) but nothing in the other two (he should not be able to CREATE PROCEDURE in these other two)?

Tom Kyte
November 08, 2006 - 8:14 pm UTC

you cannot really,

what you could do is set up a database trigger the does create/alters and fails ones you don't want (eg: extend the security model a bit by having a table that says what schemas a given user can create in - they have create any procedure, but you fail it if they are not registered in your table)

K.Naga Jyothi, March 13, 2007 - 3:40 am UTC

i have created a procedure to which if I pass a statement
it has to be executed with EXECUTE IMMEDIATE (dynamic SQL).
DOTSOFT>create or replace procedure  execute_statement (stmt varchar2) is
  2  begin
  3  execute immediate stmt;
  4  end;
  5  /

Procedure created.

DOTSOFT>exec execute_statement('create or replace view ex_view as select * from location_code');

BEGIN execute_statement('create or replace view ex_view as select * from location_code'); END;

*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "DOTSOFT.EXECUTE_STATEMENT", line 3
ORA-06512: at line 1


but if I execute like below it is not giving eror.
1
 begin
  2  execute immediate 'create or replace view ex_view as select * from location_code';
  3* end;
DOTSOFT>/

PL/SQL procedure successfully completed.

kindly help
Thanks in advance

Tom Kyte
March 13, 2007 - 11:23 am UTC

first, please drop that procedure. Man, is it unsafe. please google "sql injection" and read and read..

then:
http://asktom.oracle.com/tkyte/Misc/RolesAndProcedures.html

grant

AD, March 27, 2007 - 12:59 pm UTC

Hi Tom,

I have a requirement by which I would like to give a "non DBA" user grant to create tables onto a specific schema.

How this can be achieved?

Many thanks for your time.

Regards

Tom Kyte
March 28, 2007 - 11:11 am UTC

you can give them a stored procedure that does that if you like.

you would create a stored procedure that would accept a CREATE TABLE statement.

you would verify that you wanted to execute this statement (eg: verify it starts with CREATE TABLE for example

and then use execute immediate to execute it.

you create that in the schema you want the table to be created in

and grant execute on it to the other user.

OK

A reader, January 07, 2008 - 6:06 am UTC

Hi Tom,
Can a User be blocked from seeing the source code using
SQL revoke statement?Though I didn't grant the execute privilege on the procedure but he is able to see the contents of the procedure. I don't want this to happen.
Any idea you can give?
Tom Kyte
January 07, 2008 - 11:09 am UTC

you don't say what view they have access to.

Sounds like they have access to DBA_SOURCE (which sees all code)

ALL_SOURCE would not let them see if unless they have execute on it.

ops$tkyte%ORA10GR2> drop user a cascade;

User dropped.

ops$tkyte%ORA10GR2> grant create session to a identified by a;

Grant succeeded.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create or replace procedure p1 as begin null; end;
  2  /

Procedure created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create or replace procedure p2 as begin null; end;
  2  /

Procedure created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> grant execute on p1 to a;

Grant succeeded.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> connect a/a
Connected.
a%ORA10GR2> select * from all_source where owner = 'OPS$TKYTE' and name = 'P1';

OWNER                          NAME                           TYPE
------------------------------ ------------------------------ ------------
      LINE
----------
TEXT
-------------------------------------------------------------------------------
OPS$TKYTE                      P1                             PROCEDURE
         1
procedure p1 as begin null; end;


a%ORA10GR2> select * from all_source where owner = 'OPS$TKYTE' and name = 'P2';

no rows selected



if they have access to DBA_SOURCE, "un-access" that - otherwise, they can see all by design.


What if I don't want to grant execute privilege

Ravi, November 11, 2008 - 10:14 am UTC

Tom,
What if I would like user1 to view the procedure definition that is owned by a different user, user2? However, I don't want the user1 to be able to execute the procedure. I couldn't find a better way to do this without granting 'execute' privilege.
Grating access to dba_source wouldn't accomplish, either, (in any case it is not recommended), as user1 doesn't have 'view-only' privilege on the procedure.
Your help is appreciated!
Thanks!
Tom Kyte
November 11, 2008 - 4:46 pm UTC

you can

o grant execute on procedure
o create a view of dba_source that selects out the procedures you want and grant select on that view.
o write a procedure in user2 that displays the code, grant execute on that


ops$tkyte%ORA10GR2> create user a identified by a;

User created.

ops$tkyte%ORA10GR2> grant create session to a;

Grant succeeded.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create or replace procedure p
  2  as
  3  begin
  4          for x in (select * from dual)
  5          loop
  6                  null;
  7          end loop;
  8  end;
  9  /

Procedure created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create or replace
  2  function show_my_code
  3  ( p_name in varchar2,
  4    p_type in varchar2 default 'PROCEDURE'
  5  ) return sys_refcursor
  6  as
  7          l_cursor sys_refcursor;
  8  begin
  9          open l_cursor for select text from user_source where name = p_name and type = p_type order by line;
 10          return l_cursor;
 11  end;
 12  /

Function created.

ops$tkyte%ORA10GR2> grant execute on show_my_code to a;

Grant succeeded.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> connect a/a
Connected.
a%ORA10GR2> variable x refcursor
a%ORA10GR2> exec :x := ops$tkyte.show_my_code( 'P' )

PL/SQL procedure successfully completed.

a%ORA10GR2> print x

TEXT
-------------------------------------------------------------------------------
procedure p
as
begin
        for x in (select * from dual)
        loop
                null;
        end loop;
end;

8 rows selected.


be really careful and thoughtful in the coding of this procedure - think about what you want to expose to user1 carefully.

accessing public role through a function

karthik, February 10, 2009 - 5:29 pm UTC

Tom,

As per oracle PUBLIC is a role which is hidden. Now access to oracle STANDARD package is given to PUBLIC role. When a function in another schema calls a routine of this standard package owned by sys, how it works. Privileges granted through roles shouldnt work inside the contest of functions and procedures right. Only direct privileges i mean.So can you explain if it works - how ? and if not- what we should do.Thanks in advance
Tom Kyte
February 11, 2009 - 10:27 am UTC

well, public is more like a schema

ops$tkyte%ORA11GR1> connect / as sysoper
Connected.
public%ORA11GR1> show user
USER is "PUBLIC"
public%ORA11GR1>


and it is far from hidden (it is all over the place).. It is a schema that magically grants to ALL anything that is granted to it (eg: grant to public is like a magic "grant this to john, mary, bill, sue, ...." )

so, public isn't a role, it is a schema and the base set of privileges of this schema are "inherited" by everything.

Eg: your privileges without roles = everything granted to you PLUS everything granted to public.

Oracle support differs

Karthik, February 11, 2009 - 12:21 pm UTC

Tom,

This is an excerpt from oracle metalink 234551.1
a. During the database creation, the sql.bsq script is executed and it runs:

create role public;

The CREATE USER statement acts as if it assigned the PUBLIC role to the new
user, but this role is never displayed in DBA_SYS_PRIVS for this user nor
in SESSION_ROLES in the user's session:

Thats why i asked this question


Tom Kyte
February 11, 2009 - 1:18 pm UTC

and I answered - public behaves like a schema, I showed it acts as a schema.

I explained that it is a magic schema from which everyone inherits privileges - roles or not.

it is magic, it is like sys - sys is magic (things work differently when logged in as sys then when not), it is like DBA - if you have that role, special things happen - even if you create a role MYDBA and grant the same things to it - it will NOT work like DBA does...


alter session privilege

Nikhilesh, November 26, 2009 - 5:47 am UTC

Dear Tom,
 I don't have "alter session" privilege assigned to my user. When I try to set sql_trace ON it gives ORA-01031: insufficient privileges error but to my surprise "alter session" to set nls_date_format and parallel dml works fine. Am I missing anything or this is the way "alter session" works.

SQL> select * from session_privs;

PRIVILEGE
----------------------------------------
CREATE SESSION
MANAGE TABLESPACE
UNLIMITED TABLESPACE
CREATE TABLE
CREATE CLUSTER
CREATE PUBLIC SYNONYM
CREATE VIEW
CREATE SEQUENCE
CREATE PROCEDURE
CREATE TRIGGER
CREATE TYPE

PRIVILEGE
----------------------------------------
CREATE OPERATOR
CREATE INDEXTYPE

13 rows selected.

SQL> alter session set nls_date_format='dd-mon-rrrr';

Session altered.

SQL> alter session enable parallel dml;

Session altered.

SQL> alter session set sql_trace true;
alter session set sql_trace true
*
ERROR at line 1:
ORA-01031: insufficient privileges

Thanks in advance.

Tom Kyte
November 28, 2009 - 1:06 pm UTC

not all things "alter session" does require alter session - the system privilege.

http://docs.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_2012.htm#SQLRF00901

<quote>

Prerequisites

To enable and disable the SQL trace facility, you must have ALTER SESSION system privilege.

To enable or disable resumable space allocation, you must have the RESUMABLE system privilege.

You do not need any privileges to perform the other operations of this statement unless otherwise indicated.

</quote>

All_Source

Ananth, June 21, 2010 - 7:43 am UTC

Hi Tom,

I have one schema (say sch1), which has procedure (say proc1).
when i login to SYSTEM user and write a select query on ALL_SOURCE, i get the results..

Query:
select text from all_source where owner = 'SCH1' and name = 'PROC1' and type = 'PROCEDURE';

but in the SYSTEM if i create a package (say pkg1, and write the same into functionality into a function ( fnc1 function is pipelined returned clause is vc2_table.)

and if i call pkg1.fnc1 from sch1 schema, i dont get any results.
can you please help me out..?

Regards
Ananth


Tom Kyte
June 22, 2010 - 12:55 pm UTC

do not - do NOT - use sys or system, for anything other then upgrades and installs.

when you run a definers rights procedure (the default and almost always the correct approach) ROLES are not enabled.

do this:

sql> SET ROLE NONE;
sql> select ... from all_source ....;

and you'll see the data is "gone".

Tell us what you are trying to do (not how you are trying to do it) and we'll let you know the ways you can do it.

All_Sourcce

Ananth, June 23, 2010 - 1:24 am UTC

Hi Tom,

Thanx for the advice.

Am planning to write a interface to dbms_debug package. So, as a part of that i want end-user to select the breakpoint by looking at the source code. (which can be accessed only from all_source).

as all_source can be accessed from SYSTEM user, i thought of writing that interface pkg (pkg1) in SYSTEM schema itself.

Thanx in advance.

~Ananth
Tom Kyte
June 24, 2010 - 6:35 am UTC

ok, you have a problem here.


For security reasons, we limit who can see what - based on THEIR privs. The code you are allowed to see - is already visible in all_source, the contents of all_source changes based on who is logged in.

So, your solution is quite simple really - just query all_source as the currently logged in user - the code you are allowed to see - you'll see. Code you are not allowed to see - you won't

The work has been done for you - don't break things and expose code that shouldn't be with your project, work with the existing view.

System privileges different in 9i vs 11g?

Lise, August 13, 2010 - 6:35 pm UTC

Hi Tom,
Upgraded from 9.2.0.5 to 11.1.0.7, and finding something different happening in regards to the system privilege of CREATE PROCEDURE:
SQL> select * from dba_registry;

COMP_ID                        COMP_NAME                                                                        VERSION                        STATUS      MODIFIED             NAMESPACE                      CONTROL                        SCHEMA                         PROCEDURE                                                     STARTUP  PARENT_ID                      OTHER_SCHEMAS
------------------------------ -------------------------------------------------------------------------------- ------------------------------ ----------- -------------------- ------------------------------ ------------------------------ ------------------------------ ------------------------------------------------------------- -------- ------------------------------ --------------------------------------------------------------------------------
CATALOG                        Oracle Database Catalog Views                                                    11.1.0.7.0                     VALID       07-AUG-2010 11:19:20 SERVER                         SYS                            SYS                            DBMS_REGISTRY_SYS.VALIDATE_CATALOG                                                                    
CATPROC                        Oracle Database Packages and Types                                               11.1.0.7.0                     VALID       07-AUG-2010 11:19:21 SERVER                         SYS                            SYS                            DBMS_REGISTRY_SYS.VALIDATE_CATPROC                                                                    DBSNMP,OUTLN,SYSTEM,TSMSYS
JAVAVM                         JServer JAVA Virtual Machine                                                     11.1.0.7.0                     VALID       07-AUG-2010 11:19:36 SERVER                         SYS                            SYS                            INITJVMAUX.VALIDATE_JAVAVM                                                                            
XML                            Oracle XDK                                                                       11.1.0.7.0                     VALID       07-AUG-2010 11:19:36 SERVER                         SYS                            SYS                            XMLVALIDATE                                                                                           
CATJAVA                        Oracle Database Java Packages                                                    11.1.0.7.0                     VALID       07-AUG-2010 11:19:36 SERVER                         SYS                            SYS                            DBMS_REGISTRY_SYS.VALIDATE_CATJAVA                                                                    
XDB                            Oracle XML Database                                                              11.1.0.7.0                     VALID       07-AUG-2010 11:19:43 SERVER                         SYS                            XDB                            DBMS_REGXDB.VALIDATEXDB                                                                               
EM                             Oracle Enterprise Manager                                                                                       LOADING     09-AUG-2010 09:33:19 SERVER                         SYS                            SYSMAN                                                                                                                               

7 rows selected


SQL> 
SQL> select
  2    lpad(' ', 2*level) || granted_role "User, his roles and privileges"
  3  from
  4    (
  5    /* THE USERS */
  6      select
  7        null     grantee,
  8        username granted_role
  9      from
 10        dba_users
 11      where
 12        username like upper('%&enter_username%')
 13    /* THE ROLES TO ROLES RELATIONS */
 14    union
 15      select
 16        grantee,
 17        granted_role
 18      from
 19        dba_role_privs
 20    /* THE ROLES TO PRIVILEGE RELATIONS */
 21    union
 22      select
 23        grantee,
 24        privilege
 25      from
 26        dba_sys_privs
 27    )
 28  start with grantee is null
 29  connect by grantee = prior granted_role;

User, his roles and privileges
--------------------------------------------------------------------------------
  DDL00
    CREATE DATABASE LINK
    CREATE TABLE
    CREATE TRIGGER
    SCHEMA_CONTROLLER
      CREATE ANY DIRECTORY
      CREATE LIBRARY
      CREATE PROCEDURE
      CREATE SEQUENCE
      CREATE SESSION
      CREATE SYNONYM
      CREATE TABLE
      CREATE TRIGGER
      CREATE TYPE
      CREATE VIEW
  DDL00ADH
    CREATE SESSION
    CREATE SYNONYM

18 rows selected

SQL> SELECT * FROM session_privs;

PRIVILEGE
----------------------------------------
CREATE SESSION
CREATE TABLE
CREATE SYNONYM
CREATE VIEW
CREATE SEQUENCE
CREATE DATABASE LINK
CREATE PROCEDURE
CREATE ANY PROCEDURE
CREATE TRIGGER
CREATE ANY DIRECTORY
CREATE TYPE
CREATE LIBRARY

12 rows selected

SQL> create or replace procedure ddl01.lsp_test is
  2  begin
  3  null;
  4  end;
  5  /

Procedure created

SQL> create or replace procedure ddl01.lsp_test is
  2  begin
  3  null;
  4  null;
  5  end;
  6  /

Procedure created


SQL> select * from dba_registry;

COMP_ID                        COMP_NAME                                                                        VERSION                        STATUS      MODIFIED             CONTROL                        SCHEMA                         PROCEDURE                                                     STARTUP  PARENT_ID
------------------------------ -------------------------------------------------------------------------------- ------------------------------ ----------- -------------------- ------------------------------ ------------------------------ ------------------------------------------------------------- -------- ------------------------------
CATALOG                        Oracle9i Catalog Views                                                           9.2.0.5.0                      VALID       10-JUN-2009 13:15:08 SYS                            SYS                            DBMS_REGISTRY_SYS.VALIDATE_CATALOG                                     
CATPROC                        Oracle9i Packages and Types                                                      9.2.0.5.0                      VALID       10-JUN-2009 13:15:08 SYS                            SYS                            DBMS_REGISTRY_SYS.VALIDATE_CATPROC                                     
JAVAVM                         JServer JAVA Virtual Machine                                                     9.2.0.5.0                      VALID       10-JUN-2009 13:08:22 SYS                            SYS                                                                                                   
XML                            Oracle XDK for Java                                                              9.2.0.7.0                      VALID       10-JUN-2009 13:15:08 SYS                            SYS                            XMLVALIDATE                                                            
CATJAVA                        Oracle9i Java Packages                                                           9.2.0.5.0                      VALID       10-JUN-2009 13:15:08 SYS                            SYS                            DBMS_REGISTRY_SYS.VALIDATE_CATJAVA                                     

SQL> 
SQL> select
  2    lpad(' ', 2*level) || granted_role "User, his roles and privileges"
  3  from
  4    (
  5    /* THE USERS */
  6      select
  7        null     grantee,
  8        username granted_role
  9      from
 10        dba_users
 11      where
 12        username like upper('%&enter_username%')
 13    /* THE ROLES TO ROLES RELATIONS */
 14    union
 15      select
 16        grantee,
 17        granted_role
 18      from
 19        dba_role_privs
 20    /* THE ROLES TO PRIVILEGE RELATIONS */
 21    union
 22      select
 23        grantee,
 24        privilege
 25      from
 26        dba_sys_privs
 27    )
 28  start with grantee is null
 29  connect by grantee = prior granted_role;

User, his roles and privileges
--------------------------------------------------------------------------------
  DDL00
    CREATE DATABASE LINK
    CREATE TABLE
    CREATE TRIGGER
    SCHEMA_CONTROLLER
      CREATE ANY DIRECTORY
      CREATE LIBRARY
      CREATE PROCEDURE
      CREATE SEQUENCE
      CREATE SESSION
      CREATE SYNONYM
      CREATE TABLE
      CREATE TRIGGER
      CREATE TYPE
      CREATE VIEW
  DDL00ADH
    CREATE SESSION
    CREATE SYNONYM

18 rows selected

SQL> SELECT * FROM session_privs;

PRIVILEGE
----------------------------------------
CREATE SESSION
CREATE TABLE
CREATE SYNONYM
CREATE VIEW
CREATE SEQUENCE
CREATE DATABASE LINK
CREATE PROCEDURE
CREATE ANY PROCEDURE
CREATE TRIGGER
CREATE ANY DIRECTORY
CREATE TYPE
CREATE LIBRARY

12 rows selected

SQL> create or replace procedure ddl01.lsp_test is
  2  begin
  3  null;
  4  end;
  5  /

Procedure created

SQL> create or replace procedure ddl01.lsp_test is
  2  begin
  3  null;
  4  null;
  5  end;
  6  /

create or replace procedure ddl01.lsp_test is
begin
null;
null;
end;

ORA-01031: insufficient privileges

Any idea what I have done wrong? Is there anything else that is different from 9i to 11g?

Tom Kyte
August 19, 2010 - 12:44 am UTC

this is very confusing to follow

why is dba_registry here?

can you give a simple script that works in 9i but not 11g that includes everything I would need to reproduce (that is, it starts with CREATE USER, GRANT whatever to user, CREATE PROCEDURE command)


have the base set of privs changes - YES, dramatically, lots of stuff has been very much locked down. But I would want a soup to nuts example anyone can run to comment on.

Executing schema.procedure problem

Anne, September 28, 2010 - 10:51 am UTC

Hi Tom,

Here is a very bizarre situation and your help would be much appreciated!

I have 2 schema's in the mrispord database : bps and mr_proj.   As user bps, I am not able to execute mr_proj.test_proc even though  bps has execute privilege.

mr_proj@mris.db3> CREATE OR REPLACE procedure test_proc  AS
  2     
  3  BEGIN
  4  
  5       dbms_output.put_line('Hello');
  6   
  7    
  8     
  9   
 10  EXCEPTION
 11         when OTHERS then
 12            dbms_output.put_line('Error'); 
 13  end test_proc ;
 14  /

Procedure created.

mr_proj@mris.db3> 
  1  select object_name, status, object_type
  2  from user_objects
  3* where object_name = 'TEST_PROC'
mr_proj@mris.db3> /

OBJECT_NAME                                                                                                                      STATUS  OBJECT_TYPE
-------------------------------------------------------------------------------------------------------------------------------- ------- -------------------
TEST_PROC                                                                                                                        VALID   PROCEDURE

mr_proj@mris.db3> 


Run from mr_proj works fine :
mr_proj@mris.db3> begin
  2  mr_proj.test_proc;
  3  dbms_output.put_line('completed...');
  4  exception
  5     when others then 
  6           dbms_output.put_line(substr(sqlerrm,1,255));
  7  end;
  8  /
Hello
completed...

PL/SQL procedure successfully completed.

mr_proj@mris.db3> 

system@mris.db3> grant execute on mr_proj.test_proc to bps;

Grant succeeded.

system@mris.db3> 

Object Privileges Directly Granted to Users
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

bps@mris.db3> select
  2     p.grantee,
  3     p.privilege,
  4     o.object_type,
  5     p.owner||'.'||p.table_name obj_name,
  6     p.grantor,
  7     p.grantable
  8  from
  9     dba_tab_privs p,
 10     dba_objects o
 11  where
 12     grantee not in ('SYS','SYSTEM')
 13  and 
 14     grantee in 
 15        (select username from dba_users)
 16  and 
 17     o.owner = p.owner
 18  and
 19     o.object_name = p.table_name
 20  and o.object_name like :object
 21  order by 
 22     p.grantee,
 23     p.owner,
 24     p.table_name,
 25     p.privilege
 26  /   
more...

GRANTEE              PRIVILEGE                 OBJECT_TYPE          OBJ_NAME                       GRANTOR              GRA
-------------------- ------------------------- -------------------- ------------------------------ -
BPS                  EXECUTE                   PROCEDURE            MR_PROJ.TEST_PROC              MR_PROJ              NO
bps@mris.db3> 



bps@mris.db3> begin
  2  mr_proj.test_proc;
  3  dbms_output.put_line('completed...');
  4  exception
  5     when others then 
  6           dbms_output.put_line(substr(sqlerrm,1,255));
  7  end;
  8  /
mr_proj.test_proc;
        *
ERROR at line 2:
ORA-06550: line 2, column 9:
PLS-00302: component 'TEST_PROC' must be declared
ORA-06550: line 2, column 1:
PL/SQL: Statement ignored


bps@mris.db3> 

Database is 10.2.0.4.

This is a very simple issue and I cannot figure out what is wrong...

Tom Kyte
September 28, 2010 - 11:27 am UTC

 10  EXCEPTION
 11         when OTHERS then
 12            dbms_output.put_line('Error'); 
 13  end test_proc ;


http://asktom.oracle.com/pls/asktom/asktom.search?p_string=%22i+hate+your+code%22

My opinion of experienced coders that do that drops immediately. There is no purpose, no reason - not for a demo, not in real life, for doing that.


system@mris.db3> grant execute on mr_proj.test_proc to bps;

Grant succeeded.


why obfuscate something like that - why use system? Please do not use system - do not use sys, do not use system - they are ours, use your own accounts. sys and system are special, magical, they operate differently.




I will guess.

I will guess that BPS owns a package unfortunately named MR_PROJ (or has a synonym). And that object doesn't have TEST_PROC


ops$tkyte%ORA11GR2> create user a identified by a;

User created.

ops$tkyte%ORA11GR2> grant create session, create procedure to a;

Grant succeeded.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> create user b identified by b;

User created.

ops$tkyte%ORA11GR2> grant create session, create procedure to b;

Grant succeeded.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> connect a/a
Connected.
a%ORA11GR2> create or replace procedure test_proc
  2  as
  3  begin
  4          dbms_output.put_line( 'hello world' );
  5  end;
  6  /

Procedure created.

a%ORA11GR2> 
a%ORA11GR2> grant execute on test_proc to b;

Grant succeeded.

a%ORA11GR2> 
a%ORA11GR2> connect b/b
Connected.
b%ORA11GR2> exec a.test_proc
hello world

PL/SQL procedure successfully completed.

b%ORA11GR2> create or replace package a
  2  as
  3          procedure not_test_proc;
  4  end;
  5  /

Package created.

b%ORA11GR2> exec a.test_proc
BEGIN a.test_proc; END;

        *
ERROR at line 1:
ORA-06550: line 1, column 9:
PLS-00302: component 'TEST_PROC' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored



it would be even more confusing if your MR_PROJ package actually had a procedure test_proc!!!!



I can't believe this

Anne, September 28, 2010 - 12:43 pm UTC

Thank you so much TOm. Yes, I agree with you on the exception without a raise - I just quick put it in for the demo - Sorry about that!

And yes, I should have had mr_proj given the grants to bps instead of system.

I had searched for other packages, synonyms etc with the same name as "mr_proj" and never found anything. But your thoughts, made me think that maybe there was a table/view out there that was causing it, and boy was that right - somebody created a table called "mr_proj" in the bps schema!!

I was going bonkers on this one- thank you so much for helping me resolve this!




Question related to my previous post

Anne, September 29, 2010 - 7:44 am UTC

Hi Tom,

This is related to my previous issue where in Schema bps was not able to execute a proc in schema mr_proj.
So basically bps.mr_proj existed and mr_proj.test_proc existed.

As bps: running mr_proj.test_proc resulted in PLS-00302: component 'TEST_PROC' must be declared.

This is because it is looking at the table bps.mr_proj right ?

So it leads me to a question : If you did have a need to have a table name the same name as the schema name, how can you accomplish it ?
eg : schema A, table A.B
Schema B, test_proc
How can you access A.B and B.test_proc ?

Thanks!
Tom Kyte
September 29, 2010 - 9:11 am UTC

... This is because it is looking at the table bps.mr_proj right ?
...

correct.


... So it leads me to a question : If you did have a need to have a table name the
same name as the schema name, how can you accomplish it ?...


you cannot easily or assuredly - this is something you just don't even want to think about doing. There is no way to qualify the object reference to discriminate between the two.

Rizwan, May 11, 2011 - 5:48 am UTC

Hello Sir , 

My issue is i have created a view in schema A which links to tables in schema B . I have grant ALL privileges on view to schema C but schema C is not able to access view owned by schema A . I am getting below error . 

12:40:36 SQL> select * from osi4ldap.cg_niam;
select * from osi4ldap.cg_niam
                       *
ERROR at line 1:
ORA-01031: insufficient privileges

Schema C has all the necessary privileges on tables of schema B to which view is referencing . This is through role but i have even given it directly to schema C .
I have also give privileges on view directly (without using roles) to schema B .  

Any idea why this happening ? I am confused .. 

My database version is 

Oracle Database 11g Enterprise Edition Release 11.1.0.7.0

Tom Kyte
May 11, 2011 - 10:35 am UTC

It will work if A is granted access to B's objects with the "WITH GRANT OPTION" as demonstrated below:

ops$tkyte%ORA11GR2> create user a identified by a;

User created.

ops$tkyte%ORA11GR2> create user b identified by b default tablespace users quota unlimited on users;

User created.

ops$tkyte%ORA11GR2> create user c identified by c;

User created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> grant create session, create view to a;

Grant succeeded.

ops$tkyte%ORA11GR2> grant create session, create table to b;

Grant succeeded.

ops$tkyte%ORA11GR2> grant create session to c;

Grant succeeded.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> connect b/b
Connected.
b%ORA11GR2> create table t as select * from dual;

Table created.

b%ORA11GR2> grant select on t to a with grant option;

Grant succeeded.

b%ORA11GR2> 
b%ORA11GR2> connect a/a
Connected.
a%ORA11GR2> create view v as select * from b.t;

View created.

a%ORA11GR2> grant select on v to c;

Grant succeeded.

a%ORA11GR2> 
a%ORA11GR2> connect c/c
Connected.
c%ORA11GR2> select * from a.v;

D
-
X

Rizwan, May 11, 2011 - 7:47 am UTC

I have found a solution to my problem with the help of below link .

Do you have answer to the question asked in below link ?

http://dbaforums.org/oracle/index.php?showtopic=5583
Tom Kyte
May 11, 2011 - 10:39 am UTC

why would you point me to that forum?

If you want to ask something, ask here.

Rizwan ghadiyali, May 13, 2011 - 5:59 am UTC

Tom,

Thanks for answering the question .. I was sure that you would be having the answer .. And sorry for the second question ..

Long live Tom :)

grants effects

A reader, June 01, 2011 - 12:17 pm UTC

Greetings Thomas,

and thanks like always,

is there a way to remove or add grants to/from current connected sessions?
Tom Kyte
June 01, 2011 - 2:54 pm UTC

ops$tkyte%ORA11GR2> drop user a cascade;

User dropped.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> create user a identified by a;

User created.

ops$tkyte%ORA11GR2> grant create session to a;

Grant succeeded.

ops$tkyte%ORA11GR2> grant select on scott.dept to a;

Grant succeeded.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> connect a/a
Connected.
a%ORA11GR2> select * from scott.dept;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

a%ORA11GR2> !sqlplus /

SQL*Plus: Release 11.2.0.2.0 Production on Wed Jun 1 15:54:11 2011

Copyright (c) 1982, 2010, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

ops$tkyte%ORA11GR2> revoke select on scott.dept from a;

Revoke succeeded.

ops$tkyte%ORA11GR2> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

a%ORA11GR2> select * from scott.dept;
select * from scott.dept
                    *
ERROR at line 1:
ORA-00942: table or view does not exist




they are??? what do you mean??

grants effects

A reader, June 01, 2011 - 5:49 pm UTC

Greetings Thomas,

and thanks like always,

i am sorry for not clearing this, but what about roles?
Tom Kyte
June 02, 2011 - 8:36 am UTC

http://docs.oracle.com/docs/cd/E11882_01/server.112/e17118/statements_9020.htm#SQLRF55189

Once enabled in a session, a role remains enabled until the session terminates or the session disables the role (it won't be able to subsequently re-enable the role)

Public access

a reader, September 13, 2011 - 5:55 am UTC

Hi Tom,

I am wondering is there a way you can revoke public access to tables granted by some database users.

It happened that some of the users granting public access to their tables which is violating the security gudielines. I would like to resolve this by revoking the public access on a routine basis. These tables exist across a number of schemas.

Can you suggest some way.

Regards


Tom Kyte
September 13, 2011 - 6:26 am UTC

just run the revoke???

ops$tkyte%ORA11GR2> revoke select on scott.emp from public;

Revoke succeeded.


Is there is a reason it is still there??!!

A reader, September 13, 2011 - 9:05 pm UTC


Hello Tom,

Is there a reason that the "restriction" for having privs granted directly for procedures, etc to compile is still there?

The databases are evolving with every new release and tons of new features being added and the older ones being enhanced. But it is surprising that something this small (but not insignificant by any means) has been left unattended. Maybe the dev team is working on it or maybe it has not been granted the attention it deserves.

Anyways, I don't think it would take a lot to fix this (rather take it to the next level).

Please let us know your thoughts on this.

Cheers!!

Tom Kyte
September 14, 2011 - 7:05 pm UTC

I don't know if you read the original answer - it sort of goes into the issue. It isn't a matter of someone being 'lazy' and not providing a feature.

It is not being left unattended - it would kill you if implemented (honest).

compiling Schema object

Annie, April 27, 2012 - 8:26 am UTC

Hi,

In a db 'Test', I have different schemas, say - sch1,sch2,sch3. I would like to compile the sch2 objects from sch3 without using grants or synonyms. Is this possible? If yes how can a specific object in the sch2 can be compiled from sch3 ? Pls let me know.
Tom Kyte
April 27, 2012 - 9:48 am UTC

you sort of need grants - why would you even consider such a restriction?

erratic procedure execution time

A reader, May 06, 2012 - 3:11 pm UTC

hi tom,

our developer have an external C program which is calling an internal PL/SQL procedure inside the database.

everyday, the procedure will be called many times and normally execution would take just 1 second to return result to the C program.

however, once in awhile, from the log of the C program, for the procedure to execute finish and return result, it would take more then 20 seconds.

----------------------

We are not able to determine the frequency that this occurrence will happen. Thus we are not able to exactly replicate the issue.

Now, many times came to my mind on why the procedure would take so long to execute where normally it would take 1 second only

- network ?
- lock contention during insert or update ?
- library latches ? datafile increment ?
- C program OCI library problem ?

--------
Now i am trying to "add in" alot of "INSERTS" into a dummy table throughout the procedure to see where the choking point is.

e.g
procedure xxx

counter number;
counter := seq.nextval
..
..
insert into dummy value(counter,sysdate,'flagpoint1');
..
..
..
insert into dummy values(counter,sysdate, 'flatpoint2')
..
insert into dummy values(counter,sysdate, 'flatpoint3')
end;

-------

q1) even if i manage to find where the chokepoint is with the help of the dummy table? but what could be the reason of the chokepoint again ?

well, maybe when the slow execution time happen again and if the timing from flagpoint1 to flagpoint3 is 1 second, maybe the problem lies elsewhere (e.g. the network or the OCI library ?)

q2) is there a better way of monitoring to find out what is wrong ?

Regards,
Noob
Tom Kyte
May 06, 2012 - 3:36 pm UTC

different inputs - different amounts of data to process.

why not have it log its inputs and times in a log table of some sort as well.

q1) that might become painfully self evident when you find it - usually it will be.

q2) you could also look at an ASH report to see if the session was waiting on something for a long time during a bad run - you'd want to log the session information for that as well - and maybe have it alert you somehow if the procedure detected that it took a long time.


You might consider using "logger"

http://tylermuth.wordpress.com/2011/11/09/logger-project-moved-temporarilly/

erratic procedure execution time

A reader, May 11, 2012 - 4:31 am UTC

hi tom,

you are right. it is painfully evident on what is wrong.

From the dummy table whereby it capture execution time in each portion of the procedure
---------------------------------------------
11-MAY-12 10:08:40 update_call_online 7248 S05,9583168595255918@cc,N,GW_SH_TM_001,GK_SH_CC, 1
11-MAY-12 10:09:07 update_call_online 7248 S05,9583168595255918@cc,N,GW_SH_TM_001,GK_SH_CC, 2
---------------------------------------------
From 1 to 2, it took 27 seconds.

Between 1 and 2, there is only a SELECT statement.

SELECT User_ID, Provider_ID, NVL(Call_Online, 'N')
INTO v_UserID, v_ProviderID, v_CallOnline
FROM Subscriber
WHERE User_ID = TRIM(p_UserID);

------------------------------------------------

I rerun the select statement with the same parameter capture and it was perfectly fine and fast.

--------------------------------------------------

q1) i try to rush ASH report from the timing given
from 10:00 to 11:00, but i did not get anything i want.

seems like my sample time started from
11-MAY-12 11.28.35.494000000 AM instead.

how do i set the frequency whereby the sample is taken ?

q2) what could be the reason for this slow SELECT retrieval ? corrupted index ? insufficient buffer pool ?

---

Any advice will be deeply appreciated

Regards,
Noob

Tom Kyte
May 11, 2012 - 6:46 am UTC

how did you get "between 1 and 2 was 27 seconds hence it must be the select"???


seems to me your log is either writing out timestamps as the procedure is being called OR at the end of the procedure.

If you log the timestamp as the procedure is called - it could be (probably is) that the first procedure call took 27 seconds - the select took no time at all.

If you log the timestamp as the procedure exits - then it is probably true that the second procedure took 27 seconds and the select took no time at all.

explain to me why you are blaming a select in between two procedure calls based on the above information - I don't see it that way.

erratic procedure execution time

A reader, May 13, 2012 - 2:11 am UTC

hi tom,

i am logging within the procedure while the procedure is call.

->

create or replace procedure
as
-- declare variables
begin
insert into log table (sysdate,'someother params','1');

SELECT User_ID, Provider_ID, NVL(Call_Online, 'N')
INTO v_UserID, v_ProviderID, v_CallOnline
FROM Subscriber
WHERE User_ID = TRIM(p_UserID);

insert into log table (sysdate,'someother params','2');

-- some other codings etc etc..

insert into log table (sysdate,'someother params','3');

-- some other codings

insert into log table (sysdate,'someother params','4');

commit;
insert into log table (sysdate,'someother params','5');

end;

----------

So as you can see, i have "entered" the pl/sql procedure, the insert of flag 1 is done, and when it reaches 2, it took 27 secs.

The only code between 1 and 2 in the same procedure, is only the SELECT statement.

--------

Hope to seek your opinion on this.
Thanks!

Regards,
Noob



Tom Kyte
May 13, 2012 - 3:33 am UTC

turn on dbms_monitor.session_trace_enable( waits=> true ); get the details of the execution of that sql

or generate an ASH report if you have access to that for this statement.

at the very least - look at the plan in v$sql_plan to see if the plan looks reasonable.

erratic procedure execution time

A reader, May 14, 2012 - 3:35 am UTC

Hi Tom,

for dbms_monitor.session_trace_enable( waits=> true ), i am going to embed this inside the procedure

q1) will the tracefile be big ? as alot of session will be calling this procedure

q2) how do i match the session that is having the problem with the tracefile ?

-- i guess i have to insert the SID into the dummy table too ?
are we able to find the SID information in the tracefile as well ?
----------------

q3) For ASH report, understand it is a circular buffer, anyway can we enlarge the circular buffer (SGA) ? is there a particular parameter for the ASH report buffer ?

Need this because i try to generate ash report base on the timing whereby the issue occur, but i do not have any sampling at all during that time (believe it is overwritten) ..

-----

Lastly , i run the execute plan, it is using a unique index scan base on USER_ID primary key. So i do see any reason why it took 27 sec.

Hope to hear your advice soon

Regards,
Alan


Tom Kyte
May 14, 2012 - 12:21 pm UTC

q1) depends on the nature and number of SQL statements - it is hard to say.

do you have ASH access?

q2) use set the client identifier (dbms_session.set_identifier), it'll be written out to the trace file - just assign all of your transactions a unique id somehow and log that in your table

q3) ash is flushed to the DBA_HIST tables too - it is there for as long as your AWR data is.




your above link doesn't work

A reader, June 01, 2012 - 3:20 pm UTC

character size exception in procedure

A reader, June 02, 2012 - 11:33 am UTC

hi tom,

recently there was a change in transaction data, for a particular field, instead of being 40 characters, it become twice as big instead.

these transaction are inserted into the database table via a pl/sql procedure call.

however, the table column size is too small for the content that is going to be inserted causing the pl/sql procedure to return exception and by capturing the exception, there is nothing much to be done except altering the table column size.

thus we lost alot of inserted transactions as a result.

---------------------------------------

this pop up the question in my head.

q1) how do we make sure that the content going to be inserted into the table is always of the appropriate declared size of the table' column ?

q2) we would rather lose partial data/ or truncated data, then to have the whole row rejected / not inserted as to manually insert later would be quite tedious job.

what workaround should you suggest ? or should we abid by the rule of thumb of column constraint that if data is bigger then a column size, then it is a no no for the whole row.

Regards,
Noob

Tom Kyte
June 04, 2012 - 8:49 am UTC

thus we lost alot of inserted transactions as a result.


YOU DID NOT, you did not, you did not, you did not.


You received an error in your application, you had a bug in your application, as a result of this bug in your application - your end users lost the ability to process certain things - but you never lost any inserted transactions - they never *happened*


I'm 100% confused and bemused by your statements:

a) for a particular field, instead of being 40 characters, it become twice as big instead.

ok, that sure sounds like the table column was increased

b) however, the table column size is too small for the content that is going to be inserted

ok, that sure sounds like it WAS NOT increased


c) there is nothing much to be done except altering the table column
size.

ummm, no kidding?????




q1) we do that for you. apparently, you told us to permit 40 characters and when you tried to insert 80, it failed. If 80 is the right answer - use 80 as the defined length?????

q2) no you wouldn't, NO YOU WOULDN'T, and - in addition to that - NO YOU WOULDN'T. That would be just about the dumbest idea ever.



You incorporate proper error handling, your program STOPS when it cannot continue, you fix it then and there and you lose nothing.



I'm flabbergasted here - I'm speechless - I really do not know what to say.


You know what would be tedious - tedious would be erroneously inserting bad data into a table for weeks or months before someone notices your application is PURPOSELY corrupting it. that - that would be tedious.

Read the popup window before you answer

Michel Cadot, June 04, 2012 - 5:36 am UTC


What does this have to do with this thread titled "Procedures, roles and grants"?

Regards
Michel Cadot

Tom Kyte
June 04, 2012 - 9:14 am UTC

they just wanted to get my day off to a bad start. I'm so scared for the future of software development - some days more than others.

today has been a bad day so far.

character size exception in procedure

A reader, June 04, 2012 - 11:43 am UTC

hi tom,

i am sorry that my question had caused a bad day to you.

The actual story is like that.

1) there is a column 'X' in the "huge" table which was 40 character in size.

2) This large table store billing transactions whereby the rows are inserted via a PL/SQL procedure.

3) The column contain 'X' contain client's information which originally in the past was always around the range of 10 to 30 characters.

4) However,there is a group of new clients that joined in and they have a longer information to be stored in 'X'.

This was not foreseen initially.

Thus instead of inserting somehow 10-30 characters into the 'X' column with size 40, the information is some how about 60-80 characters.

5) This group of users perform about 20,30 thousands transaction per day but none of the transaction is executed successfully in the plsql procedure as insertion failed due to improper sizing of the column 'X'.

Thus this group of clients are not charged.

------------------

The only exception handling we can do here is to send a email alert to the administrator as we cannot alter changes to the table which is too big, 20,30 millions rows.

-------------------

Thus, i am thinking of what can be done to further improve this type of situation.

Do you mean that, the external application calling the PL/SQL procedure has to make sure that the data passed into the plsql's parameters are of correct length to the column size in the table ?

Regards,
Noob
Tom Kyte
June 04, 2012 - 12:17 pm UTC

5) This group of users perform about 20,30 thousands transaction per day but
none of the transaction is executed successfully in the plsql procedure as
insertion failed due to improper sizing of the column 'X'.

Thus this group of clients are not charged.


and thus you have a serious bug in your software, how did MORE THAN ONE of these even get attempted - what kind of error handling do you have? Why is it hard to recover from this - this should be trivial - this is what we get paid to do. This should not be tedious.


The only exception handling we can do here is to send a email alert to the
administrator as we cannot alter changes to the table which is too big, 20,30
millions rows.


that makes no sense, you rejected 20,000 to 30,000 transactions and this did not set off some HUGE, BIG, GIGANTIC red flag??? The size of the table doesn't matter - your batch program is busted, big time.


Thus, i am thinking of what can be done to further improve this type of
situation.

Do you mean that, the external application calling the PL/SQL procedure has to
make sure that the data passed into the plsql's parameters are of correct
length to the column size in the table ?



wow. again, wow.


The external application would receive an error from the database if the data it sent to the database does not conform to the integrity rules of the database (garbage in - ERROR OUT). The external program would then do something intelligent - perhaps write the rejected record to a log file, perhaps terminating after it hits like 100 bad records in a batch, perhaps doing some real error handling and logic.


I am seriously at a loss here??? People deal with bad data all day, every day, in every application, forever - since the beginning of IT. This is not something new and unsurprising.

One has to wonder how many millions of transactions you've lost over the years.



Granting Package privileges to users via roles

Shreej, June 05, 2012 - 5:50 am UTC

Hi Tom,

I have granted the following statements,

Create ROLE APP_ROLE;

GRANT APP_ROLE TO USER1;

GRANT EXECUTE ON UTL_FILE TO APP_ROLE;

Now I am trying to compile a package APP_Package_pkg using the to write a CSV file using UTL_FILE, but i am getting the error UTL_FILE is not declared.
If I give the grant directly to user rather via role i am able to compile the package succesfully.

GRANT EXECUTE ON UTL_FILE TO USER1;

I do not understand why its so ? Please Share List of Privileges that can be assigned to users diretly and not through roles.

is there any recommendations from Oracle ?

Please throw the light on the same.



Tom Kyte
June 05, 2012 - 10:24 am UTC

http://asktom.oracle.com/pls/asktom/ASKTOM.download_file?p_file=6551289900368934430


and
http://www.oracle.com/technetwork/issue-archive/2005/05-may/o35asktom-089705.html
(search for "ORA-01031: Insufficient Privileges")

Please Share List of Privileges that can be
assigned to users diretly and not through roles.


all of them.

Granting Package privileges to users via roles

shreej, June 08, 2012 - 8:18 am UTC

These were the below grants given to users via role

EXECUTE on DBMS_LOCK
EXECUTE ON UTL_FILE
EXECUTE ON DBMS_SESSION
READ, WRITE ON DIRECTORY


Tom Kyte
June 08, 2012 - 9:58 am UTC

and as per the links above, roles are never enabled during the compilation of stored objects like procedures, views, packages and so on

So those roles are useless for creating procedures/views.


bulk limit

Anna, June 11, 2012 - 3:15 am UTC

Hi,

Please let me know whether bulk limit improves the performance ?
In my dev database when i execute the code with bulk limit 100 it works faster, but in prod database the same code(record count in the database tables are same with dev ) for the bulk limit with 100 is very slow.
Why is this difference?
Tom Kyte
June 11, 2012 - 3:57 am UTC

yes, in general bulk processing is more efficient and faster than slow by slow (but not nearly as fast as a single SQL statement!)

Why is it not fast in production? Perhaps production is already so overloaded - everything is slow.

however, you have given zero bits of information here. As in nothing. Nothing but nothing.


trace it, see what it is waiting on. Explain the difference between test and production as far as configuration and load goes. Explain what you are doing. Throw us some information.

Otherwise, I have a question for you: my car won't start - why not?

It's Not Just about Object Privileges

Robert Holmgren, July 02, 2012 - 3:07 pm UTC

I have encountered another scenario where it's possible to receive an "insufficient privileges" error message when executing a compiled PL/SQL object.

Our developers have written an application interface package to allow users to schedule jobs with calls to Oracle's DBMS_SCHEDULER API. The package they wrote gathers job-specific parameters from the user and calls DBMS_SCHEDULER with the parameters input by the user.

The schema-owner of the application's interface package had the CREATE JOB system privilege granted to it by a role, not directly granted. Whenever users executed the package, they received an error, "ORA-27486: insufficient privileges."

At first I though it was because the users were missing an object privilege in one of their default roles. It wasn't until the CREATE JOB system privilege was directly granted to the schema-owner of the application's interface package was the package able to be executed by other users.

I have experienced simliar problems with application packages written to interface with DBMS_DATAPUMP. Without the CREATE TABLE and CREATE TYPE system privilege being directly granted to the application's schema owner, users executing the package received an "ORA-31633: unable to create master table" error.

So it seems to me that not only do you have to have the required object privileges directly granted to the PL/SQL object owner, the owner may also have to have directly granted system privleges as well. The link provided by Mr. Kyte ( http://tkyte.blogspot.com/2009/10/httpasktomoraclecomtkyte.html ) and most of the Oracle documentation I've read only mention object privileges.

But what about Oracle Documentation

Jo Desmet, January 23, 2014 - 1:57 am UTC

I know I am misunderstanding the documentation. However http://docs.oracle.com/cd/B19306_01/network.102/b14266/authoriz.htm#sthref451 (Oracle(R) Database Security Guide, Chapter 5 - Authorization: Privileges, Roles, Profiles, and Resource Limitations) does state:

[quote]
Invoker's Rights
An invoker's rights procedure executes with all of the invoker's privileges. Roles are enabled unless the invoker's rights procedure was called directly or indirectly by a definer's rights procedure. A user of an invoker's rights procedure needs privileges (either directly or through a role) on objects that the procedure accesses through external references that are resolved in the invoker's schema.
[/quote]

I tested it so I get a failure with the usual "ORA-00942: table or view does not exist". So I know I have understood it wrong - it is not like documentation is going to be so flagrantly wrong.

Needs updating

Jane, July 06, 2017 - 7:34 pm UTC

Thank you for the very useful explanation!
Two points:
1) the link http://asktom.oracle.com/~tkyte/Misc/RolesAndProcedures.html is not working.
2) privileges can be granted to public rather than each individual, if it is OK for every user in the database to have the privilege; e.g., all our developers in the DEV database can SELECT any table or view in CERTAIN schemas (but not all), so we don't want to GRANT SELECT ANY TABLE, but we can grant select on the specific tables and views to PUBLIC.
Connor McDonald
July 07, 2017 - 2:26 am UTC

If you go to our Resource => Presentations and filter on "roles" you can get the file.

In Dev environments fine, but in a Production environment 'select any table' and grants to public should always be used with *extreme* care.


CBAC in 12.2

Rajeshwaran, Jeyabal, August 22, 2017 - 2:59 pm UTC

Team:

Was reading through Steven's column from the Online Oracle magazine.

http://www.oracle.com/technetwork/issue-archive/2017/17-jul/o47plsql-3807380.html

when tried that on my local instance got this error ( I am on 12.2 )

rajesh@ORA12C> create user demo2 identified by demo2;

User created.

rajesh@ORA12C> alter user demo2 default tablespace ts_data;

User altered.

rajesh@ORA12C> alter user demo2 quota unlimited on ts_data;

User altered.

rajesh@ORA12C> grant create session,create procedure to demo2;

Grant succeeded.

rajesh@ORA12C> conn demo2/demo2@ora12c
Connected.
demo2@ORA12C> create procedure create_table(p_tabnm varchar2)
  2  as begin
  3     execute immediate ' create table '||
  4             p_tabnm || ' (n number) ' ;
  5  end;
  6  /

Procedure created.

demo2@ORA12C> exec create_table('t');
BEGIN create_table('t'); END;

*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "DEMO2.CREATE_TABLE", line 3
ORA-06512: at line 1


demo2@ORA12C> conn rajesh/oracle@ora12c
Connected.
rajesh@ORA12C> create role r1;

Role created.

rajesh@ORA12C> grant create table to r1;

Grant succeeded.

rajesh@ORA12C> grant r1 to procedure demo2.create_table;
grant r1 to procedure demo2.create_table
                            *
ERROR at line 1:
ORA-28702: Program unit CREATE_TABLE is not owned by the grantor.


rajesh@ORA12C> desc demo2.create_table
PROCEDURE demo2.create_table
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 P_TABNM                        VARCHAR2                IN

rajesh@ORA12C>

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library