"Warning: VKTM detected a time drift" in 11gR2

ALERT LOG

Warning: VKTM detected a time drift.
Time drifts can result in an unexpected behavior such as time-outs. Please check trace file for more details.

Cause

This problem is due to the Bug 11837095 - "TIME DRIFT DETECTED" APPEARS INTERMITTENTLY IN ALERT LOG"

Solution

Time drift" error is a " message which can be ignored, to remove the "Warning: VKTM detected set event 10795 or Apply patch 11837095

SQL> alter system set event="10795 trace name context forever, level 2" scope=spfile;

Then bounce the DB instances to implement the event/change.

PDF: Oracle Audit Vault

Oracle Audit Vault automates the collection and consolidation of audit data into a secure repository, enabling efficient monitoring and reporting. Oracle Audit Vault is providing a secure repository for audit data, built-in reporting, event alerting, and separation-of-duty.

The document cover below topics :

- Oracle Audit Vault Overview & Architecture
- Installing the Oracle Audit Vault Server
- Installing the Oracle Audit Vault Agent
- Registering Oracle Database Sources and Collectors with Audit Vault Server
- Registering Microsoft SQL Server Database Sources and Collector with Audit Vault Server
- Audit Vault Log Files

 You can download from : Oracle Audit Vault

Thanks,

Ahmed Fathi

Videos: Oracle Database 11gR2 Security Course

Oracle Database 11g: Security Release 2

I would like to share my last oracle database security course, you can download and enjoy.

Asking Allah to accept it and people get benefit from that.

Course Objectives

• Use basic Oracle Database security features
• Choose a user authentication model
• Secure the database and the listeners
• Use the Enterprise Security Manager tool
• Manage users using proxy authentication
• Implement Enterprise User Security
• Describe the benefits and requirements associated with the Oracle Advanced Security option
• Manage secure application roles
• Implement fine-grained access control
• Manage Virtual Private Database
• Implement fine-grained auditing
• Use Transparent Data Encryption
• Use file encryption
• Encrypt and decrypt table columns
• Set up Oracle Label Security policies

Course Contents :

5 Videos cover course from chapter 2 to 21

zip file contains practice scripts that used in course and some documents that help you to go in-depth.

URL : Oracle Database 11gR2 Security
YouTube:  http://www.youtube.com/playlist?list=PLbybFqHGEZ9FvFKjn3pkTsO0pA0XrjWos

Note: Course in Arabic 

Thanks,
Ahmed Fathi

Oracle Transparent Data Encryption

Oracle Transparent Data Encryption Installation

Oracle TDE is available by default in Oracle RDBMS Enteprise Edition. To be able to use this it is necessary to purchase an Oracle Advanced Security license.
Oracle Transparent Data Encryption Configuration and Administration

To start using TDE the following operations have to be performed:

1) Make sure that the wallet location exists. If a non default wallet location must be used then specify it in the sqlnet.ora file :

ENCRYPTION_WALLET_LOCATION =
   (SOURCE = (METHOD = FILE)
     (METHOD_DATA =
      (DIRECTORY = /u01/app/oracle/wallet)
     )
   )

Note: The default encryption wallet location is $ORACLE_BASE/admin/<global_db_name>/wallet. If you want to let Oracle manage a wallet in the default location then there is no need to set the ENCRYPTION_WALLET_LOCATION parameter in sqlnet.ora.

2) Generate a master key:
alter system set encryption key identified by "wallet_password";

This command will do the following:

A) If there is no wallet currently in the wallet location then a new wallet with the password "wallet_password" will be generated. The password is enclosed in double quotes to preserve the case of the characters. If the double quotes are not used then the characters of the password will be all in upper case. This command will also cause the new wallet to be opened and ready for use.

Comment: This is the only valid way of creating a new wallet for TDE, you may not use oracle wallet manager or command line tool orapki to create the TDE wallet for the first time!

B) A new master key will be generated and will be written to the wallet. This newly generated master key will become the active master key. The old master keys (if there were any) will still be kept in the wallet but they will not be active. They are kept there to be used when decrypting data that was previously encrypted using them.


To see the status of an wallet run the following query:
SQL> select * from v$encryption_wallet;

WRL_TYPE
--------------------
WRL_PARAMETER
--------------------------------------------------------------------------------
STATUS
------------------
file
/u01/app/oracle/admin/ORCL/wallet
OPEN


3) Enable encryption for a column or for an entire tablespace:

3.1) Create a table by specifying the encrypt option:

create table test(col1 number, col2 varchar2(100) encrypt using 'AES256' NO SALT);

SET LINESIZE 100
COLUMN owner FORMAT A15
COLUMN tble_name FORMAT A15
COLUMN column_name FORMAT A15

SELECT * FROM dba_encrypted_columns;


3.2) Encrypt the column(s) of an existing table:

alter table test modify( col2 encrypt SALT);

3.3) Create an encrypted tablespace :

CREATE TABLESPACE encryptedtbs
DATAFILE '/u01/app/oracle/oradata/ORCL/encryptedtbs01.dbf' SIZE 100M
ENCRYPTION USING 'AES256'
DEFAULT STORAGE(ENCRYPT);

SELECT tablespace_name, encrypted FROM dba_tablespaces;

Note: An existing  non encrypted tablespace cannot be encrypted. If you must encrypt the data from an entire tablespace then create a new encrypted tablespace and then move the data from the old tablespace to the new one

Wallets must be reopened after an instance restart and can be closed to prevent access to encrypted data.

    ALTER SYSTEM SET ENCRYPTION WALLET OPEN IDENTIFIED BY "myPassword";
    ALTER SYSTEM SET ENCRYPTION WALLET CLOSE;
   
 Database Startup

The following text shows the impact of stopping and starting the database on an encrypted tablespace. Notice how the wallet must be opened before the data is accessible.

    SQL>CONN / AS SYSDBA
    Connected.
    SQL> SHUTDOWN IMMEDIATE;
    Database closed.
    Database dismounted.
    ORACLE instance shut down.
    SQL> STARTUP
    ORACLE instance started.

    Total System Global Area  626327552 bytes
    Fixed Size                  2255832 bytes
    Variable Size             234882088 bytes
    Database Buffers          381681664 bytes
    Redo Buffers                7507968 bytes
    Database mounted.
    Database opened.
    SQL> SELECT tablespace_name, encrypted, status FROM dba_tablespaces;

    TABLESPACE_NAME                ENC STATUS
    ------------------------------ --- ---------
    SYSTEM                         NO  ONLINE
    SYSAUX                         NO  ONLINE
    UNDOTBS1                       NO  ONLINE
    TEMP                           NO  ONLINE
    USERS                          NO  ONLINE
    EXAMPLE                        NO  ONLINE
    SOE                            NO  ONLINE
    ENCRYPTED_TS                   YES ONLINE

    8 rows selected.

    SQL> SELECT * FROM test.ets_test;
    select * from test.ets_test
                       *
    ERROR at line 1:
    ORA-28365: wallet is not open

    SQL> ALTER SYSTEM SET ENCRYPTION WALLET OPEN IDENTIFIED BY "myPassword";

    System altered.

    sys@db11g> select * from test.ets_test;

            ID DATA
    ---------- --------------------------------------------------
             1 This is a secret!

    1 row selected.

    SQL>

Verify Transparent Data Encryption (TDE)

Create an encrypted tablespace :

CREATE TABLESPACE data DATAFILE '/u01/app/oracle/oradata/ORCL/data01.dbf' SIZE 10M ENCRYPTION USING 'AES256' DEFAULT STORAGE(ENCRYPT);

First check which tablespaces are encrypted and which ones are not:

SQL> select TABLESPACE_NAME, ENCRYPTED from dba_tablespaces;

TABLESPACE_NAME ENC
------------------------------ ---
SYSTEM NO
SYSAUX NO
UNDOTBS1 NO
TEMP NO
USERS NO
EXAMPLE NO
DATA YES

Then find the associated datafiles on the filesystem:

SQL> select TABLESPACE_NAME, FILE_NAME from dba_data_files where TABLESPACE_NAME in ('USERS','DATA');

TABLESPACE_NAME
------------------------------
FILE_NAME
--------------------------------------------------------------------------------
USERS
/u01/oradata/v1120/users01.dbf

DATA
/u01/oradata/v1120/data01.dbf

Create a test table in the unencrypted USERS tablespace and put a known string value in it:

SQL> create table scott.test(data varchar2(100)) tablespace users;

Table created.

SQL> insert into scott.test values ('ABCDEFGHIJKLMNOPQRSTUVWZYZ');

1 row created.

SQL> commit;

Commit complete.


Repeat the same but now put a table in the encrypted tablespace (it incidently shows the wallet needs to be open to be able to store data in the encrypted tablespsace):

SQL> create table scott.test_encrypted(data varchar2(100)) tablespace data;

Table created.

SQL> insert into scott.test_encrypted values ('ABCDEFGHIJKLMNOPQRSTUVWZYZ');
insert into scott.test_encrypted values ('ABCDEFGHIJKLMNOPQRSTUVWZYZ')
  *
ERROR at line 1:
ORA-28365: wallet is not open

SQL> alter system set wallet open identified by "welcome1";
System altered.

SQL> insert into scott.test_encrypted values ('ABCDEFGHIJKLMNOPQRSTUVWZYZ');
1 row created.

SQL> commit;
Commit complete.

Make sure all changes are flushed to disk, otherwise the data may not be written do the datafiles when performing the subsequent check:

SQL> alter system checkpoint;

System altered.

Now use the OS 'strings' command to determine whether the string value inserted in the table is 'visible':

SQL> !strings /u01/oradata/v1120/users01.dbf | grep ABCDEF
ABCDEFGHIJKLMNOPQRSTUVWZYZ

SQL> !strings /u01/oradata/v1120/data01.dbf | grep ABCDEF

<no output>