Can't find my username in the dba_sys_privs table

Question

I logged into oracle SQL Developer with user test. I need to know if I have SELECT ANY TABLE privilege. So I executed the following query:

select *
from dba_sys_privs
where grantee='test'

But it returns nothing.

What does this mean ? I don't have any privileges?

Answer 1

If you create as user by

Create user test …

Then the user is created as TEST

So first verify the case of your username

Select user
From dual

‘user‘ is a function that returns the name of the user who is logged in.

You can verify that your lowercase username is wrong by executing

Select *
From dba_users
Where username=‘test‘

This will return no rows, too.

So execute

Select *
from dba_sys_privs
where grantee=‘TEST‘

And this will show the desired result

You can also query the view user_sys_privs to see the privileges of the user logged in

Select *
From user_sys_privs

Her you avoid typing your username and you do not need privileges on the dba_sys_privs view.

If you want to create a user with a name in lowercase letters you have to use double quotes

Create user “test“ …

Answer 2

If the query doesn't return any values, then you don't have the permission to SELECT ANY TABLE.

If you connect with SYS as SYSDBA and run the same statement searching for SYS's permissions, then you receive the following information:

SET pages 50
SET lines 230
COLUMN grantee format a25

SELECT * FROM dba_sys_privs dsp WHERE grantee = 'SYS' ORDER BY Privilege;

Returns:

GRANTEE                   PRIVILEGE                                ADMIN_OPTION COMMON INHERITED
------------------------- ---------------------------------------- ------------ ------ ---------
...
SYS                       SELECT ANY TABLE                         YES          NO     NO   
...

However, you may want to query the current user's privileges...

Solution

Instead of querying system privileges, consider querying the user's table privileges instead.

3.112 ALL_TAB_PRIVS ALL_TAB_PRIVS describes grants.

ALL_TAB_PRIVS describes the following types of grants:

• Object grants for which the current user is the object owner, grantor, or grantee

• Object grants for which an enabled role or PUBLIC is the grantee ...

^{Reference: 3.113 ALL_TAB_PRIVS (Oracle | Docs)}

Query

SET pages 50
SET lines 230
COLUMN grantee format a30
COLUMN grantor format a30
COLUMN table_name format a30
COLUMN table_schema format a30
COLUMN owner format a30
COLUMN PRIVILEGE FORMAT a15
SELECT * FROM all_tab_privs WHERE grantee = 'test';

Good luck.

Answer 3

the dba_sys_privs view will only show privileges that are granted directly to your user. If it isn't returning any rows, then you don't have any.

If you have been granted privileges through a role, you would need to query dba_role_privs first to see which roles are assigned to your user, then query dba_sys_privs to see which privileges are assigned to the role.

select * from dba_role_privs where grantee='ADMIN';

GRANTEE GRANTED_ROLE ADM DEL DEF COM INH
------- ------------ --- --- --- --- ---
ADMIN   RESOURCE     YES NO  YES NO  NO 
...

select * from dba_sys_privs where grantee='RESOURCE';

GRANTEE  PRIVILEGE                                ADM COM INH
-------- ---------------------------------------- --- --- ---
RESOURCE CREATE TABLE                             NO  YES YES
RESOURCE CREATE TYPE                              NO  YES YES
RESOURCE CREATE OPERATOR                          NO  YES YES
RESOURCE CREATE INDEXTYPE                         NO  YES YES
RESOURCE CREATE CLUSTER                           NO  YES YES
RESOURCE CREATE PROCEDURE                         NO  YES YES
RESOURCE CREATE SEQUENCE                          NO  YES YES
RESOURCE CREATE TRIGGER                           NO  YES YES