Assessment of Oracle Database

Assessment Query

This SQL script is designed to assess the security configuration of an Oracle database. It focuses on several key areas:

1. Oracle Database Installation and Patching Requirements

This section is not covered in the provided script.

2. Oracle Parameter Settings

Listener Settings & Database Settings:
1
SELECT * FROM V$PARAMETER;

Specific Database Settings:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
SELECT UPPER(VALUE)
FROM V$PARAMETER
WHERE UPPER(NAME) = 'AUDIT_SYS_OPERATIONS'
OR UPPER(NAME) = 'AUDIT_TRAIL'
OR UPPER(NAME) = 'GLOBAL_NAMES'
OR UPPER(NAME) = 'LOCAL_LISTENER'
OR UPPER(NAME) = 'O7_DICTIONARY_ACCESSIBILITY'
OR UPPER(NAME) = 'OS_ROLES'
OR UPPER(NAME) = 'REMOTE_LISTENER'
OR UPPER(NAME) = 'REMOTE_LOGIN_PASSWORDFILE'
OR UPPER(NAME) = 'REMOTE_OS_AUTHENT'
OR UPPER(NAME) = 'REMOTE_OS_ROLES'
OR UPPER(NAME) = 'UTL_FILE_DIR'
OR UPPER(NAME) = 'SEC_CASE_SENSITIVE_LOGON'
OR UPPER(NAME) = 'SEC_MAX_FAILED_LOGIN_ATTEMPTS'
OR UPPER(NAME) = 'SEC_PROTOCOL_ERROR_FURTHER_ACTION'
OR UPPER(NAME) = 'SEC_PROTOCOL_ERROR_TRACE_ACTION'
OR UPPER(NAME) = 'SEC_RETURN_SERVER_RELEASE_BANNER'
OR UPPER(NAME) = 'SQL92_SECURITY'
OR UPPER(NAME) = '_TRACE_FILES_PUBLIC'
OR UPPER(NAME) = 'RESOURCE_LIMIT';

3. Oracle Connection and Login Restrictions

Profile Settings:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
SELECT * FROM DBA_PROFILES;
SELECT * FROM DBA_USERS;

SELECT * FROM DBA_PROFILES 
WHERE RESOURCE_NAME = "FAILED_LOGIN_ATTEMPTS"
OR RESOURCE_NAME = "PASSWORD_LOCK_TIME"
OR RESOURCE_NAME = "PASSWORD_LIFE_TIME"
OR RESOURCE_NAME = "PASSWORD_REUSE_MAX"
OR RESOURCE_NAME = "PASSWORD_REUSE_TIME"
OR RESOURCE_NAME = "PASSWORD_GRACE_TIME"
OR RESOURCE_NAME = "PASSWORD_VERIFY_FUNCTION"
OR RESOURCE_NAME = "SESSIONS_PER_USER";

Users with External Authentication:

1
2
3
SELECT USERNAME
FROM DBA_USERS
WHERE PASSWORD='EXTERNAL';

Users with Default Profile and Open Accounts (Excluding System Accounts):

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
SELECT USERNAME
FROM DBA_USERS
WHERE PROFILE='DEFAULT'
AND ACCOUNT_STATUS='OPEN'
AND USERNAME 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', 'CISSCAN');

4. Oracle User Access and Authorization Restrictions

Table and System Privileges:

1
2
3
4
SELECT * FROM DBA_TAB_PRIVS;
SELECT * FROM DBA_SYS_PRIVS;
SELECT * FROM DBA_ROLE_PRIVS;
SELECT * FROM ALL_TABLES

Default Public Privileges for Packages and Object Types:

 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
SELECT GRANTEE,PRIVILEGE,TABLE_NAME
FROM DBA_TAB_PRIVS
WHERE GRANTEE='PUBLIC'
AND PRIVILEGE='EXECUTE'
OR TABLE_NAME='DBMS_ADVISOR'
OR TABLE_NAME='DBMS_CRYPTO'
OR TABLE_NAME='DBMS_JAVA'
OR TABLE_NAME='DBMS_JAVA_TEST'
OR TABLE_NAME='DBMS_JOB'
OR TABLE_NAME='DBMS_LDAP'
OR TABLE_NAME='DBMS_LOB'
OR TABLE_NAME='DBMS_OBFUSCATION_TOOLKIT'
OR TABLE_NAME='DBMS_RANDOM'
OR TABLE_NAME='DBMS_SCHEDULER'
OR TABLE_NAME='DBMS_SQL'
OR TABLE_NAME='DBMS_XMLGEN'
OR TABLE_NAME='DBMS_XMLQUERY'
OR TABLE_NAME='UTL_FILE'
OR TABLE_NAME='UTL_INADDR'
OR TABLE_NAME='UTL_TCP'
OR TABLE_NAME='UTL_MAIL'
OR TABLE_NAME='UTL_SMTP'
OR TABLE_NAME='UTL_DBWS'
OR TABLE_NAME='UTL_ORAMTS'
OR TABLE_NAME='UTL_HTTP'
OR TABLE_NAME='HTTPURITYPE';

Revoke Non-Default Privileges for Packages and Object Types:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
SELECT GRANTEE,PRIVILEGE,TABLE_NAME
FROM DBA_TAB_PRIVS
WHERE GRANTEE='PUBLIC'
AND PRIVILEGE='EXECUTE'
OR TABLE_NAME='DBMS_SYS_SQL'
OR TABLE_NAME='DBMS_BACKUP_RESTORE'
OR TABLE_NAME='DBMS_AQADM_SYSCALLS'
OR TABLE_NAME='DBMS_REPCAT_SQL_UTL'
OR TABLE_NAME='INITJVMAUX'
OR TABLE_NAME='DBMS_STREAMS_ADM_UTL'
OR TABLE_NAME='DBMS_AQADM_SYS'
OR TABLE_NAME='DBMS_STREAMS_RPC'
OR TABLE_NAME='DBMS_PRVTAQIM'
OR TABLE_NAME='LTADM'
OR TABLE_NAME='WWV_DBMS_SQL'
OR TABLE_NAME='WWV_EXECUTE_IMMEDIATE'
OR TABLE_NAME='DBMS_IJOB'
OR TABLE_NAME='DBMS_FILE_TRANSFER';

Revoke Excessive System Privileges:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
SELECT GRANTEE, PRIVILEGE
FROM DBA_SYS_PRIVS
WHERE PRIVILEGE='SELECT ANY DICTIONARY' 
OR PRIVILEGE='SELECT ANY TABLE' 
OR PRIVILEGE='AUDIT SYSTEM' 
OR PRIVILEGE='EXEMPT ACCESS POLICY' 
OR PRIVILEGE='BECOME USER' 
OR PRIVILEGE='CREATE PROCEDURE' 
OR PRIVILEGE='ALTER SYSTEM' 
OR PRIVILEGE='CREATE ANY LIBRARY' 
OR PRIVILEGE='CREATE LIBRARY' 
OR PRIVILEGE='GRANT ANY OBJECT PRIVILEGE' 
OR PRIVILEGE='GRANT ANY ROLE' 
OR PRIVILEGE='GRANT ANY PRIVILEGE'; 

Revoke Role Privileges:

1
2
3
4
5
6
SELECT GRANTEE, GRANTED_ROLE
FROM DBA_ROLE_PRIVS
WHERE granted_role='DELETE_CATALOG_ROLE'
OR granted_role='SELECT_CATALOG_ROLE'
OR granted_role='EXECUTE_CATALOG_ROLE'
OR granted_role='DBA';

Revoke Excessive Table and View Privileges:

1
2
3
4
5
6
7
8
SELECT GRANTEE, PRIVILEGE
FROM DBA_TAB_PRIVS
WHERE TABLE_NAME='AUD$'
OR TABLE_NAME='USER_HISTORY$'
OR TABLE_NAME='LINK$'
OR TABLE_NAME='USER$'
OR TABLE_NAME LIKE 'DBA_%'
OR TABLE_NAME='SCHEDULER$_CREDENTIAL';

5. Audit/Logging Policies and Procedures

Traditional Auditing:
1
SELECT * FROM DBA_STMT_AUDIT_OPTS;

Unified Auditing:

1
2
SELECT * FROM AUDIT_UNIFIED_POLICIES
SELECT * FROM AUDIT_UNIFIED_ENABLED_POLICIES 

Password Management

Create a profile to restrict login attempts:

1
CREATE PROFILE  LIMIT FAILED_LOGIN_ATTEMPTS 5 PASSWORD_GRACE_TIME 10 PASSWORD_LIFE_TIME 90;

Modify a user’s profile:
1
ALTER USER PROFILE ;
Change a user’s password:
1
ALTER USER IDENTIFIED BY ;

Privileges Assignment

Grant privileges:
1
GRANT TO ;
Revoke privileges:
1
REVOKE FROM ;

For more details on system privileges, object privileges, and roles, refer to: http://www.oradev.com/oracle_grant_revoke.html

Audit “Create Session”

Enable auditing for “Create Session”:
1
AUDIT SESSION BY ;
Disable auditing for “Create Session”:
1
NOAUDIT SESSION BY ;