New features in Oracle 11g Database

What's new in an Oracle 11g Database?

1. Lets check some INIT parameters first:-

MEMORY_TARGET: Specifies the Oracle system-wide usable memory. The database tunes memory to the MEMORY_TARGET value, increasing or decreasing SGA and PGA as needed.

MEMORY_MAX_TARGET: Maximum value that MEMORY_TARGET initialization parameter can use.

DIAGNOSTIC_DEST: Diagnostics for each database instance are located in this dedicated directory

2. Password is now Case Sensitive.

SQL> create user u1 identified by U1 default tablespace users quota 50m on users;

User created.

SQL> grant create session, create table, create procedure, create view to u1;

Grant succeeded.

SQL> create user u2 identified by u2 default tablespace users quota 50m on users;

User created.

SQL> grant create session, create table, create procedure, create view to u2;

Grant succeeded.

SQL> connect u1/u1
ERROR:
ORA-01017: invalid username/password; logon denied


Warning: You are no longer connected to ORACLE.

SQL> connect u1/U1
Connected.

SQL> connect u2/U2
ERROR:
ORA-01017: invalid username/password; logon denied


Warning: You are no longer connected to ORACLE.
SQL> connect u2/u2
Connected.

3. READ-ONLY Table

SQL> connect u1/U1
Connected.

SQL> create table t1 (a number) ;

Table created.

SQL> begin
   2 for x in 1..20 loop
   3 insert into t1 values (x);
   4 end loop;
   5 commit;
   6 end;
   7 /

PL/SQL procedure successfully completed.

SQL> select READ_ONLY from user_tables where table_name='T1';

REA
---
NO

1 row selected.

SQL> alter table t1 READ ONLY;

Table altered.

SQL> select READ_ONLY from user_tables where table_name='T1';

REA
---
YES

1 row selected.

SQL> insert into t1 values (99);
insert into t1 values (99)
*
ERROR at line 1:
ORA-12081: update operation not allowed on table "U1"."T1"

SQL> alter table t1 READ WRITE;

Table altered.

SQL> insert into t1 values (99);

1 row created.

SQL> commit;

Commit complete.

4. Tablespace Encryption

As we all know, in an Oracle database all the data are finally stored in the datafiles, what if the datafiles containing sensitive data were stolen? there are always chances that these datafiles can either be disseminated or attached to a different database and the sensitive data can be exposed or revealed.

In order to overcome this, Oracle introduced Transparent Data Encryption (TDE), in Oracle 10g version, a column of a table can be encrypted based on a (master) key, and that key is not stored within the same database, but externally in an Oracle wallet.

Now in Oracle 11g, you can encrypt an entire tablespace, i.e., a whole table or all the tables in an encrypted tablespace using tablespace encryption.

Tablespace encryption is supported on all data stored in an encrypted tablespace including internal large objects (LOBs) such as BLOBs and CLOBs. But data stored externally, like BFILE data is not encrypted.

If a table is created with BFILE column in an encrypted tablespace, then this BFILE column will not be encrypted, but the rest of the table will be encrypted.

Configure wallet location by setting the ENCRYPTION_WALLET_LOCATION parameter in sqlnet.ora:-

ENCRYPTION_WALLET_LOCATION=(SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=C:\oracle\ora11\admin\ORA11\wallet)))

Tablespace Level
ENCRYPTION algorithms supported in Oracle11g are 3DES168, AES128, AES192 and AES256

SYS> ALTER SYSTEM SET ENCRYPTION KEY IDENTIFIED BY "b1b1";

System altered.

SYS> CREATE TABLESPACE ENCR_TBS_1
   2     DATAFILE 'C:\ORACLE\ORADATA\ORA11\ENCR_TBS_01.DBF' SIZE 20M
   3     AUTOEXTEND ON NEXT 1M MAXSIZE 128M
   4     EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M
   5     SEGMENT SPACE MANAGEMENT AUTO
   6     ENCRYPTION
   7     DEFAULT STORAGE(ENCRYPT);

Tablespace created.

SYS> CREATE TABLESPACE ENCR_TBS_2
   2     DATAFILE 'C:\ORACLE\ORADATA\ORA11\ENCR_TBS_02.DBF' SIZE 20M
   3     AUTOEXTEND ON NEXT 1M MAXSIZE 128M
   4     EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M
   5     SEGMENT SPACE MANAGEMENT AUTO
   6     ENCRYPTION USING 'AES256'
   7     DEFAULT STORAGE(ENCRYPT);

Tablespace created.

SQL> select * from V$ENCRYPTED_TABLESPACES;

       TS# ENCRYPT ENC
---------- ------- ---
         5 AES128  YES
         6 AES256  YES

SYS> select tablespace_name,encrypted from dba_tablespaces;

TABLESPACE_NAME     ENC
----------------    ---
SYSTEM              NO
SYSAUX              NO
UNDOTBS1            NO
TEMP                NO
USERS               NO
TEST_TS1            NO
ENCR_TBS_1          YES
ENCR_TBS_2          YES

8 rows selected.

SQL> desc DBA_ENCRYPTED_COLUMNS
Name                Null?    Type
------------------- -------- -------------
OWNER NOT           NULL     VARCHAR2(30)
TABLE_NAME          NOT NULL VARCHAR2(30)
COLUMN_NAME         NOT NULL VARCHAR2(30)
ENCRYPTION_ALG               VARCHAR2(29)
SALT                         VARCHAR2(3)

SQL> col owner for a5
SQL> col table_name for a10
SQL> col column_name for a14
SQL> select * from DBA_ENCRYPTED_COLUMNS;

OWNER TABLE_NAME COLUMN_NAME    ENCRYPTION_ALG                SAL
----- ---------- -------------- ----------------------------- ---
U1    CUST       CRED_CARD_NUM  AES 192 bits key              NO
U1    CUST_2     CRED_CARD_NUM  3 Key Triple DES 168 bits key NO

SYS> CREATE TABLE CUST_1(
   2     cust_id VARCHAR2(4),
   3     cust_name VARCHAR2(10),
   4     cred_card_num NUMBER(16))
   5     TABLESPACE ENCR_TBS_1;

Table created.

Above table is created in an encrypted tablespace, hence all data in this table is stored encrypted on the disk.

SYS> insert into CUST_1 values (30,'Kate',1234567812345678);

1 row created.

SYS> insert into CUST_1 values (40,'Deep',5678123456781234);

1 row created.

SYS> commit;

Commit complete.

SYS> col CRED_CARD_NUM for 9999999999999999
SYS> select * from CUST_1;

CUST CUST_NAME  CRED_CARD_NUM
---- ---------- -----------------
30   Kate       1234567812345678
40   Deep       5678123456781234

SYS> alter system set encryption wallet close;

System altered.

C:\>cd C:\oracle\ora11\admin\ORA11\wallet

C:\oracle\ora11\admin\ORA11\wallet>C:\oracle\ora11\BIN\orapki wallet export -wallet . -dn "CN=oracle" -request oracle.req -pwd "b1b1"

Table Level
SQL> ALTER SYSTEM SET ENCRYPTION KEY IDENTIFIED BY "b1b1";

System altered.

SQL> ALTER SYSTEM SET ENCRYPTION WALLET OPEN IDENTIFIED BY "b1b1";
ALTER SYSTEM SET ENCRYPTION WALLET OPEN IDENTIFIED BY "b1b1"
*
ERROR at line 1:
ORA-28354: wallet already open

A wallet is automatically opened when you set or reset the master encryption key. Once you set the encryption, you will be see a file created in the above DIRECTORY location.

SQL> ALTER SYSTEM SET ENCRYPTION WALLET CLOSE ;

System altered.

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

System altered.

Creating a table with an encrypted column using the default AES192 algorithm

SQL> conn u1/u1
Connected.
SQL> create table CUST (
   2     cust_id VARCHAR2(4),
   3     cust_name VARCHAR2(10),
   4     cred_card_num NUMBER(16) ENCRYPT
   5     );

Table created.

U1> desc cust
Name                Null?    Type
------------------- -------- --------------------
CUST_ID                      VARCHAR2(4)
CUST_NAME                    VARCHAR2(10)
CRED_CARD_NUM                NUMBER(16) ENCRYPT

U1> insert into CUST values (10,'Tom',1234567812345678);

1 row created.

U1> insert into CUST values (20,'Sam',5678123456781234);

1 row created.

U1> commit;

Commit complete.

U1> CREATE INDEX cust_card_idx ON CUST (cred_card_num);
CREATE INDEX cust_card_idx ON CUST (cred_card_num)
*
ERROR at line 1:
ORA-28338: cannot encrypt indexed column(s) with salt

U1> ALTER TABLE CUST MODIFY (cred_card_num ENCRYPT NO SALT);

Table altered.

U1> CREATE INDEX cust_card_idx ON CUST (cred_card_num);

Index created.

U1> col CRED_CARD_NUM for 9999999999999999
U1> select * from CUST;

CUST CUST_NAME  CRED_CARD_NUM
---- ---------- -----------------
10   Tom        1234567812345678
20   Sam        5678123456781234

Creating a table with an encrypted column using NO SALT parameter

U1> create table CUST_2 (
  2     cust_id VARCHAR2(4),
  3     cust_name VARCHAR2(10),
  4     cred_card_num NUMBER(16) ENCRYPT NO SALT
  5     );

Table created.

U1> insert into CUST_2 select * from CUST;

2 rows created.

U1> commit;

Commit complete.

U1> select * from CUST_2;

CUST CUST_NAME  CRED_CARD_NUM
---- ---------- -----------------
10   Tom        1234567812345678
20   Sam        5678123456781234

Adding Salt to an Encrypted Column

U1> ALTER TABLE CUST_2 MODIFY (cred_card_num ENCRYPT SALT);

Table altered.

Removing Salt to an Encrypted Column

U1> ALTER TABLE CUST_2 MODIFY (cred_card_num ENCRYPT NO SALT);

Table altered.

U1> ALTER TABLE CUST_2 REKEY;

Table altered.

U1> ALTER TABLE CUST_2 REKEY USING '3DES168';

Table altered.

Creating a table with an encrypted column using 3DES168 algorithm

create table CUST_3 (
    cust_id VARCHAR2(4),
    cust_name VARCHAR2(10),
    cred_card_num NUMBER(16) ENCRYPT USING '3DES168'
    );

Adding and modifying encrypted columns

ALTER TABLE CUST_3 ADD (cust_addr VARCHAR2(15) ENCRYPT);

ALTER TABLE CUST_3 modify (cust_name ENCRYPT);

Disable Column Encryption

ALTER TABLE CUST_3 MODIFY (cust_name DECRYPT);