Securing Your Oracle Database to Protect your Data
Michael Messina
Senior Managing Consultant, Rolta-AdvizeX
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.
COMAgenda
•
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)
Why Security?
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.
Be Like this Guy?
•
Organization can not fix what it is not aware of
Security Policies
•
Physical Access Control
•
Physical Protection
•
Operating System
•
Network
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
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)
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
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
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
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.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.
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
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
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)
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
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
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 TCPSClass 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
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)
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.
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.
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.
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
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
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.
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
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
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’ ‘ ‘);
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’);
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
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
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
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
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
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
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
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
Database Firewall
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
Audit Vault
•
Collects and consolidates Audit Data from a variety of Sources
Databases OS
Directories
Database Firewall
Audit Vault
•
Notice Database Firewall Events Feeding into Audit Vault working
together to provide the front line security, tracking and alerting.
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….
One Final Thought
48