Re: Need a FGAC Clue

Well, from what I understood, you can set up a policy like 'company_id = sys_context(''my_app_ctx'',''company_id'')', and apply it to all tables of interest. The main problem is how to set company_id in my_app_ctx securely (so that only application can set it and normal user coming through, say, sqlplus, will not be able to set her company id using the same method). Contexts are set through authorized packages only, so you will need to create such package and a procedure within it that will set company id when called. The only more or less secure way of doing this I can think of is to declare a procedure that will accept two parameters: company id and some secret key that only application knows (or knows how to generate if you will go for pseudo-random secrets.) Then, when application connects, it will call this procedure and set company id in application context. Since application knows the secret key (or challenge-response algorithm), context will be set. If company_id is not set, sys_context(...) will return NULL and company_id = sys_context(...) will always return no rows (you can't compare to NULL), otherwise the policy will restrict queries to only the subset of data defined by the company id no matter how queries are issued.

For certain caveats using application contexts in 8i (at least on 8.1.6 and afaik 8.1.7.0) I would recommend corresponding chapter in Thomas Kyte's book "Expert One-on-One: Oracle". These caveats mostly do not apply to 9i and, again afaik, 8.1.7.2+ though.

Corrections and additions welcome.

-- 
Vladimir Zakharychev (bob@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.


"Phil Singer" <psinger1_at_chartermi.net> wrote in message news:3D782250.383F4BC0_at_chartermi.net...


> If someone could be so kind as to give me a clue,

> I would be so graitful.....

> 

> I have several web applications, each with over

> 10,000 users.  When the application gets

> accessed, it 'sees' a userid provided by the

> web server.  This ID is authenticated thru a

> company single log-on system, and it really can

> be trusted to ensure that whoever logged onto

> the corporate intranet was who he or she claimed

> to be, and that that person is now accessing 

> my application.

> 

> This application accesses Oracle through a

> single proxy Id.  Creating the Ids in Oracle,

> is not an option.  Therefore, the application

> validates what a user can see and do in the

> application by querying tables which match

> the corporate Id (from the web server) to

> functions and rules within the application.

> 

> I would like to replace all of this with

> fine grained access control.

> 

> My problem is that all the examples I have seen

> all have the user being validated having an

> Oracle account.  I've done enough snooping

> to think that it can be used in the above

> situation, but I have not yet found any examples

> of how it is done.   So, if someone can be so

> kind as to point me toward a reference where 

> this has been done, I will be grateful.  (Will

> even buy a book if that is where the example

> is found).

> 

> 

> Thanks.

> -- 

> Phil Singer                |    psinger1_at_chartermi.net

> Oracle DBA                 |    Ford FPDS