Fundamental Security Part Four – Permissions Checking
03/07/2023 3 Comments
July 2023 – Oracle 19C
So in the previous post we talked about identifying unused and historic accounts. Old accounts should be removed, but what are the capabilities of the existing users who are accessing the systems?
A surprisingly large amount of applications request that the schema owner, and maybe the primary application connection, has DBA-level rights. This is always excessive and should be avoided.
Most DBA’s don’t need DBA rights most of the time. It makes a lot of sense to have a company-specific “DBA” role with minimal permissions. Most day-to-day checking tasks are read only. It is sensible to have a fairly low privilege account for day-to-day access and an admin account (preferable accessed via raising a ticket in response to an incident or change request) for when high privilege change is needed.
Your system permissions should be allocated on the principle of least privilege. Only grant those privileges and resources explicitly and legitimately required to perform the function.
Who Has DBA?
SELECT * FROM dba_role_privs
WHERE granted_role = 'DBA'
ORDER BY grantee;
GRANTEE GRANTED_ROLE ADM DEL DEF COM INH
------------------------------ ------------------ --- --- --- --- ---
APP_SCHEMA DBA NO NO YES NO NO
ERIK DBA NO NO YES NO NO
MARTIN DBA NO NO YES NO NO
NEIL DBA NO NO YES NO NO
TIM DBA NO NO YES NO NO
SYS DBA YES NO YES YES YES
SYSTEM DBA NO NO YES YES YES
Identify who really needs DBA privs? NEIL the DBA does, but does ERIK the Developer need such high level privs? This is unlikely, even in a dedicated Development database.
However, you do need to be aware of other privileges. A CIS-level audit will pick up on DBA privs, but will not necessarily spot who has the IMP_FULL_DATABASE role granted to them (or, more powerfully, DATAPUMP_IMP_FULL_DATABASE). These roles contain very high level privileges such as ALTER DATABASE, DELETE ANY TABLE, and BECOME USER which should be highly restricted.
SELECT * FROM dba_role_privs
WHERE granted_role = 'IMP_FULL_DATABASE'
ORDER BY grantee;
GRANTEE GRANTED_ROLE ADM DEL DEF COM INH
------------------------------ ------------------ --- --- --- --- ---
DATAPUMP_IMP_FULL_DATABASE IMP_FULL_DATABASE NO NO YES YES YES
DBA IMP_FULL_DATABASE NO NO YES YES YES
SCOTT IMP_FULL_DATABASE NO NO YES NO NO
SYS IMP_FULL_DATABASE YES NO YES YES YESPUBLIC Grants
The above roles are fairly well known. A lesser known risk is the amount of procedures with EXECUTE permissions granted to PUBLIC. Anyone with access to the database can use these. However, there are over 2,500 such permissions in Oracle 19.13. The CIS Security standard helps to identify some of the higher risk grants, to packages such as UTL_TCP (which allows access to [corrupt] the TCP stream), UTL_HTTP (which could send information to external websites), DBMS_SQL (which, if used incorrectly, allows SQL Injection attacks), and DBMS_RANDOM (because it’s not very random and shouldn’t be used for any serious work!)
Sample of potentially insecure packages granted to public:
Network Security
DBMS_LDAP
UTL_INADDR
UTL_TCP
UTL_MAIL
UTL_SMTP
UTL_DBWS
UTL_ORAMTS
UTL_HTTP
HTTPURITYPE
File Security
DBMS_ADVISOR
DBMS_LOB
UTL_FILE
Encryption
DBMS_CRYPTO
DBMS_OBFUSCATION_TOOLKIT
DBMS_RANDOM
Java
DBMS_JAVA
DBMS_JAVA_TEST
Scheduler
DBMS_SCHEDULER
DBMS_JOB
SQL Injection Helpers
DBMS_SQL
DBMS_XMLGEN
DBMS_XMLQUERY
DBMS_XLMSTORE
DBMS_XLMSAVE
DBMS_REDACT
(please review the CIS Standards for a list of the packages in your database release)These grants can be seen in DBA_TAB_PRIVS (with the privilege of EXECUTE – the view isn’t only for tables)
It is a good practice to remove the grant of all of these packages from PUBLIC
[REVOKE EXECUTE ON <pkg> FROM PUBLIC; ]
WARNING: If you revoke these privileges from PUBLIC, you may find that your application stops working. You need to identify if any of the above packages are used and grant them explicitly to only the accounts which need them. The most common grant where you would need to do this would be for DBMS_LOB, but any of the above packages may be used by your apps or users. You need to discover which are being used (running checks against the application source code and view DBA_SOURCE, as well as monitoring the contents of GV$SQL and DBA_HIST_SQLTEXT are good starting points for this.)
Dangerous Grants
There are a number of very powerful and sensitive tables and procedures within Oracle where we need to monitor the grants carefully. Access to these is not granted by default, and we need to ensure this does not happen by accident!
Powerful Procedures
DBMS_BACKUP_RESTORE
DBMS_FILE_TRANSFER
DBMS_SYS_SQL
DBMS_REPCAT_SQL_UTL
INITJVMAUX
DBMS_AQADM_SYS
DBMS_STREAMS_RPC
DBMS_PRVTAQIM
LTADM
DBMS_IJOB
DBMS_PDB_EXEC_SQL
Sensitive Tables
CDB_LOCAL_ADMINAUTH$
DEFAULT_PWD$
ENC$
HISTGRM$
HIST_HEAD$
LINK$
PDB_SYNC$
SCHEDULER$_CREDENTIAL
USER$
USER_HISTORY$
XS$VERIFIERS
You may be wondering what is so sensitive about these tables and procedures. The DBMS_SYS_SQL package could allow a user to run code as a different user without entering valid credentials. Table HISTGRM$ contains samples of (or maybe all) data from table columns. Access to these objects is a notable security risk which needs to be taken seriously.
Here’s a useful bit of SQL which will look for access to these objects
SELECT owner, table_name, grantee, privilege, type FROM dba_tab_privs
WHERE table_name IN ('DBMS_LDAP', 'UTL_INADDR', 'UTL_TCP', 'UTL_MAIL', 'UTL_SMTP',
'UTL_DBWS', 'UTL_ORAMTS', 'UTL_HTTP', 'HTTPURITYPE', 'DBMS_ADVISOR', 'DBMS_LOB',
'UTL_FILE', 'DBMS_CRYPTO', 'DBMS_OBFUSCATION_TOOLKIT', 'DBMS_RANDOM', 'DBMS_JAVA',
'DBMS_JAVA_TEST', 'DBMS_SCHEDULER', 'DBMS_JOB', 'DBMS_SQL', 'DBMS_XMLGEN',
'DBMS_XMLQUERY', 'DBMS_XLMSTORE', 'DBMS_XLMSAVE', 'DBMS_REDACT',
'CDB_LOCAL_ADMINAUTH$', 'DEFAULT_PWD$', 'ENC$', 'HISTGRM$', 'HIST_HEAD$', 'LINK$',
'PDB_SYNC$', 'SCHEDULER$_CREDENTIAL', 'USER$', 'USER_HISTORY$', 'XS$VERIFIERS', 'DBMS_BACKUP_RESTORE', 'DBMS_FILE_TRANSFER', 'DBMS_SYS_SQL', 'DBMS_REPCAT_SQL_UTL', 'INITJVMAUX', 'DBMS_AQADM_SYS', 'DBMS_STREAMS_RPC', 'DBMS_PRVTAQIM','LTADM', 'DBMS_IJOB', 'DBMS_PDB_EXEC_SQL')
ORDER BY owner,table_name;
OWN TABLE_NAME GRANTEE PRIVILEGE TYPE
SYS DBMS_ADVISOR PUBLIC EXECUTE PACKAGE
SYS DBMS_BACKUP_RESTORE SYSBACKUP EXECUTE PACKAGE
SYS DBMS_FILE_TRANSFER EXECUTE_CATALOG_ROLE EXECUTE PACKAGE
SYS DBMS_IJOB IMP_FULL_DATABASE EXECUTE PACKAGE
SYS DBMS_JAVA PUBLIC EXECUTE PACKAGE
SYS DBMS_JOB PUBLIC EXECUTE PACKAGE
SYS DBMS_LDAP PUBLIC EXECUTE PACKAGE
SYS DBMS_LOB WMSYS EXECUTE PACKAGE
SYS DBMS_LOB PUBLIC EXECUTE PACKAGE
SYS DBMS_LOB ORDPLUGINS EXECUTE PACKAGE
SYS DBMS_LOB ORDSYS EXECUTE PACKAGE
SYS DBMS_OBFUSCATION_TOOLKIT PUBLIC EXECUTE PACKAGE
SYS DBMS_PDB_EXEC_SQL XDB EXECUTE PROCEDURE
SYS DBMS_PDB_EXEC_SQL AUDSYS EXECUTE PROCEDURE
SYS DBMS_RANDOM ORDSYS EXECUTE PACKAGE
SYS DBMS_RANDOM PUBLIC EXECUTE PACKAGE
SYS DBMS_REDACT IMP_FULL_DATABASE EXECUTE PACKAGE
SYS DBMS_REDACT EXECUTE_CATALOG_ROLE EXECUTE PACKAGE
SYS DBMS_SCHEDULER PUBLIC EXECUTE PACKAGE
SYS DBMS_SCHEDULER MDSYS EXECUTE PACKAGE
SYS DBMS_SCHEDULER AUDSYS EXECUTE PACKAGE
SYS DBMS_SQL AUDSYS EXECUTE PACKAGE
SYS DBMS_SQL PUBLIC EXECUTE PACKAGE
SYS DBMS_SQL DVSYS EXECUTE PACKAGE
SYS DBMS_SQL ORDSYS EXECUTE PACKAGE
SYS DBMS_STREAMS_RPC EXECUTE_CATALOG_ROLE EXECUTE PACKAGE
SYS DBMS_XMLGEN PUBLIC EXECUTE PACKAGE
SYS DBMS_XMLQUERY PUBLIC EXECUTE PACKAGE
SYS HISTGRM$ NEIL SELECT TABLE
SYS HTTPURITYPE PUBLIC EXECUTE TYPE
SYS UTL_FILE WMSYS EXECUTE PACKAGE
SYS UTL_FILE PUBLIC EXECUTE PACKAGE
SYS UTL_FILE ORDPLUGINS EXECUTE PACKAGE
SYS UTL_FILE ORDSYS EXECUTE PACKAGE
SYS UTL_HTTP ORDPLUGINS EXECUTE PACKAGE
SYS UTL_HTTP PUBLIC EXECUTE PACKAGE
SYS UTL_INADDR PUBLIC EXECUTE PACKAGE
SYS UTL_INADDR DVSYS EXECUTE PACKAGE
SYS UTL_SMTP PUBLIC EXECUTE PACKAGE
SYS UTL_TCP PUBLIC EXECUTE PACKAGEHigh Level Permissions
Another source of risk are various high level permissions allowing access to objects outside of your own user. The obvious ones are the “ANY” privileges, such as SELECT ANY TABLE, READ ANY TABLE and EXECUTE ANY PROCEDURE. and GRANT ANY PRIVILEGE.
You need to identify which users have these privileges and consider if a focussed role granting the necessary levels of permissions is a viable solution.
SELECT dsp.grantee
,dsp.privilege
,dsp.admin_option
,decode(dr.role,null,'USER','ROLE') user_or_role
,LISTAGG(DISTINCT drp.grantee,', ' ON OVERFLOW TRUNCATE) WITHIN GROUP (ORDER BY drp.grantee) GRANTED_TO
FROM dba_sys_privs dsp LEFT OUTER JOIN dba_roles dr ON (dsp.grantee=dr.role)
LEFT OUTER JOIN dba_role_privs drp ON (dsp.grantee=drp.granted_role)
WHERE dsp.privilege LIKE '%ANY%'
-- exclude Oracle users
AND dsp.grantee NOT IN (SELECT du.username FROM dba_users du WHERE oracle_maintained = 'Y')
GROUP BY dsp.grantee,dsp.privilege,dsp.admin_option,decode(dr.role,null,'USER','ROLE')
ORDER BY user_or_role,dsp.grantee,dsp.privilege
/
GRANTEE PRIVILEGE ADM USER GRANTED_TO
-------------------------- -------------------- --- ---- ------------------------------
AQ_ADMINISTRATOR_ROLE DEQUEUE ANY QUEUE YES ROLE GSMADMIN_ROLE, GSMCATUSER, MDS
AQ_ADMINISTRATOR_ROLE ENQUEUE ANY QUEUE YES ROLE GSMADMIN_ROLE, GSMCATUSER, MDS
AQ_ADMINISTRATOR_ROLE MANAGE ANY QUEUE YES ROLE GSMADMIN_ROLE, GSMCATUSER, MDS
AUDIT_ADMIN AUDIT ANY NO ROLE SYS
...
DATAPUMP_IMP_FULL_DATABASE GRANT ANY ROLE NO ROLE DBA, GSMADMIN_INTERNAL, SYS
DATAPUMP_IMP_FULL_DATABASE SELECT ANY TABLE NO ROLE DBA, GSMADMIN_INTERNAL, SYS
DBA ALTER ANY ASSEMBLY NO ROLE APP_SCHEMA, DODGY_DBA, NEIL,
...
SCHEDULER_ADMIN EXECUTE ANY CLASS YES ROLE DBA, SYS
SCHEDULER_ADMIN EXECUTE ANY PROGRAM YES ROLE DBA, SYS
APP_USER DELETE ANY TABLE NO USER
APP_USER INSERT ANY TABLE NO USER
APP_USER SELECT ANY TABLE NO USER
APP_USER UPDATE ANY TABLE NO USER
NEIL2 SELECT ANY TABLE NO USER Please note that this is just the ‘%ANY%’ privileges. You need to be aware of users with other very high level privileges such as ALTER DATABASE, ALTER SYSTEM, AUDIT SYSTEM, ALTER USER, DROP USER and DROP PUBLIC SYNONYM. Hint: a good starting point is to remove the ‘dsp.privilege LIKE ‘%ANY%’‘ predicate and look to see what permissions SYS has granted to it. Privileges are added and removed with every Oracle release and you need to understand what they are doing.
Understand who has access to what, and if that access is justified. If not, determine how to reduce the access safely, without compromising your system availability whilst tightening your security.
Next we will talk about Observability. Things change. You need to be watching, regularly, to catch the change and ensure it’s valid.
Neil Chandler's DB Blog 