Re: Set Role in Trigger

Hi!
> This is probably too kludgy or simple-minded, or non-maintainable, but is
it technically possible?
>
> 1) Create a series of views that subset
> the actual tables, according to the rules
> you've got about who the viewer is & what
> year(s) they've selected in the Users table.
>
> 2) Redefine the public synonyms so that they
> point to your views rather than the base
> tables.

Oracle Apps actually works that way, that a user gets assigned an organization id org_id when he logs on (not using trigger, from client side instead) and uses views which restrict queries & dml by org_id. This is based on session environment variables, I believe it's better in performance point of view, if we would have to scan a "privileges" table during every select on any table, it could become the bottleneck...

Tanel.

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Tanel Poder
  INET: tanel.poder.003_at_mail.ee

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).