Zounds, that's quite an app... If you haven't yet seen it, you might have a look at metalink note 106140.1: http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_d atabase_id=NOT&p_id=106140.1
aka: http://tinyurl.com/ibcb Unfortunately, I don't think there's good news in there... HTH, -Roy p.s. Just to clarify--I didn't mean for you to create a set of views for each user--I was envisioning the views joining a table you create that has--say--oracle_username, whatever user identifier the app uses, school_district, school_year_selected, to each of the substantive tables in the app. So your views look like select * from substantive_application_table s , your_limit_table l where s.app_user_identifier = l.app_user_identifier and s.school_district = l.school_district and s.school_year = l.school_year_selected and l.oracle_username = USER ; Tanel (I think) suggested using package variables instead of a separate table to improve performance, which would be even better. But with 50,000 tables (!) even that's too much work. Roy Pardee Programmer/Analyst/DBA SWFPAC Lockheed Martin IT Extension 8487 -----Original Message----- [mailto:[EMAIL PROTECTED] Sent: Sunday, July 27, 2003 5:59 PM To: Multiple recipients of list ORACLE-L 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 ----- To: Multiple recipients of list ORACLE-L Sent: Sunday, July 27, 2003 11:59 AM 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: Pardee, Roy E 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).
