Re: Set Role in Trigger

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
>
>
>
>
> JApplewhite_at_austin.is
> d.tenet.edu To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
> Sent by: cc:
> ml-errors_at_fatcity.com Subject: Set Role in Trigger
>
>
> 07/27/2003 10:59 AM
> Please respond to
> ORACLE-L
>
>
>
>
>
>
>
> Short form of my question:
> How can I enable a Role for a User within a database trigger (owned by
> another Schema) on a table owned by yet another Schema?
> - The M's I've RTF'd indicate that a trigger (and any procedure it calls)
> can never execute with Invoker's Rights.
> - I can't find a way to execute Set Role for a User as another User, say,
> System.
> - I'm stuck.
> - Environment: 8.1.7 on Win2k and HP-UX.
>
> Longer form of my question:
> I'm in the process of adding extra security features to our 3rd Party
> Student Information System, whose code I can't touch. I've successfully
> implemented FGAC to keep Users at a School from accessing info.at other
> schools. Now I need to limit which School Year's data they can update
> (Past, Current, Next). The Application grants Sel, Ins, Upd, Del on its
> tables via a Role, so I thought I'd just switch Roles when the User
> switched School Years (via updating her record in a Users table). Seemed
> like a good idea, but now I can't see how to implement it.
>
> SQL and PL/SQL commands like Set Role, Alter Session,
> DBMS_Session.Set_Role, etc. only apply to the current User, which would be
> the Trigger Owner. I've used DBMS_System.Set_SQL_Trace_In_Session, but
> can't find an equivalent procedure to Set Role for another User.
>
> BTW, the fact that there's no Invoker_Rights_Clause in the Create Trigger
> syntax and a section in the PL/SQL User's Guide and Reference (Ch. 7
> Subprograms / Invoker Rights vs Definer Rights / Using Views and Database
> Triggers) are the basis for my being stuck.
>
> The only possible way I see to do this is to create the trigger as System,
> then use Dynamic SQL to issue the "Alter User ... Default Role ...."
> command. However, I don't know if that takes effect immediately (within
> the User's current Session) or would take effect at the User's next login.
> Before I spend a bunch of time setting up a test, I thought I'd get some
> opinions from this very knowledgeable List.
>
> Can I do it? How?
>
> TIA.
>
> 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: Jared Still
  INET: jkstill_at_cybcon.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).