Database Security - Case Study for Enterprise License

Database Security - Case Study for Enterprise License

Database Security - Case Study for Enterprise License Features Session ID: 1235 Case study of the Security Features in the Enterprise License of Oracle RDBMS Prepared by: April Sims @aprilcsims Enterprise License Security Features

Oracle External Password Store Encrypting Client Connections 12c Security Features of RDBMS 12.1.0.2 Other Security Measures beyond Oracle Case Study What did we implement?

Don't trust our slides - only believe what you've verified by yourself Mike Dietrich https://blogs.oracle.com/UPGRADE/ Now part of the Enterprise License Some features used to be part of the Advanced Security Option but now included in the Enterprise License License does have specific applications to RAC environments for COST Covers:

PKI authentication Network Encryption SSL/TLS TDE encryption Kerberos, PKI, Radius Native Network Encryption 4 Encryption Interoperability see URL in Notes

Transparent Data Encryption ASO Oracle Wallet OSB

DG, Streams and GoldenGate Exadata RMAN Compression Transportable Tablespaces SQL Loader Incompatible Features what is not encrypted 5 Addressing Data at Rest Vulnerabilities Data at Rest Encrypted RMAN Backups Encryption Password or

Key Wallet DataPump prompt for encryption key or Encryption Wallet Oracle Secure Backup free RMAN to tape single node to single tape device What did we do? Encrypted all files at the OS level including datafiles, backup to local filesystem which is backed up to tape nightly. 6 Addressing Data in Use Vulnerabilities

a. Fine Grained Access b. Data Masking c. Encrypting Client Connections b. Auditing Performance Tradeoffs related to Auditing based on implementation: DB, OS, and XML audit trail see notes 7 Passwords Password Complexity controlled by Profiles

1. Password Function 2. Recommendations Is the Password Encrypted when I Logon and Other Related Questions (Doc ID 271825.1) 8 9 10 Database Auditing a. Table Privs

b. System Privs c. We use a Logon/Logoff Trigger see notes Minimal Auditing AUDIT SESSION WHENEVER NOT SUCCESSFUL; 11 SELECT * FROM dba_stmt_audit_opts; audit DROP any TABLE BY access; audit DROP any PROCEDURE BY access; audit CREATE public DATABASE link BY access; audit ALTER USER BY access; audit CREATE USER BY access;

audit DROP USER BY access; audit ALTER DATABASE BY access; audit ALTER system BY access; audit ALTER profile BY access; audit DROP profile BY access; audit DELETE ON sys.aud$; 12 Operating System Auditing Oracle MOS Notes Master Note for Auditing 1299033.1 Audit syslog setup 553225.1 *.audit_trail='OS'

*.audit_syslog_level=LOCAL1.WARNING Integrated w/ OS LogRotate on Linux to maintain files, files are transferred to our LogSearch implementation for searching and archiving 13 Open Source LogSearch Storage of Auditing Records Integrated with Logstash , Kibana 1. Searching 2. Archiving Auditing Records Last 15 minutes, last 24 hours, compressed/archived still available

3. Screenshot next slide is a Firewall Search 14 15 Oracle External Password Store Removing clear text passwords in batch files, limit access to username/passwords Oracle External Password Store EPS TNS ALIAS to define a username/password combo for

SQLNET connections Remove the need for storing username/password in clear-text for scripts, batch jobs Can be integrated with RMAN for backups Stored in an Oracle Wallet Removes the need for distributing username/password to programming staff Use both orapki and mkstore command - more secure method Oracle login - use DBMS_PRIVILEGE_CAPTURE 17 External Password Store

Goal: Remove clear text passwords for jobs , tasks run on a regular basis Staff no longer needs to know the username/password combo Environment needs to support this methodology clear lines between production and non-production environments Shell environment variables by user Production files separated by permissions Korn Shell , Bash Shell, Output directory, Git Hub Convert Wallet to Java Keystore see notes HTTP/ SSL

18 Oracle Key Manager Another License What is the minimum configuration of Oracle Key Manger? The Oracle Key Manager 3 system at a minimum requires pair of key management appliances (KMAs), an encryption enabled storage device (i.e., Oracle's StorageTek T10000 tape drive), a connectivity kit to connect the encrypting device to the Oracle Key Manager cluster, and an encryption key for each device enrolled in the system.

19 ORAPKI & MKSTORE command orapki wallet create -wallet . -pwd pass -auto_login_local mkstore -wrl . -createCredential sqlplus /@service_name Can only login to same host and same OS user

Requires a correct /etc/hosts ORA-12578: TNS:wallet open failed error service_name matches the tnsnames.ora entry 20 NOTE:1114599.1 - How To Prevent The Secure Password Store Wallet From Being Moved to Another Host Since 11.2 new parameter Copy the wallet (ewallet.p12 and cwallet.sso) to another host and test if it can be opened.If it fails,then the wallet is a local auto login wallet.

OR The local auto login wallet is also tied to the operating system user.Try opening the wallet in the same host with another OS user.If it fails,then the wallet is auto login local wallet. 21 SQLNET.ora Client Side Batch Job sqlnet.expire_time=10 DEFAULT_SDU_SIZE=32768 NAMES.DIRECTORY_PATH= (TNSNAMES, HOSTNAME)

SQLNET.INBOUND_CONNECT_TIMEOUT=120 RECV_BUF_SIZE=11250000 SEND_BUF_SIZE=11250000 TCP.NODELAY=YES WALLET_LOCATION =(SOURCE = (METHOD = FILE) (METHOD_DATA = (DIRECTORY = /walletlocation))) SQLNET.WALLET_OVERRIDE = TRUE SSL_VERSION=1.0 22 SQLNET.WALLET_OVERRIDE = TRUE This setting causes all CONNECT /@db_connect_string statements to use the

information in the wallet at the specified location to authenticate to databases. We choose FALSE.as part of our migration..why? It requires additional compile steps to integrate with Pro Cobol and Pro C programs 3rd party requirement mandated the current setting, in the process of migrating 23 Lessons Learned Each time an entry is modified it changes the wallet permissions

Easy to Implementtakes a while to migrate all code to use new TNSALIAS New Oracle login - minimum grants Co-exists with Encrypted Connections We made different entries for production and nonproduction environments - environmental variable $PASS_FOR_JOB $PPASS_FOR_JOB $PASS_FOR_BJOB $PASS_FOR_CJOB 24 Encrypting Client Connections Encrypting Client Connections

When using standard unencrypted connections the username/password is encrypted when connecting using SQLNET, everything else is clear text. Easy to confirm by running a SQL trace Oracle refers to this as Native Network Encryption in My Oracle Support ******SSL Encryption also available with an Oracle Wallet using TCPS protocol 26 Client Types

SQL PLUS , SQLNET ODBC JDBC SQL Developer C compile Cobol compiles

Cold Fusion **** - this one has given us problems 27 Listener Side Encryption LISTENER.ORA/SQLNET.ORA - see Notes Implemented in a separate 12c Oracle Home $TNS_ADMIN Requires a database restart/listener restart Live Since Sept 2013 No problems w/ Database Links to non-encrypted Very few client issues or compatibility problems No noticeable slowness seen

28 Tracing Sessions Best way to determine if encryption is active Note Section has trace files from SQLNET connections on our Linux jobsub box to Linux Database server SQLNET Client = SQLNET.ORA/TNSNAMES.ORA Database Server = LISTENER.ORA/SQLNET.ORA Cross Platform Encryption Tracing is different as of 11g.recommended to disable adrci for control of output files

New diagnostic messages have been added in Oracle Net 12c's sqlnet.log file 29 SQLNET.ora - Database Listener DISABLE_OOB = ON SQLNET.AUTHENTICATION_SERVICES= (TCPS) SQLNET.CRYPTO_CHECKSUM_TYPES_SERVER= (MD5) SQLNET.ENCRYPTION_SERVER = required NAMES.DIRECTORY_PATH= (TNSNAMES, HOSTNAME) SSL_CLIENT_AUTHENTICATION = TRUE SQLNET.CRYPTO_SEED = YOURSEEDNUMBERS

SQLNET.ENCRYPTION_TYPES_SERVER= (RC4_40, AES256, RC4_256, AES192, 3DES168, AES128, RC4_128, 3DES112, RC4_56) SEND_BUF_SIZE = 11250000 RECV_BUF_SIZE = 11250000 DEFAULT_SDU_SIZE = 32768 TCP.NODELAY = YES SQLNET.EXPIRE_TIME = 10 SQLNET.INBOUND_CONNECT_TIMEOUT = 120 ADR_BASE = /u01/app/oracle SQLNET.CRYPTO_CHECKSUM_SERVER = required USE_NS_PROBES_FOR_DCD=true SSL_VERSION=1.0 30

SQL Net Changes in 12c Dead Connection Detection Network/switches/routers may no longer recognize Oracles DCD packets (they are zero length) but they do recognize the OS packets for keep alive (non zero length) The host OS keep alive setting (tcp_keep_alive) can be modified to be less than the firewall inactivity timeout. This will cause the OS to send a test packet to the client when the timeout is reached and the client will respond with an ACK. To all intents and purposes this is the same as turning off the firewall inactivity

timer for this host. 31 SQLNET Changes in 12c contd Larger Session Data Unit Sizes Advanced Network Compression Terminated Client Detection Intelligent Client Connection SQLNET.ALLOWED_LOGON_VERSION- Setting of 8 permits most password versions, and allows any combination of the DBA_USERS.PASSWORD_VERSIONS values 10G, 11G, and 12C.

SQLNET.ALLOWED_LOGON_VERSION_SERVER setting of 12a permits only the 12C password version. 32 Client Compatibility Recently Released Oracle Products come with Encryption enabled, used by default if configured SQL DEVELOPER uses encryption if available SQL PLUS/SQLNET - different implementation types server and/or client configured JDBC see Notes Recommendation: Use Listener-Side Encryption if all Clients Compatible

Not all compatible make encryption available but not mandatory, make plans to migrate or convert 33 Security Features in 12c Security Features in 12c a. Verifier/ DBMS_PRIVILEGE_CAPTURE b. Case Sensitivity c. Password Hash d. Unified Auditing e. RMAN

f. DataPump g. Other Notes/Tips for 12c - including bugs 35 Verifier DBMS_PRIVILEGE_CAPTURE analyzes actual permissions needed during execution CAPTURE_ADMIN privilege Populates DBA_ views DATABASE Ran in non-prod 12:47 pm ROLE SYS_CONTEXT sqlplus, session

36 Start Capture, Stop Capture, Report Username Role Sys Priv Owner Object Name Type ObjPriv UserPriv Grant Path

37 Protecting the Database Change sys,system passwords Lock, expire,change passwords, default/unused accounts Restrict access to the Oracle binaries,audit, diag, logs Review database user privileges - VERIFIER Revoke privileges from PUBLIC - VERIFIER Protect the data dictionary - VERIFIER remote_os_authent = false sec_case_sensitive_logon = true global_names = true unset parameter utl_file_dir

Protect listener and network connections Encrypt sqlnet connections using network encryption. Protect the database host Security Alerts, CPU - email notifications via MOS 38 Case Sensitive Passwords This doubled the number of passwords plus salt (makes it harder to reverse engineer 39 Unified Auditing

Oracle Database 12c Unified Auditing enables selective and effective auditing inside the Oracle database using policies and conditions. For example, audit policies can be configured to audit based on specific IP addresses, programs, time periods, or connection types such as proxy authentication. In addition, specific schemas can be easily exempted from auditing when the audit policy is enabled. New roles have been introduced for management of policies and the viewing of audit data. The AUDIT_ADMIN and AUDIT_VIEWER roles provide separation of duty and flexibility to organizations who wish to designate specific users to manage audit settings and view audit activity. The new architecture unifies the existing audit trails into a single

audit trail, enabling simplified management and increasing the security of audit data generated by the database. 40 Unified Auditing Some Unified Auditing is ON by default in MIXED MODE when you create a fresh Oracle 12c database. Just two policies are enabled by default: ORA_SECURECONFIG and ORA_LOGON_FAILURES Upgrading from previous versions there are no rows to

this query: select VALUE from V$OPTION where PARAMETER='Unified Auditing'; FALSE 41 Unified Auditing contd You can try Unified Auditing without implementing pure Unified Audit mode Pure Unified Auditing,link oracle binary uniaud_on with instance restart required Unified Auditing records to database store generates REDO, use a physical standby for reporting on the UNIFIED_AUDIT_TRAIL view

$ORACLE_BASE/audit/SID on standby is where the OS audit files are stored monitor storage Auditing SYSBA is now different SQL> CREATE AUDIT POLICY all_actions_pol ACTIONS ALL; SQL> AUDIT POLICY all_actions_pol by SYS; Several Major Bugs logon not audited, performance of the UNIFIED_AUDIT_TRAIL view, etc 42 Securing RMAN in 12c Use external Password Store to remove the need for a username/combo in clear text

If you use Doc ID 1383938.1 the wallet can be copied and used on other hosts.security flaw, use the command from slide 15. Every doc I have seen EPS have the less secure instructions! mkstore wrl $ORACLE_HOME/network/admin create In 12c Oracle, the UNIFIED_AUDIT_TRAIL data dictionary view has a set of fields (prefixed with RMAN_) which automatically record RMAN related events. However, you must have the AUDIT_ADMIN or AUDIT_VIEWER role in order to query the UNIFIED_AUDIT_TRAIL view to see these events. 43 SYSBACKUP

SYSBACKUP user must be unlocked and granted SYSDBA is still the default connection if not specified SYSBACKUP no sql access to underlying data without grants Recreate the passwordfile with FORMAT=12 and sysbackup=Y orapwd file=[fname] entries=[users] force=[y/n] asm=[y/n] dbuniquename=[dbname] format=[legacy/12] sysbackup=[y/n] sysdg=[y/n] syskm=[y/n] delete=[y/n] input_file=[input-fname] 44

RMAN Encryption Methods Transparent default, Oracle Key management infrastructure and Oracle Wallet Password - No wallet manager, but requires to specify a password for the encryption and decryption. Lost password = lost backup, lost restores Dual both types, transparent and password Oracle Key Manager 3 Hardware/software solution 45 RMAN Encryption & Oracle Secure Backup - OSB

Advanced Security Option (ASO), Enterprise Edition All options installed as of 12c Encrypted backups to tape not using OSB is not supported Encrypting backups to tape using OSB is supported without ASO, standard edition Oracle Secure Backup Express Edition (free) does not support backup encryption one database on one node directly attached to tape device Our Environment backup to encrypted OS file systems, nightly tape backups for the entire enterprise 46

DataPump Enhancements Prompt for Encryption Password Unified Auditing ASO Integration with Key Infrastructure 47 Extended Users & OS Groups SYSBACKUP, SYSDG - Standbys , SYSKM - TDE COSDBA Group OSOPER Group OSBACKUPDBA Group OSDGDBA Group

OSKMDBA Group OSDBA Group OSOPER Group OSASM Group To divide responsibilities job role separation 48 Other Security Recommendations Additional Encryption Information Encrypted FileSystems/Database Files using RH OS Linux/ UNIX Filesystem Encryption live several years no issues

Not supported by ORACLE - opened SR , Initialization parameter affected disk_asynch_io FALSE Requires a password during reboots Protects files backed up to tape and if server is turned off We use a SSL encrypted tunnel in between standbys, archive logs vulnerable Encrypting REDO traffic using Oracle requires Advanced Security Option License 50 April C Sims

aprilcsims.wordpress.com LinkedIn Please complete the session evaluation We appreciate your feedback and insight You may complete the session evaluation via the mobile app

Recently Viewed Presentations

  • Yankee Alliance Overview For MPS November 2017 Proprietary

    Yankee Alliance Overview For MPS November 2017 Proprietary

    For MPS . November 2017. To work with members to reduce supply and operating expenses through aggregation of data, purchasing, ideas, and knowledge ... Dedicated member services representative will work to ensure contract connectivity so your staff can focus on...
  • Why Not Store Everything in Main Memory? Why use disks?

    Why Not Store Everything in Main Memory? Why use disks?

    In fact the MN-MED (read "mean minus mead") is great since it picks out that last two which have the best gaps (discounting outlier gaps at the extremes which we are pretty good at detecting in other way, e.g., looking...
  • New Asian Empire Section 1 The Mughal Empire

    New Asian Empire Section 1 The Mughal Empire

    The Mughal Empire Preview Main Idea / Reading Focus Muslim Rule in India A New Empire Faces of History: Akbar Height of the Mughal Empire Quick Facts: Achievements of the Mughal Emperors Map: Mughal Empire During reign, Jahangir came into...
  • AP ECONOMICS: November Study for Phillips Model Quiz

    AP ECONOMICS: November Study for Phillips Model Quiz

    In order to understand the theory of money neutrality, I will analyze the impact of monetary policy on the short run and long run. I will know I have it when I can show: (1) on LRAS-AS-AD and Money Market...
  • Web Algorithmics - unipi.it

    Web Algorithmics - unipi.it

    Web search engines Paolo Ferragina Dipartimento di Informatica Università di Pisa
  • Kapitel 6 - univie.ac.at

    Kapitel 6 - univie.ac.at

    Für manche Tätigkeiten sind Vorgänger-Nachfolger-Beziehungen zu beachten, die im einfachsten Fall lauten: X ist Vorgänger von Y Vorgang X muss abgeschlossen sein, bevor Y beginnen kann Darstellung der Vorgänger-Nachfolger-Beziehungen in Graph zwei verschiedene Ansätze: Vorgangspfeilnetz Vorgangsknotennetz 9.1.1 Vorgangspfeilnetz ...
  • The New Public Health System A new system...

    The New Public Health System A new system...

    North / Central / East. Provide Public Health leadership and support to CCGs, district level partnerships and district councils. Teams that work across the three domains of public health . Largely align with LCC locality footprints
  • Natural History of Sharks, Skates, and Rays Phylogeny

    Natural History of Sharks, Skates, and Rays Phylogeny

    Natural History of Sharks, Skates, and Rays Phylogeny of Selachii Da'Sharks MARE 394 Dr. Turner Summer 2008 Zoogeographic Patterns Study of patterns of distribution of animals on earth and the biological, geological, and climactic processes that influence these patterns Zoogeographic...