To solve your first problem, correctly identifying the current session from
v$session, in your login trigger include:
client_info_str := 'SOME_LITTERAL_' || LTRIM(dbms_random.value,'.');
DBMS_APPLICATION_INFO.SET_CLIENT_INFO(client_info_str);
Then you can:
SELECT program, username,
osuser, terminal, machine
INTO loc_program, loc_username,
loc_osuser,loc_terminal,loc_machine
FROM V$SESSION
WHERE client_info=client_info_str;
I've never tried the SET ROLE in a login trigger but I don't know why it
wouldn't work.
"Pardee, Roy E"
<roy.e.pardee To: Multiple recipients of list
ORACLE-L <[EMAIL PROTECTED]>
@lmco.com> cc:
Sent by: root Subject: 8.1.6: possible to set role in
db's logon trigger?
01/13/2003 10:43
AM
Please respond
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: [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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Thomas Day
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).
