Protecting Data Assets and Reducing Risk

(1)

Protecting Data Assets

and Reducing Risk

Michelle Malcher

Enterprise Database Security

Oracle Open World 2014

(2)

Introduction

Michelle Malcher

 Enterprise Database Security – Wells Fargo

 Oracle ACE Director

 16+ years DBA experience

 Co-Author, Oracle Database 12c Install, Configure &

Maintain Like a Professional, Securing Oracle Database 12c: A Technical Primer, Oracle Database

Administration for the Microsoft SQL Server DBA

(3)

Security Journey

Database Security for the Practical DBA

 Database Access

 Protect Data Assets

 Maintain Secure Environments

 Simplify Access and Changes

(4)

Security Journey

Multiple Levels of Security

 Securing Access not enough

– Encryption – Firewalls

– Privileged Users – Auditing

(5)

(6)

Security Journey

Security Planning  Security Features  Secure Configuration  Patching  Auditing  Compliance  Responsibilities – DBA – Security Admin

(7)

Security Journey

Security Planning  Data Classification – Sensitive Data – Access limited – Public information – By database or scattered

(8)

Security Journey

Security Planning

 Security matching the business need

 Sensitive data protected

 Least Privilege

(9)

(10)

Practical Security

Practical Policies

 Easy to maintain

 Transparent data protection

 Creating uniform ways to secure information

(11)

Practical Security

Practical Policies

 Use tools provided

 Application security can find sensitive information

– Credit Card – SS# – E-Business Suite – Fusion Apps  Schema Owners – Create tables – Read, write – Execute

(12)

Practical Security

Passwords

 Password Functions

• Verify_function_11g

• 8-30 characters and not usernames

– Ora12c_verify_funciton

– Ora12c_strong_verify_function – Utlpwdmg.sql

– ALTER PROFILE default LIMIT PASSWORD_VERIFY_FUNCTION ora12c_strong_verify_function;

(13)

Practical Security

Passwords

 External password store

– Store credentials for connecting to databases – Stop embedding usernames and passwords

connect hrmgr@hr:1525 Password:

(14)

Practical Security

Passwords

 Client side wallet

mkstore –wrl /u01/hr/wallets -create Password: (for wallet)

mkstore –wrl /u01/hr/wallets –create credential db01 hr Password: (for hr)

(15)

Practical Security

Wallets  sqlnet.ora SQLNET.WALLET_OVERRIDE=TRUE WALLET_LOCATION =(source = method = file) (method_data = (directory =/private/orcl/network/admin))) mkstore –wrl wallet_location -listcredential

(16)

Practical Security

Encryption

 Transparent Data Encryption (TDE)

– Column Data – Tablespace Level – Wallet

• Master Encryption key • Table Encryption key • Password protected

(17)

Practical Security

Encryption

 Wallet for encryption

SQLPLUS> alter system set encryption key identified by password; SQLPLUS> alter system set encryption wallet open identified by

password;

 Auto Login wallet

– Do not use alter system set encryption

– Use orapki utility or Oracle Wallet Manager

(18)

Practical Security

Encryption

 Column Encryption

CREATE TABLE CUSTOMER

(CUST_ID NUMBER NOT NULL, FIRST_NAME VARCHAR2(50) NOT NULL,

LAST_NAME VARCHAR2(50) NOT NULL, ACCOUNT_ID NUMBER ENCRYPT using ‘AES128’,

CUST_TYPE VARCHAR2(30), CREATED DATE,

(19)

Practical Security

Encryption

 Tablespace level encryption

– Wallet needs to be open to view data – Remains encrypted in RMAN backup – Transparent to application

– Not for system tablespaces

• SYSTEM, SYSAUX, UNDO, TEMP

– DBA_TABLESPACES – ENCRYPTED column – V$ENCRYPTED_TABLESPACES

(20)

Practical Security

Encryption

 Encrypted Tablespace

CREATE tablespace DATA_ENCRYPT01

datafile ‘/u01/oracle/oradata/mmtest/data_encrypt01.dbf’ size 100M ENCRYPTION default storage(ENCRYPT);

(21)

Practical Security

Encryption/Wallets  Useful Views – v$encrypted_tablespaces – v$encrypted_wallet – v$wallet – v$client_secrets – v$encryption_keys – DBA_ENCRYPTION_KEY_USAGE

(22)

Practical Security

Redaction  Mask Data – FULL • XXXXXX ******** – Partial • XXX XX 1234 – Random • 123 45 6789 -> 454 34 2932

(23)

Practical Security

Redaction  Policies – Users – Applications – Label Security

(24)

Practical Security

(25)

Practical Security

(26)

Practical Security

Restrict System

 07_DICTIONARY_ACCESSIBILITY=FALSE

– Restricts changes to SYSTEM objects – Default is now FALSE

 ANY privilege does not = SYSTEM objects

 SELECT_CATALOG_ROLE

 Role changes

– Connect – Resource

(27)

Practical Security

Controlling Caller Privileges

 Procedure runs as invoker rights

 GRANT INHERIT PRIVILEGES on USER invoking_user to

procedure_user (role)

 Public inherit privilege

(28)

(29)

Practical Security

Use Roles

 Manage Permissions and Privileges with roles

 Keep it simple and set standards

 New roles for administrators

(30)

(31)

Least Privileged

 New accounts without DBA and SYSTEM access

 Only logging in with privileges needed

 SELECT CATALOG?

 Different system roles

(32)

Least Privileged

 Privilege Analysis

– See the permissions used

– Be able to maintain least privilege – Verify Permissions

(33)

Least Privileged

Privilege Analysis

 Getting to the Least Privilege Model

 Captures and reports on permissions used

 Shows unused privileges

 DBMS_PRIVILEGE_CAPTURE

– Enable Policies – Generate Reports

 DBA_ tables

– DBA_USED … (PRIVS, OBJPRIVS) – DBA_UNUSED … (PRIVS, OBJPRIVS)

(34)

Least Privileged

Privilege Analysis BEGIN DBMS_PRIVILEGE_CAPTURE.CREATE_CAPTURE (NAME => ‘dba_capture_all_privs’, DESCRIPTION=>‘privilege_analysis_exp_for_all_user’, TYPE=> DBMS_PRIVILEGE_CAPTURE.G_DATABASE); END; BEGIN DBMS_PRIVILEGE_CAPTURE.GENERATE_RESULT (NAME => ‘dba_capture_all_privs’);

(35)

Least Privileged

Privilege Analysis

 Capture different privileges for the database, roles and

context

– G_DATABASE, G_ROLE, G_CONTEXT – G_ROLE_AND_CONTEXT

 Creating scripts to create a role or revoke privileges

– DBA_USED_OBJPRIVS – DBA_UNUSED_OBJPRIVS

(36)

(37)

Least Privileged

Container and Pluggable

 Oracle 12c CDB and PDB

– Separate administration

– Application DBAs permission by PDB – Different permissions by tasks

(38)

Least Privileged

Common and Local

 Oracle 12c CDB and PDB

– Common

• Container => existing and future

– Local

• Where permission was granted • Container=current

– PUBLIC should not get common – PDB own set of PUBLIC

(39)

Least Privileged

Virtual Private Database

 Based on policies to restrict access to data

 Examples:

– By Job Title – By Department – By program

(40)

Least Privileged

 Triggers and Policies in the database

 Protects and Restricts the data

– Inside Applications – Ad-Hoc Queries

 Restrict Columns or other values even with permissions

(41)

Least Privileged

Create Policy BEGIN DBMS_RLS.ADD_POLICY ( object_schema => ‘HR’ ,object_name => ‘EMP_DETAILS’ ,policy_name => ‘EMP_IU’ ,function_schema => ‘HR’ ,policy_function => ‘MANAGER_ROLE_ONLY’ ,statement_types => ‘SELECT’ ,policy_type => dbms_rls.dynamic ,long_predicate => FALSE ,update_check => TRUE ,static_policy => FALSE ,enable => TRUE); END;

(42)

Least Privileged

Virtual Private Database Create Procedure

CREATE or REPLACE PROCEDURE HR.set_role_mgr as

var_role varchar2(30); begin

select rolename into var_role from HR_ROLES

where upper(username)=

upper(sys_context(‘userenv’,’session_user’));

dbms_session.set_context(namespace=>‘realm_role_ctx’, attribute => ‘rolename’, value => var_role);

(43)

Least Privileged

Create Trigger

CREATE or REPLACE TRIGGER SYS.set_user_role after logon on database

begin HR.set_role_mgr; exception when no_data_found then null; end;

(44)

Least Privileged

Database Vault

 Restrict privileged users from sensitive data

 Access to perform DBA tasks

 Simple Configuration

 Mandatory Realms

(45)

44

Least Privileged

Database Vault

 Simple Configuration

– Installed with database (12c)

• Configure users

 Vault admin and vault manager  Different roles

 Security team? Different than DBAs

• Enable

• Create Realms

• Create and use roles

(46)

Least Privileged

Database Vault

 Mandatory Realms (12c)

– Highly sensitive data protected from all users

(47)

46

Least Privileged

Database Vault

 Administration tools

– Create and Edit Realms – Add and Manage Users

– Security Team can use to grant access – Another team can manage Realms

 Validate Install

– Check that it is enabled in the database, run the following: • SQLPLUS> select * from v$option

where parameter = ‘Oracle Database Vault’; – Bring up Vault Administration in OEM

(48)

(49)

(50)

Reporting and Maintenance

Auditing  Login  Logoff  Failed Attempts  Object creation  New users

 Change in permissions and privileges

(51)

Reporting and Maintenance

Auditing

 Audit Reports

 OEM and security tools

 Security options implemented

– Reporting – Add new – Upgrade

(52)

Reporting and Maintenance

Auditing

 Default reports

 Maintaining information that fits the security put into

place

 Privileged users – report

 Reporting on least privilege

 After changes, verify secure configuration

(53)

Reporting and Maintenance

Unified Auditing (12c)

 Policies and Conditions

 AUDIT_ADMIN and AUDIT_VIEWER roles

 Single audit trail

 Includes Database Firewall

(54)

Reporting and Maintenance

 Security policies and conditions

– Vault – Audit

– Redaction

 Roles

– New roles and separation of duties – Validating Privileges

(55)

Summary

 Layers of security  Data Classification  Least Privilege – Especially admins  Practical Security

– Matching the level of data – Maintainable

(56)