Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Les.

Asked: August 02, 2000 - 5:36 pm UTC

Last updated: October 25, 2005 - 6:55 am UTC

Version: 8.1.5

Viewed 10K+ times! This question is

You Asked

Good day Tom,

I have been trying to build a function for fine grained security,
and am having a stop of trouble.

The basic idea is as follows:

1. I have created two roles, one called ALL_ACCESS the other is
called PARENT_ACCESS. These roles have the alter session and create
session privledges (the get-by minimum for logging in).

2. I created 2 users, user1 has ALL_ACCESS, and user2 has PARENT_ACCESS
roles assigned respectivly.

3. My function does a select count(*) from user_role_privs
where granted_role = 'ALL_ACCESS'. The idea being that the
predicate returned will be determined by the role assigned.

My spot of trouble is when the function executes, to return a predicate
the count(*) of granted_role from user_role_privs is always 0 (zero).

When I execute the same select (without the into var_name of course)
from a SQL*PLUS session, the count returned is always the
number of roles assigned (I have logged on as both user1 and user2
to be sure that the select statement is correct).

It seems that for some reason (hopefully you will know what that
reason is ) that the function cannot select the count(*) from
user_role_privs.

My question is how do I get that function to return the number
of roles for the user?

Here is one of the select statements from the function:

select count(*) into assignedRole
from user_role_privs
where username = sys_context('userenv','session_user')
and granted_role = 'ALL_ACCESS';


I granted execute to public for the package.

I know the function is executing, becuase I put some debug
statements in it, that indicate it is executing (I used that
debug package by Christopher Beck that I got from one of your
articles on fine grained security.

I just know there is some silly thing I am doing (or not),
I just do not seem to be able to figure out what that could be.

Many thanks in advance Tom.

and Tom said...

Well, I've a couple of things to say for this one....

One is, if you haven't, you might want to read:
</code> http://asktom.oracle.com/~tkyte/article2/index.html <code>

That is a paper I've written on fine grained access control. One of the topics I have in there is "Handling Errors and debugging". In the debugging part (which is really hard with FGAC), I suggest using a "debug.f" routine we have. It lets you easily create trace files from PLSQL. I'm going to use it to demonstrate to you what is happening.

In a nutshell -- user_* views, when queried in PLSQL, always return the rows for the user who created the procedure (unless they are invokers rights but thats another story). So, by querying user_* views, you've effectively fixed the rows that can be returned at compile time. You need to query the DBA_* views in order to see anyones privs. In fact, since the dba_user_privs view performs pretty poorly, I'm going to suggest going directly after the underlying tables in this case (a rarity).

Lets start by writing a policy that simply dumps the user_roles_privs view, the dba_roles_privs for the current user and the modified query to get the roles. We'll time them as well. That policy is:

ops$tkyte@DEV8I.WORLD> create or replace
2 function my_security_function( p_schema in varchar2,
3 p_object in varchar2 )
4 return varchar2
5 as
6 l_start number;
7 begin
8 debug.f( 'User Role Privs' );
9 l_start := dbms_utility.get_time;
10 for x in ( select * from user_role_privs )
11 loop
12 debug.f( 'Username = "%s" Granted_Role = "%s"',
13 x.username, x.granted_role );
14 end loop;
15 debug.f( 'end, time = %d',
16 round( (dbms_utility.get_time-l_start)/100, 2 ) );
17
18 debug.f( 'Dba Role Privs' );
19 l_start := dbms_utility.get_time;
20 for x in ( select *
21 from dba_role_privs
22 where grantee =
23 sys_context('userenv','session_user') )
24 loop
25 debug.f( 'Username = "%s" Granted_Role = "%s"',
26 x.grantee, x.granted_role );
27 end loop;
28 debug.f( 'end, time = %d',
29 round( (dbms_utility.get_time-l_start)/100, 2 ) );
30
31 debug.f( 'Direct query' );
32 l_start := dbms_utility.get_time;
33 for x in ( select u1.name granted_role,u2.name grantee
34 from sys.user$ u1,
35 sys.user$ u2,
36 sys.sysauth$ sa
37 where u1.user# = sa.privilege#
38 and u2.user# = sa.grantee#
39 and u2.name =
40 sys_context('userenv','session_user') )
41 loop
42 debug.f( 'Username = "%s" Granted_Role = "%s"',
43 x.grantee, x.granted_role );
44 end loop;
45 debug.f( 'end, time = %d',
46 round( (dbms_utility.get_time-l_start)/100, 2 ) );
47
48
49 return '';
50 end;
51 /

Function created.

Now, lets create a table and attach this policy on it:

ops$tkyte@DEV8I.WORLD> drop table t;
Table dropped.

ops$tkyte@DEV8I.WORLD> create table t ( x int );
Table created.

ops$tkyte@DEV8I.WORLD> grant all on t to public;
Grant succeeded.

ops$tkyte@DEV8I.WORLD> begin
2 dbms_rls.add_policy
3 ( object_schema => user,
4 object_name => 'T',
5 policy_name => 'MY_POLICY',
6 function_schema => user,
7 policy_function => 'My_Security_Function',
8 statement_types => 'select, insert, update, delete' ,
9 update_check => TRUE );
10 end;
11 /

PL/SQL procedure successfully completed.

Now, lets set up to "trace". I'll enable tracing for all modules to the file /tmp/tkyte.dbg for the current user (me)

ops$tkyte@DEV8I.WORLD> host rm /tmp/tkyte.dbg
ops$tkyte@DEV8I.WORLD> exec debug.init( 'all', '/tmp/tkyte.dbg', USER );

PL/SQL procedure successfully completed.

ops$tkyte@DEV8I.WORLD> select * from t;

no rows selected

Now, lets see what we got:

ops$tkyte@DEV8I.WORLD> host cat /tmp/tkyte.dbg

User Role Privs
Username = "OPS$TKYTE" Granted_Role = "CONNECT"
Username = "OPS$TKYTE" Granted_Role = "DBA"
Username = "PUBLIC" Granted_Role = "IAP_USER"
Username = "PUBLIC" Granted_Role = "PLUSTRACE"
Username = "PUBLIC" Granted_Role = "SURVEY_USER"
Username = "PUBLIC" Granted_Role = "WWV_PALM_SYNC_ROLE"
end, time = 2.29

Dba Role Privs
Username = "OPS$TKYTE" Granted_Role = "CONNECT"
Username = "OPS$TKYTE" Granted_Role = "DBA"
end, time = 1.9

Direct query
Username = "OPS$TKYTE" Granted_Role = "CONNECT"
Username = "OPS$TKYTE" Granted_Role = "DBA"
end, time = .01

That looks OK so far -- user_role_privs and dba_role_privs agree with each other so far. But thats ONLY because the currently logged in user = the owner of the procedure.

I would like to point out that in my database I have over 30k users created. The performance of the *_role_privs view is not very good in my case. It takes far too long to get the answer. The last query, directly against the SYS tables is very very fast however...

Now, lets try this as another user and you'll see where you went wrong...


ops$tkyte@DEV8I.WORLD> connect scott/tiger
Connected.
ops$tkyte@DEV8I.WORLD> @login

scott@DEV8I.WORLD> host rm /tmp/scott.dbg

scott@DEV8I.WORLD> exec debug.init( 'all', '/tmp/scott.dbg', USER );

PL/SQL procedure successfully completed.

scott@DEV8I.WORLD> select * from ops$tkyte.t;

no rows selected

scott@DEV8I.WORLD> host cat /tmp/scott.dbg

User Role Privs
Username = "OPS$TKYTE" Granted_Role = "CONNECT"
Username = "OPS$TKYTE" Granted_Role = "DBA"
Username = "PUBLIC" Granted_Role = "IAP_USER"
Username = "PUBLIC" Granted_Role = "PLUSTRACE"
Username = "PUBLIC" Granted_Role = "SURVEY_USER"
Username = "PUBLIC" Granted_Role = "WWV_PALM_SYNC_ROLE"
end, time = 2.33

Notice how the username above is still OPS$TKYTE -- thats cause the USER_* view in a procedure will return the data for the owner of the procedure (except with invokers rights..)

Dba Role Privs
Username = "SCOTT" Granted_Role = "CONNECT"
Username = "SCOTT" Granted_Role = "DBA"
Username = "SCOTT" Granted_Role = "RESOURCE"
end, time = 1.89

But dba role privs gives us the correct info (what we wanted) albeit slowly...

Direct query
Username = "SCOTT" Granted_Role = "CONNECT"
Username = "SCOTT" Granted_Role = "RESOURCE"
Username = "SCOTT" Granted_Role = "DBA"
end, time = .03

The direct query gives us what we want but fast...

scott@DEV8I.WORLD>




Rating

  (5 ratings)

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

Comments

Difference between sys_context('userenv','session_user') & user

Tony, April 17, 2003 - 3:39 pm UTC

Tom,

Will the below variables always have the same value ?

l_user1 := sys_context('userenv','session_user');
l_user2 := user;

Thanks !

Tom Kyte
April 17, 2003 - 6:03 pm UTC

suppose you create a trigger like this:

drop table log;
drop trigger ddl_trigger;
create table log
( uname varchar2(30),
session_user varchar2(30)
)
/

create or replace trigger ddl_trigger
before create or alter or drop
on database
declare
begin
insert into log
( uname, session_user )
values
( user, sys_context('userenv','session_user') );
end;
/



and do a full database import into that database. you'll discover USER is sys and sys_context('session_user') is a bunch of different names.

full imports use the "become user" priv to switch identities mid stream.

don't know of any "normal" cases (including ntier proxy) that would do this tho

Tony, April 17, 2003 - 6:38 pm UTC

So what should I use if I want a procedure to insert the user name in a table for the rows created ?

Say if I connect as scott to the database, will the above assignments anytime give me different values ? If yes, when ???

Tom Kyte
April 17, 2003 - 7:18 pm UTC

(it is best to ask the question you meant, rather then something else -- you get your answers faster that way)

does scott have become user? does scott do a full database import? does your trigger exist under such circumstances?


I would just default the column to USER, create a view that selects all but that column -- have the applications use that view.

Security?

Tom, December 19, 2003 - 7:32 am UTC

Tom,

Are there any security issues we should be aware of if we create a view based on your query and grant this to the role used by users connecting through the web. The grants I need to be able to do this are

grant select on sysauth$ to <code owner> with grant option;
grant select on user$ to <code owner> with grant option;

I assume that granting select on these tables is fine as long as we don't grant update, insert or delete!



Tom Kyte
December 19, 2003 - 7:53 am UTC

correct.

"security issues" are such that "code owner" has access to this info and may convey access to this info (eg: security issues are "yourself", is code owner protected enough to allow this access)

Selecting dba_role_privs

Kamal, October 25, 2005 - 6:22 am UTC 

Hi Tom,

I faced a strange issue and i want some clarification on this.I granted DBA privelege to Scott


SQL> select granted_role from dba_role_privs where grantee = 'SCOTT';

GRANTED_ROLE
---------------------------------------------------------------------------

DBA
CONNECT
RESOURCE

Now i was able to query the dba_role_privs table.

SQL> select count(*) from dba_role_privs;

  COUNT(*)
----------
       113

But i try to query this table dba_role_privs from a Procedure, it is saying that table does not exist. I need to grant a Explicit Select to make it work.. what is the reason behind this

Tom Kyte
October 25, 2005 - 6:55 am UTC

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

Excellent

Paul, May 31, 2007 - 10:28 am UTC

I've just come across this very issue and Ask Tom has come to my rescue again, thanks.

How do you get the knowledge for all these answers Tom?


more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Security

All of the vital components for a secure database are covered in the Security guide.