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