Finding all grants to a particular user

[Aerobatty]

I have an issue that will require exactly duplicating an oracle users grants.

Is it possible to view a users entire set of grants to all objects by a single SQL??

 

[LKBrwnDBA]

Yes,

Just search this forum and you will find some great scripts posted by Mr. Santa Mufasa.


----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[SantaMufasa]

Aerobatty,

Your original request is a bit ambiguous:

I have an issue that will require exactly duplicating an oracle users grants.

I infer from this that you would like to see all of the GRANTs for which a particular user is the GRANTEE. This includes GRANTs to:


1) Objects: Tables, Indexes, et cetera
2) Roles : "CONNECT", "RESOURCE", "DBA", et cetera
3) System Privileges: "CREATE TABLE", "CREATE VIEW", et cetera

Yet at the end of your request, you said:

Is it possible to view a users entire set of grants to all objects by a single SQL?

From this part of your request, I infer that you want to see just Item 1, above.

Which do you prefer?...Items 1-3, or just item 1?

Also, regardless of your response above, do you want to see output that includes objects owned by "SYS" (i.e., data dictionary objects), and do you wish to see output that includes ROLEs as the grantee (e.g., DBA, PUBLIC, et cetera)?

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[Turkbear]

Hi,
To help clarify, do you want:

All the grants made TO that user

OR

All the Grants made BY that user to others?





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[Aerobatty]

I have searched the archives and found something very close to the answer I was after in thread759-1205523 . I have added a hierarchical lookup to allow returning the privileges of all nested roles a user belongs to.


SELECT grantee, 'Explicit System Privilege', null object_name, privilege
FROM dba_sys_privs WHERE grantee = 'USER_NAME'
UNION ALL
SELECT grantee, 'Explicit Object Privilege',table_name, privilege
FROM dba_tab_privs WHERE grantee = 'USER_NAME'
UNION ALL
SELECT rp.grantee, 'System Privilege From Role '||rp.granted_role, null, sp.privilege
FROM dba_role_privs rp, dba_sys_privs sp
WHERE rp.granted_role = sp.grantee AND (rp.grantee = 'USER_NAME'
OR
rp.grantee in
(SELECT granted_role
FROM dba_role_privs
START WITH grantee = 'USER_NAME'
CONNECT BY PRIOR granted_role = grantee)
)
UNION ALL
SELECT rp.grantee, 'Object Privilege From Role '||rp.granted_role, table_name, op.privilege
FROM dba_role_privs rp, dba_tab_privs op
WHERE rp.granted_role = op.grantee AND (rp.grantee = 'USER_NAME'
OR
rp.grantee in
(SELECT granted_role
FROM dba_role_privs
START WITH grantee = 'USER_NAME'
CONNECT BY PRIOR granted_role = grantee)
)

 

[Aerobatty]

Thanks, I've already solved the problem.

I wanted to see everything, i.e. as per my original question
"require EXACTLY duplicating". I had a problem where a particular user was having problems. I think it's network related though.

The SQL I included should do what I want. It looks like I was writing my reply while you were writing yours but you beat me to it.

How do I close this thread?? Forgive me I'm new here.

 

[SantaMufasa]

Consider it closed.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]