Re: Set Role in Trigger

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