Chapter 12 Implementing Oracle Database Security

Database Security and Principle of Least Privilege
Principle of Least Privilege: no one should have access to anything beyond the absolute minimum needed to perform their work, and anything not specifically allowed is forbidden.

Public Privileges should be revoked.
UTL_FILE, UTL_TCP, UTL_SMTP, UTL_HTTP

Security-Critical Instance Parameters (all static, alter with scope=spfile)
UTL_FILE_DIR=NULL (default)
REMOTE_OS_AUTHENT=FALSE (default)
OS_AUTHENT_PREFIX for external authentication, either local or remote

07_DICTIONARY_ACCESSIBILITY=FALSE (default) for object privileges with the ANY keyword, FALSE means cannot see data dictionary tales (objects owned by SYS)
Can use SELECT ANY DICTIONARY privilege instead of setting it to TRUE

REMOTE_LOGIN_PASSWORDFILE=NONE (default)
to connect to the instance as SYSDBA or SYSOPER over network.

SHARED: use common password file
EXCLUSIVE: use instance-specific password file


Work with Standard Database Auditing
Auditing SYSDBA Activity
AUDIT_SYS_OPERATIONS=TRUE (default is FALSE)
audit records are stored in AUDIT_FILE_DEST (Unix, Linux) and Windows Application Log (Windows)

Database Auditing for Select & DML, capture commands only
capture events related to use of privileges (Privileges), execution of certain statements (Commands), and access to certain objects (Objects).

AUDIT_TRAIL=
NONE (or FALSE)
OS stores audit records in AUDIT_FILE_DEST (Unix, Linux) and Windows Application Log
DB, DB_EXTENDED stores audit records in SYS.AUD$, DBA_AUDIT_TRAIL view
XML, XML EXTENDED same as OS but stores in XML format

In addtion to DBA_AUDIT_TRAIL view, there are
DBA_AUDIT_OBJECT
DBA_AUDIT_STATEMENT
DBA_AUDIT_SESSION

AUDIT ... BY SESSION; (default)
AUDIT ... BY ACCESS; one audit record for every violation
AUDIT ... WHENEVER SUCCESSFUL;
AUDIT ... WHENEVER NOT SUCCESSFUL;
e.g.: AUDIT session WHENEVER NOT SUCCESSFUL;
AUDIT ... (default successful or not are audited)

Value-Based Auditing with Triggers (DML only)
capture more information about what the changes, which rows, what actual values by using PL/SQL code.

Fine-Grained Auditing (FGA)
audit certain rows and certain columns of certain rows
can run PL/SQL code

DBMS_FGA.ADD_POLICY(
OBJECT_SCHEMA,
OBJECT_NAME,
POLICY_NAME,
AUDIT_CONDITION, if left NULL means any row is audited
AUDIT_COLUMN, if left NULL means any column is audited
HANDLER_SCHEMA,
HANDLER_MODULE, PL/SQL procedure to be call when condition is met
ENABLE, default TRUE
STATEMENT_TYPES, default SELECT, can be one or more DML
AUDIT_TRAIL, SQL_TEXT and SQL_BIND
AUDIT_COLUMN_OPTS); DBMS_FGA.ANY_COLUMNS or DBMS_FGA.ALL_COLUMNS


DBMS_FGA.ENABLE_POLICY
DBMS_FGA.DISABLE_POLICY

audit records are stored in DBA_FGA_AUDIT_TRAIL view

DBA_COMMON_AUDIT_TRAIL = DBA_AUDIT_TRAIL + DBA_FGA_AUDIT_TRAIL