Re: how to create VPD with SINGLE ORACLE USER ?

From: Vladimir M. Zakharychev <bob_at_dpsp-yes.com>
Date: Wed, 5 Mar 2003 14:42:35 +0300
Message-ID: <b44nt2$p20$1_at_babylon.agtel.net>


"DA Morgan" <damorgan_at_exesolutions.com> wrote in message news:3E652371.1863083D_at_exesolutions.com...

> If a database has only a single schema and user ... then everyone connecting
> has full privileges to all objects in the schema. Maybe you think no one will
> ever access your database except through your front-end and that you will
> therefore be able to kludge together something that looks like security ...
> but to me the answer is still that you can't do it.

Well, actually you can do it by combining RLS aka FGAC aka VPD, your custom RLS policies and some application context, which will provide info on who's logged in and will be used in your policy functions to determine what you can see and what you can't. Not without some work and thorough understanding of what you're doing, but doable. One caveat with FGAC is that it doesn't cover LOBs being altered through DBMS_LOB - you can alter them without being noticed and caught even if the table has a policy that denies any updates (you should only be able to select a LOB locator from it.) But otherwise you can pretty easily come up with your own set of access controls to data within one schema, which will be in effect regardless how you got in, and it will be absolutely transparent - you won't even know you're not seeing the whole picture when you SELECT * FROM a table under RLS protection (well, the query plan may give you some hints sometimes - for example, you may note that your SELECT * doesn't do FTS you'd expect, but an index range scan instead.)

-- 
Vladimir Zakharychev (bob_at_dpsp-yes.com)                http://www.dpsp-yes.com
Dynamic PSP(tm) - the first true RAD toolkit for Oracle-based internet applications.
All opinions are mine and do not necessarily go in line with those of my employer.
Received on Wed Mar 05 2003 - 12:42:35 CET

Original text of this message