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.
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 only takes a minute to sign up.Sign up to join this community
Perhaps you are looking for
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 ;