| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Follow-up: It's NOT possible to set role in db's logon trigger
In case anyone cares--it looks like it is *not* possible to set a role in an
after logon trigger. Had I only looked at metalink:
AFTER LOGON Triggers Don't Allow DBMS_SESSION.SET_ROLE to Keep Roles Enabled http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_datab ase_id=NOT&p_id=106140.1
Bummer, that.
Thanks again to all who responded.
Cheers,
-Roy
Roy Pardee
Programmer/Analyst
SWFPAC Lockheed Martin IT
Extension 8487
-----Original Message-----
Sent: Monday, January 13, 2003 7:42 AM
To: 'ORACLE-L'
Greetings all,
I'm trying to support a COTS application that is back-end agnostic & makes only minimal use of security on the db. In particular, it requires that users be granted a default role that has *very* heavy permissions--enough to do some major mischief should they ever figure out how to use odbc or sql*plus.
My collegues & I have devised a kludgy method for getting around this problem, involving a shill startup program that turns the default-ness of the role on & off in conjunction with users opening & closing the client program. This works, but is a pain to maintain.
I've recently discovered the v$session.program field & am now wondering whether it would be possible to use the new-fangled logon system trigger to set the role only for cases where v$session.program = the COTS client.
Can anybody comment as to whether this is a viable approach on an 8.1.6 database & if not, on a 9i db?
In particular, there are two things I don't know--first, how to select just the one row in v$session that corresponds to the current connection. If a user was to start up the COTS client & then connect to the same db via sql*plus, I would want the role set *only* for the COTS client session. My best thought so far here is to use the most recently started connection based on v$session.logon_time.
Second, whether the SET ROLE statement is legal in a logon trigger.
All help will be most welcome.
Thanks!
-Roy
Roy Pardee
Programmer/Analyst
SWFPAC Lockheed Martin IT
Extension 8487
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Pardee, Roy E
INET: roy.e.pardee_at_lmco.com
Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting servicesto: 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). Received on Mon Jan 27 2003 - 17:13:42 CST
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
 |
 |