How to determine what privileges a user has when the username is not present in the DBA_TAB_PRIVS table?

Question

The users listed in the DBA_USERS table do not match the grantees listed in the DBA_TAB_PRIVS table.

So how to I get the privileges for users that are only listed in the DBA_USERS table?

For example, if I do

 SELECT USERNAME FROM DBA_USERS;

Then I get a list of login names, such as

 USERNAME
 --------
 BOB17
 DBA
 JANET5
 STEVE34
 SYSTEM

And now I want to know what roles and privileges the 'STEVE34' user has.

But when I run the queries suggested online:

SELECT * FROM DBA_SYS_PRIVS;
SELECT * FROM DBA_TAB_PRIVS;
SELECT * FROM DBA_ROLE_PRIVS;

The "Grantee" column does not contain 'BOB17', 'JANET5', 'STEVE34', instead it contains 'SYSTEM', 'PUBLIC', and 'DBA'.

So if 'STEVE34' is not in the Grantee column of any of the PRIVS tables, how do I find out what privileges the user 'STEVE34' has?

The question How to find the privileges and roles granted to a user in Oracle? has 8 answers, none of which solve this question.

I need to know what permissions and roles the users listed in DBA_USERS have, and those users are NOT found in the suggested tables from the linked question.

Further clarification:

If I run this query:

 Select USERNAME, GRANTEE from dba_users u
 LEFT JOIN dba_tab_privs p
 ON u.USERNAME = p.GRANTEE
 order by username

This is the result:

 USERNAME  GRANTEE
 --------  -------
 BOB17     (null)
 DBA       DBA
 DBA       DBA
 DBA       DBA
 DBA       DBA
 JANET5    (null)
 STEVE34   (null)
 SYSTEM    SYSTEM
 SYSTEM    SYSTEM
 SYSTEM    SYSTEM

How can I determine the privileges for users that are not in the DBA_TAB_PRIVS table?

I am absolutely certain that the users do exist and have some kind of privileges, I watched them log in and watched them successfully do SELECT queries.

Answer 1

I may be going on a limb here, but I could not help but notice the sample list of users is in lower case. If you copied and pasted the list (as opposed to incidentally typing the names in lower-case) it means those users were created using double quotes. You can only find them in any Oracle dictionary view (such as dba_users, dba_tab_privs, dba_sys_privs, dba_role_privs) if you take into account that fact. Double quoted lower case is different from non-quoted username.

Whenever a grant, revoke, create table or any other DDL must be run against the lower-case double-quoted "steve34", it needs to be double quoted or Oracle will run it against the unquoted version case-insensitive STEVE34.

Same applies to where clause in SQL on dictionary views. Double-quoted "steve34" becomes case sensitive.

Note in the example below I can create 2 users with apparently the same name, but which are different Oracle users.

FSITJA@db01> create user "steve34" identified by 123;

User created.

FSITJA@db01> create user steve34 identified by 123;

User created.

FSITJA@db01> grant create session to steve34;

Grant succeeded.

FSITJA@db01> select username from dba_users u where upper(u.username) = 'STEVE34';

USERNAME
------------------------------
steve34
STEVE34

FSITJA@db01> select * from dba_sys_privs sp where sp.grantee = 'STEVE34';

GRANTEE PRIVILEGE                                ADM COM
------- ---------------------------------------- --- ---
STEVE34 CREATE SESSION                           NO  NO

FSITJA@db01> select * from dba_sys_privs sp where sp.grantee = 'steve34';

no rows selected

FSITJA@dbd01 2019-07-31 17:18:00> grant create session to "steve34" with admin option;

Grant succeeded.

FSITJA@db01> select * from dba_sys_privs sp where sp.grantee = 'steve34';

GRANTEE PRIVILEGE                                ADM COM
------- ---------------------------------------- --- ---
steve34 CREATE SESSION                           YES NO