OraFAQ Forum: Security » object privileges

Home » RDBMS Server » Security » object privileges

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

object privileges [message #176217]

Wed, 07 June 2006 06:57

Bruce Carson
Messages: 38
Registered: February 2005
Location: Nova Scotia, Canada

Member

I am logged in to an instance as a user with the DBA role. I wanted to see all privileges a specific user has (object,system,roles). What I don't see are the object privileges from other schemas. I have to log in as the granting schema owner to see the object privs for the specific user. Is there something I can grant or a script I can run to get a complete picture of a specific user to see everything that has been granted to that user? I thought the grant any privilege within DBA would give this to me.

Report message to a moderator

Re: object privileges [message #176900 is a reply to message #176217]

Sun, 11 June 2006 11:55

nmacdannald
Messages: 460
Registered: July 2005
Location: Stockton, California - US...

Senior Member

In plsql try:
select grantee, privilege from dba_sys_privs where grantee = <the user you want to see> order by grantee;
commit;

select role from dba_roles;
commit;

select grantee, owner, table_name, column_name from dba_col_privs where owner = <the user you want to check> order by 1,2,3;
commit;

check out column_privileges table;
desc column_privileges

check out all_tab_privs_made table;

I usually create a role, assign object privileges to that role, and then assign the role to a group of users.
create public synonym testit for <schema>.<testit>;
grant select, update, insert, delete on testit to role01.

spool userprivs.txt
set pagesize 0
set linesize 100
set echo off
set feed off
set verify off

-- Users list
ttitle 'All users'
select username "Users"
from dba_users
where username not in ('SYS','SYSTEM','OUTLN',
'DBSNMP','SCOTT','DB_CONTROL',
'OPS$ORACLE','ORADBA')
/

-- All user's granted
break on user skip 1 on user
col user format a15
col grant format a30
ttitle 'All users granted'
select grantee "User" ,granted_role "Grant", 'role' "Type"
from dba_role_privs
where grantee in (select username from dba_users
where username not in ('SYS','SYSTEM',
'OUTLN','DBSNMP','SCOTT','DB_CONTROL','OPS$ORACLE','XXX'))
and granted_role not in ('CONNECT')
union all
select grantee "User", privilege "Grant", 'priv' "Type"
from dba_sys_privs
where grantee in (select username from dba_users
where username not in ('SYS','SYSTEM',
'OUTLN','DBSNMP','SCOTT','DB_CONTROL','OPS$ORACLE','XXX'))
and privilege not in ('CONNECT')
union all
select grantee "User", owner||'.'||table_name "Grant", lower(privilege)
"Type"
from dba_tab_privs
where grantee in (select username from dba_users
where username not in ('SYS','SYSTEM',
'OUTLN','DBSNMP','SCOTT','DB_CONTROL','OPS$ORACLE','XXX'))
order by 1
/

commit;
-- All role's granted

Report message to a moderator

Previous Topic:	Hide SQL Statements
Next Topic:	I loss my internal password what can i do

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Apr 17 21:53:24 CDT 2024