A few thoughts about redaction for what they are worth…
I’ve been working with redaction for a while now. In its basic form, it’s
pretty easy to work with. However anything beyond that, and it gets pretty
complex.
Also, keep in mind that using redaction requires and Advanced Security license.
One redaction complexity comes about because of the limited number of ways you
can define the expression of a redaction policy. The expression basically
provides the logic that defines if the policy is applied to a given query or
not.
For example, assume I define a redaction policy on a table called people and a
column called SSN using dbms_redact.add_policy.
In that definition, there is a parameter called expression which you set that
indicates what conditions should be met for the policy to be applied. For
example, if I want the policy to be applied all the time then I’d set the
expression like this:
expression=>’1=1’
You can add complexity to the expression, so that redaction only applies in
certain cases. For example, let’s say that my application is setting MODULE via
dbms_application_info. I could say that I only want redaction to be applied if
MODULE is set to ‘USER_INTERFACE_MODULE’ module by doing something like this
(specific example not tested so possible typos here):
Expression=>’sys_context(‘’USERENV’’,
‘’MODULE’’,’’USER_INTERFACE_MODULE’’)=’’TRUE’’’
In this case, the redaction policy would ONLY be applied in cases where the
USER_INTERFACE_MODULE was set when the query was issued, otherwise the data
would not be redacted.
You can use custom created contexts as well, setting the appropriate values in
things like logon triggers, from the application or whatever.
The biggest problem with redaction that I see (and this sounds like your kind
of situation) involve cases where an application needs to internally have
access to non-redacted data for downstream processing (say some form of PII is
queried and is needed for further application related queries) but you want any
display of that data to be redacted. COTs and Legacy applications tend to make
this really difficult since they generally do not supply sufficient context
information to develop an expression that can correctly indicate if the data
should, or should not, be redacted.
Now, I have seen some legacy and COTS applications set certain values available
in SYS_CONTEXT (such as ACTION, CURRENT_SQL, MODULE or SERVICE_NAME) that might
be used to identify specific areas that redaction would be desirable. I these
cases, you might be able to craft a redaction policy where data is not redacted
by default (allowing the app to have access to unredacted data). If you can
identify unique context settings that occur at the times you want the data to
be redacted (perhaps there is a MODULE called print_module that gets set
whenever the user data needs to be redacted) then you could craft a redaction
policy around those specific conditions. (I hope this makes sense).
It would be nice if redaction could take advantage of a user created function,
that allowed some PL/SQL logic to determine if the data should be redacted.
Then, you might be able to do something like look at the SQL statement being
executed and redact based on a set of blacklisted SQL statements/SQLID’s.
However, this functionality does not exist. Redaction only supports use of
sys_context or xs_sys_context functions to return values set in a specific
namespace. Also, you can define redaction expressions based on Oracle Label
Security, APEX Expression states and you can use substr and length in the
function.
RLS gives you a bit more flexibility in some respects. You can write some logic
around RLS in the policy function itself. However, at the column level only
allows you to NULL out the column ( I have yet to figure a way around that
one), as opposed to masking it. Additionally, RLS comes with potential
performance issues since it appends predicates to the where clause of your SQL.
So, you can find yourself chasing performance issues all over creation….
Hope that helps….
RF
Robert G. Freeman
Deliverer of Enterprise Data®
Businessolver
Cell: 801-703-3405
“Greater than the death of flesh is the death of hope. The death of dreams.
Against this peril we can never surrender. The future is all around us, waiting
in moments of transition, to be born in moments of revelation. No one knows the
shape of that future, or where it will take us. We know only that it is always
born in pain.”
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On ;
Behalf Of Syed Jaffar Hussain
Sent: Thursday, October 04, 2018 1:10 PM
To: Matthew Parker <dimensional.dba@xxxxxxxxxxx>
Cc: Oracle-L Freelists <oracle-l@xxxxxxxxxxxxx>; tim.evdbt@xxxxxxxxx
Subject: Re: Hiding sensitive EBS column data from certain users
Thank you all for fhe swift response.
Its a production EBS 12.2.6 environment. The requirement is to hide certain
columns data in HR and Finance modules to specific users through forms.
If we apply VPD it will break forms functionality. Also APPS. These are
application defined users. In EBS its not simple VpD. So, any body with EBS
functional and development knowledge can respond this.
Regards
On Thu, 4 Oct 2018 at 8:56 PM Matthew Parker
<dimensional.dba@xxxxxxxxxxx<mailto:dimensional.dba@xxxxxxxxxxx>> wrote:
Just need to highlight the problem in prod.
The rules applied can actually cause problems with the COTS applications like
EBS that has their own internal security architecture.
Just need to do lots of testing.
Matthew Parker
Chief Technologist
Dimensional DBA
Oracle Gold Partner
425-891-7934 (cell)
D&B 047931344
CAGE 7J5S7
Dimensional.dba@xxxxxxxxxxx<mailto:Dimensional.dba@xxxxxxxxxxx>
View Matthew Parker's profile on
LinkedIn<http://www.linkedin.com/pub/matthew-parker/6/51b/944/>
www.dimensionaldba.com<http://www.dimensionaldba.com/>
From: oracle-l-bounce@xxxxxxxxxxxxx<mailto:oracle-l-bounce@xxxxxxxxxxxxx>
<oracle-l-bounce@xxxxxxxxxxxxx<mailto:oracle-l-bounce@xxxxxxxxxxxxx>> On Behalf
Of Tim Gorman
Sent: Thursday, October 4, 2018 10:52 AM
To: dimensional.dba@xxxxxxxxxxx<mailto:dimensional.dba@xxxxxxxxxxx>;
sjaffarhussain@xxxxxxxxx<mailto:sjaffarhussain@xxxxxxxxx>; 'Oracle-L Freelists'
<oracle-l@xxxxxxxxxxxxx<mailto:oracle-l@xxxxxxxxxxxxx>>
Subject: Re: Hiding sensitive EBS column data from certain users
For production environments, technologies which mask data in-flight, including
Oracle's data redaction and SQL Server's dynamic data masking are appropriate
solutions when a portion of the user community should not have access to
certain data.
In development or testing (a.k.a. non-production) environments, there is no
reason for anyone to have access to confidential data, including database
administrators and systems administrators, partially because of the movement of
development and testing environments to out-sourced, off-shore, or cloud
environments. Masking data at-rest is the appropriate solution for
non-production environments by permanently and irreversibly obfuscating data in
datafiles, thus removing any value to intruders.
Following the implementation of
GDPR<https://en.wikipedia.org/wiki/General_Data_Protection_Regulation> in
Europe this past May, CCPA<https://www.caprivacy.org/> in California has
already been signed into law, with more countries and states to follow. The
professional honor code to which all of IT has adhered for the past 40-50 years
is no longer sufficient to protect confidential data. Essentially, unmasked
data in non-production is becoming a liability to the DBAs, developers, and
testers who work with it, because at some point, all these laws may hold
individuals (as well as organizations) liable for the damages from data
breaches. I expect that, like SOX, individual liability will begin at the top
of the organization (i.e. CEO, CFO, etc) but with examples like Snowden there
is no reason why those lower in the hierarchy cannot be targeted.
On 10/4/18 11:04, Matthew Parker wrote:
In Production or in Development? Different ways to do things based on the
environment.
What version of the database are you running?
In 12.1 there is RAS Security (VPD 2.0) that also does column level data
masking at no extra cost, but you have to create/implement the rules yourself.
Normally you control PROD by standard security controls, but you can implement
RAS against report users if they are landing on your primary database. Just
need to make sure anything you implement it doesn’t affect base EBS apps.
Matthew Parker
Chief Technologist
Dimensional DBA
Oracle Gold Partner
425-891-7934 (cell)
D&B 047931344
CAGE 7J5S7
Dimensional.dba@xxxxxxxxxxx<mailto:Dimensional.dba@xxxxxxxxxxx>
View Matthew Parker's profile on
LinkedIn<http://www.linkedin.com/pub/matthew-parker/6/51b/944/>
www.dimensionaldba.com<http://www.dimensionaldba.com/>
From: oracle-l-bounce@xxxxxxxxxxxxx<mailto:oracle-l-bounce@xxxxxxxxxxxxx>
<oracle-l-bounce@xxxxxxxxxxxxx><mailto:oracle-l-bounce@xxxxxxxxxxxxx> On Behalf
Of Syed Jaffar Hussain
Sent: Thursday, October 4, 2018 9:51 AM
To: Oracle-L Freelists <oracle-l@xxxxxxxxxxxxx><mailto:oracle-l@xxxxxxxxxxxxx>
Subject: Hiding sensitive EBS column data from certain users
Hello List,
Is there anyway to hide data of sensitive columns in Oracle EBS (v12.2) to
certain users? I thought of VPD, but, it seems, it has different approaches in
EBS. Something like, personalizing the form to hide the values of the columns,
though not sure.
Appreciate if any EBS expert can shed some light on this.
Thanks in advance,
--
Best Regards,
Syed Jaffar Hussain
--
Best Regards,
Syed Jaffar Hussain
Oracle ACE Director
<http://apex.oracle.com/pls/otn/f?p=19297:4:4640302666204919::NO:4:P4_ID:186>
Oracle Certified Master
(10g)<http://education.oracle.com/education/otn/shussain.html>
Authored Expert Oracle RAC 12c<http://www.apress.com/9781430250449>
Oracle 11gR1/R2 RAC Essentials<http://link.packtpub.com/yNZicz>,
Oracle Exadata Expert's Handbook
<http://www.pearsonhighered.com/educator/product/Oracle-Exadata-Experts-Handbook/9780321992604.page>
Oracle Problem Solving and Troubleshooting
Handbook<http://www.pearson.com.au/products/A-C-Ault-Czuprynski/Oracle-Problem-Solving-and-Troubleshooting-Handbook/9780134429205?R=9780134429205>
Oracle Magazine DBA of year (2011)
OCP 8i/9i/10g/11g DBA, RAC Certified Expert, ITIL V3 foundation certified
Mini MBA
Certified Oracle Exadata Database Machine Implementation Essentials
Oracle Certified Expert, Oracle Exadata X3 and X4 Administrator
Charity : www.sajcharity.org<http://www.sajcharity.org>
I blog at : http://jaffardba.blogspot.com/
LinkedIn : http://www.linkedin.com/in/sjaffarhussain
Follow me on twitter :
http://twitter.com/#!/sjaffarhussain<http://twitter.com/#%21/sjaffarhussain>
--------------------
"Winners don't do different things. They do things differently."
