Transcription
Securing Your Oracle Database to Protect your DataMichael MessinaSenior Managing Consultant, Rolta-AdvizeXmmessina@advizex.com / mike.messina@rolta.com
Introduction Michael Messina Oracle Database OCPSenior Managing Consultant Rolta-AdvizeX,Working with Oracle Approximately 20 yearsBackground includes Performance Tuning, High Availability and DisasterRecoveryOracle RAC Certified ExpertOracle Exadata Implementation SpecialistOracle ACEMMESSINA@ADVIZEX.COM/ MIKE.MESSINA@ROLTA.COMwww.tusc.com / www.roltasolutions.com
Agenda Security Policies Physical AccessNetwork AccessOperating SystemDatabaseData MaskingTransparent Data EncryptionDatabase ListenerClass of Secure Transport (COST)Oracle Password Store WalletDatabase VaultDatabase 12c New Privilege Management featuresAudit Vault and Database Firewall (AVDF)Questions / Discussion
Why Security? From Oracle Corporation
Security Insider threat often more overlooked then outside threat so need to ensurefocus on security includes the more likely insider threat to data breach Physical Security typically has had more focus then data security thoughthat is changing. Data Security is getting more focus as more breaches are highlighted inMedia as they happen. Regulatory Compliance must be adhered to for Health Care and Financialinformation some critical business data is left unprotected leavingorganization exposed to that data being stolen by competitors andorganization may not even realize it.
What do we do?
Be Like this Guy? Organization can not fix what it is not aware of Organization needs to take compliance and security gaps seriously
Security Policies Physical Access ControlPhysical ProtectionOperating SystemNetworkDatabase Access
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 Control Access to Machine ILOM
Physical Access Control Network Controls Limit Access to ability to physically connect to networkLimit Physical Access to Network switchesLimit Physical Access to Network FirewallsLimit 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 theoverheat 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 theoverheat and access to cooling controls Fire – Fire Suppression System and backup fire suppression and access
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 OptionSqlnet.oraAES 256-bit key, 3-key 3DES and many other optionsOn 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[,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 donot 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 andstop connections for IP address not in the list Applying Security Patches as they are Released
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 wherepossible.
Data Masking Mask Sensitive data in Non-Production Environments Developers to do not see actual identifiable data during the developmentprocess 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 forReplay on masked test environment. Do this as a best practice regardless of Regulation Requirementstherefore you are always securing you data across your environments thesame 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 restRequires a wallet at the database level that isFocus is to protect the datafiles of the databaseKeeps users and applications from having to manage an encryption keyfor 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 500MAUTOEXTEND ON NEXT 500M MAXSIZE UNLIMITED encryption using 'AES256'default storage (encrypt) ; Encrypt Column alter table emp modify (ssn encrypt) ;
Database Access REMOTE OS AUTHENT In some cases central Authentication will have this TRUE, if not required forCentral authentication set to FALSE Least Privilege Control access to database tables to onlytables user is required access to Fine Grained Access Control Virtual Private Database Do not use common shared databaseaccounts This creates an elevated level of privilegesto accommodate multiple user roles Reduces auditing accuracy and ability totrack at lowest layer, the database
Database Access User Account Approval Process Ensure all database account creations are controlled through a multi-layerapproval process Password Rules / Enforcement Ensure all database accounts have a password rules in place and enforced forall 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) Two factor authentication (password / PIN [token card])
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 notrecommended to use passive exclude list. Controlled in sqlnet.ora tcp.validnode checking YES tcp.invited nodes {list of IP addresses}
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.orafor 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"cn secure register" -keysize 1024 -validity 3650
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
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.orafor 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 alter system set remote listener ' endpoint : port ' scope memory sid '*';
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 tocreate 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 linkControl analyze on a tableSQL Statements For Example if DBA attempts to access table protected database vault blocks the SQLStatement 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 accessrequired. 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 SQLStatement 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 additionallevel 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 canuse multiple wallets to create more security and control wallet access. Great for batch processes, eliminates users having to know password, does notshow password on command line, no more special processes for batch to getpasswords. Available since 10g
Oracle Password Store Wallet Create Wallet for password store cd TNS ADMINmkdir authentmkstore –wrl TNS ADMIN/authent –createEnter 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 usernamepassword Unique to each TNS entry within the wallet to have different database users for sametns entry must use different wallet
Password Store Wallet Update sqlnet.ora to set for standard wallet locationsWALLET LOCATION (SOURCE (METHOD FILE)(METHOD DATA (DIRECTORY LET OVERRIDE TRUESSL CLIENT AUTHENTICATION FALSESSL VERSION 0
Password Store Wallet Test the wallet credentials to login Now that the wallet password store has been created, the sqlnet.ora andtnsnames.ora setup to utilize the wallet we need to test and make sure thewallet 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 differentTNS ADMIN location then database to be able to use. Does not required Advanced Security Option and does not have anyadditional 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 Manage keys for Transparent Data Encryption
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 ysysdg 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 New roles incorporated with Database Vault as well.
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 aroles’, 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 theanalysis period DBA USED OBJPRIVS shows which object privileges were used during theanalysis period DBA USED OBJPRIVS PATH shows how the privileges were granted – directto the user or via a role DBA UNUSED PRIVS shows privileges that were granted to the users but notused during the analysis period DBA PRIV CAPTURES shows information about the various captures thathave 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 Will stop DB activity if Audit record can not be written
Database Auditing and Database Audit Reporting OS Audit Trail AUDIT TRAIL XMLDynamic View V XML AUDIT TRAILStored on the OS file SystemCAN miss audit records if file system becomes fullSome actions always written to OS Audit Trail SYSLOG Can be used for security reasons such as DBA/oracle account canmodify/delete OS and DB Audit Trails (Trust!!! ?) Can Goto a remote syslog Can be used for a dedicated logging host
Database Auditing and Database Audit Reporting Actions Always Written to SYSLOG / OS Audit Trail Instance startup OS user starting instanceTerminal of userTime stampDone 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 Done for accountability for users connecting with administrator privileges.
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 2audit records would be created For OS / SYSLOG - generates and stores an audit record each time an accessis made so it acts just like BY ACCESS. Audit BY ACCESS Audit record is created for each operation For example user a access table a 5 times 5 audit records would be created.
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 andreporting on that protection For environments with high security requirements and ability to identifyand investigate threats Not Just for Oracle Databases, has capability to work across thedatabases in the environment. OracleMySQLMS SQL ServerIBM DB2SybaseOS, Unix and WindowsDirectory 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 No Database Changes Required for protection to work
Database Firewall Thanks for the image Oracle very Nice illustration
Database Firewall Again another great Illustration from Oracle In-line Blocking and monitoring Traffic is evaluated/inspected forwarded to database or blocked/modified basedon policy Out-of Band Monitoring Traffic is recorded before sent to database, but not evaluated/inspectedtherefore never blocked from database. HA Mode 2 firewalls for uninterrupted SQL monitoring.
Audit Vault Collects and consolidates Audit Data from a variety of Sources DatabasesOSDirectoriesDatabase Firewall Ability to alert to security violations found in the audit data
Audit Vault Notice Database Firewall Events Feeding into Audit Vault workingtogether to provide the front line security, tracking and alerting.
Audit Vault Alerting Analysis of specific eventsDefine event thresholdsAlter to defined activitiesAlert 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 . Custom Reporting Capabilities
One Final Thought This is what security is about
Any Further Questions?48
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
