Re: Set Role in Trigger

[Arup Nanda]

Jack,   After theclarification, my recommendation actually holds more ground - I hope you can see that in the explanation here. Creating views are not practical due to the number of users and year combinations involved. Since you are using FGAC, that is precisely you should do.   <you wrote> Actually, you should review the docs on Application Context.  No User can set his Application Context Variables by calling DBMS_Session.Set_Context, since each Application Context is "registered" to its specific package, which should be inaccessible to the User. </you wrote>   I am aware of that; in fact that is what makes this process more secure.   I am writing a book to be published in September that describes exactly how this is done; as a matter of fact, the example described mimics your case. Unfortunately as this stage I will violate the contract with the publisher if I divulge too much details; so I have to be succinct!   I am assuming you have 10 years of data in table such as year1, year2, etc. When a user logins, he may be given a role year1, which has select privileges on year1 only.   1. create a procedure set_role (p_user_id). this procedure selects from some table that stores the username and role allocated. from the role allocated, it calls dbms_session.set_role (p_role_name); you can also specify dbms_session.set_context 2. create a role year1 identified by set_role. Note "identified by set_role"; this is important. Similarly create all roles year2, year3, etc. 3. grant select on year1 to year1, etc. 4. create the user myuser 5. grant year1, year2 to myuser. 6. alter user myuser default role none; 7. create a procedure validate_me (p_username, p_password) that returns only YES or NO based on the username/password combination. Inside this procedure call the set_role and set_context procs. 8. When the user calls this validate_me procedure, the application context as well as role can be set.   I wish it were more descriptive; but ..   <you wrote> Using an Application Context Variable won't work, since the App. Ctx. Var. User_Role is not really a Role to which I could grant object privileges </you wrote>   the app ctx attr value is simply for the role name. The actual role setting is done by set_role procedure.   The only thing I don't understand is why this has to be called from a DML trigger on table. Which table's trigger will trigger this role setting event; could you elaborate on that?   I really HTH.   Arup Nanda www.proligence.com ----- Original Message ----- From: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L Sent: Sunday, July 27, 2003 8:59 PM Subject: Re: Set Role in Trigger Arup, Thanks for your reply.  As I said in my memo, I really want the User's Role to be changed from within a Trigger on a table, not a System Trigger.  Actually, it doesn't matter, since no database trigger can be defined with Invoker's Rights.  That means that any trigger in which I issue execute DBMS_Session.Set_Role would fail, since Roles are disabled in any PL/SQL module executed with Definer's Rights - which is all triggers.  Yes, this App. updates the User table when the User chooses to change to a different set of Schoolyear tables, but it creates no system insecurity.  Why would it? Using an Applicatioin Context Variable won't work, since the App. Ctx. Var. User_Role is not really a Role to which I could grant object privileges.  My situation is your Scenario #1.  I understand Application Context and am using it in the current FGAC implementation I've created for this app.  However, I can't see how I can substitute an App. Ctx. Var. for a "real" Role.  Actually, you should review the docs on Application Context.  No User can set his Application Context Variables by calling DBMS_Session.Set_Context, since each Application Context is "registered" to its specific package, which should be inaccessible to the User. Roy suggested views, which might be an option for a well-designed, low user-count app, but this app. (remember, 3rd Party, we didn't design it!) has over 50,000 tables.  Also, we have several thousand teachers and administrators who access the system.  Multiply the two and you can see that millions of views are not feasible. I've really got to find a way to change a User's "real" Role from within a Trigger.  It can't be the After_Logon Trigger, since the User's Role must be changeable up to many times during a single Session.  Also, remember that I can't make a single modification to the Application's code. Unless someone comes up with something else, I'll experiment with having the table Trigger owned by System and executing "Alter User... Default Role..." as a Dynamic SQL statement to see if that works to change the User's Role. Thanks. Jack C. Applewhite Database Administrator Austin Independent School District Austin, Texas 512.414.9715 (wk) 512.935.5929 (pager) [EMAIL PROTECTED] "Arup Nanda" <[EMAIL PROTECTED]> Sent by: [EMAIL PROTECTED] 07/27/2003 01:24 PM Please respond to ORACLE-L                 To:        Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>         cc:                 Subject:        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: [EMAIL PROTECTED] 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) [EMAIL PROTECTED]