Oracle provides excellent protection against data loss from equipment failure, accident, or malicious damage.   Administrator should focus on stopping someone from gaining access via stealing passwords, hacking accounts, or accessing restricted data elements.

Security Policy should:

1. Reduce possibility of unauthorized access to database.
2. Reduce damage that can be caused by user-level access.
3. Detect illicit access to the database and restrict data access/damage

 

Three primary areas to address in Security are:

1. User Accounts
2. Privileges and roles given to user
3. Auditing

 

Users:

Users must authenticate to the database before access is granted.  Most common method is username/password combination.  Since 11g, passwords have been case sensitive.  12c provides two new password complexity functions:

• ORA12C_VERIFY_FUNCTION (default for 12c using DBCA)
• ORA12C_STRONG_VERIFY_FUNCTION

Either function can be modified to increase or customize password complexity.  If you create database with CREATE DATABASE command, no password function is used unless you execute the script.

 

Privileges and Roles:

Best practice is to provide least privilege and roles required for a user account to do their job.

Privileges can be granted:

• Explicit – Privilege granted directly to user
• Role-based – Privilege granted to a role then the role is granted to one or more users.

Role-based privileges is easy to administer because you classify users into groups.  Create a role based on the group and assign members of that group that particular role.

 

Auditing:

Allows you to monitor and record the actions of user and administrators.  In 12c you can use unified audit policies, DBMS_FA package for fine-grained auditing policies.   Auditing allows you to determine if malicious activity is in the database.

 

 

Larry Catt

OCP

 

 

 

Transparent Data Encryption (TDE) is used for both column and tablespace encryption.    It uses a software key store which is a container that stores the TDE master encryption key.   There exist one TDE master encryption key per database and the location is defined in the SQLNET.ora file.    In previous releases this key store was referred to as an Oracle Wallet.   Software key store must be open before you can create an encrypted column or tablespace and before you can store or retrieve encrypted data.  Once you open, it is open to all sessions until it is explicitly close it or database shutdown.  Transparent Data Encryption is designed to protect data stored on a disk or other media and protects data from access by other means outside of the database.  It protects backups on tape and from accessing data via the OS directly to datafiles.  TDE supports the encryption types:  3DES168, AES128, AES192, AES256.  You specify the encryption algorithm when creating encrypted column or tablespace.  By default TDE used AES192.   TDE adds a salt to plaintext before encrypt to increase security.   TDE also uses Message Authentication Code (MAC) to data for integrity checking and uses SHA-1 by default.

 

Column Encryption

Columns are encrypted and decrypted at the SQL layer and any service that bypasses the SQL layer cannot use TDE column encryption.  TDE column encryption cannot be used with the following features:

1. Index other than B-TREE
2. Range scan through indexes.
3. Synchronous change data capture.
4. Transportable tablespaces.
5. Columns used in foreign key constraints.

BUT you can encrypt these columns using DBMS_CRYPTO package.  You can encrypt the following datatypes: Note some size restrictions exist.

1. BINARY_DOUBLE
2. BINARY_FLOAT
3. CHAR
4. DATA
5. INTERVAL DAY TO SECOND
6. INTERVAL YEAR TO MONTH
7. NCHAR
8. NUMBER
9. NVARCHAR2
10. RAW
11. TIMESTAMP
12. VARCHAR2

Example of encrypted column with AES192:

 

CREATE TABLE emp (

ssn number,

fname varchar(100),

lname varchar(100),

salary  number encrypt);

 

Never salt a column that is an index and being encrypted.  Example:

 

CREATE TABLE emp (

ssn number encrypt no salt,

fname varchar(100),

lname varchar(100),

salary  number encrypt);

 

Change encrypt type by the USING clause. Example

 

CREATE TABLE emp (

ssn number encrypt no salt,

fname varchar(100),

lname varchar(100),

salary  number encrypt USING ‘3DES168’);

 

You can use the ALTER TABLE ADD to add an encrypted column to existing table.  You can use the ALTER TABLE MODIFY to make an existing column encrypted.

 

 

Tablespace Encryption

You can encrypt a permanent tablespace to protect the data.  This encrypts all data blocks of any segment type.  Data of an encrypted tablespace is also encrypted in undo, redo and all temp space.   There is no overhead in encrypting tablespace.  Restricting of encrypting tablespaces:

1. You cannot encrypt an existing tablespace.
2. Encrypted tablespace cannot be moved to another database.
3. You must open key store after mount and before open to recover an encrypted tablespace.

 

Creation of encrypted tablespace example

 

CREATE TABLESPACE data_secret

DATAFILE ‘/u01/oradata/data_secret01.dbf’ size 1g

ENCRYPTION

DEFAULT STORAGE(ENCRYPT);

 

 

Larry Catt

OCP