Neil Chandler's DB Blog

Fundamental Security Part Four – Permissions Checking

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 YES

PUBLIC 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    PACKAGE

High 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.