Oracle – How to see all of the permissions of an Oracle AQ user

database-administrationoracleoracle-aqqueue

I am trying to set up a new database user to listen to a topic in our OracleAQ queue.

I have an old user set up years ago that can read and post messages to the queue.

Now I'm trying to set up a new user and it seems it doesn't have all the GRANTS needed.

What is the best way to see the differences in the GRANTS that each user has ? I'm trying to compare them to see what is different.

For example, how do I determine if this has been granted ?

 grant execute on sys.dbms_aqjms to NEWUSER;

Best Solution

You can check which privileges a certain user was granted using system views:

  • USER_SYS_PRIVS - System privileges granted to current user
  • DBA_SYS_PRIVS - System privileges granted to users and roles
  • USER_TAB_PRIVS - Grants on objects for which the user is the grantor, grantee, or owner
  • ALL_TAB_PRIVS - Grants on objects for which the user is the grantor, grantee, owner, or an enabled role or PUBLIC is the grantee
  • DBA_TAB_PRIVS - Grants on all objects in the database
  • DBA_ROLES All - roles that exist in the database
  • DBA_ROLE_PRIVS - Roles granted to users and roles
  • DBA_SYS_PRIVS - System privileges granted to users and roles
  • DBA_TAB_PRIVS - All grants on objects to users and roles
  • ROLE_ROLE_PRIVS - Roles that are granted to roles
  • ROLE_SYS_PRIVS - System privileges granted to roles
  • ROLE_TAB_PRIVS - Table privileges granted to roles

To check, for example, which system privileges a user has that another user was not granted, and vice versa, you could use (you'd have to run it as SYS user):

CREATE USER user1 IDENTIFIED BY user1;

GRANT CREATE SESSION TO user1;
GRANT CREATE VIEW TO user1;

CREATE USER user2 IDENTIFIED BY user2;

GRANT CREATE SESSION TO user2;
GRANT CREATE TABLE TO user2;

(SELECT 'System privileges granted to user1 which user2 does not have' AS description, 1 AS ord FROM dual
UNION
SELECT privilege, 2 FROM dba_sys_privs WHERE grantee = 'USER1'
MINUS
SELECT privilege, 2 FROM dba_sys_privs WHERE grantee = 'USER2')
UNION
(SELECT 'System privileges granted to user2 which user1 does not have', 3 FROM dual
UNION
SELECT privilege, 4 FROM dba_sys_privs WHERE grantee = 'USER2'
MINUS
SELECT privilege, 4 FROM dba_sys_privs WHERE grantee = 'USER1')
ORDER BY 2;

Output:

DESCRIPTION                                                         ORD
------------------------------------------------------------ ----------
System privileges granted to user1 which user2 does not have          1 
CREATE VIEW                                                           2 
System privileges granted to user2 which user1 does not have          3 
CREATE TABLE                                                          4

To check if a user was granted the EXECUTE ON sys.dbms_aqjms privilege, you would issue the following query:

GRANT EXECUTE ON sys.dbms_aqjms TO user1;

SELECT * FROM dba_tab_privs WHERE grantee = 'USER1' AND table_name = 'DBMS_AQJMS';

Output:

GRANTEE   OWNER  TABLE_NAME  GRANTOR  PRIVILEGE  GRANTABLE HIERARCHY
--------- ------ ----------- -------- ---------- --------- ---------
USER1     SYS    DBMS_AQJMS  SYS      EXECUTE    NO        NO

If you don't have access to SYS user, you can query the user_ versions of system tables to check privileges owned by the user you are currently logged as. Then you can compare results with privileges of another user.

You have take into account, that user may by granted role, and that roles may be granted to roles, so you will also have to compare roles granted to each user.

Related Question