Re: 8.1.6: possible to set role in db's logon trigger?

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 <ORACLE-L_at_fatcity.com>                    
                      @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: 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 services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: 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).






-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Thomas Day
  INET: tday6_at_csc.com

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: 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).