Home » SQL & PL/SQL » SQL & PL/SQL » DBA_ ,USER_ views and procedure calls (Oracle 10g)
DBA_ ,USER_ views and procedure calls [message #346931] Wed, 10 September 2008 02:25 Go to next message
didiera
Messages: 134
Registered: August 2007
Location: Mauritius
Senior Member
Hello everyone,

This message is more of a sharing of experience with you. I have happen to witness something that might look trivial to the more experienced of you. It has to do with the context under which a procedure called from another schema might sometimes outwit the expected result.

Some Theory First:
Oracle 10g comes with a number of system tables and views that store meta-data. Data about data, yeah that's right. This means that Oracle itself uses oracle tables and views to store data about other tables and objects that are part of any database.

Two main sets of system tables are recognsied:

Dictionnary views and Dynamic Performance views. The former is the one which we will refer to.They are prefixed with either of these: DBA_ , ALL_ and USER_

What these prefixes stand for?

DBA_
These views contain information about objects database-wide.


ALL_
Views prefixed this way will contain data about all objects pertaining to the current schema or these for which some privilege has been granted to the current schema (select|insert|update|delete|execute...).

USER_
These views only contain information about objects owned and pertaining to the current schema.

Schema refers to any database user actually (e.g SCOTT is a schema in Oracle)


The Plot:
I have written a procedure in a given schema, say SCOTT for instance. I use the procedure to see if the caller has got some specific role granted to perform some action, say the role is COACH.

Some important thing before we go any further, how my procedure achieves this query is by selecting granted_role from user_role_privs and that is where everything originates.

So Once I completed the procedure and compiled it, I was eager to go further with testing. I first created a new user, say DIDIERA to whom I gave the EXECUTE right on my procedure (in SCOTT schema). However, when I made a call to the latter from schema DIDIERA, it did not behaved the way I expected.

Diagnosis
I modified the procedure to write some logs to a table and noticed that the logs were the ones as if the procedure was running as SCOTT in fact though I first believed that querying from user_role_privs would have beed bound to the calling user's schema(DIDIERA) rather than the executing caller's schema (SCOTT) but no.

Cure
To counter this issue, I had to modfy my procedure to query from dba_role_privs rather based on the grantee name. Why? Well just because in dba_role_privs, I have role allocation database-wide while in user_role_privs, i'm only restricted to the schema to which the procedure belongs.


Though it looked weird to me and hard to deduce, when we thing it over it somehow makes sense. So you guys be careful out there.

cheers,
Didier


Re: DBA_ ,USER_ views and procedure calls [message #346936 is a reply to message #346931] Wed, 10 September 2008 02:36 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is the expected behaviour.
By default procedure run in the schema of the procedure owner and with its privileges.
If you want to execute in the environment of caller you have to give the option "AUTHID CURRENT_USER" on procedure creation.

Details in:
PL/SQL User's Guide and Reference
Application Developer's Guide - Fundamentals
SQL Reference


Regards.
Michel
Re: DBA_ ,USER_ views and procedure calls [message #346942 is a reply to message #346931] Wed, 10 September 2008 02:44 Go to previous messageGo to next message
Littlefoot
Messages: 20888
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Perhaps you should read more about "definer and invoker rights" (search for it!).

Here's an example which uses the AUTHID clause (so that you shouldn't have to query DBA_ view).

A procedure has been created in Scott's schema:
SQL> show user
USER is "SCOTT"
SQL> select * from user_role_privs where granted_role = 'MY_ROLE';

USERNAME                       GRANTED_ROLE                   ADM DEF OS_
------------------------------ ------------------------------ --- --- ---
SCOTT                          MY_ROLE                        YES NO  NO

SQL> create or replace procedure prc_role
  2    authid current_user
  3  is
  4    l_role varchar2(1);
  5
  6  begin
  7    select 'x' into l_role
  8      from user_role_privs
  9      where granted_role = 'MY_ROLE';
 10
 11    dbms_output.put_line('my_role has been granted to this user');
 12
 13  exception
 14    when no_data_found then
 15       dbms_output.put_line('my_role has NOT been granted to this user');
 16  end;
 17  /

Procedure created.

SQL> set serveroutput on
SQL> exec prc_role;
my_role has been granted to this user

PL/SQL procedure successfully completed.

SQL> grant execute on prc_role to mike;

Grant succeeded.

Now, let us switch over to Mike:
SQL> connect mike/lion@ora10
Connected.
SQL> select * from user_role_privs where granted_role = 'MY_ROLE';

no rows selected

SQL> set serveroutput on
SQL> exec scott.prc_role;
my_role has NOT been granted to this user

PL/SQL procedure successfully completed.

SQL>



P.S. While I was creating an example, Michel has already said what should be said.
P.P.S. BTW, nice message you have posted, Didier; a little piece of art.

[Updated on: Wed, 10 September 2008 02:49]

Report message to a moderator

icon14.gif  Re: DBA_ ,USER_ views and procedure calls [message #346978 is a reply to message #346942] Wed, 10 September 2008 05:17 Go to previous message
didiera
Messages: 134
Registered: August 2007
Location: Mauritius
Senior Member
Hello again,

thanks for the valuable tip, the authid current_user option suits me very well.

regards,
Didier
Previous Topic: Delete with commit scope
Next Topic: ORA-22905 Error in a PIPELINED function
Goto Forum:
  


Current Time: Sat Dec 03 06:02:29 CST 2016

Total time taken to generate the page: 0.11801 seconds