Database Assessment - Oracle

Assessment Query

/** 1: Oracle Database Installation and Patching Requirements
**/
SELECT * FROM DBA_USERS_WITH_DEFPWD;
SELECT * FROM ALL_USERS;

/** 2: Oracle Parameter Settings
 * Listener Settings
 * Database Settings
**/
SELECT * FROM V$PARAMETER;

/** 4.2 Database Settings **/ 

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
**/
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";

SELECT USERNAME
FROM DBA_USERS
WHERE PASSWORD='EXTERNAL';

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
 * Default Public Privileges for Packages and Object Types
 * Revoke Non-Default Privileges for Packages and Object Types
 * Revoke Excessive System Privileges
 * Revoke Role Privileges
 * Revoke Excessive Table and View Privileges
**/
SELECT * FROM DBA_TAB_PRIVS;
SELECT * FROM DBA_SYS_PRIVS;
SELECT * FROM DBA_ROLE_PRIVS;
SELECT * FROM ALL_TABLES


/** 4.1 Default Public Privileges for Packages and Object Types **/

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';

/** 4.2 Revoke Non-Default Privileges for Packages and Object Types **/
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';


/** 4.3 Revoke Excessive System Privileges **/
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'; 


/** 4.4 Revoke Role Privileges **/
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';


/** 4.5 Revoke Excessive Table and View Privileges **/
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
 * Unified Auditing
**/
SELECT * FROM DBA_STMT_AUDIT_OPTS;
SELECT * FROM AUDIT_UNIFIED_POLICIES
SELECT * FROM AUDIT_UNIFIED_ENABLED_POLICIES

Password Management

To create profile with login restriction

CREATE PROFILE <PROFILE NAME> LIMIT FAILED_LOGIN_ATTEMPTS 5 PASSWORD_GRACE_TIME 10 PASSWORD_LIFE_TIME 90;

To alter the profile for a user

ALTER USER <USERNAME> PROFILE <PROFILE NAME>;

To change user password

ALTER USER <USERNAME> IDENTIFIED BY <NEW PASSWORD>;

Privileges Assignment

To assign privileges

GRANT <PRIVILEGE> TO <GRANTEE>;

To revoke privileges

REVOKE <PRIVILEGE> FROM <GRANTEE>;

Please visit http://www.oradev.com/oracle_grant_revoke.html to study more on system privilege, object privilege and role.

Audit "Create Session"

To audit "Create Session"

AUDIT SESSION BY <USERNAME>;

To prevent audit "Create Session"

NOAUDIT SESSION BY <USERNAME>;