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, [EMAIL PROTECTED] 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) > [EMAIL PROTECTED] > > > > > > [EMAIL PROTECTED] > > d.tenet.edu To: Multiple recipients of > list ORACLE-L <[EMAIL PROTECTED]> > Sent by: cc: > > [EMAIL PROTECTED] 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) > [EMAIL PROTECTED] > > > > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: > INET: [EMAIL PROTECTED] > > 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
