Database Security and Auditing

---------------------------------------------

### **Oracle Database Security and Auditing**

-------------------------------------------

Oracle Database Security and Auditing are mechanisms to protect sensitive data, control access, and monitor user activity within the database. These features ensure compliance with security standards and safeguard against internal and external threats.

---

### **1. Oracle Database Security** :

---------------------------------------

Oracle Database Security involves implementing measures to protect data from unauthorized access, alteration, or destruction. Key aspects include:

#### **Authentication**

- Verifying the identity of users attempting to access the database.

- **Techniques**: Passwords, Kerberos, LDAP, PKI certificates, and OS-based authentication.

#### **Authorization**

- Controlling what authenticated users can do.

- **Methods**: 

  - Roles and privileges (e.g., `GRANT SELECT ON employees TO user1;`).

  - Least privilege principle (assigning only the necessary permissions).

#### **Encryption**

- Protecting data at rest and in transit:

  - **Transparent Data Encryption (TDE)**: Encrypts database files and backups.

  - **Network Encryption**: Secures communication between client and server.

#### **Data Masking**

- Obfuscating sensitive data in non-production environments to prevent misuse.

  - Example: Replacing real credit card numbers with dummy values.

#### **Data Redaction**

- Dynamically masking sensitive data in query results.

  - Example: Showing only the last 4 digits of a credit card number.

#### **Security Policies**

- Defining rules for database access and usage with tools like **Virtual Private Database (VPD)**.

---

### **2. Oracle Database Auditing**

Auditing involves tracking and logging database activities for accountability, compliance, and forensic purposes. It helps identify unauthorized or suspicious activities.

#### **Types of Auditing**

1. **Standard Auditing**

   - Tracks specific SQL operations or schema objects.

   - Example: Auditing SELECT operations on a sensitive table.

     ```sql

     AUDIT SELECT ON employees;

     ```

2. **Fine-Grained Auditing (FGA)**

   - Enables conditional auditing based on specific criteria.

   - Example: Log access only when salaries above $100,000 are queried.

     ```sql

     BEGIN

       DBMS_FGA.ADD_POLICY(

         object_schema   => 'hr',

         object_name     => 'employees',

         policy_name     => 'high_salary_audit',

         audit_condition => 'salary > 100000',

         audit_column    => NULL

       );

     END;

     ```

3. **Unified Auditing**

   - Introduced in Oracle 12c, consolidates audit logs for better manageability.

   - Example: Enable unified audit policy.

     ```sql

     CREATE AUDIT POLICY user_activity_policy

       ACTIONS ALL ON HR.EMPLOYEES;

     AUDIT POLICY user_activity_policy;

     ```

#### **Audit Trails**

- **Database Audit Trail**: Stores logs in the database (`AUD$` table).

- **OS Audit Trail**: Logs activities in the operating system.

- **XML Audit Trail**: Saves logs as XML files.

Enable auditing for critical schema objects:

AUDIT SELECT ON my_schema.employees;

location :

traditional Auditing: Logs can be stored in SYS.AUD$ or OS files (like $ORACLE_BASE/admin/prod/adump).

Unified Auditing: Logs are stored in the UNIFIED_AUDIT_TRAIL table, and optional OS file output can be configured.

Alert log :

$ORACLE_BASE/diag/rdbms/<dbname>/<dbname>/trace/alert_<dbname>.log 

ex :

/u01/app/oracle/diag/rdbms/orcl/orcl/trace/alert_orcl.log

---

### **3. Benefits of Security and Auditing**

1. **Compliance**: Ensures adherence to regulations like GDPR, HIPAA, and PCI DSS.

2. **Risk Mitigation**: Reduces the likelihood of data breaches and insider threats.

3. **Accountability**: Tracks user activities to hold them accountable.

4. **Incident Response**: Facilitates investigations with detailed logs.

Oracle's advanced security features, combined with robust auditing capabilities, create a comprehensive framework for protecting and monitoring your database environment.

------------------------------

Transparent Data Encryption :-

================================

TDE is an encryption mechanism present in Oracle database used to encrypt the data stored in a table column or tablespace. 

It protects the data stored on database files (DBF) by doing an encryption in case the file is stolen or hacked.

Transparent Data Encryption (TDE) provides mechanism to encrypt the data stored in the OS data files. TDE enables the encryption 

of data at the storage level to prevent data tempering from outside of the database.

When using transparent encryption, the Oracle encryption wallet must be created and the wallet should be opened every time the database starts.

TDE supports two levels of encryption

Columns Level Encryption:- Encrypt the table column data.

Tablespace Level Encryption:- Encrypt all the data in a tablespace

Steps to create wallet and enable encryption for table column and tablespace:

==============================================================================

1)Create a wallet location :

[oracle@orcl:~ ] mkdir -p /u01/app/oracle/admin/prod/WALLET

Update the wallet/keystore location in SQLNET.ORA :

A keystore must be created to hold the encryption key.Add the below entry to SQLNET.ORA,

2)[oracle@orcl:~ orcldemo] cat $ORACLE_HOME/network/admin/sqlnet.ora

# sqlnet.ora Network Configuration File: /oradb/app/oracle/product/12.2.0.1/db_1/network/admin/sqlnet.ora

# Generated by Oracle configuration tools.

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

ENCRYPTION_WALLET_LOCATION=(SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=$ORACLE_BASE/admin/$ORACLE_SID/WALLET )))

3)Create a keystore :

SQL> ADMINISTER KEY MANAGEMENT CREATE KEYSTORE '/u01/app/oracle/admin/devdb/WALLET' IDENTIFIED BY wallet$123;

keystore altered.

SQL> HOST ls /u01/app/oracle/admin/devdb/WALLET

ewallet.p12

Open the keystore :

SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY wallet$123;

close the keystore:

SQL> administer key management set keystore close identified by wallet$123;

keystore altered.

Check the v$encryption_keys view to see the key activated:

SQL> SELECT con_id, key_id FROM v$encryption_keys;

no rows selected

Set the encyrption key :

SQL> ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY wallet$123 WITH BACKUP;

4)WITH BACKUP creates a backup of the software keystore.It also creates a backup of the keystore before creating the new master encryption key.

Now,check the v$encryption_keys view to check the keystore is enabled.

SQL> SELECT con_id, key_id FROM v$encryption_keys;

 CON_ID          KEY_ID

---------- ----------------------------------------------------

0          AVRRh/0Uok8dvyvCAPMtZhwAAAAAAAAAAAAAAAAAAAAAAAAAAAAA

Check keystore information from v$encryption_wallet view.

SQL> select * from v$encryption_wallet ;

====================================

** TDE Implementation in Oracle 12c database :

Table creation with encrypted wallet :

SQL> CREATE TABLE student (name VARCHAR2(30),rollno NUMBER,dept VARCHAR2(30) ENCRYPT);

Table created.

SQL> INSERT INTO student values('RAM',102,'BCA');

1 row created.

SQL> commit;

Commit complete.

SQL> select owner,table_name,column_name,encryption_alg from dba_encrypted_columns where table_name='STUDENT';

OWNER      TABLE_NAME COLUMN_NAME          ENCRYPTION_ALG

---------- ---------- -------------------- -----------------

HARI       STUDENT    DEPT                 AES 192 bits key

Restart the database,

SQL> startup force

ORACLE instance started.

Total System Global Area 4294967296 bytes

Fixed Size 8628936 bytes

Variable Size 603981112 bytes

Database Buffers 1526726656 bytes

Redo Buffers 8146944 bytes

In-Memory Area 2147483648 bytes

Database mounted.

Database opened.

After restart database,check the encrypted table column data,

SQL> SELECT * FROM sbz.student;

SELECT * FROM sbz.student;

                   *

ERROR at line 1:

ORA-28365: wallet is not open

Reopen the keystore :

SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY wallet$123;  

keystore altered.

SQL>  select * from sbz.student;

NAME                               ROLLNO DEPT

------------------------------ ---------- ------------------------------

hari                                  101 MCA

Here the wallet_type is PASSWORD , i.e every time we restart the database, we need to open the key/wallet separately.

To overcome this, we can enable auto login ,so that next time when db gets restart, it will open the wallet automatically.

======================================

Auto-login software keystore: 

======================================

This kind of keystores are protected by system-generated password, and does not need to opened explicitly because these keystores open automatically.

Enable AUTOLOGIN  wallet type :

SQL> SELECT * FROM v$encryption_wallet;

WRL_TYPE WRL_PARAMETER    STATUS WALLET_TYP WALLET_OR  FULLY_BAC CON_ID

-------- -------------    ------ ---------- ---------- --------- ------

FILE     /u01/app/oracle/admin/prod/WALLET OPEN PASSWORD SINGLE     NO        0

SQL> ADMINISTER KEY MANAGEMENT CREATE AUTO_LOGIN KEYSTORE FROM KEYSTORE '/u01/app/oracle/admin/devdb/WALLET' IDENTIFIED BY wallet$123;

keystore altered.

SQL> HOST ls /u01/app/oracle/admin/devdb/WALLET

cwallet.sso  ewallet_2024071906543405.p12  ewallet.p12

(To close/off the auto-login need to delete or rename sso file  mv cwallet.sso cwallet.sso_bkp and restart db)

As soon as we execute above statement, we will see cwallet.sso file gets created under keystore location directory. 

Once we have AUTOLOGIN keystore, there is no need to open keystore for individual pluggable databases because auto-login

keystore would open automatically for all pluggable databases as well.

Restart the database,

SQL> startup force

ORACLE instance started.

After restarting database,check the wallet type:

SQL> SELECT * FROM v$encryption_wallet;

WRL_TYPE WRL_PARAMETER STATUS WALLET_TYP WALLET_OR FULLY_BAC CON_ID

-------- ------------- ------ ---------- ---------- --------- ------

FILE /home/oracle/wallet/ OPEN AUTOLOGIN SINGLE NO 0

Now,the encrypted table data and column name ‘DEPT’,

SQL> SELECT * FROM sbz.student;

NAME  ROLLNO  DEPT

----- ------- -----

hari  101      MCA

AES - Adavanced Encryption Standard:

-----------------------------------

Encryption Algorithm Options

When using AES, you can specify the key length based on the security requirements:

AES128 (128-bit key)

AES192 (192-bit key)

AES256 (256-bit key)

For stronger security, it is recommended to use AES256.

AES Key Features in TDE :

Algorithm Options: AES supports key lengths of 128, 192, and 256 bits.

Performance: AES is highly efficient for encryption and decryption operations.

Compatibility: Supported in both TDE tablespace encryption and column encryption.

Why Use AES with TDE? :

Strong Encryption: AES is a widely accepted encryption standard for protecting sensitive data.

Compliance: Meets regulatory requirements like GDPR, PCI DSS, and HIPAA.

Ease of Use: Transparent to applications; no code changes required.

Performance: Optimized for database operations with minimal performance overhead.

===============================

Create Encrypted Tablespace :

================================

SQL> CREATE TABLESPACE encrypt_ts DATAFILE '/u01/app/oracle/oradata/PROD/datafile/encrypt_ts.dbf' SIZE 20M ENCRYPTION USING 'AES256' DEFAULT STORAGE(ENCRYPT);

Tablespace created.

SQL> CREATE TABLE student_demo (name VARCHAR2(30),rollno NUMBER,dept VARCHAR2(30)) tablespace encrypt_ts ;

Table created.

SQL> INSERT INTO student_demo values('shiv',101,'MCA'); 

1 row created. 

SQL> commit; 

Commit complete.

SQL> select tablespace_name,encrypted from dba_tablespaces where tablespace_name='ENCRYPT_TS';

TABLESPACE_NAME   ENC

---------------   ---

ENCRYPT_TS        YES

Restart the database,

SQL> startup force

ORACLE instance started.

Check the table data present in tablespace ‘ENCRYPT_TS’        

SQL> SELECT * FROM student_demo;

NAME  ROLLNO  DEPT

----- ------- ----

hari  101     MCA

Local auto-login software keystores:

This type of keystores have auto-login functionality on the computer where these are created and these cannot be opened from any other computer.

To create a local auto-login keystore, use following syntax

SQL> ADMINISTER KEY MANAGEMENT CREATE LOCAL AUTO_LOGIN KEYSTORE FROM KEYSTORE '/u01/app/oracle/wallet' IDENTIFIED BY wallet$123;

But for creation of LOCAL AUTO_LOGIN keystore,the wallet type before has to be PASSWORD ( password-based keystore).

Close the Password based software keystore :

SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE CLOSE IDENTIFIED BY wallet$123;

keystore altered.

This above option can be enable when the wallet_type is PASSWORD.

To close a password-based software keystore or a hardware keystore, specify the Keystore password.

Close the Autologin based software keystore :

SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE CLOSE;

keystore altered.

To close an auto-login keystore, do not specify Keystore password.Before you close an auto-login keystore,

check the WALLET_TYPE  column of the v$encryption_wallet view. If it returns  AUTOLOGIN, then you can close the keystore. 

Otherwise, if you attempt to close the keystore, then an error occurs.

DBOPTIONS DECRYPTPASSWORD sharedsecret {AES128 | AES192 | AES256} ENCRYPTKEY keyname

DBOPTIONS DECRYPTPASSWORD AXaEwHuAh09mv3j8/yJd858AAAAAAAAAAAAAAAAAAAAAAAAAAAAA AES192 ENCRYPTKEY mykey1

============================

Oracle Flashback

============================

Oracle Flashback

Oracle flashback allows you to move database back in time. You can use flashback technology to move entire database or a particular table inside database.

Flashback Table Before Drop

==================

SHOW RECYCLEBIN;

FLASHBACK TABLE "BIN$gk3lsj/3akk5hg3j2lkl5j3d==$0" TO BEFORE DROP;

FLASHBACK TABLE emp TO BEFORE DROP;

==============

Flashback Table

===========

You can flashback table to a particular SCN or time in the past. Before you can flashback table, you must enable row movement

SQL> ALTER TABLE emp ENABLE ROW MOVEMENT;

Table altered.

SQL> select current_scn from v$database ;

CURRENT_SCN

-----------

    6669646

Now you are ready to flashback table to SCN or timestamp

FLASHBACK TABLE emp  TO SCN 6669646;

FLASHBACK TABLE HR.EMPLOYEES TO TIMESTAMP 

TO_TIMESTAMP(‘2016-05-12 18:30:00’, ‘YYYY-MM-DD HH24:MI:SS’);

==================

Flashback Database

====================

We can move an entire database back in time to a particular SCN or a timestamp. Flashback Database must be already enabled on the database to user this feature.

Enable Flashback Database

Make sure DB_RECOVERY_FILE_DEST parameter is set. This is the location where Oracle will store flashback logs

SQL> alter system set db_recovery_file_dest='/u02/flash_logs' SCOPE=spfile;

Set DB_RECOVERY_FILE_DEST parameter as per requirement

SQL> alter system set db_recovery_file_dest_size=50G SCOPE=spfile;

Set the DB_FLASHBACK_RETENTION_TARGET parameter which specifies the upper limit (in minutes) on how far back in time the database can be flashed back

SQL> alter system set db_flashback_retention_target=2880;

Enable flashback database which requires database bounce

SQL> shutdown immediate;

SQL> startup mount;

SQL> alter database flashback on;

SQL> alter database open;

SQL> select flashback_on from v$database;

==================

Create Sample User

=================

Let us capture the database SCN number before we create a user

SQL> SELECT current_scn, SYSTIMESTAMP FROM v$database;

Current SCN: 6672589

Create a user FLASH_USR and try to connect the database with same user

SQL> create user flash_usr identified by flash_usr;

SQL> grant connect, resource to flash_usr;

SQL> conn flash_usr/flash_usr;

===================================

Flashback Database to SCN or Timestamp

Assume that the user has been created by mistake and you want to flashback database to the SCN just before the user creation. Shutdown DB and startup mount

SQL> shut immediate;

SQL> startup mount;

Flashback database to SCN before user creation and open database with resetlogs

SQL> Flashback database to scn 6672589;

SQL> Alter database open resetlogs;

You can flashback database to particular timestamp too

FLASHBACK DATABASE TO TIMESTAMP 

TO_TIMESTAMP(‘2016-05-12 18:30:00’, ‘YYYY-MM-DD HH24:MI:SS’);