6

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.

7

Perhaps you are looking for SYSTEM_PRIVILEGE_MAP?

  • Yes, it's this view. How about all object privileges? – Ogrish Man Aug 29 '11 at 9:18
  • If you want a list of all possible object privileges, you will have to construct it from dba_objects – Jack Douglas Aug 29 '11 at 9:22
0

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.

0

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
;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.