Extended Data Types Oracle 12c

Till Oracle 11g character data types like varchar2, nvarchar2 supports only 4000 bytes where as RAW supports only 2000 bytes. From Oracle 12c, Oracle has extended these datatypes length to 32Kbytes (32767 bytes). For the extended datatype support we need to configure it.

Enabling the extended datatype support on non-cdb Oracle 12c database.

Connect to non cdb database. In my case orcl is non cdb database.

. oraenv
sqlplus / as sysdba

Shutdown database and startup on upgrade mode.

shutdown immediate
startup upgrade

Modify system parameter max_string_size to extended.

alter system set max_string_size=extended;

Execute the sql file that is located under rdbms/admin directory.

@?/rdbms/admin/utl32k.sql

After completing the execution of above sql command startup database in normal mode.

shutdown immediate
startup

Enabling the extended datatype support on cdb Oracle 12c database. In my case cdb1 is cdb database with 3 pluggable database: pdb1, pdb2, pdb3.

On root container database execute the following commands

. oraenv
sqlplus / as sysdba

Modify system parameter max_string_size to extended.

alter system set max_string_size=extended scope=spfile;

Shutdown database and startup on upgrade mode.

shutdown immediate
startup upgrade

Open all pluggable database on upgrade mode.

alter pluggable database all open upgrade;

Execute utl32k.sql for all the pdbs.

Change directory to script file directory i.e. ORACLE_HOME then rdbms/admin. We are using catcon.pl, it can run sql in the root and in specified PDBs in the correct order, and it generates log files that you can view to confirm that the SQL script or SQL statement did not generate unexpected errors. It also starts multiple processes and assigns new scripts to them as they finish running scripts previously assigned to them. 

cd $ORACLE_HOME/rdbms/admin
../../perl/bin/perl catcon.pl -d $ORACLE_HOME/rdbms/admin -b /tmp/utl32k.out utl32k.sql

Login to the cdb, shutdown the database and start database in the normal mode.

sqlplus / as sysdba
shutdown immediate
startup;
alter pluggable database all open;

Using extended datatypes

Create sequence seq_id;

Create table that use extended datatype.

Create table tbl_memo (
id number default seq_id.nextval,
notes varchar2(32767),
enabled char default 'Y',
primary key(id));

Creating table on non cdb database.

Creating table on cdb root database

It is noticed that we cannot enable extended datatype at cdb root database. Even I have executed "@?/rdbms/admin/utl32k.sql" at cdb root database but there is no luck. Following error has encountered.

ORA-00910: specified length too long for its datatype

Whereas on the pdb database, we can create tables with extended datatypes.

Read More

Oracle 12c Monitor Privileges Usage

Over the course of time, database user get more privilege than actually required. This could be security loophole which may result in unauthorised database access. More over excessive privileges violate the basic security principle of least privilege.
In Oracle 12c we have new database package, DBMS_PRIVLEGE_CAPTURE helps us on capture and analyse the privileges usage. On basic of analysis we can determine the privileges that can be revoked for normal operations. This package will generate information of privileges usage between time the capture is enabled and the capture is disabled.

CAPTURE_ADMIN role should be granted to the user for executing package DBMS_PRIVLEGE_CAPTURE.

grant capture_admin to monitoring;

Following are the procedures available on the package.

• create_capture: Creates a policy that specifies the conditions for analyzing privilege use.
• enable_capture: Starts the recording of privilege analysis for a specified privilege analysis policy.
• disable_capture: Stops the recording of privilege use for a specified privilege analysis policy
• generate_result: Populates the privilege analysis data dictionary views with data
• drop_capture: Removes a privilege analysis policy together with the data recorded.

Create_capture procedure creates the policy to analyse privilege usage. Privileges can be
analysed on following level.

G_DATABASE : Analyse the privilege usage on database level for all the users except SYS
G_ROLE : Analyse the privilege usage for specific role(s). ROLE_NAME_LIST need to be used for the roles list.
G_CONTEXT : Analyse the privilege whenever the condition specified get matched and returns TRUE.
G_ROLE_AND_CONTEXT : Analyse the privileges whenever the role and the condition get matched and returns TRUE.

Create capture at database level.

begin
DBMS_PRIVILEGE_CAPTURE.create_capture(
name => 'db_capture',
type => DBMS_PRIVILEGE_CAPTURE.g_database
);
end;
/

Create capture at role level.

begin
DBMS_PRIVILEGE_CAPTURE.create_capture(
name => 'rl_capture',
type=> DBMS_PRIVILEGE_CAPTURE.g_role,
roles => role_name_list('CONNECT','RL_DILLI')
);
end;
/

Create capture at context level.

begin
DBMS_PRIVILEGE_CAPTURE.create_capture(
name => 'cont_capture',
type => DBMS_PRIVILEGE_CAPTURE.g_context,
condition => 'SYS_CONTEXT(''USERENV'',''SESSION_USER'') = ''DILLI'''
);
end;
/

Create capture on role and context combined.

begin
DBMS_PRIVILEGE_CAPTURE.create_capture(
name => 'rl_cont_capture',
type => DBMS_PRIVILEGE_CAPTURE.g_role_and_context,
roles => role_name_list('RL_DILLI'),
condition => 'SYS_CONTEXT(''USERENV'',''SESSION_USER'') IN (''SCOTT'',''DILLI'')'
);
end;
/

View captured lists.

We can view the list of the capture and its details using DBA_PRIV_CAPTURES data dictionary views.
desc DBA_PRIV_CAPTURES;

set linesize 200
col name for a16
col roles for a20
col context for a100

select name,type,enabled,roles,context from dba_priv_captures;

Enable the captures created earlier.

ENABLE_CAPTURE procedure is used to enable capture. Basically it will start capturing. Typically only one capture can be enabled at one time. Exception is that we can enable one database  level capture and another none database level at same time.

begin
DBMS_PRIVILEGE_CAPTURE.enable_capture('cont_capture');
DBMS_PRIVILEGE_CAPTURE.enable_capture('rl_cont_capture');
end;
/

An error will be encountered if we try to enable both none db level capture at once. But first one among the two will be enabled.

We can enable one db level capture and another none db level capture at once.

begin
DBMS_PRIVILEGE_CAPTURE.enable_capture('db_capture');
DBMS_PRIVILEGE_CAPTURE.enable_capture('rl_cont_capture');
end;
/

Disable capture is used to disable capture. Basically it stop capture of the policy specified.

begin
DBMS_PRIVILEGE_CAPTURE.disable_capture('db_capture');
DBMS_PRIVILEGE_CAPTURE.disable_capture('rl_cont_capture');
end;
/

Finally we need to use GENERATE_RESULTS procedure to generate report on basic of the
capture.

begin
DBMS_PRIVILEGE_CAPTURE.generate_result('rl_cont_capture');
DBMS_PRIVILEGE_CAPTURE.generate_result('db_capture');
end;
/

Following data dictionary views are available to get the details on the privilege capture result.

• DBA_PRIV_CAPTURES
• DBA_USED_OBJPRIVS
• DBA_USED_OBJPRIVS_PATH
• DBA_USED_PRIVS
• DBA_USED_PUBPRIVS
• DBA_USED_SYSPRIVS
• DBA_USED_SYSPRIVS_PATH
• DBA_USED_USERPRIVS
• DBA_USED_USERPRIVS_PATH
• DBA_UNUSED_OBJPRIVS
• DBA_UNUSED_OBJPRIVS_PATH
• DBA_UNUSED_PRIVS
• DBA_UNUSED_SYSPRIVS
• DBA_UNUSED_SYSPRIVS_PATH
• DBA_UNUSED_USERPRIVS
• DBA_UNUSED_USERPRIVS_PATH

Accessing dba_used_privs to view all the privileges used during enable and disable capture.

col capture for a16

col USED_ROLE for a30

col SYS_PRIV for a30

set linesize 200

col username for a30

select capture,username,used_role,sys_priv from dba_used_privs;

Accessing dba_unused_privs to view all the privileges not used during enable and disable capture.

col capture for a16
col rolename for a30
col SYS_PRIV for a30
set linesize 200
col username for a30

select capture,username,rolename,sys_priv from dba_unused_privs;

On basic on above data we can revoke unnecessary privileges granted to the user SCOTT.

DROP_CAPTURE is used to drop all the captures created.

begin
DBMS_PRIVILEGE_CAPTURE.drop_capture(name => 'db_capture');
DBMS_PRIVILEGE_CAPTURE.drop_capture(name => 'rl_capture');
DBMS_PRIVILEGE_CAPTURE.drop_capture(name => 'cont_capture');
DBMS_PRIVILEGE_CAPTURE.drop_capture(name => 'rl_cont_capture');
end;
/

Read More

Oracle 12c new feature - Data Redaction

Introduction

Data Redaction is new security features on Oracle 12c that will help us hiding the data from unauthorised user. It has a lot of different options and control over the Oracle masking features that was introduced with oracle 10g. DBMS_REDACT is the package that is used for data redaction and has greater level of control and protection for the data.

Before moving ahead with data redaction, Grant execute object level privilege on package dbms_redact to the user.

sqlplus / as sysdba
grant execute on dbms_redact to scott;

Connect as user scott and describe table emp

conn scott

desc EMP;

Execute select command to display empno and sal column.

select empno,sal from emp;

Applying redaction policy without Filtering. i.e expression => '1=1'. It means this redaction policy will be applied to all the users and roles.

begin

dbms_redact.add_policy(

object_schema => 'SCOTT', 

object_name => 'EMP', 

column_name => 'SAL',

policy_name => 'SCOTT_EMP_REDACT',

function_type => DBMS_REDACT.full,

expression => '1=1');

end;

/

Once again select EMPNO and SAL column from emp table and verify the change. All data on SAL column will be displayed as 0.

We can view the default redact value for the numeric data and other data type. Use SQL command below to view.

desc redaction_values_for_type_full;

select NUMBER_VALUE from redaction_values_for_type_full;

We can update the default redact value with the following command. In this case we have replaced default numeric reduction value from 0 to 6.

EXEC DBMS_REDACT.UPDATE_FULL_REDACTION_VALUES (number_val => 6);

Restart the database instance to get the change into effect.

We can use following data dictionaries to view the details of redaction.

desc redaction_policies

desc redaction_columns

set linesize 250

col OBJECT_OWNER for a30

col OBJECT_NAME for a30

col POLICY_NAME format a32

col EXPRESSION format a20

col POLICY_DESCRIPTION for a20

select * from redaction_policies;

col OBJECT_OWNER for a20

col OBJECT_NAME for a32

col COLUMN_NAME for a30

col FUNCTION_PARAMETERS for a20

col REGEXP_PATTERN for a20

col REGEXP_REPLACE_STRING for a20

col REGEXP_POSITION for a20

col REGEXP_OCCURRENCE for a25

col REGEXP_MATCH_PARAMETER for a20

col COLUMN_DESCRIPTION for a10

select * from redaction_columns;

Modify an Existing policy.

We need to use ALTER_POLICY procedure to modify existing redaction policy. Instead of 

displaying salary as 6 we can hide some digits from the whole column. As per modification below SAL column value will be displayed as 9 for 2 digits from left. If someone's salary is 2500 then it will be displayed as 9900. The first 2 digits will be converted to 9.

begin

dbms_redact.alter_policy(

object_schema => 'SCOTT',

object_name => 'EMP',

policy_name => 'SCOTT_EMP_REDACT',

action => DBMS_REDACT.modify_column,

column_name => 'SAL',

function_type => DBMS_REDACT.partial,

function_parameters => '9,1,2'

);

end;

/

Execute following command to verify the changes.

select empno,sal from emp;

We can add additional column to protect additional columns. We are adding hiredate column it will display month as JAN and day as 01 and year as 01(RR value)

begin

dbms_redact.alter_policy(

object_schema => 'SCOTT',

object_name => 'EMP',

policy_name => 'SCOTT_EMP_REDACT',

action => DBMS_REDACT.add_column,

column_name => 'hiredate',

function_type => DBMS_REDACT.partial,

function_parameters => 'm1d1y01'

);

end;

/

Select columns to verify the change

select empno, sal,hiredate from emp;

We can use expression to control redaction policy to certain user or roles.

begin

dbms_redact.alter_policy(

object_schema => 'SCOTT',

object_name => 'EMP',

policy_name => 'SCOTT_EMP_REDACT',

action => DBMS_REDACT.add_column,

column_name => 'JOB',

expression => 'SYS_CONTEXT(''USERENV'',''SESSION_USER'') != ''SCOTT'''

);

end;

/

Execute following SQL statement as user SCOTT.

select empno, sal,hiredate,job  from emp;

Execute same SQL statement as different user. In case below we executed following statement as user DILLI.

select empno, sal,hiredate,job  from scott.emp;

Drop column from the redaction policy. 

begin

dbms_redact.alter_policy(

object_schema => 'SCOTT',

object_name => 'EMP',

policy_name => 'SCOTT_EMP_REDACT',

action => DBMS_REDACT.drop_column,

column_name => 'JOB'

);

end;

/

Drop redaction policy using drop_policy procedure.

begin

dbms_redact.drop_policy(

object_schema => 'SCOTT',

object_name => 'EMP',

policy_name => 'SCOTT_EMP_REDACT');

end;

/

Read More