Home » RDBMS Server » Security » Prevent any query that makes simultaneous references to mutually exclusive columns. (11.2.0.4.0 and 12.1.0.2.0)
Prevent any query that makes simultaneous references to mutually exclusive columns. [message #657094] Thu, 27 October 2016 14:42 Go to next message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
I have a need to not allow the simultaneous use and/or viewing of specific columns. As an example let us consider the overworked SSN and NAME situations. Here is a sampling of the ideas I need to address.

So far I have no clue what features to use, or if this is even possible. VPD won't do it (at least I can't think of how to make it). Nor have I any idea of how to exploit SQL PATCHING.

So I am open to suggestions, and hoping maybe someone has done this already and knows how to work it.

Example: assume that social security number and employee name should never be referenced in the same query (select/insert/update/delete).

!!! all the below queries should fail (never be executed). !!!
-------------------------------------------------------------------------------------------------------------------
1.  simplest case
-------------------------------------------------------------------------------------------------------------------

create table emp (ename varchar2(10),ssn integer);

select * from emp;


-------------------------------------------------------------------------------------------------------------------
2.  columns are in different tables.
-------------------------------------------------------------------------------------------------------------------

create table emp (empid integer,ename varchar2(10));
create table emp_part2 (empid integer,ssn integer);

select a.ename,b.ssn
from emp a
    ,emp_part2 b
where a.empid = b.empid
/


-------------------------------------------------------------------------------------------------------------------
3.  one or even both columns are not actually selected in the result set, but are referenced somewhere in the query.
-------------------------------------------------------------------------------------------------------------------

create table emp (empid integer,ename varchar2(10));
create table emp_part2 (empid integer,ssn integer);

select a.ename
from emp a
where exists (
               select null
               from emp_part2 b
               where a.empid = b.empid
               and b.ssn = '123456789'
             )
/


-------------------------------------------------------------------------------------------------------------------
4.  lots of redirection and renaming and hidden predicate action but still, ultimately both columns are referenced.
-------------------------------------------------------------------------------------------------------------------

create table emp (empid integer,ename varchar2(10));
create table emp_part2 (empid integer,ssn integer);

create or replace view emp_v1
as
select ssn employee_socsec,empid
from emp_part2
where ssn like '123%'
/

create or replace view emp_v2
as
select ename employee_name,empid
from emp
/


create synonym my_emp_syn_1 for emp_v1;
create synonym my_emp_syn_2 for my_emp_syn_1;
create synonym mu_emp_syn_3 for my_emp_syn_2;

create synonym my_emp_part_2_syn_1 for emp_v2;
create synonym my_emp_part_2_syn_2 for my_emp_part_2_syn_1;
create synonym my_emp_part_2_syn_3 for my_emp_part_2_syn_2;

select employee_name
from my_emp_part_2_syn_2
where empid in (
                 select empid
                 from my_emp_syn_2
               )
/

Kevin

[Updated on: Thu, 27 October 2016 14:51]

Report message to a moderator

Re: Prevent any query that makes simultaneous references to mutually exclusive columns. [message #657120 is a reply to message #657094] Fri, 28 October 2016 03:59 Go to previous messageGo to next message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
I'm assuming the reality is sufficiently complex or the requirement sufficiently (I guess regulatory) rigid that you cannot use a series of predefined views with no way to connect them logically to hide this? I mean the queries won't "fail" but will be logically useless.

[Updated on: Fri, 28 October 2016 04:01]

Report message to a moderator

Re: Prevent any query that makes simultaneous references to mutually exclusive columns. [message #657121 is a reply to message #657094] Fri, 28 October 2016 04:57 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
I can suss your first example with fine grained audit:
orclz>
orclz> exec dbms_fga.add_policy(-
> object_schema=>'scott',-
> object_name=>'emp',-
> policy_name=>'kevpol',-
> audit_column=>'ename,ssn',-
> handler_schema=>'scott',-
> handler_module=>'does_not_exist',-
> statement_types=>'select',-
> audit_column_opts=>dbms_fga.all_columns)

PL/SQL procedure successfully completed.

orclz> select ename from emp;

ENAME
----------
jw

orclz> select ssn from emp;

       SSN
----------
         1

orclz> select * from emp;
select * from emp
              *
ERROR at line 1:
ORA-28144: Failed to execute fine-grained audit handler
ORA-06550: line 1, column 7:
PLS-00201: identifier 'SCOTT.DOES_NOT_EXIST' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored


orclz>

Re: Prevent any query that makes simultaneous references to mutually exclusive columns. [message #657122 is a reply to message #657094] Fri, 28 October 2016 06:16 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
For the more complex examples, you could do something with DBMS_REDACT, assuming access to the Adv Security Option. For the policy's EXPRESSION, you are very limited in what you can do but you can refer to any USERENV variable. That means you you could look at the CURRENT_SQL to detect the column names. It could cover your examples 2 and 3.
Re: Prevent any query that makes simultaneous references to mutually exclusive columns. [message #657148 is a reply to message #657094] Fri, 28 October 2016 09:08 Go to previous message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
Thanks Roachcoach and John. Both your comments have given me more to think about. I will be discussing application design questions next with which will likely involve the view question, and though I looked at dbms_rls, I did not look at dbms_fga. So I am there now.

If I get further with something I will post again with the details.

Both your comments actually have helped me a great deal. Kevin
Previous Topic: How to connect Oracle 8 client to 11gR2 when using network data encryption
Next Topic: DEBUG ANY PROCEDURE
Goto Forum:
  


Current Time: Fri Mar 29 03:00:33 CDT 2024