How to finds users that can access a particular table?

Question

How do I find all the users that can access one particular table?

For example table 'Table01'

Thanks

In 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 Cave, Aug 18 '16 at 17:43
I would probably at least start with Pete Finnigan's "who_can_access" script. http://www.petefinnigan.com/who_can_access.sql from his tools page http://www.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 Cave, Aug 19 '16 at 04:31

score 2 · Accepted Answer · edited Apr 13 '17 at 12:42

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

This would only return users who were granted access directly, but not those who can access the table via a role. — mustaccio, Aug 18 '16 at 17:03

How to finds users that can access a particular table?

1 Answers1