This is probably too kludgy or simple-minded, or non-maintainable, but is it
technically possible?
1) Create a series of views that subset
the actual tables, according to the rules
you've got about who the viewer is & what
year(s) they've selected in the Users table.
2) Redefine the public synonyms so that they
point to your views rather than the base
tables.
Cheers,
-Roy
Roy Pardee
Programmer/Analyst/DBA
SWFPAC Lockheed Martin IT
Extension 8487
-----Original Message-----
Sent: Sunday, July 27, 2003 11:24 AM
To: Multiple recipients of list ORACLE-L
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).
