Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
In today’s article, we will examine what we need to pay attention to when performing Critical Authority Controls in Oracle Database.
The most critical authorizations for database users are “ALTER” and “ANY” privileges.
Users who receive this authorization can also dominate over other users.
It can delete, recreate and edit all tables on the database.
This type of authorization is strongly discouraged. What is required is authorization according to need.
Oracle Database default users, for example ” SYS ” or ” SYSTEM, need these privileges.
The SQL query is given below, where you can find the people with critical privileges mentioned above, excluding such users.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 | /* Checking critical Authorities */ SELECT A.*, 'GRANTED TO USER' TYPE FROM DBA_SYS_PRIVS A, DBA_USERS B WHERE A.GRANTEE = B.USERNAME AND PRIVILEGE IN( 'BECOME USER', 'ALTER USER', 'DROP USER', 'CREATE ROLE', 'ALTER ANY ROLE', 'DROP ANY ROLE', 'GRANT ANY ROLE', 'CREATE PROFILE', 'ALTER PROFILE', 'DROP PROFILE', 'CREATE ANY TABLE', 'ALTER ANY TABLE', 'DROP ANY TABLE', 'INSERT ANY TABLE', 'UPDATE ANY TABLE', 'DELETE ANY TABLE', 'SELECT ANY TABLE', 'CREATE ANY PROCEDURE', 'ALTER ANY PROCEDURE', 'DROP ANY PROCEDURE', 'EXECUTE ANY PROCEDURE', 'CREATE ANY TRIGGER', 'ALTER ANY TRIGGER', 'DROP ANY TRIGGER', 'CREATE TABLESPACE', 'ALTER TABLESPACE', 'DROP TABLESPACES', 'ALTER DATABASE', 'ALTER SYSTEM', 'SELECT ANY DICTIONARY', 'EXEMPT ACCESS POLICY', 'CREATE ANY LIBRARY', 'GRANT ANY OBJECT PRIVILEGE', 'GRANT ANY PRIVILEGE', 'AUDIT ANY' ) AND GRANTEE NOT IN( 'ANONYMOUS', 'CTXSYS', 'DBSNMP', 'EXFSYS', 'LBACSYS', 'MDSYS', 'MGMT_VIEW', 'OLAPSYS', 'OWBSYS', 'ORDPLUGINS', 'ORDSYS', 'OUTLN', 'SI_INFORMTN_SCHEMA', 'SYS', 'SYSMAN', 'SYSTEM', 'TSMSYS', 'WK_TEST', 'WKSYS', 'WKPROXY', 'WMSYS', 'XDB', 'APEX_PUBLIC_USER', 'DIP', 'FLOWS_30000', 'FLOWS_FILES', 'MDDATA', 'ORACLE_OCM', 'SPATIAL_CSW_ADMIN_USR', 'SPATIAL_WFS_ADMIN_USR', 'XS$NULL', 'OWBSYS_AUDIT', 'ORDDATA', 'APEX_030200', 'APPQOSSYS', 'DVSYS', 'DVF' ) UNION ALL SELECT A.*, 'GRANTED TO ROLE' TYPE FROM DBA_SYS_PRIVS A, DBA_ROLES B WHERE A.GRANTEE = B.ROLE AND PRIVILEGE IN( 'BECOME USER', 'ALTER USER', 'DROP USER', 'CREATE ROLE', 'ALTER ANY ROLE', 'DROP ANY ROLE', 'GRANT ANY ROLE', 'CREATE PROFILE', 'ALTER PROFILE', 'DROP PROFILE', 'CREATE ANY TABLE', 'ALTER ANY TABLE', 'DROP ANY TABLE', 'INSERT ANY TABLE', 'UPDATE ANY TABLE', 'DELETE ANY TABLE', 'SELECT ANY TABLE', 'CREATE ANY PROCEDURE', 'ALTER ANY PROCEDURE', 'DROP ANY PROCEDURE', 'EXECUTE ANY PROCEDURE', 'CREATE ANY TRIGGER', 'ALTER ANY TRIGGER', 'DROP ANY TRIGGER', 'CREATE TABLESPACE', 'ALTER TABLESPACE', 'DROP TABLESPACES', 'ALTER DATABASE', 'ALTER SYSTEM', 'SELECT ANY DICTIONARY', 'EXEMPT ACCESS POLICY', 'CREATE ANY LIBRARY', 'GRANT ANY OBJECT PRIVILEGE', 'GRANT ANY PRIVILEGE', 'AUDIT ANY' ) AND GRANTEE NOT IN( 'DBA', 'IMP_FULL_DATABASE', 'DATAPUMP_IMP_FULL_DATABASE', 'OLAP_DBA', 'EXP_FULL_DATABASE', 'OEM_MONITOR' ); |
When you run the above code block, you will get a dump like below.
To be clear, I have authorized the user “HR”.
We see it below. You should examine the users you come across in detail and get their authorization if necessary.
So how do you get these powers?
Syntax : REVOKE {system_privilege | object_privilege } FROM user;
Example ;
1 2 | REVOKE ALTER ANY TRIGGER FROM HR; REVOKE ALTER ANY TABLE FROM HR; |
They may have received these authorizations with the additional feature we call “ADMIN OPTION”.
ADMIN OPTION is a feature that you can give to someone else the privileges given to you.
In other words, if you have been given the “CREATE ANY TABLE” authority with “ADMIN OPTION”, you can give this authority to someone else.
Clearly, this authority should not belong to anyone other than DBA managers.
To see the ADMIN OPTION privilege assigned to users:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 | /* Users with ADMIN OPTION authority */ SELECT A.GRANTEE, A.PRIVILEGE, A.ADMIN_OPTION FROM DBA_SYS_PRIVS A, DBA_USERS B WHERE A.ADMIN_OPTION = 'YES' AND A.GRANTEE NOT IN ('ANONYMOUS', 'CTXSYS', 'DBSNMP', 'EXFSYS', 'LBACSYS', 'MDSYS', 'MGMT_VIEW', 'OLAPSYS', 'OWBSYS', 'ORDPLUGINS', 'ORDSYS', 'OUTLN', 'SI_INFORMTN_SCHEMA', 'SYS', 'SYSMAN', 'SYSTEM', 'TSMSYS', 'WK_TEST', 'WKSYS', 'WKPROXY', 'WMSYS', 'XDB', 'APEX_PUBLIC_USER', 'DIP', 'FLOWS_30000', 'FLOWS_FILES', 'MDDATA', 'ORACLE_OCM', 'SPATIAL_CSW_ADMIN_USR', 'SPATIAL_WFS_ADMIN_USR', 'XS$NULL', 'OWBSYS_AUDIT', 'ORDDATA', 'APEX_030200', 'APPQOSSYS', 'DVSYS', 'DVF') AND A.GRANTEE = B.USERNAME; |
When I run the code block, I see that the user “HR” has this privilege.
The command for authorization is the same.
1 | REVOKE ALTER ANY TABLE FROM HR; |
However, users with SYSDBA authorization should be examined.
1 2 3 4 | /* Users with SYSDBA Authorization */ SELECT * FROM v$pwfile_users WHERE SYSDBA = 'TRUE'; |
The sample output is as follows, I only see SYS.
If there are different users, it should be investigated.
1 | REVOKE SYSDBA FROM HR |
However, users with the “DBA” role should be examined.
If you want to see the users with this authority;
1 2 3 4 5 6 7 8 9 10 11 | /* Formatted on 10/18/2020 12:53:38 PM (QP5 v5.300) */ SELECT DISTINCT A.GRANTEE, A.GRANTED_ROLE, 'DBA' GRANTED_CRITIC_ROLE FROM ( SELECT DISTINCT LEVEL LEVEL_DEEP, GRANTEE, GRANTED_ROLE FROM DBA_ROLE_PRIVS START WITH GRANTED_ROLE = 'DBA' CONNECT BY PRIOR GRANTEE = GRANTED_ROLE) A, DBA_USERS B WHERE A.GRANTEE = B.USERNAME AND B.USERNAME NOT IN ('SYSTEM', 'SYS') AND B.ACCOUNT_STATUS = 'OPEN'; |
When I run the above code block, I see that the user “HR” has this privilege.
If you see different users, you should research it.
If we want to get the authority;
1 | REVOKE DBA FROM HR |
Finally, let’s take a look at the CATALOG privileges. Catalog authorizations are examined in three different sections.
DELETE_CATALOG_ROLE; Audit trails are kept here in the database.
For example, this permission is needed to delete the AUD$ table.
If a user has this authority, he can delete the audit log records in the database.
You can request users with this authority with the following query.
1 2 3 4 5 6 7 8 9 10 11 12 | /* Delete Catalog Role control */ SELECT DISTINCT A.GRANTEE, GRANTED_ROLE, 'DELETE_CATALOG_ROLE' GRANTED_CRITIC_ROLE FROM ( SELECT DISTINCT LEVEL LEVEL_DEEP, GRANTEE, GRANTED_ROLE FROM DBA_ROLE_PRIVS START WITH GRANTED_ROLE = 'DELETE_CATALOG_ROLE' CONNECT BY PRIOR GRANTEE = GRANTED_ROLE) A, DBA_USERS B WHERE A.GRANTEE = B.USERNAME AND B.USERNAME NOT IN ('SYSTEM', 'SYS', 'SYSMAN') AND B.ACCOUNT_STATUS = 'OPEN'; |
To get authorization
1 | REVOKE DELETE_CATALOG_ROLE FROM HR |
SELECT_CATALOG_ROLE; It is the role where other users can list and query the tables in the SYS user.
1 2 3 4 5 6 7 8 9 10 11 | /* Select Catalog Role control */ SELECT DISTINCT A.GRANTEE, GRANTED_ROLE, 'SELECT_CATALOG_ROLE' GRANTED_CRITIC_ROLE FROM ( SELECT DISTINCT LEVEL LEVEL_DEEP, GRANTEE, GRANTED_ROLE FROM DBA_ROLE_PRIVS START WITH GRANTED_ROLE = 'SELECT_CATALOG_ROLE' CONNECT BY PRIOR GRANTEE = GRANTED_ROLE) A, DBA_USERS B WHERE A.GRANTEE = B.USERNAME AND B.USERNAME NOT IN ('SYSTEM', 'SYS', 'SYSMAN') AND B.ACCOUNT_STATUS = 'OPEN'; |
To get authorization
1 | REVOKE SELECT_CATALOG_ROLE FROM HR |
EXECUTE_CATALOG_ROLE; It gives the user the right to run procedures and functions on the SYS user.
1 2 3 4 5 6 7 8 9 10 11 | /* Execute Catalog Role control */ SELECT DISTINCT A.GRANTEE, GRANTED_ROLE, 'EXECUTE_CATALOG_ROLE' GRANTED_CRITIC_ROLE FROM ( SELECT DISTINCT LEVEL LEVEL_DEEP, GRANTEE, GRANTED_ROLE FROM DBA_ROLE_PRIVS START WITH GRANTED_ROLE = 'EXECUTE_CATALOG_ROLE' CONNECT BY PRIOR GRANTEE = GRANTED_ROLE) A, DBA_USERS B WHERE A.GRANTEE = B.USERNAME AND B.USERNAME NOT IN ('SYSTEM', 'SYS', 'SYSMAN') AND B.ACCOUNT_STATUS = 'OPEN'; |
1 | REVOKE EXECUTE_CATALOG_ROLE FROM HR |
