• No results found

Securing Your Oracle Database to Protect your Data

N/A
N/A
Protected

Academic year: 2021

Share "Securing Your Oracle Database to Protect your Data"

Copied!
48
0
0

Loading.... (view fulltext now)

Full text

(1)

Securing Your Oracle Database to Protect your Data

Michael Messina

Senior Managing Consultant, Rolta-AdvizeX

(2)

Introduction

Michael Messina

Senior Managing Consultant Rolta-AdvizeX,

Working with Oracle Approximately 20 years

Background includes Performance Tuning, High Availability and Disaster

Recovery

Oracle Database OCP

Oracle RAC Certified Expert

Oracle Exadata Implementation Specialist

Oracle ACE

MMESSINA

@

ADVIZEX

.

COM

/

MIKE

.

MESSINA

@

ROLTA

.

COM

(3)

Agenda

Security Policies

 Physical Access  Network Access  Operating System  Database

Data Masking

Transparent Data Encryption

Database Listener

Class of Secure Transport (COST)

Oracle Password Store Wallet

Database Vault

Database 12c New Privilege Management features

Audit Vault and Database Firewall (AVDF)

(4)

Why Security?

(5)

Security

Insider threat often more overlooked then outside threat so need to ensure

focus on security includes the more likely insider threat to data breach

Physical Security typically has had more focus then data security though

that is changing.

Data Security is getting more focus as more breaches are highlighted in

Media as they happen.

Regulatory Compliance must be adhered to for Health Care and Financial

information some critical business data is left unprotected leaving

organization exposed to that data being stolen by competitors and

organization may not even realize it.

(6)
(7)

Be Like this Guy?

Organization can not fix what it is not aware of

(8)

Security Policies

Physical Access Control

Physical Protection

Operating System

Network

(9)

Physical Access Control

Physical Access to servers that run the database

 Machine Room Access Controls

 Biometric

 Card Key

 Physical Security Guards

 Access to Power feeding machine room

 Gate/Wall around power feeds/utilities

 Restricted Access to Power Controls

 Access to ventilation and cooling systems to machine room.

 Gate/Wall around cooling / heating systems pushing air into machine room.

 Access to controls for Cooling and heating systems

 Machine Console access limited to essential personnel

 Physical machine consoles offer direct login for super user

 Control access to machine console

(10)

Physical Access Control

Network Controls

 Limit Access to ability to physically connect to network  Limit Physical Access to Network switches

 Limit Physical Access to Network Firewalls

 Limit Access for physical connectivity to network to authorized personnel (separate guest networks)

(11)

Physical Protection

Database Server protection Physical

 Power - Control against power surges, etc. and access to power controls  Cooling – Backup cooling to machines or protection to bring them down if the

overheat and access to cooling controls

 Fire – Fire Suppression System and backup fire suppression and access

Network Physical Protection

 Power - Control against power surges, etc. and access to power controls  Cooling – Backup cooling to machines or protection to bring them down if the

overheat and access to cooling controls

(12)

Network

Utilize Firewalls Control Connections

 IP Filtering

 Database Firewall

 Activity Monitoring/Blocking  Network Firewall

 Network access control

Network Level Encryption between clients and database

 Oracle Advanced Security Option  Sqlnet.ora

 AES 256-bit key, 3-key 3DES and many other options  On the server:

SQLNET.ENCRYPTION_SERVER = [accepted | rejected | requested | required] SQLNET.ENCRYPTION_TYPES_SERVER = (valid_encryption_algorithm

[,valid_encryption_algorithm])

 On the client:

SQLNET.ENCRYPTION_CLIENT = [accepted | rejected | requested | required] SQLNET.ENCRYPTION_TYPES_CLIENT = (valid_encryption_algorithm

(13)

Database

Utilize same security in Non-Production

 Make sure security Controls are working

 Ensure that security procedures are full proof

 Ensure items like encryption, advanced security and other security controls do not affect production operations, performance and procedures.

Database Listener Filtering

 Listener IP Access Control list

 Listener has list of IP addresses that it will allow connections to database and stop connections for IP address not in the list

(14)

Operating System

Access to Login to Physical Server

• Operating system account to only Administration personnel ( DBAs and System Admins)

Latest Operating System Security Updates Applied

• Apply security updates from operating system vendor within 30 days of release

Ensure Operating system security controls are well known and utilized where possible.

(15)

Data Masking

Mask Sensitive data in Non-Production Environments

 Developers to do not see actual identifiable data during the development process

 Quality assurance does not see actual identifiable data during release testing.  Integrated with Real Application Testing

 Can have masked data on a test environment from captured production workload for Replay on masked test environment.

Do this as a best practice regardless of Regulation Requirements

therefore you are always securing you data across your environments the

same way. This ensures that it is an integrated part of you daily support,

processes and activities.

(16)

Transparent Data Encryption

Transparent data encryption protects you data at rest

Requires a wallet at the database level that is

Focus is to protect the datafiles of the database

Keeps users and applications from having to manage an encryption key

for increased security without having to change the application in anyway.

 Create Wallet for Encryption

 system set encryption key identified by "xxxxxxxxx" ;  Open Encryption Wallet

 alter system set encryption wallet open identified by "xxxxxxxxx" ;  Close Encryption Wallet

 alter system set encryption wallet close identified by "xxxxxxxxx" ;  Create encrypted tablespace

 CREATE BIGFILE TABLESPACE MY_DATA datafile '+DATA_HC' size 500M

AUTOEXTEND ON NEXT 500M MAXSIZE UNLIMITED encryption using 'AES256' default storage (encrypt) ;

 Encrypt Column

(17)

Database Access

Least Privilege

 Control access to database tables to only tables user is required access to

 Fine Grained Access Control  Virtual Private Database

Do not use common shared database

accounts

 This creates an elevated level of privileges to accommodate multiple user roles

 Reduces auditing accuracy and ability to track at lowest layer, the database

REMOTE_OS_AUTHENT

 In some cases central Authentication will have this TRUE, if not required for Central authentication set to FALSE

(18)

Database Access

User Account Approval Process

 Ensure all database account creations are controlled through a multi-layer approval process

Password Rules / Enforcement

 Ensure all database accounts have a password rules in place and enforced for all accounts

 Passwords are required to be changed regularly even system level

Central Authentication

 Kerberos Authentication  LDAP

 Wallets

 S/MIME signing certificate

 S/MIME encryption certificate

 Code-signing certificate

 RADIUS (Remote Authentication Dial-in User Service)

(19)

Database Listener

Prevent online administration

 Require administrators to have write privileges on the listener.ora file

 ADMIN_RESTRICTIONS_LISTENER=ON  Listener Password

 Remote Listener management is disabled when password is not used

Database Listener Filtering

 Control to a list of IP addresses that are allowed to connect, active list not recommended to use passive exclude list.

 Controlled in sqlnet.ora

 tcp.validnode_checking = YES

(20)

Class of Secure Transport (COST) (TNS Poison) < 12c

Work around is to IP Filter Listener to restrict access to listener

Can not use with Password Store Wallet, must use a different sqlnet.ora

for clients to be able to use Password Store Wallet

Patches Require for environments < 11.2.0.4

 Cumulative Patch Inclusion - DBPSU 10.2.0.5.8 (Released)  Cumulative Patch Inclusion - DBPSU 11.2.0.2.7 (Released)  Cumulative Patch Inclusion - DBPSU 10.2.0.4.13 (Released)  Cumulative Patch Inclusion - DBPSU 11.2.0.3.3 (Released)  Cumulative Patch Inclusion - DBPSU 11.1.0.7.12 (Released)  Windows patch availability for bugfix:12880299

Create and enable wallet

 orapki wallet create -wallet <wallet_location> -auto_login  orapki wallet add -wallet <wallet_location> -self_signed -dn

(21)

Class of Secure Transport (COST) (TNS Poison) < 12c

Set Wallet Location in sqlnet.ora / listener.ora (each ORACLE_HOME)

and each node in a RAC Cluster

 WALLET_LOCATION = (SOURCE = (METHOD = FILE) (METHOD_DATA = (DIRECTORY = <Wallet_Location>) ) )

Add TCPS and Port to Listener

 (ADDRESS = (PROTOCOL = TCPS)(HOST = IPAddress)(PORT = 1523))  Comment out TCP address line(s)

Database Secure Listener Registration Parameter listener.ora / sqlnet.ora

SECURE_REGISTER_listener_name

 SECURE_REGISTER_listener = (TCP, TCPS, IPC)

 SECURE_REGISTER_LISTENER_SCAN1 = (IPC,TCPS)

Set database remote_listener to port for TCPS

(22)

Class of Secure Transport (COST) (TNS Poison) >= 12c

VNCR

 Valid Node Checking for Registration

Can be used with Password Store Wallet do not need separate sqlnet.ora

for client and database on database server

Listener.ora

 VALID_NODE_CHECKING_REGISTRATION_LISTENER_SCAN1=SUBNET  VALID_NODE_CHECKING_REGISTRATION_LISTENER=SUBNET

 Value of ON instead of SUBNET more restrictive ON used non-RAC

 REGISTRATION_INVITED_NODES_LISTENER_SCAN1=(dbserver1,dbserver2)

Set remote_listener for database

(23)

Database Vault

Licensed Option for Oracle Database

Privileged User Access Control

 Database Administrator

Protects application data access in the database

Policy based access controls

Protection Policies for Many Enterprise Applications Available (no need to

create you own)

(24)

Database Vault

Database Configuration Controls

 create, alter, drop user  alter system

 etc.

Database DDL/DML Control

 Control Create table, truncate table, drop table, etc.  Control create database link

 Control analyze on a table  SQL Statements

 For Example if DBA attempts to access table protected database vault blocks the SQL Statement and creates an audit record of the access attempt.

Focus on separation of duties

 Control grant/revoke for database roles, limit privileged users power  Freeze role privileges, no changes to role privileges allowed.

(25)

Database Vault

With 12c Can identify unused privileges. (called Privilege Analysis)

 Allow unused privileges to be removed to tighten security to least access required.

Fully Integrated with oracle Enterprise Manager 12c

 Simplify Management

Reporting

 SQL Statements Blocked by Database Vault

 For Example if DBA attempts to access table protected database vault blocks the SQL Statement and creates an audit record of the access attempt.

 Any Security Policy Changes

 Any Changes to Vault Configuration

Compliments Database Firewall and Audit Vault well to provide additional

level of security and view into overall environment.

(26)

Oracle Password Store Wallet

Utilize to access database without password for client level accounts

 Can be setup on client machines

 Reads Wallet for username/password based on TNS entry used.

 Unique to each TNS_ADMIN location, multiple TNS_ADMIN locations, you can use multiple wallets to create more security and control wallet access.

 Great for batch processes, eliminates users having to know password, does not show password on command line, no more special processes for batch to get passwords.

(27)

Oracle Password Store Wallet

Create Wallet for password store

 cd $TNS_ADMIN  mkdir authent

 mkstore –wrl $TNS_ADMIN/authent –create

 Enter password for wallet (will have to be entered 2 times for confirmation)*

 * Creates 2 files ewallet.p12 and cwallet.sso

Create credential(s) for Wallet

 mkstore -wrl $TNS_ADMIN/authent –createCredential tnsstring username password

 Unique to each TNS entry within the wallet to have different database users for same tns entry must use different wallet

(28)

Password Store Wallet

Update sqlnet.ora to set for standard wallet locations

WALLET_LOCATION=

(SOURCE=

(METHOD=FILE)

(METHOD_DATA=

(DIRECTORY= /u01/app/oracle/network/admin/authent)

)

)

SQLNET.WALLET_OVERRIDE = TRUE

SSL_CLIENT_AUTHENTICATION = FALSE

SSL_VERSION = 0

(29)

Password Store Wallet

Test the wallet credentials to login

 Now that the wallet password store has been created, the sqlnet.ora and tnsnames.ora setup to utilize the wallet we need to test and make sure the wallet password store is functioning as expected.

 sqlplus /@tnsstring

Select user from dual ;

 Show us that we logged in as the user we set in the wallet

Can not be used with TNS Poisoning Fix in < 12c, must use a different

TNS_ADMIN location then database to be able to use.

Does not required Advanced Security Option and does not have any

additional license requirements to use.

(30)

Database 12c New Privilege Management Features

Task Based System Privileges

 SYSDBA

 Same as prior versions  SYSOPER

 Same as prior versions  SYSASM

 Same as prior versions ASM Only  SYSBACKUP

 Ability to perform RMAN backups and recovery

 Applies to both the SQL and RMAN command lines  SYSDG

 For Data Guard Operations

 dgmgrl command line

 Applies to Data Guard Broker  SYSKM

(31)

Database 12c New Privilege Management Features

changes to the password file

 remote connections for roles

 orapwd file=orapwSID password=xxxx entries=5 format=12 sysbackup=y sysdg=y

 Format – 12 for the 12c format of password file

 Sysdg – for remote access for new sysdg

 Sysbackup – for remote access for new sysbackup  Migrate old password file to new format

 Input_file parameter

 orapwd format 12 sysbackup=y sysdg=y input_file=$ORACLE_HOME/dbs/orapwdb1

AUDIT_SYS_OPERATIONS=TRUE

 Audit operations with new roles

(32)

Database 12c New Privilege Management Features

Privilege Usage Capture

 New package to capture privilege usage for analysis  Database level

 For all Privilege usage for entire database

 exec SYS.DBMS_PRIVILEGE_CAPTURE.CREATE_CAPTURE

(name=>’DB_PRIV_ANALYSIS’, description=>’Captures all Privilege Usage’, type => dbms_privilege_capture.g_database);

 Role level

 Privilege usage by roles

 exec SYS.DBMS_PRIVILEGE_CAPTURE.CREATE_CAPTURE (

name=>’ROLE_PRIVS_ANALYSIS’, description=>’Captures Privilege Usage for a roles’, type=> dbms_privilege_capture.g_role, roles=>

role_name_list(‘DBA’,’IMP_FULL_DATABASE’,’EXP_FULL_DATABASE’);  Context

 exec SYS.DBMS_PRIVILEGE_CAPTURE.CREATE_CAPTURE ( name=>’PRIV_FOR_AP’, description=>’Captures All AP’,

type=>dbms_privilege_capture.g.context, condition=> ‘SYS_CONTEXT (‘ ‘USERENV’ ‘,’ ‘MODULE’ ‘)=’ ‘Accounts Payable’ ‘ ‘);

(33)

Database 12c New Privilege Management Features

Enable Capture

 exec SYS.DBMS_PRIVILEGE_CAPTURE.ENABLE_CAPTURE ( name =>’DB_PRIV_ANALYSIS’) ;

Disable Capture

 exec SYS.DBMS_PRIVILEGE_CAPTURE.DISABLE_CAPTURE (name =>’DB_PRIV_ANALYSIS’) ;

Analysis Report

 exec SYS.DBMS_PRIVILEGE_CAPTURE.GENERATE_RESULT (name=>’DB_PRIV_ANALYSIS’);

Remove Capture Data

 exec SYS.DBMS_PRIVILEGE_CAPTURE.DROP_CAPTURE (name=>’DB_PRIV_ANALYSIS’);

(34)

Database 12c New Privilege Management Features

New Dictionary Views

 DBA_USED_SYSPRIVS shows which system privileges were used during the analysis period

 DBA_USED_OBJPRIVS shows which object privileges were used during the analysis period

 DBA_USED_OBJPRIVS_PATH shows how the privileges were granted – direct to the user or via a role

 DBA_UNUSED_PRIVS shows privileges that were granted to the users but not used during the analysis period

 DBA_PRIV_CAPTURES shows information about the various captures that have been created

(35)

Database Auditing and Database Audit Reporting

Database Auditing

 Monitor data/database access execution of dml/ddl

 Deter users for doing or attempting ill advised activities

 Investigate Suspicious activity and creates accountability for data/database changes

What is Covered

 Statements Auditing – Audit by type of Statement (CREATE TABLE, etc)  Privilege Auditing – Audit use of system privileges (Create, Drop, Alter, etc)  Schema Object Auditing - Audit on object (Select, Insert, Update, Delete)  Fine Grained Auditing – Audit Access to certain records

Database Audit Trail

 AUDIT_TRAIL=DB

 Recommended Backup and Recovery with DB

(36)

Database Auditing and Database Audit Reporting

OS Audit Trail

 AUDIT_TRAIL=XML

 Dynamic View V$XML_AUDIT_TRAIL  Stored on the OS file System

 CAN miss audit records if file system becomes full  Some actions always written to OS Audit Trail

SYSLOG

 Can be used for security reasons such as DBA/oracle account can modify/delete OS and DB Audit Trails (Trust!!! ????)

 Can Goto a remote syslog

(37)

Database Auditing and Database Audit Reporting

Actions Always Written to SYSLOG / OS Audit Trail

 Instance startup

 OS user starting instance

 Terminal of user

 Time stamp

 Done because database audit trail not there yet  Instance shutdown

 OS user stopping instance

 Terminal of user

 Time stamp

 Connections made with administrator privileges

 OS User

(38)

Database Auditing and Database Audit Reporting

Audit BY SESSION

 Will only insert a single audit

 record for example user a access table a 5 times and table b 3 times only 2 audit records would be created

 For OS / SYSLOG - generates and stores an audit record each time an access is made so it acts just like BY ACCESS.

Audit BY ACCESS

 Audit record is created for each operation

(39)

Database Auditing and Database Audit Reporting

Reporting

 Database Audit Trail

 dba_audit_trial (view) / sys.aud$ (table)

 dba_fga_audit_trail (view) / sys.fga_log$ (table)

 dba_common_audit_trail (Combined Audit Trail)  OS Audit Trail

 Dynamic View V$XML_AUDIT_TRAIL

Good for certain monitoring of events as well

 Find Audit Record Accessing or changing Salary table

 Find Audit Record where change was made to an Accounts Payable Record  Other rules that organization would want to be aware of

(40)

Audit Vault and Database Firewall (AVDF)

Additionally Licensed Product suite

Combined into one Comprehensive product for database protection and

reporting on that protection

For environments with high security requirements and ability to identify

and investigate threats

Not Just for Oracle Databases, has capability to work across the

databases in the environment.

 Oracle  MySQL

 MS SQL Server  IBM DB2

 Sybase

 OS, Unix and Windows  Directory Services

(41)

Database Firewall

First Line of Defense for Oracle and non-Oracle Databases

Enables perimeter security controls

Monitoring incoming SQL

Block unauthorized SQL statements from Reaching database

Policy Driven controls

Protect against network based attacks

Protect against SQL Injection Based Attacks

Control Access for added level of security

Record activity for Compliance Reporting

Compatible with Oracle Advanced Security Network encryption

(42)

Database Firewall

(43)

Database Firewall

Again another great Illustration from Oracle

In-line Blocking and monitoring

 Traffic is evaluated/inspected forwarded to database or blocked/modified based on policy

Out-of Band Monitoring

 Traffic is recorded before sent to database, but not evaluated/inspected therefore never blocked from database.

HA Mode

(44)

Audit Vault

Collects and consolidates Audit Data from a variety of Sources

 Databases  OS

 Directories

 Database Firewall

(45)

Audit Vault

Notice Database Firewall Events Feeding into Audit Vault working

together to provide the front line security, tracking and alerting.

(46)

Audit Vault

Alerting

 Analysis of specific events  Define event thresholds  Alter to defined activities

 Alert to defined event thresholds

Reporting

 Predefined reports

 Sarbanes-Oxley (SOX)

 Payment Card Industry Data Security Standard (PCI)

 Health Insurance Portability and Accountability Act (HIPAA)

 Gramm-Leach-Bliley Act (GBLA)

 Data Protection Act (DPA)

 Others….

(47)

One Final Thought

(48)

48

References

Related documents

Motivation and General Overview Posting of FI Documents Reporting: Once the documents are in – there are new possibilities in reporting Mapping and Error Handling Posting of

Comparing the results in the fixed effect model in specifications (iv), we find that after acquisition, skilled labor employment, on average, grew by 8 percent, whereas

Berdasarkan paparan tersebut di atas, maka penelitian ini dimaksudkan untuk: Pertama , membangun model traded spread saham di Bursa Efek Indonesia, Kedua, menentukan

A proposed office property containing 170,000 SF, located in Fort Worth, Texas..

 Increase managerial control over work and results.  Increase managerial ability to identify problem areas.  Link individual objectives to organisational objectives 

+ Hadoop/NoSQL Exadata + Oracle Database Oracle Catalog External Table Hive metadata External Table Hive Metastore.|. Copyright © 2014 Oracle and/or

Oracle Audit Vault and Database Firewall APPS Users AUDIT VAULT Firewall Events Database Firewall AUDIT DATA Operating Systems File Systems Directories Custom Audit Data

Except as diluted for admixture with local anesthetics to reduce absorption and prolong action, epinephrine should not ordinarily be used in those cases where vasopressor drugs may be