Oracle FAQ Your Portal to the Oracle Knowledge Grid

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

Re: Set Role in Trigger

From: <>
Date: Wed, 30 Jul 2003 11:54:25 -0800
Message-ID: <>

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)

                              To:       Multiple recipients of list ORACLE-L <>         
                      Sent by:                     cc:                                                                           
                    Subject:  Set Role in Trigger                                                 
                      07/27/2003 10:59 AM                                                                                        
                      Please respond to                                                                                          

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

Please see the official ORACLE-L FAQ:

Fat City Network Services    -- 858-538-5051
San Diego, California        -- Mailing list and web hosting services
To REMOVE yourself from this mailing list, send an E-Mail message
to: (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

Original text of this message