How do I find all the users that can access one particular table?
For example table 'Table01'
Thanks
asked Aug 18, 2016 at 16:01
-
2In general, this is hard. You can have privileges granted to you. You can have privileges granted to a role that you have. That role might be password protected, though, and you don't have the password. That role might be a non-defalult role that may or may not be available to a particular session. You can have access through system privileges. And you can have roles within roles.– Justin CaveCommented Aug 18, 2016 at 17:43
-
I would probably at least start with Pete Finnigan's "who_can_access" script. petefinnigan.com/who_can_access.sql from his tools page petefinnigan.com/tools.htm . That runs to 400 lines and doesn't check every possible permutation but it's a good start. A generic answer would need to know things like whether you want to consider a password-protected non-default role the same as a direct object grant, ignore it, or add it to a list of "people who may have access depending on what else they know". Then there are things like proxy users and other avenues like views.– Justin CaveCommented Aug 19, 2016 at 4:31
Add a comment
|
1 Answer
If you are sys user then you can use dba_tab_privs view as shown below.
SQL>Conn / as sysdba
SQL>select grantee, table_name, privilege from dba_tab_privs where table_name='Table01';
Update:
As Justin Cave and mustaccio said, its a bit complex to get the accurate result from general dba_ or role_ views.
Though you can query role_tab_privs to get all the roles and its object privileges.
SQL> select grantee, privilege from dba_tab_privs where table_name='EMPLOYEES' and owner='HR';
GRANTEE PRIVILEGE
------------------------------ ----------------------------------------
SCOTT SELECT
OE SELECT
OE REFERENCES
JAY UPDATE
R1 SELECT
In the above result Grantee R1 is a role which has SELECT object privilege on EMPLOYEES table.
Here is a useful post similar to this. oracle - list users with access to certain tables
-
1This would only return users who were granted access directly, but not those who can access the table via a role. Commented Aug 18, 2016 at 17:03
-
mustaccio, then what query would you use to display both? Commented Aug 18, 2016 at 18:46