Oracle Database Security
Version 12.1.0.1

General Information
Library Note Morgan's Library Page Footer
The Library is currently in the process of being upgraded from Oracle Database Version 11.2.0.3 to 12.1.0.1. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.
Purpose There are many aspects of Oracle Database security that touch every aspect of working with the database. There are configuration issues arising from SQL*Net connections, how users are created and log on, granting of privileges and roles, profile settings, SQL Injection, and encryption. Auditing too should be considered with respect to good security practices though an audit record will tell you after the fact what happened not prevent it from happening and audit records are of zero value if no one ever looks at them which is what happens 99.99% of the time.
Dependencies
CDB_ROLE_PRIVS    
CDB_SYS_PRIVS    
CDB_TAB_PRIVS    
 
Secure Configuration
The 11g and 12c databases contains a script specifically written to be a starting point for creating a secure configuration. The contents are to the right: Formatted for readability but otherwise unedited. $ORACLE_HOME/rdbms/admin/secconf.sql
Rem
Rem $Header: rdbms/admin/secconf.sql /st_rdbms_12.1.0.1/1 2013/03/13 09:38:23 amunnoli Exp $
Rem
Rem secconf.sql
Rem
Rem Copyright (c) 2006, 2013, Oracle and/or its affiliates.
Rem All rights reserved.
Rem
Rem NAME
Rem secconf.sql - SECure CONFiguration script
Rem
Rem DESCRIPTION
Rem Secure configuration settings for the database include a reasonable
Rem default password profile, password complexity checks, audit settings
Rem (enabled, with admin actions audited), and as many revokes from PUBLIC
Rem as possible. In the first phase, only the default password profile is
Rem included.
Rem
Rem
Rem NOTES
Rem Only invoked for newly created databases, not for upgraded databases
Rem
Rem MODIFIED (MM/DD/YY)
Rem amunnoli 03/05/13 - XbranchMerge amunnoli_bug-16310544 from main
Rem vpriyans 06/05/12 - Bug 12904308: Audit CREATE DIRECTORY by default
Rem vpriyans 03/22/12 - Bug 13413683: Rename predefined audit policies
Rem and add few more actions and privileges
Rem nkgopal 09/08/11 - Bug 12794116: Configure Audit based on input
Rem argument
Rem apsrivas 09/30/08 - bug 7428539: Add missing audit settings
Rem asurpur 06/16/06 - audit changes for sec config
Rem rburns 06/12/06 - secure configuration script
Rem rburns 06/12/06 - Created
Rem

Rem Secure configuration settings. Currently, only the default password
Rem profile is included, without the password complexity check and has
Rem the recommended audit settings. We will add the revokes from PUBLIC, and
Rem the password complexity checks.

-- Create password profile without a password complexity routine, for backward
-- compatibility. Add the routine if possible without breaking tests

ALTER PROFILE DEFAULT LIMIT
PASSWORD_LIFE_TIME 180
PASSWORD_GRACE_TIME 7
PASSWORD_REUSE_TIME UNLIMITED
PASSWORD_REUSE_MAX UNLIMITED
FAILED_LOGIN_ATTEMPTS 10
PASSWORD_LOCK_TIME 1
;

-- Turn on auditing options

PROMPT Do you wish to configure 11g style Audit Configuration OR
PROMPT Do you wish to configure 12c Unified Audit Policies?
PROMPT Enter RDBMS_11G for former or RDBMS_UNIAUD for latter

DECLARE
 USER_CHOICE VARCHAR2(100);
 RDBMS11_CHOICE CONSTANT VARCHAR2(20) := 'RDBMS_11G';
 UNIAUD_CHOICE CONSTANT VARCHAR2(20) := 'RDBMS_UNIAUD';
BEGIN
  USER_CHOICE := '&1';

  -- Audit policy to audit user account and privilege management
  EXECUTE IMMEDIATE 'CREATE AUDIT POLICY ORA_ACCOUNT_MGMT ' ||
  'ACTIONS CREATE USER, ALTER USER, DROP USER, ' ||
  'CREATE ROLE, DROP ROLE, ALTER ROLE, ' ||
  'SET ROLE, GRANT, REVOKE';

  -- Audit policy to audit Database parameter settings
  EXECUTE IMMEDIATE 'CREATE AUDIT POLICY ORA_DATABASE_PARAMETER '||
  'ACTIONS ALTER DATABASE, ALTER SYSTEM, CREATE SPFILE';

  -- Audit policy containing all Secure Configuration audit-options
  EXECUTE IMMEDIATE  'CREATE AUDIT POLICY ORA_SECURECONFIG ' ||
  'PRIVILEGES ALTER ANY TABLE, CREATE ANY TABLE, ' ||
  'DROP ANY TABLE, CREATE ANY PROCEDURE, ' ||
  'DROP ANY PROCEDURE, ALTER ANY PROCEDURE, '||
  'GRANT ANY PRIVILEGE, ' ||
  'GRANT ANY OBJECT PRIVILEGE, GRANT ANY ROLE, '||
  'AUDIT SYSTEM, CREATE EXTERNAL JOB, ' ||
  'CREATE ANY JOB, CREATE ANY LIBRARY, ' ||
  'EXEMPT ACCESS POLICY, CREATE USER, ' ||
  'DROP USER, ALTER DATABASE, ALTER SYSTEM, '||
  'CREATE PUBLIC SYNONYM, DROP PUBLIC SYNONYM, ' ||
  'CREATE SQL TRANSLATION PROFILE, ' ||
  'CREATE ANY SQL TRANSLATION PROFILE, ' ||
  'DROP ANY SQL TRANSLATION PROFILE, ' ||
  'ALTER ANY SQL TRANSLATION PROFILE, ' ||
  'TRANSLATE ANY SQL, ' ||
  'CREATE ANY SQL TRANSLATION PROFILE, ' ||
  'DROP ANY SQL TRANSLATION PROFILE, ' ||
  'ALTER ANY SQL TRANSLATION PROFILE, ' ||
  'TRANSLATE ANY SQL, EXEMPT REDACTION POLICY, ' ||
  'PURGE DBA_RECYCLEBIN, LOGMINING, ' ||
  'ADMINISTER KEY MANAGEMENT ' ||
  'ACTIONS ALTER USER, CREATE ROLE, ALTER ROLE, DROP ROLE, '||
  'SET ROLE, CREATE PROFILE, ALTER PROFILE, ' ||
  'DROP PROFILE, CREATE DATABASE LINK, ' ||
  'ALTER DATABASE LINK, DROP DATABASE LINK, '||
  'LOGON, LOGOFF, CREATE DIRECTORY, DROP DIRECTORY, '||
  'CREATE PLUGGABLE DATABASE, ' ||
  'DROP PLUGGABLE DATABASE, '||
  'ALTER PLUGGABLE DATABASE ';

  IF USER_CHOICE = RDBMS11_CHOICE THEN
    -- 11g Secure Audit Configuration
    EXECUTE IMMEDIATE 'AUDIT ALTER ANY TABLE BY ACCESS';
    EXECUTE IMMEDIATE 'AUDIT CREATE ANY TABLE BY ACCESS';
    EXECUTE IMMEDIATE 'AUDIT DROP ANY TABLE BY ACCESS';
    EXECUTE IMMEDIATE 'AUDIT CREATE ANY PROCEDURE BY ACCESS';
    EXECUTE IMMEDIATE 'AUDIT DROP ANY PROCEDURE BY ACCESS';
    EXECUTE IMMEDIATE 'AUDIT ALTER ANY PROCEDURE BY ACCESS';
    EXECUTE IMMEDIATE 'AUDIT GRANT ANY PRIVILEGE BY ACCESS';
    EXECUTE IMMEDIATE 'AUDIT GRANT ANY OBJECT PRIVILEGE BY ACCESS';
    EXECUTE IMMEDIATE 'AUDIT GRANT ANY ROLE BY ACCESS';
    EXECUTE IMMEDIATE 'AUDIT AUDIT SYSTEM BY ACCESS';
    EXECUTE IMMEDIATE 'AUDIT CREATE EXTERNAL JOB BY ACCESS';
    EXECUTE IMMEDIATE 'AUDIT CREATE ANY JOB BY ACCESS';
    EXECUTE IMMEDIATE 'AUDIT CREATE ANY LIBRARY BY ACCESS';
    EXECUTE IMMEDIATE 'AUDIT CREATE PUBLIC DATABASE LINK BY ACCESS';
    EXECUTE IMMEDIATE 'AUDIT EXEMPT ACCESS POLICY BY ACCESS';
    EXECUTE IMMEDIATE 'AUDIT ALTER USER BY ACCESS';
    EXECUTE IMMEDIATE 'AUDIT CREATE USER BY ACCESS';
    EXECUTE IMMEDIATE 'AUDIT ROLE BY ACCESS';
    EXECUTE IMMEDIATE 'AUDIT CREATE SESSION BY ACCESS';
    EXECUTE IMMEDIATE 'AUDIT DROP USER BY ACCESS';
    EXECUTE IMMEDIATE 'AUDIT ALTER DATABASE BY ACCESS';
    EXECUTE IMMEDIATE 'AUDIT ALTER SYSTEM BY ACCESS';
    EXECUTE IMMEDIATE 'AUDIT ALTER PROFILE BY ACCESS';
    EXECUTE IMMEDIATE 'AUDIT DROP PROFILE BY ACCESS';
    EXECUTE IMMEDIATE 'AUDIT DATABASE LINK BY ACCESS';
    EXECUTE IMMEDIATE 'AUDIT SYSTEM AUDIT BY ACCESS';
    EXECUTE IMMEDIATE 'AUDIT PROFILE BY ACCESS';
    EXECUTE IMMEDIATE 'AUDIT PUBLIC SYNONYM BY ACCESS';
    EXECUTE IMMEDIATE 'AUDIT SYSTEM GRANT BY ACCESS';
    EXECUTE IMMEDIATE 'AUDIT CREATE SQL TRANSLATION PROFILE BY ACCESS';
    EXECUTE IMMEDIATE 'AUDIT CREATE ANY SQL TRANSLATION PROFILE BY ACCESS';
    EXECUTE IMMEDIATE 'AUDIT DROP ANY SQL TRANSLATION PROFILE BY ACCESS';
    EXECUTE IMMEDIATE 'AUDIT ALTER ANY SQL TRANSLATION PROFILE BY ACCESS';
    EXECUTE IMMEDIATE 'AUDIT TRANSLATE ANY SQL BY ACCESS';
    EXECUTE IMMEDIATE 'AUDIT PURGE DBA_RECYCLEBIN BY ACCESS';
    EXECUTE IMMEDIATE 'AUDIT LOGMINING BY ACCESS';
    EXECUTE IMMEDIATE 'AUDIT EXEMPT REDACTION POLICY BY ACCESS';
    EXECUTE IMMEDIATE 'AUDIT ADMINISTER KEY MANAGEMENT BY ACCESS';
    EXECUTE IMMEDIATE 'AUDIT DIRECTORY BY ACCESS';
    EXECUTE IMMEDIATE 'AUDIT PLUGGABLE DATABASE BY ACCESS';
  ELSIF USER_CHOICE = UNIAUD_CHOICE THEN
    -- 12c Secure Audit Configuration

    -- Enable ORA_SECURECONFIG for all users
    EXECUTE IMMEDIATE 'AUDIT POLICY ORA_SECURECONFIG';
  ELSE
    DBMS_OUTPUT.PUT_LINE('Invalid Input "' || USER_CHOICE || '". Please try again');
  END IF;
END;
/
I personally find Oracle's file far too loose and thus am providing my own variant. It is not substantially better but provides an incremental improvement. conn / as sysdba

@?/rdbms/admin/utlpwdmg.sql

ALTER PROFILE DEFAULT LIMIT
PASSWORD_LIFE_TIME 90
PASSWORD_GRACE_TIME 10
PASSWORD_REUSE_TIME 0
PASSWORD_REUSE_MAX 0
FAILED_LOGIN_ATTEMPTS 3
PASSWORD_LOCK_TIME 30;
PASSWORD_VERIFY_FUNCTION verify_function;

Audit create any procedure by access;
Audit alter any procedure by access;
Audit grant any privilege by access;
Audit grant any object privilege by access;
Audit grant any role by access;
Audit audit system by access;
Audit create external job by access;
Audit create any job by access;
Audit create any library by access;
Audit create public database link by access;
Audit exempt access policy by access;
Audit alter user by access;
Audit create user by access;
Audit role by access;
Audit create session by access;
Audit alter database by access;
Audit alter system by access;
Audit alter profile by access;
Audit drop profile by access;
 
Additional Actions
DDL Event Triggers CREATE OR REPLACE TRIGGER save_our_db
BEFORE DROP OR TRUNCATE
ON DATABASE

DECLARE
 oper VARCHAR2(30);
BEGIN
  SELECT ora_sysevent
  INTO oper
  FROM dual;

  IF oper = 'DROP' THEN
    RAISE_APPLICATION_ERROR(-20998, 'Attempt To Drop A Production Object Has Been Logged');
  ELSIF oper = 'TRUNCATE' THEN
    RAISE_APPLICATION_ERROR(-20999, 'Attempt To Truncate A Production Table Has Been Logged');
  END IF;
END save_our_db;
/

-- it is highly advisable to alter this basic trigger to include
-- logging and and email created with UTL_MAIL routed to security
Enable Case Sensitive Passwords set linesize 121
col name format a30
col value format a30

SELECT name, value
FROM gv$parameter
WHERE (name LIKE '%sensitive%');

SELECT username, password_versions
FROM dba_users;

alter user sh identified by Sh;

conn sh/sh@pdborcl

conn sh/Sh@pdborcl
Object Privileges -- there are a number of very dangerous built-in packages owned by SYS. Verify that access has not been granted in appropriately.
-- the object list included here is a partial and intended only for guidance.


SELECT grantee, table_name AS OBJECT_NAME, privilege
FROM cdb_tab_privs
WHERE table_name IN ('DBMS_LOB', 'DBMS_SQL', 'DBMS_SYS_SQL', 'UTL_FILE', 'DBMS_NETWORK_ACL_ADMIN')
ORDER BY 1,2;

SELECT grantee, granted_role, admin_option
FROM cdb_role_privs
WHERE granted_role IN ('EXECUTE_CATALOG_ROLE', 'DBA', 'EXP_FULL_DATABASE', 'IMP_FULL_DATABASE', 'DATAPUMP_EXP_FULL_DATABASE', 'DATAPUMP_IMP_FULL_DATABASE');

-- corrective action should look something like this.

REVOKE execute ON utl_file FROM public;

-- a privilege granted by default by Oracle which is wholly unnecessary and irresponsible.
Remove Default Passwords SELECT d.username, u.account_status
FROM dba_users_with_defpwd d, dba_users u
WHERE d.username = u.username
ORDER BY 2,1;
SQLNET.ORA Alteration tcp.validnode_checking=yes
tcp.invited_nodes=(<comma_delimited_list_of_specific_ip_addresses)
sqlnet.encryption_server=required
sqlnet.encryption_client=required
Secure Roles SELECT grantee, granted_role
FROM dba_role_privs
WHERE granted_role IN ('CONNECT', 'RESOURCE')
ORDER BY 2,1;

Revoke CONNECT and RESOURCE from any unlocked account and create your own role with those specific privileges required only.
SQL Injection Click Here
SYSDBA and SYSOPER -- these two privileges are not "normal" system privielges and can not be found in the grants in dba_sys_privs
-- as they are recorded in the password file. To locate them:


conn / as sysdba

SELECT *
FROM v$pwfile_users
WHERE sysdba='TRUE';

GRANT sysdba TO uwclass;

SELECT *
FROM v$pwfile_users
WHERE sysdba='TRUE';

REVOKE sysdba FROM uwclass;

SELECT *
FROM v$pwfile_users
WHERE sysdba='TRUE';
System Event Triggers Click Here
System Events Click Here
System Privileges Grant CREATE privileges such as CREATE TABLE and CREATE PROCEDURE only for the duration of schema creation. As soon as the application schemas has been created revoke those privileges and do not grant them again except during, and for the duration, of a maintenance window.
Tablespace Encryption Click Here
Transparent Data Encryption Click Here
Wallet -- in sqlnet.ora
ENCRYPTION_WALLET_LOCATION = (SOURCE = (METHOD=FILE) (METHOD_DATA = (DIRECTORY = c:\oracle\admin\orabase\wallet)))

-- in SQL*Plus
conn / as sysdba

ALTER SYSTEM SET ENCRYPTION KEY AUTHENTICATED BY "N0way!iN";

desc gv$encryption_wallet

col wrl_parameter format a40

SELECT * FROM gv$encryption_wallet;

Related Topics
ACCESSIBLE BY Clause
Auditing
DBMS_DISTRIBUTED_TRUST_ADMIN
DBMS_CRYPTO
DBMS_FGA
DBMS_RANDOM
DBMS_RLS
DDL Event Triggers
FIPS Flagging
How Can I Secure Credit Cards
Net Services
Object Privileges
Policies
Profiles
Real Application Security
Roles
SecureFiles
SQL*Net
System Event Triggers
System Privileges
Transparent Data Encryption
Unified Audit Policies

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2014 Daniel A. Morgan All Rights Reserved