1

DB: Oracle 10g

I can grant DML commands to a user for some tables:

GRANT SELECT, UPDATE, DELETE, INSERT ON USER_A.TABLE_1 TO USER_B;

How to list all grants (about select, update, insert, delete) that USER_B has received, and for what tables?

I checked table "all_tab_privs_recd", but doesn't have what I'm looking for.

Delmonte
  • 411
  • 2
  • 9
  • 33
  • possible duplicate of [How can I list ALL grants a user received?](http://stackoverflow.com/questions/1298473/how-can-i-list-all-grants-a-user-received). – DCookie Aug 23 '12 at 22:57
  • It's not a duplicated question of "How can I list ALL grants...". View all_tab_privs doesn't help to answer my question. – Delmonte Aug 24 '12 at 14:44

3 Answers3

2

Pete Finnegan, Oracle security expert extrordinaire, has several different tools available that will help you answer these types of questions.

See: http://www.petefinnigan.com/tools.htm

In particular, for the question above, see find_all_privs.sql

Hope that helps.

Mark J. Bobak
  • 13,720
  • 6
  • 39
  • 67
1

I can't figure out how to use those views that you suggest for listing all grants that USER_B received.

GRANT SELECT, UPDATE, DELETE, INSERT ON USER_A.TABLE_1 TO USER_B;

I query:

select * from all_tables where owner='USER_A'
shows 16 rows

Select * from all_tab_privs_recd  where grantor = 'USER_A'
shows 0 rows

Select * from all_tab_privs_recd  where grantee = 'USER_A'
shows 0 rows

Select * from all_tab_privs_recd  where grantee = 'USER_B'
shows 0 rows

Select * from all_tab_privs_recd  where grantor = 'USER_B'
shows 129 rows, but USER_A is not in grantee, nor in grantor nor in owner
Delmonte
  • 411
  • 2
  • 9
  • 33
0

The all_tab_privs_recd (and the all_tab_privs) views only show the tables that have explicit grants on them, they don't show the tables that are owned by USER_B. (Unless grants have been given to other users. That is, where the GRANTEE and OWNER are the same.) For that you'd have to look at all_tables to see what tables they own and therefore have full access to.

John Doyle
  • 7,475
  • 5
  • 33
  • 40