Consolidating resources and Expanding Thoughts

  First of all if you haven't seen this yet Joel Kallman has started an Official Apex group on Linkedin.  In less than a week the membership has ballooned from 15 (when I joined) to just under 400.  The group was created with the express thought of consolidating the APEX contributors on Linkedin and to keep the content to the point of tech and discussions that relate directly to APEX.  This brought me into an inventive mode this weekend and I finally had something technical to go with a previous post HERE.

  So if you are interested in APEX and would like another really good resource, go to linkedin.com and join the "Oracle Application Express (APEX) - The Official Group".

  In the previous post "Lessons of Locks, Keys and Screwdrivers", I was playing with the idea of Virtual Columns.  As it turns out the new Linkedin group produced a discussion on how to audit the data viewed by an interactive report.  There are a lot of methods available and many showed up here and all seem like very good prospects.  Since this was a line I was already on you can guess where I was leaning towards.  The discussion got me to look back at this work and focus it to just that.  The result is a set of code that can prove the point that the virtual column is indeed as useful as I had thought and more so.  I have posted the DDL and function below for the linkedin discussion, but my point here is that this is just one case where security ( in this case an audit) can be implemented at a sufficiently low level as to prevent or detect data usage not just change.

 (One note here is that when creating the table with the virtual column you need to use a very basic calculation first and then altering the column to use your new function.)

--------------------------------------------------------
-- DDL for Table PG_READ_AUDIT
--------------------------------------------------------

CREATE TABLE "PG_READ_AUDIT"
( "ID_REC" NUMBER,
"TABLE_NAME" VARCHAR2(50 BYTE),
"ID_RECORD" NUMBER,
"USER_NAME" VARCHAR2(50 BYTE),
"DATE_AUDIT" DATE,
"NOTES" VARCHAR2(4000 BYTE)
) ;

--------------------------------------------------------
-- DDL for Table PG_VIR_COL_TBL2
--------------------------------------------------------

CREATE TABLE "PG_VIR_COL_TBL2"
( "ID_REC" NUMBER,
"FIRST_NAME" VARCHAR2(50 BYTE),
"LAST_NAME" VARCHAR2(50 BYTE),
"CODE" VARCHAR2(100 BYTE),
"NOTES" VARCHAR2(4000 BYTE),
"VC_VALUE" NUMBER GENERATED ALWAYS AS ("PG_ACCESS_EVAL"("ID_REC",'PG_VIR_COL_TBL2')) VIRTUAL VISIBLE
) ;

--------------------------------------------------------
-- DDL for Function PG_ACCESS_EVAL
--------------------------------------------------------

CREATE OR REPLACE FUNCTION "PG_ACCESS_EVAL"
(p_id_record in number
, p_id_table_name in varchar2)
return number
as
pragma AUTONOMOUS_TRANSACTION;

l_retvar number := 1;

l_user_name varchar2(50) := v('APP_USER');
l_sc_amethod varchar2(20) := sys_context('USERENV','AUTHENTICATION_METHOD');
l_sc_auth_id varchar2(20) := sys_context('USERENV','AUTHENTICATED_IDENTITY');
l_sc_host varchar2(20) := sys_context('USERENV','HOST');
l_sc_ip varchar2(20) := sys_context('USERENV','IP_ADDRESS');
l_sc_isDBA varchar2(20) := sys_context('USERENV','ISDBA');
l_sc_os_user varchar2(20) := sys_context('USERENV','OS_USER');
l_sc_p_user varchar2(20) := sys_context('USERENV','PROXY_USER');
l_c_scn number;

l_notes varchar2(4000);
l_id_audit number;

begin

SELECT current_scn
into l_c_scn
FROM sys.v_$DATABASE;

l_notes := ('['||to_char(systimestamp,'YYYY-MM-DD HH24:MI:SS.FF3')||'] '||
nvl(l_user_name,nvl(l_sc_p_user,l_sc_auth_id))||':'||
nvl(l_sc_isdba,' ')||':'||
nvl(l_sc_host,' ')||':'||
nvl(l_sc_ip,' ')||':'||
nvl(l_sc_os_user,' ')||':'||
nvl(l_sc_p_user,' ')||':'||
nvl(l_sc_auth_id,' ')||':'||
l_c_scn);

dbms_output.put_line(l_notes);

select pg_read_audit_seq.nextval
into l_id_audit
from dual;

insert into pg_read_audit
values
(l_id_audit, p_id_table_name, p_id_record,
nvl(l_user_name,nvl(l_sc_p_user,l_sc_os_user)),
sysdate, l_notes);

commit;

return l_retvar;
end pg_access_eval; 

/

cheers!