Data Redaction using DBMS_REDACT - Oracle 19c

 Rem: This explains how to restrict others to  not view data for sensitive columns except some IPs (DBA)

Rem: DBA have exception to this rule

Rem: REDACT only hide data. The actual data remains intact

Rem: Code mentioned in this blog is only for knowledge purpose. Don't execute in production without taking prior verification

Rem: 06-December-2023

Rem: Hayat Mohammad Khan @ hayathk@hotmail.com

Note: Advance data security licenses is a must to use this feature.

STEP#1

grant execute on sys.dbms_redact to HR;

STEP#2

BEGIN

DBMS_REDACT.ADD_POLICY(

  object_schema => 'HR',

  object_name   => 'EMPLOYEES',

  column_name   => 'FULL_NAME',

  policy_name   => 'redact_EMPLOYEES_name',

  function_type => DBMS_REDACT.FULL,

  expression    => 'SYS_CONTEXT(''USERENV'',''IP_ADDRESS'') NOT IN (''192.168.1.101'',''192.168.1.102'',''192.168.1.103')'

);

END;

/

BEGIN

DBMS_REDACT.alter_policy(

  object_schema => 'HR',

  object_name   => 'EMPLOYEES',

  action    =>     dbms_redact.add_column,  

  column_name   => 'FIRST_NAME',

  policy_name   => 'redact_EMPLOYEES_name',

  function_type => DBMS_REDACT.FULL,

  expression    => 'SYS_CONTEXT(''USERENV'',''IP_ADDRESS'') NOT IN (''192.168.1.101'',''192.168.1.102'',''192.168.1.103')'

);

END;

/

BEGIN

DBMS_REDACT.alter_policy(

  object_schema => 'HR',

  object_name   => 'EMPLOYEES',

  column_name   => 'LAST_NAM',

  policy_name   => 'redact_EMPLOYEES_name',

  function_type => DBMS_REDACT.FULL,

  expression    => 'SYS_CONTEXT(''USERENV'',''IP_ADDRESS'') NOT IN (''192.168.1.101'',''192.168.1.102'',''192.168.1.103')'

);

END;

/

If policy needs to be disable / Drop

BEGIN

  DBMS_REDACT.DISABLE_POLICY

  (

      object_schema    =>  'HR',

      object_name      =>  'EMPLOYEES',

      policy_name      =>  'redact_EMPLOYEES_name'

);   

END;

/

BEGIN

   DBMS_REDACT.DROP_POLICY 

(

      object_schema    =>  'HR',

      object_name      =>  'EMPLOYEES',

      policy_name      =>  'redact_EMPLOYEES_name'

);

END;

/

Example 2: DBA and HR user can view, other will see #### in Full_Name Column

BEGIN

  DBMS_REDACT.alter_policy (

    object_schema       => 'HR',

    object_name         => 'EMPLOYEES',

    policy_name         => 'redact_TEST_NAME',

    action              => DBMS_REDACT.modify_expression,

    column_name         => 'FULL_NAME',

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

  );

/

Options available:

--   function_type => dbms_redact.full,

--   function_type => dbms_redact.partial,

--   function_parameters => DBMS_REDACT.REDACT_CCN16_F12,

Useful Views:

select * from REDACTION_POLICIES;

select * from REDACTION_COLUMNS;

SET LINESIZE 250

COLUMN char_value FORMAT A10

COLUMN varchar_value FORMAT A10

COLUMN nchar_value FORMAT A10

COLUMN nvarchar_value FORMAT A10

COLUMN timestamp_value FORMAT A27

COLUMN timestamp_with_time_zone_value FORMAT A32

COLUMN blob_value FORMAT A20

COLUMN clob_value FORMAT A10

COLUMN nclob_value FORMAT A10

SELECT *  FROM   redaction_values_for_type_full;

Credit Given to below URL: Knowledge of this blog is taken from below websites. Thanks to them for sharing knowledge

https://oracle-base.com/articles/12c/data-redaction-12cr1

https://www.morganslibrary.org/reference/sys_context.html

https://docs.oracle.com/en/database/oracle/oracle-database/12.2/asoag/configuring-oracle-data-redaction-policies.html#GUID-4ACBFFF5-175D-4A05-B4B4-A18C12B2E325

Email Configuration to send notification for Locked Users

 Rem: This blog is related to send notification for locked users in Oracle database.

Rem: This is test code, and cannot be used in production without proper verification.

Rem: The author take no responsibility of this code.

Rem: Dated 19th September, 2023 - Version 1

Rem: Prepared by Hayat Mohammad Khan

Rem: Contact hayathk@hotmail.com

Rem: This blog is only for knowledge sharing purpose. The concept is taken from google blogs and research community.

Below steps needs to be performed in order.

Step#1: Write procedure code to send notification email for locked users.

create or replace NONEDITIONABLE procedure send_service_account_lock_job as

l_html VARCHAR2(32000);

my_text_msg VARCHAR2(32000);

v_data_exist number(3);

BEGIN

l_html := '';

my_text_msg :='';

v_data_exist :=0;

SELECT count(*) into v_data_exist from dba_users

--where account_status = 'LOCKED' and u in ('Application_Users_Profile');

--once service account is finalized uncomment above

where account_status like '%LOCKED%' and username in ('EM','HR','SCOTT');

if v_data_exist > 0 then

for i in (select username, account_status, lock_date from dba_users where account_status like '%LOCKED%' and username in ('EM','HR','SCOTT')) loop

--for i in (select username, account_status, lock_date from dba_users where account_status like '%LOCKED%' and profile in ('Application_Users_Profile')) loop

my_text_msg := my_text_msg || ' Service Account: ' ||  i.username || ' is in ' || i.account_status || ' status since ' || to_char(i.lock_date,'DD/MON/RRRR HH24:MI:SS') || chr(13) || chr(10);

my_text_msg := my_text_msg || '  ' || chr(13) || chr(10);

end loop;

my_text_msg := my_text_msg || '  Check on urgent basis ....' || to_char(sysdate,'DD/MON/RRRR HH24:MI:SS') || chr(13) || chr(10);

my_text_msg := my_text_msg || '  From Oracle MOH DB Team ....' || chr(13) || chr(10);

send_mail( p_to        => 'to_user@abc.com',

            p_from      => 'from_user@abc.com',

            p_subject   => 'Account Lock Status DB: CRM 12c -> crmdb           Host:myservname ' ,

            p_text_msg  => my_text_msg,

            p_html_msg  => l_html,

            p_smtp_host => 'my_smtp_server_IP',

p_cc => 'cc_users_list@abc.com'

);

end if;

END;

Step#2: Send email Procedure Code.

create or replace PROCEDURE send_mail (

    p_to           IN VARCHAR2,

    p_from         IN VARCHAR2,

    p_subject      IN VARCHAR2,

    p_text_msg     IN VARCHAR2 DEFAULT NULL,

    p_html_msg     IN VARCHAR2 DEFAULT NULL,

    p_smtp_host    IN VARCHAR2,

    p_smtp_port    IN NUMBER DEFAULT 25,

    p_cc           IN VARCHAR2 DEFAULT NULL

)

AS

    l_mail_conn    UTL_SMTP.connection;

    l_boundary     VARCHAR2(50) := '----=*#abc1234321cba#*=';

BEGIN

    l_mail_conn := UTL_SMTP.open_connection(p_smtp_host, p_smtp_port);

    UTL_SMTP.helo(l_mail_conn, p_smtp_host);

    UTL_SMTP.mail(l_mail_conn, p_from);

    UTL_SMTP.rcpt(l_mail_conn, p_to);

    IF p_cc IS NOT NULL THEN

        UTL_SMTP.rcpt(l_mail_conn, p_cc);

    END IF;

    UTL_SMTP.open_data(l_mail_conn);

    UTL_SMTP.write_data(l_mail_conn, 'Date: ' || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS') || UTL_TCP.crlf);

    UTL_SMTP.write_data(l_mail_conn, 'To: ' || p_to || UTL_TCP.crlf);

    IF p_cc IS NOT NULL THEN

        UTL_SMTP.write_data(l_mail_conn, 'Cc: ' || p_cc || UTL_TCP.crlf);

    END IF;

    UTL_SMTP.write_data(l_mail_conn, 'From: ' || p_from || UTL_TCP.crlf);

    UTL_SMTP.write_data(l_mail_conn, 'Subject: ' || p_subject || UTL_TCP.crlf);

    UTL_SMTP.write_data(l_mail_conn, 'Reply-To: ' || p_from || UTL_TCP.crlf);

    UTL_SMTP.write_data(l_mail_conn, 'MIME-Version: 1.0' || UTL_TCP.crlf);

    UTL_SMTP.write_data(l_mail_conn, 'Content-Type: multipart/alternative; boundary="' || l_boundary || '"' || UTL_TCP.crlf || UTL_TCP.crlf);

    IF p_text_msg IS NOT NULL THEN

        UTL_SMTP.write_data(l_mail_conn, '--' || l_boundary || UTL_TCP.crlf);

        UTL_SMTP.write_data(l_mail_conn, 'Content-Type: text/plain; charset="iso-8859-1"' || UTL_TCP.crlf || UTL_TCP.crlf);

        UTL_SMTP.write_data(l_mail_conn, p_text_msg);

        UTL_SMTP.write_data(l_mail_conn, UTL_TCP.crlf || UTL_TCP.crlf);

    END IF;

    IF p_html_msg IS NOT NULL THEN

        UTL_SMTP.write_data(l_mail_conn, '--' || l_boundary || UTL_TCP.crlf);

        UTL_SMTP.write_data(l_mail_conn, 'Content-Type: text/html; charset="iso-8859-1"' || UTL_TCP.crlf || UTL_TCP.crlf);

        UTL_SMTP.write_data(l_mail_conn, p_html_msg);

        UTL_SMTP.write_data(l_mail_conn, UTL_TCP.crlf || UTL_TCP.crlf);

    END IF;

    UTL_SMTP.write_data(l_mail_conn, '--' || l_boundary || '--' || UTL_TCP.crlf);

    UTL_SMTP.close_data(l_mail_conn);

    UTL_SMTP.quit(l_mail_conn);

END;

Step#2: Configure Job to run after every 10 minutes.

BEGIN

    DBMS_SCHEDULER.CREATE_JOB (

            job_name => 'Job_Lock_Notif_Email',

            job_type => 'STORED_PROCEDURE',

            job_action => 'send_service_account_lock_job',

            number_of_arguments => 0,

            start_date => TO_TIMESTAMP_TZ('2023-05-11 14:00:20.000000000 ASIA/BAGHDAD','YYYY-MM-DD HH24:MI:SS.FF TZR'),

            repeat_interval => 'FREQ=MINUTELY;INTERVAL=10',

            end_date => NULL,

            enabled => True,

            auto_drop => FALSE,

            comments => '');

END;

/

BEGIN

DBMS_SCHEDULER.set_attribute( name => 'Job_Lock_Notif_Email', attribute => 'job_action', value => 'send_service_account_lock_job');

DBMS_SCHEDULER.set_attribute( name => 'Job_Lock_Notif_Email', attribute => 'repeat_interval', value => 'FREQ=MINUTELY;INTERVAL=10');

DBMS_SCHEDULER.SET_ATTRIBUTE( name=>'Job_Lock_Notif_Email',attribute => 'RAISE_EVENTS',value=> SYS.DBMS_SCHEDULER.JOB_ALL_EVENTS);

DBMS_SCHEDULER.disable('Job_Lock_Notif_Email');

DBMS_SCHEDULER.enable('Job_Lock_Notif_Email');

END; 

/

---------------------------------------------------------------------------

If manual job needs to be run

begin

dbms_scheduler.run_job (job_name => 'Job_Lock_Notif_Email');

end;

/

--OR

execute dbms_scheduler.run_job('Job_Lock_Notif_Email');

How to generate CSV file using Oracle SQL Developer Command Line (SQLcl)

 This blog is explaining how to generate controlled CSV file using Oracle SQL Developer Command Line (SQLcl).

Oracle SQL Developer Command Line (SQLcl) is a free command line interface for Oracle Database. It allows you to interactively or batch execute SQL and PL/SQL. SQLcl provides in-line editing, statement completion, and command recall for a feature-rich experience, all while also supporting your previously written SQL*Plus scripts.

Download Link:

https://www.oracle.com/database/sqldeveloper/technologies/sqlcl/

Note: Below image is modified. Original image is taken from website:

https://exasol.my.site.com/s/article/Proper-csv-export-from-Oracle?language=en_US

For exploraing features of SQLcl, below see:

https://docs.oracle.com/en/database/oracle/sql-developer-command-line/22.4/index.html