Re: Set Role in Trigger

Jack,

Question - why do you want to use System triggers to change roles? I don't see how you can define an event for this trigger to be fired. If the event is the updating of record in USERS table, who updates it? Obviously not the user himeself; then the whole system becomes insecure to the core.

Your choice of using a role to allow access to a specific yesr is sound design. I assume each year is stored in separate tables. If that is not correct, i.e. the record for all years are stored in a single table, then roles will not help you, you need to enable FGAC using something called application context.

Scenario 1: Each year's data is stored in a seprate table.

You would create roles, but not normal roles. The roles should be identified by a procedure. This procedure is owned by SYS or some other secued schema. Inside this procedure you would assign a value to the application context attribute called user_role, which is set via dbms_Session.set_context. All the users are revoked execute priv on dbms_session; so they will never be able to call this procedure directly. The only way they can do it is by calling the trusted procedure you have defined for that role.

Scenario 2: All the tables have data for all the years.

Inthis case you will have to use FGAC; but the FGAC policy will have to depend on the application context you defined earlier. You wil define another context attribute called school_year, whic is again set by the trusted procedure of the role. Since the user does not have the privilege to call dbms_Session, he will not be able to set the value of this attrbute to any other year at will.

Summary:

You will define several roles ideintified by procedure. All these roles are granted to the user but none is a default role.

When a user logs in, all roles assigned to him are disabled, since none is a default role. Then he calls the procedure set_role(), no arguments. Inside the procedure set_role(), you will read the users table, see the role the user is supposed to have, enable this role via dbms_Session.set_role and then set the application context, if any.

Since the user does not have execute privs on dbms_session, he will not be able to set the app context.

Since the role is identified by a procedure, i.e. set_role(), the user will not be able to set the role himself using "SET ROLE" command in sql*plus. The only way he can do that is by calling the set_role procedure.

If the user does not call the procedure, none of the roles are enabled; therefore he will not be able to do anything.

In other words, you have a secured system.

As an added bonus: you will have the application context attribute you can use any way you want to use. Right now you can use it for FGAC; but later when you are in 9i, you can use it in other cooler features such as Fine Grained Auditing.

HTH. Arup Nanda
www.proligence.com

• Original Message ----- From: JApplewhite_at_austin.isd.tenet.edu To: Multiple recipients of list ORACLE-L Sent: Sunday, July 27, 2003 11:59 AM Subject: Set Role in Trigger

  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: Arup Nanda
  INET: orarup_at_hotmail.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).