Oracle 12c has increased the security of the RDBMS by increasing the number of user accounts for administrative tasks and reducing the overall permissions these users have in the RDBMS.   This increases security by limiting the amount of control a single user account has over your data.   This article outlines the general changes to administrative account privileges in Oracle 12c.

1. New roles were created to increase security through separation of duties and the principle of least privilege. SYSDACKUP, SYSDG, SYSKM.   This new roles are used to eliminate the need to grant SYSDBA to some users.
   1. SYSBACKUP – Allows for connection to DB through RMAN for all backup and recovery operation.
   2. SYSDG – Allows for Data Guard operations and can be used either through Data Guard Broker or the DGMGRL command line. To connect with a password, you must create a password file for this user.
   3. SYSKM – Allows for management of Transparent Data Encryption wallet operations. To connect with a password, you must create a password file for this user.
2. New privilege PURGE DBA_RECYCLEBIN has been created to execute PURGE DBA_RECYCLEBIN command without requiring the SYSDBA privilege.
3. SELCT ANY DICTIONARY privilege no longer permits access to tables:
   1. DEFAULT_PWD$
   2. ENC$
   3. LINK$
   4. USER$
   5. USER_HISTORY$
   6. XS$VERIFIERS
4. UNLIMITED TABLESPACE privilege no longer included in RESOURCE role.

Larry Catt

OCP

Sizing of the redo logs has a large impact on database performance because the redo log size has a heavy impact on the behavior of DBW and ARCH processes.  Generally, large redo logs redo provide better performance by decreasing the amount of check points.   However, the parameter FAST_START_MTTR_TARGET is used to limit the amount of time required to recover the database and more frequent checkpoints make for a shorter recovery time, thus it is a balance between recovery time and performance.

V$INSTANCE_RECOVERY view column OPTIMAL_LOGFILE_SIZE shows the optimal size of the redo log file.  This info is also available in Redo Log Group page of OEM.  As a general rule, redo logs should switch once every 20 minutes.  NOTE:  the initialization parameter FAST_START_MTTR_TARGET must be set to have this column populated.

SQL> select optimal_logfile_size from v$instance_recovery;

OPTIMAL_LOGFILE_SIZE

——————–

               41425

SQL>

Larry Catt

OCP

You can now make columns within a table invisible and general access will only display visible tables.  The following queries will not display invisible columns:

1. Select * from
2. Describe command
3. %ROWTYPE declaration in PL/SQL
4. Describe on OCI

Invisible columns will be displayed if they are explicitly called by column name and you can insert data by explicitly naming the column, but if insert does not list column names you cannot insert into invisible columns.  Columns can be made invisible during table creation, adding columns, or modifying them.  Virtual columns can be invisible also.  You can use an invisible column as a partition key.

Invisible columns have the following restrictions.

1. External, cluster, and temporary tables cannot have invisible columns.
2. Attributes of user-defined types cannot be invisible.

Example of Column invisibility using SCOTT.EMP table.

1. Logon to SQLPLUS as the user SCOTT

[oracle@linux2 ~]$ sqlplus scott/password

SQL*Plus: Release 12.1.0.2.0 Production on Thu Dec 29 14:14:06 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Last Successful login time: Thu Dec 29 2016 13:47:09 -05:00

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production

With the Partitioning, OLAP, Advanced Analytics, Real Application Testing

and Unified Auditing options

SQL>

2. select table_name, column_name, hidden_column from USER_TAB_COLS.

SQL>  column table_name format a10

SQL> column column_name  format a10

SQL> select table_name, column_name, hidden_column from user_tab_cols where table_name=’EMP’;

TABLE_NAME COLUMN_NAM HID

———- ———- —

EMP        EMPNO      NO

EMP        ENAME      NO

EMP        JOB        NO

EMP        MGR        NO

EMP        HIREDATE   NO

EMP        SAL        NO

EMP        COMM       NO

EMP        DEPTNO     NO

8 rows selected.

SQL>

NOTE:  None of the columns are hidden.

3. Now select the first three rows from table SCOTT.EMP.

SQL> set linesize 200

SQL>  select * from scott.emp where rownum<4;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO

———- ———- ——— ———- ——— ———- ———- ———-

      7369 SMITH      CLERK           7902 17-DEC-80        800                    20

      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30

      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30

SQL>

4. Now alter the table SCOTT.EMP and make column HIRDATE, SAL, COMM invisible.

alter table scott.emp modify hiredate invisible;

alter table scott.emp modify sal invisible;

alter table scott.emp modify comm invisible;

SQL> alter table scott.emp modify hiredate invisible;

alter table scott.emp modify sal invisible;

alter table scott.emp modify comm invisible;

Table altered.

SQL>

Table altered.

SQL>

Table altered.

SQL>

5. Select the first 3 rows of table SCOTT.EMP.

SQL>  select * from scott.emp where rownum<4;

     EMPNO ENAME      JOB              MGR     DEPTNO

———- ———- ——— ———- ———-

      7369 SMITH      CLERK           7902         20

      7499 ALLEN      SALESMAN        7698         30

      7521 WARD       SALESMAN        7698         30

SQL>

NOTE:  You no longer see the columns HIREDATE, SAL, or COMM

6. Now desc the table SCOTT.EMP.

SQL> desc scott.emp;

 Name                                Null?                        Type

 ————                           ——–                   ——————————————-

 EMPNO                             NOT NULL             NUMBER(4)

 ENAME                                                          VARCHAR2(10)

 JOB                                                                 VARCHAR2(9)

 MGR                                                               NUMBER(4)

 DEPTNO                                                         NUMBER(2)

SQL>

NOTE:  You no longer see the columns HIREDATE, SAL, or COMM

7. Now select the first 3 values but explicitly request the columns, NOTE they are still there but need to be explicitly queried.

SQL> select EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO from scott.emp where rownum<4;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO

———- ———- ——— ———- ——— ———- ———- ———-

      7369 SMITH      CLERK           7902 17-DEC-80        800                    20

      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30

      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30

SQL>

8. Additionally the hidden_columns value in user_tab_cols will also be updated accordingly.

SQL> column table_name format a10

column column_name  format a10

select table_name, column_name, hidden_column from user_tab_cols where table_name=’EMP’;

SQL> SQL>

TABLE_NAME COLUMN_NAM HID

———- ———- —

EMP        EMPNO      NO

EMP        ENAME      NO

EMP        JOB        NO

EMP        MGR        NO

EMP        HIREDATE   YES

EMP        SAL        YES

EMP        COMM       YES

EMP        DEPTNO     NO

8 rows selected.

SQL>

9. This completes using invisible columns in Oracle 12c.

Larry Catt

OCP

SecureFile LOBs were added to Oracle 11g to provide better performance over the legacy LOB format of BasicFile LOBs.

New features of SecureFile LOBs:

1. Intelligent LOB compression allows user to compress file.
2. Intelligent LOB encryption in place and allows for random reads and writes.
3. Deduplication option allows for duplicate data to only be stored once.
4. LOB data path optimization allows for logical cache above storage level, read prefetching, new caching modes, vectored IO

In Oracle 12c the initialization parameter of DB_SECUREFILE determines use of LOB type with the following options:

ALWAYS – Attempts to use SecureFile LOBS but uses BasicFile LOB if ASSM is not in use.

PERMITTED – Allows SecureFile LOBs to be created.

PREFERRED – All LOBs are SecureFile unless BasicFile is specified.  When PREFERRED is sent inherited                                type from partition and columns are ignored.

NEVER – Disallows use of SecureFile LOBs, attempts to create SecureFile LOBs will be created as BasicFile.

IGNORE – SECUREFILE keyword and all options are ignored.

Online redefinition can be performed at table/partition level and does not require the table or partition to be taken offline. However redefinition will require storage equal to double current LOB and global indexes will have to be rebuilt.  Redefined SecureFiles inherit LOB column settings for deduplication, encryption, and compression made at original BasicFile LOB.

DBMS_LOB package has several options for redefine:

DBMS_LOB.GETOPTIONS – give current settings of SecureFile LOB.

DBMS_LOB.SETOPTIONS – allows to change current settings.

DBMS_LOB.IISECUREFILE – Determines if it is SECUREFILE LOB or not.

DBMS_SPECE.SPACE_USAGE  – Procedure returns amount of space used in blocks by all LOBs and can only be used if auto segment space management is in use.

Larry Catt

OCP

This procedure outlines the steps of performing real-time database operation monitoring via the sqlplus console using the package DBMS_SQL_MONITOR.

1. Start SQLPLUS and connect as a user with the appropriate privileges.

[oracle@linux2 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Tue Jul 20 08:00:16 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production

With the Partitioning, OLAP, Advanced Analytics, Real Application Testing

and Unified Auditing options

SQL>

2. Create user test and table test_tab, than insert 10 miliion records into test.test_tab for monitoring test.

create user test identified by test

default tablespace users quota unlimited on users;

grant dba to test;

create table test.test_tab(col_a number,

col_b varchar2(10));

Jullare

val_a number:=1;

val_b varchar2(10):=’a’;

begin

while val_a<20000001

loop

insert into test.test_tab(col_a, col_b)

values(val_a, val_b);

val_a:=val_a+1;

if val_b=’z’

then

val_b:=’a’;

else

val_b:=chr(ascii(val_b) + 1);

end if;

end loop;

commit;

end;

/

Execution:

SQL> create user test identified by test

default tablespace users quota unlimited on users;

  2

User created.

SQL> grant dba to test;

Grant succeeded.

SQL> create table test.test_tab(col_a number,

col_b varchar2(10));

  2

Table created.

SQL> Jullare

val_a number:=1;

  2    3  val_b varchar2(10):=’a’;

  4  begin

  5  while val_a<20000001

  6  loop

  7  insert into test.test_tab(col_a, col_b)

  8  values(val_a, val_b);

  9  val_a:=val_a+1;

 10  if val_b=’z’

 11  then

 12  val_b:=’a’;

 13  else

 14  val_b:=chr(ascii(val_b) + 1);

 15  end if;

 16  end loop;

 17  commit;

 18  end;

 19  /

PL/SQL procedure successfully completed.

SQL>

3. Define a variable to hold the execution ID.

var opid NUMBER

4. Begin the database operation.

EXEC :opid := DBMS_SQL_MONITOR.BEGIN_OPERATION(‘op_test’);

5. Run the queries in the operation.

SELECT col_a FROM test.test_tab order by col_a;

SELECT col_b FROM test.test_tab order by col_a;

6. End the database operation.

EXEC DBMS_SQL_MONITOR.END_OPERATION(‘op_test’, :opid);

7. Confirm that the database operation completed.

SQL> SELECT dbop_name, status FROM v$sql_monitor WHERE dbop_name = ‘op_test’;

DBOP_NAME                      STATUS

—————————— ——————-

rtsm_op                        DONE

SQL>

8. This completes manual execution of real_time database operation monitoring with DBMS_SQL_MONITOR.

Larry Catt

OCP