Skip to Main Content
  • Questions

  • Column level access restrictions on a table

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Venkat.

Asked: October 19, 2016 - 7:24 pm UTC

Last updated: October 21, 2016 - 1:38 am UTC

Version: 12

Viewed 1000+ times

You Asked

Let's say I have a table T with columns A, B, C and D. Data in each column by itself is not considered sensitive, but a combination of columns A,B,C in the same resultset is considered sensitive. Is it possible to allow queries that select A,B,D or A,C,D or B,C,D to return data but not allow queries that select columns A,B,C (with or without D)? A query that uses this table along with others needs to satisfy the same requirements.

Thanks!

and Connor said...

Sorry - I am unaware of any facility to do that.

DBMS_RLS lets you choose columns upon which a row limiting or masking policy becomes activated, but that is a ANY rather than ALL condition, ie,

sec_relevant_cols=>'A,B,C'

means *any* of those columns referenced in the query.

I'll publish this so anyone with some suggestions can add their thoughts.

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

A reader, October 20, 2016 - 11:20 am UTC

You could disable a direct query for that table and create a pipelined function, where parameter would be which columns to query, and then you could filter the result as needed.

e.g.

type
t_rec is record(
a t.a%type,
b t.a%type,
c t.a%type,
d t.a%type);

type t_tab is table of t_rec;

function foo(pi_columns in varchar2) return t_tab pipelined is
begin
if a,b,c in pi_columns then raise no_data_found
...
end;

and then
select * from table(foo('a,b,d'));



Anubha, October 20, 2016 - 1:03 pm UTC

Hi Venkat & Tom,
Probably a combination of rls/fga with before select can help you. I have not tried but a related article is available @ https://technology.amis.nl/2005/09/26/select-trigger-in-oracle-database-introducing-fine-grained-auditing/
Connor McDonald
October 21, 2016 - 1:38 am UTC

nice input
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Security

All of the vital components for a secure database are covered in the Security guide.