Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Set Role in Trigger

Re: Set Role in Trigger

From: <Jared.Still_at_radisys.com>
Date: Fri, 01 Aug 2003 09:39:23 -0800
Message-ID: <F001.005C8448.20030801093923@fatcity.com>


Thanks Tanel.

I didn't want to know badly enough to test it myself. :)

Looks like about 3.5 hours on your system.

Jared

"Tanel Poder" <tanel.poder.003_at_mail.ee>
Sent by: ml-errors_at_fatcity.com
 08/01/2003 06:49 AM
 Please respond to ORACLE-L  

        To:     Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
        cc: 
        Subject:        Re: Set Role in Trigger


Hi!

Just try it out in test env...

There's gonna be a lot of compiling, inserts on source$, idl_ub1$ and idl_sb4$ tables (actually more tables such are procedure$, access$, obj$ etc..). Of course if your trigger code is small, then no huge amounts of data will be generated... Also 50000 implicit commits will hit your redologs.

In my home computer the average was 0.25 secs per trigger when creating dummy triggers on a dummy table using execute immediate in anonymous pl/sql
block and CPU was the bottleneck.

Tanel.

> Jack,
>
> Sorry for being unclear: I meant how long would it take to actually
> create 50k triggers in the database. It shouldn't take long to generate
> the trigger code, but creating 50k triggers sounds kind of interesting.
>
> Jared
>
>
>
>
>
> JApplewhite_at_austin.isd.tenet.edu
> Sent by: ml-errors_at_fatcity.com
> 07/31/2003 10:59 AM
> Please respond to ORACLE-L
>
>
> To: Multiple recipients of list ORACLE-L
<ORACLE-L_at_fatcity.com>
> cc:
> Subject: Re: Set Role in Trigger
>
>
>
> Jared,
>
> Yeah, I'm sure our Developers complain to their boss "That dang DBA's
> Jacking with my application again." ;-)
>
> Actually, it would only take as long to generate the 50k triggers as it
> would to loop through the list of tables in a PL/SQL procedure and
> generate/execute the Dynamic SQL "Create Trigger" commands for those
> tables. The Before Statement Triggers would all have the same simple
call
> to a security package that checked the User's UserClass and
allowed/failed
> the DML.
>
> Since tables are created by each Campus from time to time, I could have
an
> After Create Trigger on the App Schema and add that generic security
> trigger to each new table.
>
> As opposed to Triggers, I'm really excited about Granting/Revoking
System
> Privileges to control each User's access to the various sets of tables
> they
> hit for different Schoolyears. That way there's just a couple of
triggers
> for me to maintain, not 50,000, and the App's native security
(incomplete
> as it is) controls most of the access. I'm just applying an extra,
> transparent, layer.
>
> Jack C. Applewhite
> Database Administrator
> Austin Independent School District
> Austin, Texas
> 512.414.9715 (wk)
> 512.935.5929 (pager)
> JApplewhite_at_austin.isd.tenet.edu
>
>
>
>
> Jared Still
> <jkstill_at_cybcon.c To: Multiple
> recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
> om> cc:
> Sent by: Subject: Re: Set Role in
> Trigger
> ml-errors_at_fatcity
> .com
>
>
> 07/31/2003 12:34
> AM
> Please respond to
> ORACLE-L
>
>
>
>
>
>
>
> Incredible amount of work you've had to do here Jack.
>
> Now I can say "I know Jack about roles in triggers".
>
> Yeah, ok, sorry. ;)
>
> Jack, how long do you think it will take to create 50k triggers?
>
> Jared
>
> On Wed, 2003-07-30 at 12:54, JApplewhite_at_austin.isd.tenet.edu wrote:
> >
> > OK, here's what I've found so far.
> >
> > There is no way at all in Oracle8i (don't know about 9i or 10g) to
> > enable/disable/affect a User's Role from within a Trigger. Role
> processing
> > is automatically disabled in any Definer Rights PL/SQL module. All
> > Triggers can only be created to execute with Definer Rights. So it
> doesn't
> > matter if the User owns the Trigger or any Invoker Rights procedure it
> > calls - Role processing is disabled, period. (Thanks to Roy Pardee
for
> > pointing me to MetaLink Note 106140.1, which lays it out pretty
> clearly.)
> > FGAC and/or Application Context is also of no help in
enabling/disabling
> > Roles from within a Trigger. Notice I said "from within a Trigger" -
> > that's my requirement. I can't add any code to call an Invoker Rights
> > Stored Procedure to switch Roles from within the (3rd Party, remember)
> App
> > itself - that would have been easy.
> >
> > I tried to do an "Alter User <User> Default Role
<TheRoleIWantToEnable>"
> > from the Trigger. First of all, it's got to be an Autonomous
> Transaction
> > Trigger, since Alter User issues an implicit Commit. I did that, but
> even
> > when the Trigger was owned by System, an "Insufficient Privileges"
error
> > was generated. If the Trigger was owned by Sys, however, it fired
> > successfully and the User's Default Roles were changed (as evidenced
by
> the
> > User querying User_Role_Privs). However, the User's ENABLED Roles
were
> > unaffected for that session, which is just what the docs indicate. The
> > User's new Default Roles would be enabled at the next Login. No help
> > there.
> >
> > I thought about setting up different Schemas with only synonyms and
> > different privileges on the main App Schemas tables, then switching
> Schemas
> > via "Alter Session Set Current_Schema = <DifferentSchema>". However,
I
> > quickly realized that was a stupid idea since I'd have to grant the
User
> > Ins/Upd/Del on one of those Schemas, which wouldn't "go away" when I
> > switched Schemas. The only way this would work would be to put each
> > SchoolYear's actual tables in a separate Schema with different
> Privileges
> > granted to Users (via Roles). Then switching Schemas would really
> switch
> > Privileges as well. This is not feasible, since the App would not be
> > expecting it and would probably barf. I might be able to fool it with
> > synonyms and grants, but it's a lot of work and too iffy.
> >
> > The solution I'm pursuing now is to Grant and Revoke System Privileges
> > (which take effect immediately) to each User from within an After
Logon
> > Trigger and the After Row Trigger that really needs to drive this part
> of
> > the Security layer I'm implementing. The App already grants Select
Any
> > Table, Insert Any Table, Update Any Table, and Delete Any Table to the
> Role
> > assigned to all App Users. I'll simply use the Application Context
> > variables I'm setting up to support the FGAC piece I've implemented to
> > determine which of those System Privileges to Grant at Logon and which
> ones
> > to Grant/Revoke when the User switches SchoolYears (from within the
> After
> > Row Trigger).
> >
> > Before y'all respond with howls of protest at my granting
> Sel/Ins/Upd/Del
> > Any Table to Users, rest assured that:
> > 1. The App does that anyway and now I'll be exercising even more
control
> > over them.
> > 2. This is the only App in this database and DB access is strictly
> > controlled by the App.
> > 3. Because of a "Double Logon" mechanism, Users only know their App
> Login,
> > not the Oracle Login by which they actually create a session in the
> > database. Also, the way the Oracle Logins are created - passwords are
> > created "behind the scenes and stored encrypted" - not even the App
> > Administrator knows anyone's password, much less the Users.
> >
> > Also, since the App has over 50,000 tables, I can't easily
Grant/Revoke
> > Object Privileges on that many tables each time a User switches
> > SchoolYears.
> >
> > I'm confident that this will work. However, my final fallback
solution
> > would be to write a PL/SQL procedure that would create 50,000 triggers
> that
> > would call a security package to check the User's UserClass and either
> fail
> > or let the DML succeed. It would be pretty simple, but I don't want
to
> > mess with that if I don't have to.
> >
> > BTW, I did try switching the User's UserClass (this App's equivalent
of
> a
> > Role) when a SchoolYear change was made, but it only reads UserClass
at
> > Login (like Oracle and Default Roles), so the switch didn't help for
the
> > current Session.
> >
> > Thanks for all your suggestions. I'll let you know the final outcome.
> >
> > Jack C. Applewhite
> > Database Administrator
> > Austin Independent School District
> > Austin, Texas
> > 512.414.9715 (wk)
> > 512.935.5929 (pager)
> > JApplewhite_at_austin.isd.tenet.edu
> >
> ..
>
>
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author:
> INET: JApplewhite_at_austin.isd.tenet.edu
>
> 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).
>
>
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author:
> INET: Jared.Still_at_radisys.com
>
> 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).
>

-- 
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).




-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: 
  INET: Jared.Still_at_radisys.com

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).
Received on Fri Aug 01 2003 - 12:39:23 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US