Oracle Database Administration/Database Security

This lesson introduces Oracle database security.

Objectives and SkillsEdit

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

ReadingsEdit

  1. Oracle: Database 2 Day + Security Guide

MultimediaEdit

  1. YouTube: Security in the Database

ActivitiesEdit

PrivilegesEdit

  1. Review Oracle: Managing Compliance.
  2. 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;
  3. 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;

AuditingEdit

  1. Review Oracle: Audit Trail.
  2. 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;
  3. Examine the audit log.
    1. View the audit log using the following query:
      SELECT * FROM DBA_AUDIT_TRAIL;

See AlsoEdit

ReferencesEdit