Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
dba_sys_privs? –  Jack Douglas Aug 29 '11 at 8:31
    
DBA_SYS_PRIVS describes system privileges granted to users and roles. What I need is all the privileges names that Oracle support. –  Ogrish Man Aug 29 '11 at 8:41
    
so would all_sys_privs if it existed –  Jack Douglas Aug 29 '11 at 8:59

3 Answers 3

up vote 7 down vote accepted

Perhaps you are looking for SYSTEM_PRIVILEGE_MAP?

share|improve this answer
    
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

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.

share|improve this answer

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
;
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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