Are there any tables that I can get all Oracle supported system privileges and object privileges?

Posted on

Question :

Are there any tables that I can get all Oracle supported system privileges and object privileges? I know there is a view called all_sys_privs in Oracle. But I can’t find more information about it.

Thanks.

Answer :

Perhaps you are looking for SYSTEM_PRIVILEGE_MAP?

You can look into sys.table_privilege_map (at-least 11g onwards, might exist before but I have no way to check) for a listing of possible object privileges, be warned not all apply to all object types.

Try this query for object privileges by object type.

SELECT o.object_type, t.privilege 
FROM dba_tab_privs t
JOIN dba_objects   o
  ON t.owner = o.owner
 AND t.table_name = o.object_name
GROUP BY o.object_type, t.PRIVILEGE
ORDER BY o.object_type, t.PRIVILEGE
;

The following takes into account privilege types that exist in table_privilege_map but not in the existing database objects.

SELECT o.object_type, m.name AS privilege 
FROM table_privilege_map m
LEFT OUTER JOIN (dba_tab_privs t
      JOIN dba_objects   o
        ON t.owner = o.owner
       AND t.table_name = o.object_name)
  ON m.NAME = t.PRIVILEGE
GROUP BY o.object_type, m.NAME
ORDER BY o.object_type, m.name
;

Leave a Reply

Your email address will not be published. Required fields are marked *