| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Set Role in Trigger
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.
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).Received on Wed Jul 30 2003 - 14:54:25 CDT
 |
 |