Oracle 12c New Features - Change in DDL log location

Unlike Oracle 11g the DDL statement is not logged to alert log file in Oracle 12c. The ddl statement is logged into another directory log/ddl in diagnostic_dest location.

 Enable DDL logging in Oracle 11g 

In Oracle 11g the ENABLE_DDL_LOGGING parameter logs the DDL statements into the database instance alert log.

Let us test it.

Set the ENABLE_DDL_LOGGING parameter to TRUE.

SQL> SHOW PARAMETER ENABLE_DDL_LOGGING

NAME                                 TYPE        VALUE
------------------------------------ ----------- -----------
enable_ddl_logging                   boolean     FALSE

SQL> ALTER SYSTEM SET ENABLE_DDL_LOGGING=TRUE SCOPE=MEMORY;

System altered.

SQL> SHOW PARAMETER ENABLE_DDL_LOGGING

NAME                                 TYPE        VALUE
------------------------------------ ----------- -----------
enable_ddl_logging                   boolean     TRUE

Let us execute some DDL statements here

SQL>
SQL> CONN test@ddb
Enter password:
Connected.
SQL> CREATE TABLE T1(eid NUMBER,ENAME VARCHAR2(30));

Table created.

SQL> ALTER TABLE T1 DROP COLUMN ENAME;

Table altered.

SQL>

The alert log shows the record of DDL statments executed in database.

-- Snippet of alert log --
...
....
..
Mon Jan 12 19:23:50 2015
ALTER SYSTEM SET enable_ddl_logging=TRUE SCOPE=MEMORY;
Mon Jan 12 19:24:54 2015
CREATE TABLE T1(eid NUMBER,ENAME VARCHAR2(30))
Mon Jan 12 19:25:13 2015
ALTER TABLE T1 DROP COLUMN ENAME
....
..

---

 Enable DDL logging in Oracle 12c 

There is a change in Oracle 12c how DDL statement is recorded when ddl logging is enabled.The DDL statement is recorded in DDL log (not in alert log)


SQL> show parameter enable_ddl_logging

NAME     TYPE VALUE
------------------------------------ ----------- ------------------------------
enable_ddl_logging     boolean FALSE

SQL> ALTER SYSTEM SET enable_ddl_logging=TRUE SCOPE=MEMORY;

System altered.

SQL> show parameter enable_ddl_logging

NAME     TYPE VALUE
------------------------------------ ----------- ------------------------------
enable_ddl_logging     boolean TRUE

SQL> DROP TABLE T1 PURGE;

Table dropped.

SQL> CREATE TABLE T1(eid NUMBER, ename VARCHAR2(30)) TABLESPACE users;

Table created.

SQL> ALTER TABLE T1 DROP COLUMN ename;

Table altered.

SQL>

The DDL statement in recorded in DIAG_DEST/rdbms/<db name>/<instance_name>/ddl

[oracle@norac ddl]$ pwd
/u01/app/oracle/diag/rdbms/db12c/DB12C/log/ddl
[oracle@norac ddl]$ cat log.xml
<msg time='2015-01-12T23:41:22.286+05:30' org_id='oracle' comp_id='rdbms'
 msg_id='opiexe:4383:2946163730' type='UNKNOWN' group='diag_adl'
 level='16' host_id='norac.localdomain' host_addr='192.168.56.101'
 version='1'>
 <txt>DROP TABLE T1 PURGE
 </txt>
</msg>
<msg time='2015-01-12T23:43:11.838+05:30' org_id='oracle' comp_id='rdbms'
 msg_id='opiexe:4383:2946163730' type='UNKNOWN' group='diag_adl'
 level='16' host_id='norac.localdomain' host_addr='192.168.56.101'>
 <txt>CREATE TABLE T1(eid NUMBER, ename VARCHAR2(30)) TABLESPACE users
 </txt>
</msg>
<msg time='2015-01-12T23:43:45.599+05:30' org_id='oracle' comp_id='rdbms'
 msg_id='opiexe:4383:2946163730' type='UNKNOWN' group='diag_adl'
 level='16' host_id='norac.localdomain' host_addr='192.168.56.101'>
 <txt>ALTER TABLE T1 DROP COLUMN ename
 </txt>
</msg>
[oracle@norac ddl]$

- 0 -

Oracle 12c New Features - Invisible Columns

Oracle 12c allows us to make a column invisible.
The invisible column is not allowed for the below table types

External tables
Cluster tables
Temporary tables

Let us check how to make a column invisible.

SQL> CREATE TABLE t1(eid NUMBER, ename VARCHAR2(30)) TABLESPACE users;

Table created.

SQL> DESC t1
 Name      Null?    Type
 -------------------------------- -------- ----------------------
 EID                 NUMBER
 ENAME         VARCHAR2(30)

SQL> SELECT table_name,column_id,column_name FROM user_tab_columns WHERE table_name='T1' order by column_id;

TABLE_NAME       COLUMN_ID COLUMN_NAME
--------------------   ----------         --------------------
T1                                         1           EID
T1                                         2           ENAME

SQL> 

SQL> INSERT INTO t1 VALUES(100,'Test Employee 1');

1 row created.

SQL> COMMIT;

Commit complete.

SQL> SELECT * FROM t1;

       EID ENAME
---------- ------------------------------
       100 Test Employee 1

-- > Set the column invisible

SQL> ALTER TABLE t1 MODIFY (ename INVISIBLE);

Table altered.

SQL> DESC t1
 Name      Null?    Type
 -------------------------------- -------- ----------------------
 EID                     NUMBER

-- > Setting the sqlplus to show invisible column in DESCRIBE output (set colinvisible off | on)

SQL> set colinvisible on 
SQL> DESC t1
 Name      Null?                   Type
 -------------------------------- -------- ----------------------
 EID                                    NUMBER
 ENAME (INVISIBLE)      VARCHAR2(30)


-- > The column_id in user_tab_columns does not show any value after making column invisible

SQL> SELECT table_name,column_id,column_name FROM user_tab_columns WHERE table_name='T1' order by column_id;

TABLE_NAME   COLUMN_ID COLUMN_NAME
------------------------------ ---------- ------------------------------
T1                                            1   EID
T1                                                 ENAME

-- > The ename column is not visible to the "SELECT *" query.

SQL> SELECT * FROM t1;

       EID
----------
       100

-- > Still the ename column shows value by explicitly specifying the ename column in query 

SQL> select ename from t1;

ENAME
------------------------------
Test Employee 1
Test Employee 2

-- > The below error shows that ename column is not avilable to insert statement


SQL> INSERT INTO t1 VALUES(200,'Test Employee 2');
INSERT INTO t1 VALUES(100,'Test Employee 2')
            *
ERROR at line 1:
ORA-00913: too many values

-- > Let us make the column visible as below

SQL> ALTER TABLE t1 MODIFY (ename VISIBLE);

Table altered.

SQL> DESC t1
 Name      Null?    Type
 -------------------------------- -------- ----------------------
 EID                    NUMBER
 ENAME            VARCHAR2(30)


SQL> INSERT INTO t1 VALUES(200,'Test Employee 2');

1 row created.

SQL> COMMIT;

Commit complete.

SQL> SELECT * FROM t1;

       EID ENAME
---------- ------------------------------
       100 Test Employee 1
       200 Test Employee 2

SQL> 

-- > The column_id in user_tab_columns shows value as 2 after making column visible

SQL> SELECT table_name,column_id,column_name FROM user_tab_columns WHERE table_name='T1' order by column_id;

TABLE_NAME       COLUMN_ID COLUMN_NAME
-------------------- ---------- --------------------
T1                                       1           EID
T1                                       2           ENAME

Oracle 12c New Features - Renaming and Relocating Online Data Files

Oracle 12c new feature allows us to rename,relocate,copy datafile online with asm or non-asm type of storage.

The below test shows that datafile can be renamed online without taking the datafile offline.

SQL> SELECT name,open_mode FROM v$database;

NAME  OPEN_MODE
--------- --------------------
DB12C  READ WRITE

SQL> SELECT file_id,file_name,tablespace_name,status FROM dba_data_files WHERE tablespace_name='USERS';

   FILE_ID FILE_NAME       TABLESPACE_NAME    STATUS
---------- ----------------------------------- -------------------- ---------
6 +DATA/DB12C/users01.dbf       USERS    AVAILABLE

SQL> ALTER DATABASE MOVE DATAFILE '+DATA/DB12C/users01.dbf' TO '+DATA/DB12C/users02.dbf';

Database altered.

SQL> SELECT file_id,file_name,tablespace_name,status FROM dba_data_files WHERE tablespace_name='USERS';

   FILE_ID FILE_NAME       TABLESPACE_NAME    STATUS
---------- ----------------------------------- -------------------- ---------
6 +DATA/DB12C/users02.dbf       USERS    AVAILABLE

Oracle : Data Security - Hide data from unauthorized users using dbms_rls

Hide Data in Oracle using DBMS_RLS

The DBMS_RLS package is helpful in masking data in oracle.

Let’s see the below steps to test a simple data masking functionality using dbms_rls.

Setting up access policy

1) Create two users. The data_owner user is the owner of the table. The data_user is used for reading the tables from data_owner user.

conn / as sysdba

CREATE USER data_owner IDENTIFIED BY data_owner;

GRANT CONNECT,RESOURCE TO data_owner;

GRANT EXECUTE ON dbms_rls TO data_owner; 

CREATE USER data_user IDENTIFIED BY data_user;

GRANT CONNECT,RESOURCE TO data_user;

2) Create a table in the data owner.

CONN data_owner/data_owner@tdb

CREATE TABLE t_employee (empid NUMBER PRIMARY KEY, ename VARCHAR2(30),location VARCHAR2(30),salary NUMBER);

INSERT INTO t_employee VALUES (1001,'Alex','NY','2000');
INSERT INTO t_employee VALUES (1002,'Scott','NY','5000');
INSERT INTO t_employee VALUES (1003,'Tim','NJ','10000');
INSERT INTO t_employee VALUES (1004,'Ryan','NJ','2000');
INSERT INTO t_employee VALUES (1005,'Anderson','NJ','15000');

commit;

3) Grant select privilege to data_user

CONN data_owner/data_owner@tdb

GRANT SELECT ON t_employee to data_user;

4) Create a policy function for column level masking. The other users except data_owner shouldn't see the SALARY column.

CONN data_owner/data_owner@tdb

CREATE OR REPLACE FUNCTION fn_colmask(powner IN VARCHAR2, pjname IN VARCHAR2)
RETURN VARCHAR2
IS
   v_pred VARCHAR2(30);
BEGIN

IF USER<>'DATA_OWNER' THEN
   v_pred:= '1=2';
ELSE
   v_pred:= '1=1';
END IF;

RETURN v_pred;

END;
/

5) Add the policy for column masking

CONN data_owner/data_owner@tdb

BEGIN

DBMS_RLS.ADD_POLICY (
   object_schema=>'DATA_OWNER',
   object_name=>'T_EMPLOYEE',
   policy_name=>'T_EMPLOYEE_COL_POLICY',
   function_schema=>'DATA_OWNER',
   policy_function=>'FN_COLMASK',
   sec_relevant_cols=>'SALARY',
   sec_relevant_cols_opt=>dbms_rls.ALL_ROWS);

END;
/

6) The data_owner is able to see all the data

CONN data_owner/data_owner@tdb

select * from data_owner.t_employee;

     EMPID ENAME           LOCATION       SALARY
---------- --------------- ---------- ----------
      1001 Alex            NY               2000
      1002 Scott           NY               5000
      1003 Tim             NJ              10000
      1004 Ryan            NJ               2000
      1005 Anderson        NJ              15000

7) The salary column is not visible to data_user due to column masking on SALARY column.

conn data_user/data_user@tdb

select * from data_owner.t_employee;

     EMPID ENAME           LOCATION       SALARY
---------- --------------- ---------- ----------
      1001 Alex            NY              
      1002 Scott           NY              
      1003 Tim             NJ             
      1004 Ryan            NJ            
      1005 Anderson        NJ

8) Create a policy function for row masking as well.

The users except data_owner should see only rows of NY location. Not the rows of other locations.

CONN data_owner/data_owner@tdb

CREATE OR REPLACE FUNCTION fn_rowmask(powner IN VARCHAR2, pjname IN VARCHAR2)
RETURN VARCHAR2
IS
   v_pred VARCHAR2(30);
BEGIN

IF USER<>'DATA_OWNER' THEN
   v_pred:= 'LOCATION=''NY''';
ELSE
   v_pred:= '1=1';
END IF;

RETURN v_pred;

END;
/

9) Add the policy function for row level masking

CONN data_owner/data_owner@tdb

BEGIN

DBMS_RLS.ADD_POLICY (
   object_schema=>'DATA_OWNER',
   object_name=>'T_EMPLOYEE',
   policy_name=>'T_EMPLOYEE_ROW_POLICY',
   function_schema=>'DATA_OWNER',
   policy_function=>'FN_ROWMASK',
   sec_relevant_cols=>'SALARY');

END;
/

10) Now the policy has row level masking and column level masking

CONN data_owner/data_owner@tdb

select * from data_owner.t_employee;

     EMPID ENAME           LOCATION       SALARY
---------- --------------- ---------- ----------
      1001 Alex            NY               2000
      1002 Scott           NY               5000
      1003 Tim             NJ              10000
      1004 Ryan            NJ               2000
      1005 Anderson        NJ              15000

conn data_user/data_user@tdb

select * from data_owner.t_employee;

     EMPID ENAME           LOCATION       SALARY
---------- --------------- ---------- ----------
      1001 Alex            NY
      1002 Scott           NY

Exempt Access policy

SYS user is always exempted from access policy. Other users can be exempted from access policy by using exempt access policy privilege.

conn / as sysdba

grant exempt access policy to data_user;

CONN data_user/data_user@tdb

select * from data_owner.t_employee;

     EMPID ENAME           LOCATION       SALARY
---------- --------------- ---------- ----------
      1001 Alex            NY               2000
      1002 Scott           NY               5000
      1003 Tim             NJ              10000
      1004 Ryan            NJ               2000
      1005 Anderson        NJ              15000

User data_user is able to see all the data because of exemption of access policy

conn / as sysdba

revoke exempt access policy from data_user;

conn data_user/data_user@tdb

select * from data_owner.t_employee;

     EMPID ENAME           LOCATION       SALARY
---------- --------------- ---------- ----------
      1001 Alex            NY
      1002 Scott           NY

Viewing policy details

V$VPD_POLICY displays all the fine-grained security policies and predicates associated with the cursors currently in the library cache.

Dropping policy

The policy can be dropped using DBMS_RLS.DROP_POLICY procedure.

BEGIN

DBMS_RLS.DROP_POLICY (
   object_schema=>'DATA_OWNER',
   object_name=>'T_EMPLOYEE',
   policy_name=>'T_EMPLOYEE_COL_POLICY');

END;
/

DBMS_RLS.DROP_POLICY (
   object_schema=>'DATA_OWNER',
   object_name=>'T_EMPLOYEE',
   policy_name=>'T_EMPLOYEE_ROW_POLICY');

END;
/