Oracle Database Administration/Database Security

This lesson introduces Oracle database security.

Objectives and Skills

Objectives and skills for the database security portion of Oracle Database Administration I certification include:^[1]

Implementing Oracle Database Security
- Database Security and Principle of Least Privilege
- Work with Standard Database Auditing

Review Oracle: Managing Compliance.
Use the following query to identify UTL packages that PUBLIC has EXECUTE privileges for:
SELECT DISTINCT TABLE_NAME FROM DBA_TAB_PRIVS
WHERE GRANTEE = 'PUBLIC' AND TABLE_NAME LIKE 'UTL_%' AND PRIVILEGE = 'EXECUTE'
ORDER BY TABLE_NAME;
Consider revoking EXECUTE access from PUBLIC for all UTL packages, but at a minimum restrict the following:
REVOKE EXECUTE ON DBMS_JOB FROM PUBLIC;
REVOKE EXECUTE ON DBMS_LOB FROM PUBLIC;
REVOKE EXECUTE ON UTL_FILE FROM PUBLIC;
REVOKE EXECUTE ON UTL_HTTP FROM PUBLIC;
REVOKE EXECUTE ON UTL_SMTP FROM PUBLIC;
REVOKE EXECUTE ON UTL_TCP FROM PUBLIC;

Review Oracle: Audit Trail.
Enable auditing.
1. Use the following queries to enable auditing:
  ALTER SYSTEM SET AUDIT_TRAIL = DB_EXTENDED SCOPE = SPFILE;
2. Stop and restart the database so that the changes take effect using the following queries:
  shutdown transactional
  startup
3. Audit database connections using the following queries:
  AUDIT SESSION WHENEVER SUCCESSFUL;
  AUDIT SESSION WHENEVER NOT SUCCESSFUL;
Examine the audit log.
1. View the audit log using the following query:
  SELECT * FROM DBA_AUDIT_TRAIL;