Hi, Tom,

When i run:

select u2.name granted_role
from ( select *
from sys.sysauth$
connect by prior privilege# = grantee#
start with grantee# = uid or grantee# = 1) sa,
sys.user$ u2
where u2.user#=sa.privilege#
union all select user from dual
union all select 'PUBLIC' from dual;

every thing is Ok, but it gave me error as long as i run:

create view user_role_hierarchy
as
select u2.name granted_role
from ( select *
from sys.sysauth$
connect by prior privilege# = grantee#
start with grantee# = uid or grantee# = 1) sa,
sys.user$ u2
where u2.user#=sa.privilege#
union all select user from dual
union all select 'PUBLIC' from dual

the error is:

from sys.sysauth$
*
ERROR at line 4:
ORA-00942: table or view does not exist

Does something wrong?

Thanks

As an Infosec Officer I very often control right in ower DBs and this help me a lot to do my work.
Thanks.

Is there any easy way to do this for the entire instance? That is, list out all users, their roles, and their privileges, not just for one user?

Tom:

Does the DBA role mean that this account has DBA privilege?  If not how do you find out if an account has a DBA privilege? Is this error because I am not logged in as SYS?

SQL> grant select on user_role_hierarchy to public;
grant select on user_role_hierarchy to public
                *
ERROR at line 1:
ORA-01720: grant option does not exist for 'SYS.USER$'

SQL> select * from user_role_hierarchy;

GRANTED_ROLE
------------------------------
CONNECT
RESOURCE
DBA
SELECT_CATALOG_ROLE
HS_ADMIN_ROLE
EXECUTE_CATALOG_ROLE
HS_ADMIN_ROLE
DELETE_CATALOG_ROLE
EXP_FULL_DATABASE
SELECT_CATALOG_ROLE
HS_ADMIN_ROLE
EXECUTE_CATALOG_ROLE
HS_ADMIN_ROLE
IMP_FULL_DATABASE
SELECT_CATALOG_ROLE
HS_ADMIN_ROLE
EXECUTE_CATALOG_ROLE
HS_ADMIN_ROLE
PCSADMIN
PUBLIC

20 rows selected.
 

Tom:

well i can not log in as sys as a developer. But is this the correct way of determining if this account has dba privilege? is that the DBA role that defines it?

"
following lists all of your roles for you (might list some N times)...
"

Is it possible to modify this, to get similar
results for any given user other than logged on user

Thanks

Why "grantee# = 1" in the query. I queried the dba_users table to find out which user had the user_id of 1 but I couldn't see any.

Thanks

Why does oracle not cascade the effects of system privs when it is revoked and the same is not true with revoking object privs as cascading effect is there with object privs? Is there any logic or rational behind this implementation.

Thanks.

If I granted a system priv to userX with admin option and userY grants that priv to UserZ. Now if i drop userY, what happens to the priv that was granted to UserZ by UserY? I was asked this question but did not know the answer. Could you help? Also, if the same is done with object priv with grant option, what Oracle would do if I dropped userY? Thanks. You are amazing. I enjoy reading your site almost everyday.

<b>system privs persist -- things granted via the WITH ADMIN OPTION persist.  object privs are retracted -- things granted via the WITH GRANT OPTION go away.</b>

Consider -- A and B:

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

ops$tkyte@ORA920> create user a identified by a;
User created.

ops$tkyte@ORA920> create user b identified by b;
User created.

ops$tkyte@ORA920> grant select on t to a <b>with grant option;</b>
Grant succeeded.

ops$tkyte@ORA920> grant create view to a <b>with admin option;</b>
Grant succeeded.

ops$tkyte@ORA920> grant create session to a;
Grant succeeded.

ops$tkyte@ORA920> grant create session to b;
Grant succeeded.

ops$tkyte@ORA920> @connect a/a

a@ORA920> grant select on ops$tkyte.t to b;
Grant succeeded.

a@ORA920> grant create view to b;
Grant succeeded.

a@ORA920> @connect b/b

b@ORA920> desc ops$tkyte.t
 Name                                Null?    Type
 ----------------------------------- -------- ------------------------
 X                                            NUMBER(38)

b@ORA920> create view v1 as select * from dual;

View created.

<b>so, B can see ops$tkyte.t and B can create views...  now we'll drop A</b>


b@ORA920> @connect /

ops$tkyte@ORA920> drop user a;
User dropped.

ops$tkyte@ORA920> @connect b/b

b@ORA920> desc ops$tkyte.t
ERROR:
ORA-04043: object ops$tkyte.t does not exist


b@ORA920> create view v2 as select * from dual;

View created.

<b>B can no longer see ops$tkyte.t but B can still create views</b>

b@ORA920>
 

Since this thread is on Roles/privileges and accessibility, please do read Metalink doc# 9368.996 before using some of the system privileges like "create any view/procedure"...etc.

Thanks

Hi Tom,

Can we grant select privileges on certain column of a table ?

for eg. I have table x with column a,b
I want to grant select on only column a to public.

How can I do that?

Thanks

Tom,

I am using Oracle 8i (8.1.5). When I connect as
conn sys/oracle as sysoper......
it shows connected to an ideal instance.
but the database is already open. Also where are these roles(Sysdba and Sysoper) listed.
and why are they called as roles. Becoz, normally we don't login as conn user/pwd as role.

pl. clarify. thanks in adv.

When I wrote ..........startup
it says ... can not start already running oracle.

Can U try this on your machine.

thanks ...

How to get information about a role wrt roles and other privileges assigned to it? If a role is assigned to a role then just its name is okay, no recursion needed.

Which views/tables to query to get this info?

Tom

We have a table T, it has a post insert trigger post_insert_T_trigger. The trigger calls a package P, to insert into another table TI.

Logged in as user1, when I inserted rows into the table, the trigger did not fire( this was evident from the fact that ti was not getting populated).

But when I logged in as user2, and inserted rows into T, the trigger fired and TI got populated.

1.Now I want to know, what can be the different reasons due to which user1 was not able to fire the trigger.

2.How can we find out whether user1 has execute privilege on the trigger T. Which V$view gives that information.

If privileges are being given via a role, how can we find out which role has the respective privilege, and whether user1 has the role assigned. If the role is assigned to user1, then to check and see if the execute privilege on trigger T is not assiged to user1.

How can I verify if I have privilege to insert into the table.

RE: How can I verify

Have a look at user_tab_privs.

HTH,

Mark

Is there a way to find out "when" a grant is created or modified? I have looked into USER_TAB_PRIVS and USER_OBJECTS...

Is there anything special about granting select user_tables to a role?
>GRANT SELECT ON USER_TABLES TO my_role;

But when I log as my username that has been granted that role I can't see anything in user_tables?

1.I want to know the privileges assigned to a user on a particular object.

2.I want to know all the roles assigned to a particular user.

3.I want to see all the roles assigned to a particular role.

Please show the best way to achieve the above 3.

Hello Tom,

I'm trying to get a list of usernames base on the dba_users table with their privileges
I'm using:
select u.username, p.privilege, p.admin_option
from dba_sys_privs p, dba_users u
where p.username = p.grantee;

But I'm not able to get all of them, there are about 80 usernames on the dba_users table that are not showing up. In the other hand if I use "p.username = p.grantee(+)" I won't shows blank space under the privileges column. Would please advice or point out what I'm doing wrong or what needs to be modified in order to get this right. Thanks.

Oracle 9i R2
AIX 5.2

Cheers
Alf

Hi Tom,

Thanks for your help.

Please allow me to correct errors for the where clause of my original listing, it should has been:
u.username = p.grantee(+); instead of p.username = p.grantee

However I modified it to include the full outer join:
select u.username, p.privilege, p.admin_option
from dba_sys_privs p full outer join dba_users u on (u.username = p.grantee)
where u.username = p.grantee;

But it wouldn't work, I get disconnect from the DB (I'm trying this statement from TOAD) I can’t catch what I’m doing wrong here!

In the other hand, I decided to change this statement to:
select u.username, pr.privilege
from dba_users u,
dba_role_privs r,
dba_sys_privs pr
where r.grantee = pr.grantee and
u.username = u.username

Although this one worked and returned results as expect. I’m wondering if there this is a good way to accomplish this.
Any suggestions would be greatly appreciated, thanks.

Hi Tom,

This is my goal - spool into a csv file all the object and system privileges granted (even ones through roles) to every user in the database (except for system and sys).

I found this page (the query at the very top) and various other pages on your site to figure out how to write data out to a file (utl_file).

I am a dba 'learning' to code with pl/sql. Can you please look at this code to make sure I am not doing anything stupid? I ran this against my mini-test db of 4 users (9.2.0.5 oracle on solaris 5.9) and it does the job. I do plan to run this against my 8i and 9i production dbs(which have on average, 50 users).

Additional Questions -
1. As you can see, when marching through the roles for a specific user, I query for the object privs and system privs for every role rather than doing one obj priv query with all the roles in the predicate (ie. "where grantee in ('...','...')... ) Reason is because I do not know how to dynamically create the predicates. I searched this site and I came across "ref_cursors" and "dynamic sql" (dbms_sql). Which one should I use? Or should I bother?
2. I found in your expert 1:1 book in the appendix for utl_file, a section for 'exceptions'. Where would I stick this in my code (for good practice)? Is it between "utl_file.fclose" and "end"?

Here's my code --
create or replace procedure lkup_role3 is

type user_record_type is record
(id number,
name varchar2(30));

type priv_record_type is record
(privilege varchar2(40),
owner varchar2(30),
table_name varchar2(30));

v_user user_record_type;
v_role varchar2(30);
v_objpriv_rec priv_record_type;
v_syspriv varchar2(40);


l_output utl_file.file_type;
l_line long;

cursor c_allusers is
select user#, name from sys.user$
where type# = 1 and name not in ('SYSTEM', 'SYS');

cursor c_usr_role is
select u2.name granted_role
from ( select * from sys.sysauth$
connect by prior privilege# = grantee#
start with grantee# = v_user.id or grantee# = 1) sa,
sys.user$ u2
where u2.user#=sa.privilege#
union all select v_user.name from dual;

cursor c_objprivs is
select distinct PRIVILEGE, owner, table_name
from dba_tab_privs
where grantee = v_role;

cursor c_sysprivs is
select distinct PRIVILEGE
from dba_sys_privs
where grantee = v_role;

begin
l_output := utl_file.fopen ( '/space3/temp/utlfile_out' , 'priv.csv', 'a', 32760);

-- Find all the users, except for sys and system, that exist in the database.
open c_allusers;
loop
-- Loop through all the users and get their assigned roles
fetch c_allusers into v_user;
exit when c_allusers%NOTFOUND;
open c_usr_role;
loop
fetch c_usr_role into v_role;
exit when c_usr_role%NOTFOUND;

-- For a specific role, look up all the object privs
open c_objprivs;
loop
fetch c_objprivs into v_objpriv_rec;
exit when c_objprivs%NOTFOUND;
l_line := v_user.name ||','|| v_role ||','|| v_objpriv_rec.privilege ||','|| v_objpriv_rec.owner ||','|| v_objpriv_rec.table_name;
utl_file.put_line(l_output, l_line);
end loop;
close c_objprivs;

-- For a specific role, look up all the system privs
open c_sysprivs;
loop
fetch c_sysprivs into v_syspriv;
exit when c_sysprivs%NOTFOUND;
l_line := v_user.name ||','|| v_role ||','|| v_syspriv;
utl_file.put_line(l_output, l_line);
end loop;
close c_sysprivs;
end loop;
close c_usr_role;
end loop;
close c_allusers;
utl_file.fclose(l_output);

end;


Thank you!

thank you --tom!

I want to get the same info (all roles granted to a user) but for a user other than the one querying the view. This is for developers to check they have granted the appropriate roles to users.
I created a similar view based on yours that shows (granted_role,grantee) for all roles granted with admin option to the querying user.
What bothers me is that this view is pretty much what I would have expected to find (even though not recursive) as a native all_role_privs (which unlike other dba/all/user views doesn't exist). Is my approach good, or is there any other way intended to do this?
Thanks a 10^6

Thanks for answering! I'm not sure I was clear enough. My reasoning was:
Premises
- Most such views come in user, all and dba flavours. These seem to be aimed at showing a users' own records, a broader set of records somehow restricted to what the user should be allowed to see based on his grants, and all the records.
- This one (role privs) lacks the all version. Odd.
- Stranger still because there seems to be no way for a user to query which roles he has granted to other users. It would be reasonable for the DB to let you access something you created (the grant). It does with your tables, you have admin option on roles you created, and so on.

If these three assumptions were right, the only conclusion I could come up with was that the all_role_privs had been skipped deliberately, and for a reason strong enough to outweigh the incovenience of not being able to query roles you granted. If the designers actually had a purpose for not defining the view (a security reason was my concern) it'd be a bad idea for me to thwart it by creating my own one ...

Or (likely) my assumptions could be wrong. Can you enlighten me?
Terathanks!

ROLES are not owned by any users even if they are created by them. Like DB links, they are owned by SYS objects.

That could be one of the reasons for merging all_role_privs with user_role_privs.

Not sure I follow you both. I guess we have in mind different definitions for the missing all_role_privs. SQL may help me show you my problem:
<SQL_example>
SQL> conn /as sysdba
Connected.
SQL> create user developer identified by developer 
  2  default tablespace usuarios temporary tablespace temp
  3  quota 1 M on usuarios;

User created.

SQL> grant create session, create table, create role to developer;
 
Grant succeeded.

SQL> conn developer/developer
Connected.
SQL> create table t (x number(1));

Table created.

SQL> create role my_role;
 
Role created.
 
SQL> grant select on t to my_role;
 
Grant succeeded.
 
SQL> grant my_role to user1;
 
Grant succeeded.

SQL> select * from user_role_privs;
 
USERNAME        GRANTED_ROLE    ADM DEF OS_
--------------- --------------- --- --- ---
DEVELOPER       MY_ROLE         YES YES NO

</SQL_example>
Now say you're developer and user1 calls reporting an error related to table t, so you want to make sure you granted him my_role. What do you do to check? User_role_privs won't help.

Well, what happened in real life was developer asked me to grant him select on dba_role_privs. Then I thought of creating this view:

create or replace view all_role_privs
as
select * from dba_role_privs
where granted_role in (
    select granted_role from user_roles
    where admin_option = 'YES'
)
/

where user_roles is a recursive view like user_role_hierarchy but augmented to show the admin_option column as well. I thought this definition of all_role_privs would match the user/all/dba philosophy shared by the rest of the views ...

Anyway, what would be the intended way to solve developer's problem? If none, what do you think of the view instead of a grant on dba_role_privs from a security perspective?

Thanks very much indeed for your time and help. 

Tom,

1) When an Forms/any other application is running, is it possible to know what roles current application has got enabled?

2) Logged on as a different user, is it possible to know what the CURRENT_USER is for a different session that is currently executing a program?

Thanks

Which column in V$session should be used to get CURRENT_USER of a session, is there something for SESSION_USER as well?

Tom,

can you comment on:

</code> http://www.petefinnigan.com/tools.htm <code>--> find_all_privs.sql

Because of historical reasons I belive on things provided by you or by oracle.

Thanks for such a wonderful activity of helping a newbie like me...

I want to get all user in oracle DB
all roles in the DB?

Also how can i get all roles assigned to a user?

can u help me?

I have installed windows xp and oracle 9i
How many authentication mode are supported in oracle?
I want to add a user like machinename\username?
How to add this?

Also can i add a computer in a domianlike in MSSQL

like domainname\username?

Please help me out.....

read:

but in general, having sqlnet.authentication_services=(nts) in your sqlnet.ora and then:

create user "OPS$XP10GR1\TKYTE" identified externally;
             ^^^^^^^^^^^ domain
                         ^^^^^ username 

How to get all users of a role?

I have installed oracle 9i on window XP. I have added a user as OPS$DOMAIN\USERNAME to the server.
Now i want to check that this user has access to the database.
How do i do this?
I know that oracle 9i shud be on windows 2000 ,but does it wrk on window XP?

I have started the NTLM servicer from administrative tools.
Please any help is welcome.

Hi,

Take care that the domain name may or may not be included in Oracle user name depending on the data (TRUE/FALSE) of the OSAUTH_PREFIX_DOMAIN value in HKLM\Software\Oracle\HOMEx key.

Regards
Michel

These are the steps that i have follwed

1. Create a user that is a windows user in my domain.
Create user ops$domain\username identified externally.

2.My DB server is on win XP.I started the ntlm service there.

3. I tried connecting using c:> sqlplus \
4.It said TNS adapter error and prompted for username.

I tried locating the key in HKLM\Software\Oracle\HOMEx key but cud not find it.

Where am i goin wrong?

I have created a user in the default db after installation.
If i create a new db, and i want to provide access to user in default db to this db, how to do this?

I have user
grant link database dbname to user identified by password;
but itz giving me error

Suppose i have a user amit.
I have assigned roles to it.
How to list all roles assigned to amit?

I have oracle 9i on Windows XP.
I want to add a user to it that will be authenticated by OS.
I create a user through OEM that will be identified externally.

Then i start the NTLM service on my machine.The sqlnet.ora has SQLNET.AUTHENTICATION_SERVICES= (NTS)

Now i use the enterprise security manager tool to connect to the server using native authentication options....

But it gives me directory authentication failed?
Can u just list down the procedure to add and test windows authenticated user?

I have made a connection to oracle DB by using thin driver.
But if i want to have secure JDBC transactions then is there any parameter that i need to add in url

"jdbc:oracle:thin:@oracle.jdbc.driver.OracleDriver"

like ssl=require...
what are the configurations that are needed to be done for it lke installing ssl certificates and all
Help me out

I have a user A who has select any table system privilege granted through a role.This user can basically see all the
tables in all the Schemas in the Oracle instance.Now we need to block this user from seeing just one table..Is there a way to do it??
Thanks

So..there is no specific way to just block access for this table??your explanation makes sense..i was wondering if there is
any quick/easy way out as this is a one time problem we are facing?

Is it? (find/fined) :))

-- Description : Use this script to find which privileges have been granted to a
-- particular user. This scripts lists ROLES, SYSTEM privileges
-- and object privileges granted to a user. If a ROLE is found
-- then it is checked recursively.
--
-- The output can be directed to either the screen via dbms_output
-- or to a file via utl_file. The method is decided at run time
-- by choosing either 'S' for screen or 'F' for File. If File is
-- chosen then a filename and output directory are needed. The
-- output directory needs to be enabled via utl_file_dir prior to
-- 9iR2 and a directory object after.

whenever sqlerror exit rollback
set arraysize 1
set space 1
set verify off
set pages 25
set lines 90
set termout on
clear screen
set feed off
set head off
set echo off
set serveroutput on size 1000000
col system_date noprint new_value val_system_date
select to_char(sysdate,'YYYYMMDD') system_date from sys.dual;

undefine user_to_find

prompt
accept user_to_find char prompt 'NAME OF USER TO CHECK : '

/* Creates a temporary view to get the list of ROLES recursivly for a user*/
create or replace view Privileges_Grant_user
(GRANTEE#,PRIVILEGE#,SEQUENCE#,LEV_EL) as
SELECT GRANTEE#,PRIVILEGE#,SEQUENCE#,LEVEL
from sys.sysauth$
connect by prior privilege# = grantee#
start with grantee# = (select USER# from user$ where name = upper('&&user_to_find'));

/* list the roles Hierarchy */

spool find_all_roles_privs_&val_system_date..log;

prompt *********************************************************
prompt List of roles Hierarchically Granted to &user_to_find
prompt *********************************************************
select lpad( ' ', 6 * ( lev_el - 1 ) ) || u2.name "Role Name"
from Privileges_Grant_user v ,
sys.user$ u2
where u2.user#=v.privilege#;
prompt
drop view Privileges_Grant_user;

declare
--
lv_tabs number:=0;

procedure write_op (pv_str in varchar2) is
begin
dbms_output.put_line(pv_str);
exception
when others then
dbms_output.put_line('ERROR (write_op) => '||sqlcode);
dbms_output.put_line('MSG (write_op) => '||sqlerrm);

end write_op;
--
procedure get_privs (pv_grantee in varchar2,lv_tabstop in out number) is
--
lv_tab varchar2(50):='';
lv_loop number;
--
cursor c_main (cp_grantee in varchar2) is
select 'ROLE' typ,
grantee grantee,
granted_role priv,
admin_option ad,
'--' tabnm,
'--' colnm,
'--' owner
from dba_role_privs
where grantee=cp_grantee and
GRANTED_ROLE NOT IN ('CONNECT','SELECT_CATALOG_ROLE','EXECUTE_CATALOG_ROLE',
'RESOURCE','DBA','IMP_FULL_DATABASE','EXP_FULL_DATABASE','AQ_ADMINISTRATOR_ROLE')
union
select 'SYSTEM' typ,
grantee grantee,
privilege priv,
admin_option ad,
'--' tabnm,
'--' colnm,
'--' owner
from dba_sys_privs
where grantee=cp_grantee
union
select 'TABLE' typ,
grantee grantee,
privilege priv,
grantable ad,
table_name tabnm,
'--' colnm,
owner owner
from dba_tab_privs
where grantee=cp_grantee
union
select 'COLUMN' typ,
grantee grantee,
privilege priv,
grantable ad,
table_name tabnm,
column_name colnm,
owner owner
from dba_col_privs
where grantee=cp_grantee
order by 1;
begin
lv_tabstop:=lv_tabstop+1;
for lv_loop in 1..lv_tabstop loop
lv_tab:=lv_tab||chr(9);
end loop;
for lv_main in c_main(pv_grantee) loop
if lv_main.typ='ROLE' then
write_op(lv_tab||'ROLE => '
||lv_main.priv||' which contains =>');
get_privs(lv_main.priv,lv_tabstop);
elsif lv_main.typ='SYSTEM' then
write_op(lv_tab||'SYS PRIV => '
||lv_main.priv
||' grantable => '||lv_main.ad);
elsif lv_main.typ='TABLE' then
write_op(lv_tab||'TABLE PRIV => '
||lv_main.priv
||' object => '
||lv_main.owner||'.'||lv_main.tabnm
||' grantable => '||lv_main.ad);
elsif lv_main.typ='COLUMN' then
write_op(lv_tab||'COL PRIV => '
||lv_main.priv
||' object => '||lv_main.tabnm
||' column_name => '
||lv_main.owner||'.'||lv_main.colnm
||' grantable => '||lv_main.ad);
end if;
end loop;
lv_tabstop:=lv_tabstop-1;
lv_tab:='';
exception
when others then
dbms_output.put_line('ERROR (get_privs) => '||sqlcode);
dbms_output.put_line('MSG (get_privs) => '||sqlerrm);
end get_privs;

begin
write_op('User => '||upper('&&user_to_find')||' has been granted the following privileges');
write_op('*********************************************************');
get_privs(upper('&&user_to_find'),lv_tabs);
exception
when others then
dbms_output.put_line('ERROR (main) => '||sqlcode);
dbms_output.put_line('MSG (main) => '||sqlerrm);

end;
/
prompt
prompt ********************************************************
prompt End of the report
prompt ********************************************************
spool off
whenever sqlerror continue

The out put of the above report:
*********************************************************
List of roles Hierarchically Granted to mtest
*********************************************************

CONNECT
RESOURCE
ROLE_PARENT
ROLE_CHILD01
ROLE_CHILD02
ROLE_OTHER
ROLE_OTHER_CHILD01

User => MTEST has been granted the following privileges
*********************************************************
ROLE => ROLE_OTHER which contains =>
ROLE => ROLE_OTHER_CHILD01 which contains =>
SYS PRIV => SELECT ANY TABLE grantable => NO
SYS PRIV => CREATE ANY TABLE grantable => NO
TABLE PRIV => SELECT object => SLS.STGNG_TOPPROSPECTSCORES grantable => NO
TABLE PRIV => SELECT object => SLS.STGNG_TOPPROSPECTSCORES_EMP grantable => NO
TABLE PRIV => SELECT object => SLS.STGNG_TOPPROSPECTSCORES_LOC grantable => NO
TABLE PRIV => SELECT object => SLS.STGNG_TOPPROSPECTSCORES_SAL grantable => NO
ROLE => ROLE_PARENT which contains =>
ROLE => ROLE_CHILD01 which contains =>
ROLE => ROLE_CHILD02 which contains =>
TABLE PRIV => ALTER object => MTEST.EDS grantable => NO
TABLE PRIV => DEBUG object => MTEST.EDS grantable => NO
TABLE PRIV => DELETE object => MTEST.EDS grantable => NO
TABLE PRIV => FLASHBACK object => MTEST.EDS grantable => NO
TABLE PRIV => INSERT object => MTEST.EDS grantable => NO
TABLE PRIV => ON COMMIT REFRESH object => MTEST.EDS grantable => NO
TABLE PRIV => QUERY REWRITE object => MTEST.EDS grantable => NO
TABLE PRIV => SELECT object => MTEST.EDS grantable => NO
TABLE PRIV => UPDATE object => MTEST.EDS grantable => NO
TABLE PRIV => INSERT object => MTEST.WORK grantable => NO
TABLE PRIV => SELECT object => MTEST.WORK grantable => NO
TABLE PRIV => UPDATE object => MTEST.SALES grantable => NO
TABLE PRIV => SELECT object => MTEST.ABCD grantable => NO
TABLE PRIV => UPDATE object => MTEST.EMP grantable => NO
SYS PRIV => CREATE SESSION grantable => NO
SYS PRIV => UNLIMITED TABLESPACE grantable => NO
TABLE PRIV => ALTER object => ENT.XEROX_ESTAB grantable => NO
TABLE PRIV => ALTER object => ENT.XEROX_INDUSTRY_SEGMENT grantable => NO
TABLE PRIV => DEBUG object => ENT.XEROX_ESTAB grantable => NO
TABLE PRIV => DEBUG object => ENT.XEROX_INDUSTRY_SEGMENT grantable => NO
TABLE PRIV => DELETE object => ENT.XEROX_ESTAB grantable => NO
TABLE PRIV => DELETE object => ENT.XEROX_INDUSTRY_SEGMENT grantable => NO
TABLE PRIV => FLASHBACK object => ENT.XEROX_ESTAB grantable => NO
TABLE PRIV => FLASHBACK object => ENT.XEROX_INDUSTRY_SEGMENT grantable => NO
TABLE PRIV => INDEX object => ENT.XEROX_ESTAB grantable => NO
TABLE PRIV => INDEX object => ENT.XEROX_INDUSTRY_SEGMENT grantable => NO
TABLE PRIV => INSERT object => ENT.XEROX_ESTAB grantable => NO
TABLE PRIV => INSERT object => ENT.XEROX_INDUSTRY_SEGMENT grantable => NO
TABLE PRIV => ON COMMIT REFRESH object => ENT.XEROX_ESTAB grantable => NO
TABLE PRIV => ON COMMIT REFRESH object => ENT.XEROX_INDUSTRY_SEGMENT grantable => NO
TABLE PRIV => QUERY REWRITE object => ENT.XEROX_ESTAB grantable => NO
TABLE PRIV => QUERY REWRITE object => ENT.XEROX_INDUSTRY_SEGMENT grantable => NO
TABLE PRIV => REFERENCES object => ENT.XEROX_ESTAB grantable => NO
TABLE PRIV => REFERENCES object => ENT.XEROX_INDUSTRY_SEGMENT grantable => NO
TABLE PRIV => SELECT object => SLS.XEROX_EQUIP_PROFILE grantable => NO
TABLE PRIV => SELECT object => ENT.XEROX_ESTAB grantable => NO
TABLE PRIV => SELECT object => ENT.XEROX_INDUSTRY_SEGMENT grantable => NO
TABLE PRIV => SELECT object => ENT.XNAC_MSC grantable => NO
TABLE PRIV => UPDATE object => ENT.XEROX_ESTAB grantable => NO
TABLE PRIV => UPDATE object => ENT.XEROX_INDUSTRY_SEGMENT grantable => NO
TABLE PRIV => UPDATE object => SLS.XTI_TRADE_OUT_INFO grantable => NO

********************************************************
End of the report
********************************************************

Tom,

Thanks again for all you help.

I wonder if there is any way to copy the user from one db to other by using exp/imp fromuser touser and copy all privileges(granted to the user directly and through the roles) without searching dba_role_privs/dba_sys_privs etc on the one system and executing grant on the other.

Thanks in advance

Olaf

I want to move object privileges from one database to other database. We can generate a dynamic script to move the privs. but my question is what about the grantor. I want to keep the same grantor for the privileges in the destination database.

how can we achieve this?

thanks,

How can i distiguish default roles and userdefined roles?

Just curious, is i am missing any view?

It is very useful.
Tom, I need one more help from you. Is there any qeury to find the privileges of the functions?

Hi Tom,

There is function in database.. I am dropping the function and again I am creating the same function in that database.
But while recreating the function .. I want to grant the same privileges that the function had previously(Other then create or replace). Is there is any query to capture the privilege on function??

Thanks for the Help in advance

I am looking at taking this script or idea, and finding all users who have been granted the DBA role somewhere down the line (ie. either directly, or through a role, or through a role granted to a role, etc).

so basically, is there someway to return the top level record in the hierarchy, when the 'DBA' role is found anywhere in the tree?

To "A reader" above:

You shouldn't thank Velu for his useful code. You should thank Pete Finnegan and his really helpful security website from where Velu copied the code and removed all the credits and copyright notices.

http://www.petefinnigan.com/find_all_privs.sql

Pete says on his website that his script is free to use as long as you don't remove the copyright notices. Velu could have just posted the link.

So credit where credit is due - Thanks Pete, nice script. And thanks Tom, nice recursive script too. I'm using both of them.

Hi Tom,

I'm trying to find a specific script to check all databases roles privs. I tried to create some scripts but I don't have much success. :(

My problem is. I work in a company that have many databases, and some of these, have roles granted to roles, granted to roles,... I need a script that show me a roles list with the sum of all sys and tab privs recursively.

Do you have any tip for this?

I can send my last script if it's useful (but it's not working perfectly)

Thank very much for your time and help! :)

I wanted a report that looked like this with roles, users
with those roles, and then privilges

ROLE USERA TABLEA INSERT
USERB TABLEA UPDATE
TABLEA DELETE

select c.granted_role, a.grantee, a.TABLE_NAME, a.PRIVILEGE
from dba_tab_privs a,
dba_role_privs c
where a.grantee=c.grantee

Seems close, but it gives different results for each user,
which tells me it is somehow not getting the actual role privileges.

How do I find list of database users who have access to a particular schema? there are roles defined in the database and privileges to tables and pl/sql procedures/triggers/functions are granted to the user ids through roles. I was looking at dba_tab_privs but how can I get a comprehensive list as the roles are involved. Thank you for your time.

ops$tkyte%ORA11GR2> variable owner varchar2(30)
ops$tkyte%ORA11GR2> exec :owner := 'HR'

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> with init_users
  2  as
  3  (
  4  select grantee
  5    from dba_tab_privs
  6   where owner = :owner
  7   union
  8  select grantee
  9    from dba_sys_privs
 10   where privilege = 'SELECT ANY TABLE'
 11  ),
 12  final_list
 13  as
 14  (
 15  select grantee
 16    from dba_role_privs
 17   start with granted_role in (select * from init_users)
 18  connect by prior grantee = granted_role
 19   union
 20  select *
 21    from init_users
 22  )
 23  select grantee
 24    from final_list
 25   where grantee in (select username from dba_users)
 26  /

GRANTEE
------------------------------
FB_DEMO
BIG_TABLE
OPS$TKYTE
OLAPSYS
WMSYS
MDSYS
SYSTEM
SYS
NORM
OPS$ORA11GR2
DIY

11 rows selected.

ops$tkyte%ORA11GR2> create role a;

Role created.

ops$tkyte%ORA11GR2> create role b;

Role created.

ops$tkyte%ORA11GR2> create role c;

Role created.

ops$tkyte%ORA11GR2> grant select on hr.employees to c;

Grant succeeded.

ops$tkyte%ORA11GR2> grant c to b;

Grant succeeded.

ops$tkyte%ORA11GR2> grant b to a;

Grant succeeded.

ops$tkyte%ORA11GR2> grant a to scott;

Grant succeeded.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> with init_users
  2  as
  3  (
  4  select grantee
  5    from dba_tab_privs
  6   where owner = :owner
  7   union
  8  select grantee
  9    from dba_sys_privs
 10   where privilege = 'SELECT ANY TABLE'
 11  ),
 12  final_list
 13  as
 14  (
 15  select grantee
 16    from dba_role_privs
 17   start with granted_role in (select * from init_users)
 18  connect by prior grantee = granted_role
 19   union
 20  select *
 21    from init_users
 22  )
 23  select grantee
 24    from final_list
 25   where grantee in (select username from dba_users)
 26  /

GRANTEE
------------------------------
FB_DEMO
BIG_TABLE
SCOTT
OPS$TKYTE
OLAPSYS
WMSYS
MDSYS
SYSTEM
SYS
NORM
OPS$ORA11GR2
DIY

12 rows selected.