Getting Started With Advanced Security Option

1
Getting Started With Advanced Security Option
Bob Mycroft Capgemini Outsourcing
2

• Going to talk about today
• Wallets aka Software Keystores
• Column and Tablespace encryption (TDE)
• Encrypting Backups and Datapump Dumps
• SQLNET Network Encryption (NDE)
• Data Redaction in 12c

• Out of scope for today
• Secure Authentication such as Kerberos, Radius
• Using HSM

3

• What is the Advanced Security Option
• What its Not
• Not an access control mechanism like roles and
  grants.
• Your DBA can see your data.
• Your users can see the data they are authorised
  to see.
• Its NOT Database Vault!
• What it IS-
• Largely about encryption, Wallets and also now
  Data Redaction.

December 2010
4

• Data Theft Why Encryption Matters

Heartland Payment Systems 2008100 Million
credit card numbers stolen- 140 million
fine. TJ-Max 2007 45 million credit card
numbers stolen. Estimated cost 250
Million. Sony 2011 credit, debit, bank account
details of Sony Online Entertainment users. Could
potentially cost Sony anywhere up to 2Billion.
December 2010
5

• Encrypting Data At Rest

• Number of Options
• Do it yourself with DBMS_CRYPTO database PLSQL
  package.
• Use Encrypted Disks. Use Tape Encryption.
• Use Transparent Data Encryption (TDE).
• All have their own benefits and cost.

December 2013
6

• The Oracle Wallet, or Software Keystore

• What are Wallets
• A store of Secrets, looking at Software
  Keystores.
• OS file protected by a password, encrypted by
  the password.
• Stores Keys, Certificates and Secrets for
  other tools like Goldengate.
• Needs to be OPEN for Database or Client to
  access the Secrets
• One Keystore/Wallet per database is supported.

• Tools
• Alter system commands via sqlplus.
• orapki and mkstore executables (deprecated in
  12c) .
• Oracle Wallet Manager (OWM) GUI and Grid/Cloud
  Control.
• New in 12c - ADMINISTER KEY MANAGEMENT commands
  in Sqlplus.

December 2013
7

• Types of Wallet

• Password Wallets
• Require a password to open, more secure than
  Auto-Open Wallets.
• -rw------- 1 oracle oinstall 2408 Sep 17 1042
  ewallet.p12
• Auto Open Wallets
• No password required to open..
• Local auto-open wallet can only be opened on
  the server on which it was created.
• -rw------- 1 oracle oinstall 2408 Sep 17 1042
  ewallet.p12
• -rw------- 1 oracle oinstall 2453 Sep 17 1044
  cwallet.sso

December 2013
8

• Wallet Locations

SQLNET.ORA Settings ENCRYPTION_WALLET_LOCATION
(SOURCE (METHOD FILE) (METHOD_DATA
(DIRECTORY /oracle/TDE_WALLETS/ORACLE_U
NQNAME) ) ) WALLET_LOCATION
(SOURCE (METHOD FILE) (METHOD_DATA
(DIRECTORY /oracle/TDE_WALLETS/ORACLE_UNQNAME
) ) )
December 2013
9

• Wallets - Creating

sqlplus keysec as syskm Enter password
password Connected. SQLgt ADMINISTER KEY
MANAGEMENT CREATE KEYSTORE '/oracle/TDE_WALLETS/as
odb/' IDENTIFIED BY bmpassword1234 keystore
altered. SQLgt ADMINISTER KEY MANAGEMENT CREATE
LOCAL AUTO_LOGIN KEYSTORE FROM KEYSTORE
'/oracle/TDE_WALLETS/asodb' IDENTIFIED BY
bmpassword1234 keystore altered. NOW BACK
IT UP!!! (Incedentally, can protect the wallet
from being overwritten/deleted by making
Immutable)- chattr i ewallet.p12 chattr i
cwallet.sso
December 2013
10

• Opening Wallets and setting Master Key

Open/Close ADMINISTER KEY MANAGEMENT SET KEYSTORE
OPEN IDENTIFIED BY software_keystore_password
CONTAINER ALL CURRENT ADMINISTER KEY
MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY
bmpassword12 ADMINISTER KEY MANAGEMENT SET
KEYSTORE CLOSE IDENTIFIED BY bmpassword12 Set
Key ADMINISTER KEY MANAGEMENT SET KEY USING TAG
'tag' IDENTIFIED BY password WITH BACKUP USING
'backup_identifier' CONTAINER ALL
CURRENT ADMINISTER KEY MANAGEMENT SET KEY
USING TAG 'Encryption_key_20092013' IDENTIFIED
BY bmpassword12 WITH BACKUP using 'backup_wallet'
?ewallet_2013112014572678_backup_wallet.p12
December 2013
11

• Wallet Operations

• Open and Close a Wallet.
• Change the Wallet Password.
• Merge one Wallets into another.
• Merge two Wallets to create a third.
• Export and Import Keys.
• Change the Master Key.
• Dont back up Wallet to same tape as the
  database backups! Backup your Wallet BEFORE and
  AFTER making any changes.

December 2013
12

• Wallet Views

SQLgt desc vencryption_wallet Name Type
-----------------------------------------
------------------ WRL_TYPE
VARCHAR2(20) WRL_PARAMETER
VARCHAR2(4000) STATUS VARCHAR2(30)
WALLET_TYPE VARCHAR2(20)
WALLET_ORDER VARCHAR2(9)
FULLY_BACKED_UP VARCHAR2(9) CON_ID
NUMBER SQLgt desc vwallet Name
Type -----------------------------------------
---------------------------- CERT_ID
VARCHAR2(52) DN VARCHAR2(255)
SERIAL_NUM VARCHAR2(40) ISSUER
VARCHAR2(255) KEYSIZE NUMBER
STATUS VARCHAR2(16) CON_ID
NUMBER
December 2013
13

• Transparent Data Encryption Column Encryption

Database
Wallet
December 2013
14

• TDE Column Encryption

How It Works Every table has own key stored in
data dictionary, encrypted by Master Key. Data
is held encrypted in memory- buffers are
encrypted. Decryption handled by the SQL layer-
see restrictions. Supported algorithms are- -
AES128, AES192, AES256, 3DES168. AES192 is the
default Limited list of Supported Datatypes- -
CHAR, VARCHAR2, DATE, NUMBER, TIMESTAMP,
INTERVAL, RAW. Use DBA_ENCRYPTED_COLUMNS to see
what columns are encrypted.
December 2013
15

• TDE Example

CREATE TABLE employee ( first_name
VARCHAR2(128), surname VARCHAR2(128),
employee_no NUMBER ENCRYPT NO SALT,
salary NUMBER(6) ENCRYPT USING
AES256') SQLgt desc employee Name Null?
Type -----------------------------------------
-------- ------------------- FIRST_NAME
VARCHAR2(128) SURNAME VARCHAR2(128)
EMPLOYEE_NO NUMBER ENCRYPT SALARY
NUMBER(6) ENCRYPT
December 2013
16

• TDE Column Encryption Parameters

• SALT, NOSALT, NOMAC parameters
• SALT parameter adds extra characters to a
  string/data pre encryption to mask character
  frequency and common character combinations ie
  sh, ch, ee and also where there are low
  number of distinct values for a column eg Y and
  N.
• NOMAC parameter enables you to skip the
  integrity check performed by TDE and saves 20
  bytes of disk space per encrypted value.

December 2013
17

• TDE Column Encryption Restrictions

• Encrypted columns cannot be used as a foreign
  key because each table has its own encryption
  key!
• Data encrypted at the sql level any utilities
  that bypass that layer wont work ie CDC stuff,
  ( Streams before 11g).
• Cannot use TransportableTablespaces.
• Cannot use index types other than B-tree (and
  then only used in equality checks).
• Cannot do range scan on an index on an encrypted
  column.
• Cannot have MVIEW Logs
• High overhead 5 additional CPU for those
  queries touching the columns.
• Columns encrypted consumes more space due to
  padding, salt and checksum.
• Cannot have SALT on a column that will have an
  index.

December 2013
18

• TDE- Tablespace Encryption 11g onwards

Database
Wallet
December 2013
19

• TDE- Tablespace Encryption

• Each tablespace has its own Encryption Key stored
  in both the file and tablespace headers,
  encrypted by the Master Key in the Wallet.
• Can only CREATE encrypted Tablespaces, cannot
  migrate an existing Tablespace.
• CREATE TABLESPACE secure_ts_1
• DATAFILE /u01/oracle/oradata/asodb/my_sec_ts01.db
  f SIZE 500M
• ENCRYPTION USING 3DES168'
• DEFAULT STORAGE(ENCRYPT)
• Migrate via alter table move tablespace,expdp/
  impdb,DBMS_REDEFINITION.
• Drop old tablespace with keep datafiles
  clause - and shred the datafiles using
  (sdelete/shred os commands).

December 2013
20

• TDE Tablespace Encryption- Restrictions

• None of the restrictions that you get with Column
  Encryption. Decryption is done at the IO level so
  blocks are unencrypted in the buffer cache. This
  removes all the restrictions of Column
  Encryption.
• IE
• Foreign Keys are supported.
• All data types are supported.
• All Index types are supported.
• Index Range Scans are supported.
• Transportable Tablespaces are supported
• MVIEW Logs are supported.
• Performance expect 4-8 in end user response
  times, 1-5 cpu increase

December 2013
21

• TDE Tablespace Encryption Views

 VENCRYPTED_TABLESPACES Name
Null? Type
-----------------------------------------
-------- ----------- TS
NUMBER ENCRYPTIONALG
VARCHAR2(7)
ENCRYPTEDTS
VARCHAR2(3) ENCRYTPEDKEY
RAW(32) MASTERKEYID
RAW(16)
BLOCKS_ENCRYPTED
NUMBER BLOCKS_DECRYPTED
NUMBER
December 2013
22

• Encrypting RMAN Backups

RMAN can encrypt backups whether or not you are
using TDE. For TDE (Wallet must be
open) RMANgtCONFIGURE ENCRYPTION FOR DATABASE
ON Dual mode (Password and TDE) RMANgtSet
encryption on identified by password RMANgtSet
decryption on identified by password (Dual
mode good for backups to be used offsite. Lose
the password AND the Wallet lost backups) No
TDE, Password only RMANgtSet encryption on
identified by password ONLY RMANgtSet
decryption on identified by password ..Lose
the password, lose the backup!
December 2013
23

• Encrypting Backups Available Algorithms

SQLgt select ALGORITHM_NAME, ALGORITHM_DESCRIPTION,
IS_DEFAULT from VRMAN_ENCRYPTION_ALGORITHMS AL
GORITHM_ ALGORITHM_DESCRIPTION
IS_DEFAULT ---------- -------------------------
--- AES128 AES 128-bit key
YES AES192 AES 192-bit key
NO AES256 AES 256-bit key NO RMANgt
CONFIGURE ENCRYPTION ALGORITHM TO 'AES256'
December 2013
24

• Encrypting Datapump Exports

Datapump has 4 parameters for encryption and
dictates whether files are encrypted using a
password or the TDE Master Key (Or
both) ENCRYPTION ENCRYPTED_COLUMNS_ONLY
DATA_ONLY METADATA_ONLY ALL ENCRYPTION_PASSWORD
876ijjhgjh8765d ENCRYPTION_MODE TRANSPARENT
DUAL PASSWORD ENCRYPTION_ALGORITH AES128
AES192 AES256 ENCRYPTION_PASSWORD implies
decrypt via TDE and reencrypt via the
password ENCRYPTION_MODE DUAL means encrypt
as both TDE and via password. Cannot set
TRANSPARENT mode and use a password. Can use
DBMS_DATAPUMP.GET_DUMPFILE_INFO to show if a
dumpfile is encrypted.
December 2013
25

• Encrypting Sqlnet Network Traffic

SQL Traffic plain text (except passwd
commands) Two offerings - Network Data
Encryption (NDE) and SSL. Neither are (now) part
of ASO (as of 12c) and come with the
Std/Enterprise License. - SSL setup requires
signed certificates installed to wallets at
either side plus allows log on via certificate.
Takes a lot of setting up and is slow. - NDE
requires very little setup, faster communication
compared to SSL.
December 2013
26

• Encrypting Network Traffic - NDE

Must Install ASO on client and server. SQLNET.ora
parameters say whether encryption is to be
enforced, and what algorithms to
use. Server-side sqlnet.ora SQLNET.ENCRYPTION_SER
VER ACCEPTEDREJECTEDREQUESTEDREQUIRED SQLNET.E
NCRYPTION_TYPES_SERVERalgorithm1,algorithm2 Clie
nt-side sqlnet.ora SQLNET.ENCRYPTION_CLIENT
ACCEPTEDREJECTEDREQUESTEDREQUIRED SQLNET.ENCRYP
TION_TYPES_CLIENTalgorithm1,algorithm2 Algorithm
s are DES, DES40, 3DES112, 3DES168 RC4_40,
RC4_56, RC4_128, RC4_256 AES128, AES192, AES256
December 2013
27

• Encrypting Network Traffic - NDE

Combinations of ENCRYPTION_CLIENT and
ENCRYPTION_SERVER
Rejected Accepted Requested Required
Rejected OFF OFF OFF ORA-12660
Accepted OFF OFF ON ON
Requested OFF ON ON ON
Required ORA-12660 ON ON ON
December 2013
28

• Encrypting Network Traffic NDE and JDBC

If you are using JDBC type 2 just a wrapper for
OCI so use sqlnet.ora JDBC Type 4 though puts
sqlnet packets directly on the network. propertie
s props new Properties() props.put("user",
"bob") props.put("password", "password") props.p
ut("oracle.net.encryption_client",
"accepted") props.put("oracle.net.encryption_type
s_client", "AES256") Connection conn
DriverManager.getConnection("jdbcoraclethin_at_dbs
rv11521mydbsid1", props)
December 2013
29

• Network Traffic Integrity and NDE

SQLNET.ora parameters say whether data integrity
checking is to be enforced, and what algorithms
to use. Stops packets being modified in transit
or resent multiple times. Serverside
sqlnet.ora SQLNET.CRYPTO_CHECKSUM_SERVER
ACCEPTED SQLNET.CRYPTO_CHECKSUM_TYPE_SERVER
Checksum_Type Client side sqlnet.ora SQLNET.CRYPT
O_CHECKSUM_CLIENT ACCEPTED SQLNET.CRYPTO_CHECKSUM
_TYPE_CLIENT Checksum_Type Checksum Types are
(SH1) or (MD5) and for JDBC Type
4 props.put("oracle.net.crypto_checksum_client",r
equired") props.put("oracle.net.crypto_checksum_t
ypes_client",(MD5))
December 2013
30

• Data Redaction New 12c Feature

Conditionally hides, reformats column data on
the fly rather than hiding rows of data. Limits
exposure of critical data. Implemented on the fly
at the kernel level so fast and secure. Requires
no code changes to application layer so can be
applied to 3rd party systems Redacted data
retains same datatype as original. Implemented
by creating a redaction policy (via PLSQL) on one
or more columns. Only one Policy can exist per
table.. A variety of redaction schemes are
supported - ( Partial, Full, RegExp or Random).
December 2013
31

• Data Redaction - examples

On-the-fly redaction of sensitive data in SQL
query results prior to display by
applications. Ie credit card numbers
(Partial) 8725-1324-7656-9987 to
---9987 Dates of birth
(Full) 04-MAR-1971 to 01-JAN-2001 Email(RegExp
) Bob_mycroft_at_gmail.com to hidden_at_gmail.com
December 2013
32

• Data Redaction - Datatypes

Supported Types Supported Types (FULL only) Unsupported Types
CHAR, NCHAR BLOB RAW, LONG RAW
VARCHAR2, NVARCHAR2 CLOB INTERVAL
LONG BFILE
NUMBER User Defined
DATE XML Types
TIMESTAMP Spatial Types
December 2013
33

• Data Redaction DBMS_REDACT PLSQL

• Implemented via DBMS_REDACT PLSQL Package
• DBMS_REDACT.ADD_POLICY
• DBMS_REDACT.ENABLE_POLICY
• DBMS_REDACT.DISABLE_POLICY
• DBMS_REDACT.DROP_POLICY
• DBMS_REDACT.ALTER_POLICY
• action gt
• DBMS_REDACT.ADD_COLUMN
• DBMS_REDACT.DROP_COLUMN
• DBMS_REDACT.MODIFY_EXPRESSION
• DBMS_REDACT.MODIFY_COLUMN
• DBMS_REDACT.SET_POLICY_DESCRIPTION
• DBMS_REDACT.SET_COLUMN_DESCRIPTION

December 2013
34

• Data Redaction - Full

BEGIN DBMS_REDACT.ADD_POLICY( object_schema
gt 'hr', object_name gt 'employees',
column_name gt 'commission_pct',
policy_name gt 'redact_com_pct',
function_type gt DBMS_REDACT.FULL,
expression gt '11') END / SELECT
COMMISSION_PCT FROM HR.EMPLOYEES WHERE
EMPID123454 COMMISSION_PCT ------------ 0 ALSO
- expression gtSYS_CONTEXT('SYS_SESSION_ROLES',
'SUPERVISOR') 'FALSE,
December 2013
35

• Data Redaction - Full

SQLgt desc REDACTION_VALUES_FOR_TYPE_FULL Name
Null? Type
-----------------------------------------
-------- ----------------------------
NUMBER_VALUE NOT
NULL NUMBER BINARY_FLOAT_VALUE
NOT NULL BINARY_FLOAT BINARY_DOUBLE_VALUE
NOT NULL BINARY_DOUBLE
CHAR_VALUE
VARCHAR2(1) VARCHAR_VALUE
VARCHAR2(1) NCHAR_VALUE
NCHAR(1)
NVARCHAR_VALUE
NVARCHAR2(1) DATE_VALUE
NOT NULL DATE TIMESTAMP_VALUE
NOT NULL TIMESTAMP(6)
TIMESTAMP_WITH_TIME_ZONE_VALUE NOT
NULL TIMESTAMP(6) WITH TIME ZONE BLOB_VALUE
BLOB
CLOB_VALUE
CLOB NCLOB_VALUE
NCLOB Can change the defaults
with- Exec DBMS_REDACT.UPDATE_FULL_REDACTION_VAL
UES()
December 2013
36

• Data Redaction - Partial

BEGIN DBMS_REDACT.ADD_POLICY( object_schema
gt 'HR', object_name gt
'EMPLOYEES', column_name gt
'SALARY', column_description gt
'emp_sal_comm shows employee salary and
commission', policy_name gt
'redact_emp_sal_comm', policy_description
gt 'Partially redacts the salary column',
function_type gt DBMS_REDACT.PARTIAL,
function_parameters gt '9,1,4', expression
gt '11') END SELECT (SALARY
(SALARYCOMMISSION_PCT)) total_emp_compensation FR
OM HR.EMPLOYEES WHERE DEPARTMENT_ID
80 TOTAL_EMP_COMPENSATION ----------------------
9999.9 9999.95
99990.95
December 2013
37

• Data Redaction - Random

BEGIN DBMS_REDACT.ADD_POLICY( object_schema
gt HR', object_name gt employees',
column_name gt 'login_name', policy_name
gt 'redact_emplogin_random_vals',
function_type gt DBMS_REDACT.RANDOM,
expression gt 'SYS_CONTEXT(''USERENV'',''SESS
ION_USER'') ! ''APP_USER''') END / SELECT
login_name FROM HR.employees LOGIN_VALS --------
-- NCG\pTVcK
December 2013
38

• Data Redaction Regular Expression

BEGIN DBMS_REDACT.ADD_POLICY( object_schema
gt HR', object_name gt
employees', column_name gt
'emp_id', policy_name gt
'redact_employee_ids', function_type
gt DBMS_REDACT.REGEXP, expression
gt '11', regexp_pattern gt
'(\d\d\d)(\d\d)(\d\d\d\d)', regexp_replace_stri
ng gt 'XXXXX\3', regexp_position gt
1, regexp_occurrence gt 0,
regexp_match_parameter gt 'i',
policy_description gt 'Redacts employee IDs
using regular expression') END SELECT emp_id
FROM hr.employees EMP_ID ------------ XXXXX1234
XXXXX5678
December 2013
39

• Data Redaction Regular Expression

Redaction pattern and replace library provides
pre-configured column templates for common types
(SSN, credit card, email, DOB).
regexp_pattern gt DBMS_REDACT.RE_PATTERN_
EMAIL_ADDRESS DBMS_REDACT.RE_PATTERN_ANY_DIGIT DBM
S_REDACT.RE_PATTERN_CC_L6_T4 DBMS_REDACT.RE_PATTER
N_US_PHONE DBMS_REDACT.RE_PATTERN_IP_ADDRESS
regexp_replace_string gt DBMS_REDACT.RE_REDACT_WI
TH_SINGLE_X DBMS_REDACT.RE_REDACT_CC_MIDDLE_DI
GITS DBMS_REDACT.RE_REDACT_EMAIL_NAME
DBMS_REDACT.RE_REDACT_EMAIL_DOMAIN
DBMS_REDACT.RE_REDACT_EMAIL_ENTIRE DBMS_REDACT.RE
_REDACT_IP_L3
December 2013
40

• Data Redaction weaknesses?

Doesnt prevent exhaustive range attacks
ie SELECT forename, surname FROM customers
WHERE credit_card LIKE 1 ....where
credit_card like 2 .... where credit_card like
21 .... where credit_card like 22 ....
where credit_card like 23 .... where
credit_card like 231
December 2013
41

• Data Redaction - Views

SQLgt desc redaction_policies Name Null?
Type -----------------------------------------
-------- ----------------------------
OBJECT_OWNER NOT NULL VARCHAR2(128)
OBJECT_NAME NOT NULL VARCHAR2(128)
POLICY_NAME NOT NULL VARCHAR2(128)
EXPRESSION NOT NULL VARCHAR2(4000) ENABLE
VARCHAR2(7) POLICY_DESCRIPTION
VARCHAR2(4000) SQLgt desc
redaction_columns Name Null? Type
-----------------------------------------
-------- ----------------------------
OBJECT_OWNER NOT NULL VARCHAR2(128)
OBJECT_NAME NOT NULL VARCHAR2(128)
COLUMN_NAME NOT NULL VARCHAR2(128)
FUNCTION_TYPE VARCHAR2(27)
FUNCTION_PARAMETERS VARCHAR2(1000)
REGEXP_PATTERN VARCHAR2(512)
REGEXP_REPLACE_STRING VARCHAR2(4000)
REGEXP_POSITION NUMBER
REGEXP_OCCURRENCE NUMBER
REGEXP_MATCH_PARAMETER
VARCHAR2(10) COLUMN_DESCRIPTION
VARCHAR2(4000)
December 2013
42

• Data Redaction impdb/expdb

How does Redaction fit with expdp/impdp? - Just
Another Client - SYS, SYSTEM users and DBA role
are excempt. - DBA has DATAPUMP_EXP_FULL_DATABAS
E. - DATAPUMP_EXP_FULL_DATABASE has EXEMPT
REDACTION POLICY
December 2013
43

• Conclusions
• ASO is an Extra Cost Option.
• TDE Encrypts Data At Rest (datafiles,
  datapump, external tables)
• ASO allows Encrypted backups.
• Network Encryption and Secure authentication no
  longer in ASO.
• Data Redaction might be useful especially for
  3rd party apps.
• ASO is not your only option!
• Backup your Wallet before and after any
  modifications!

44
Q and (maybe) A
bob_mycroft_at_yahoo.co.uk
45
References

• How to secure and Audit Oracle 10g and 11g, Ron
  Ben Natan. Very good
• Oracle Database 12c Release 1 New features
  guide
• http//www.oracle-base.com/articles/11g/data-pump
  -enhancements-11gr1.phpencryption_algorithm
• Oracle Database Advanced Security Guide, 12c
  Release 1 (12.1)
• Encryption and Redaction in Oracle Database 12c
  with Oracle Advanced Security - whitepaper 2013
• Oracle Advanced Security Transparent Data
  Encryption Best Practices (White Paper 2012)
  Very good
• Note 749947.1 ASO Native network encryption
• Note 1143443.1 SSO Encryption