Return-Path: Received: from ensim.rackshack.net (root@localhost) by orafaq.net (8.11.6/8.11.6) with ESMTP id h0DKSa919708 for ; Mon, 13 Jan 2003 14:28:36 -0600 X-ClientAddr: 209.68.248.164 Received: from newsfeed.cts.com (newsfeed.cts.com [209.68.248.164]) by ensim.rackshack.net (8.11.6/8.11.6) with ESMTP id h0DKSZc19703 for ; Mon, 13 Jan 2003 14:28:36 -0600 Received: from fatcity.UUCP (uucp@localhost) by newsfeed.cts.com (8.9.3/8.9.3) with UUCP id JAA36176; Mon, 13 Jan 2003 09:10:02 -0800 (PST) Received: by fatcity.com (26-Feb-2001/v1.0g-b72/bab) via UUCP id 0052DC19; Mon, 13 Jan 2003 08:43:59 -0800 Message-ID: Date: Mon, 13 Jan 2003 08:43:59 -0800 To: Multiple recipients of list ORACLE-L X-Comment: Oracle RDBMS Community Forum X-Sender: "Koivu, Lisa" Sender: root@fatcity.com Reply-To: ORACLE-L@fatcity.com Errors-To: ML-ERRORS@fatcity.com From: "Koivu, Lisa" Subject: RE: 8.1.6: possible to set role in db's logon trigger? Organization: Fat City Network Services, San Diego, California X-ListServer: v1.0g, build 72; ListGuru (c) 1996-2001 Bruce A. Bergman Precedence: bulk Mime-Version: 1.0 Content-Type: multipart/alternative; boundary="----_=_NextPart_001_01C2BB22.B92FE080" ------_=_NextPart_001_01C2BB22.B92FE080 Content-Type: text/plain; charset="iso-8859-1" Hi Roy, Note 122230.1 will answer your first question about session id's. Lisa Koivu Oracle Dogbone Administrator Fairfield Resorts, Inc. 5259 Coconut Creek Parkway Ft. Lauderdale, FL, USA 33063 -----Original Message----- Sent: Monday, January 13, 2003 10:44 AM To: Multiple recipients of list 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@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@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). ------_=_NextPart_001_01C2BB22.B92FE080 Content-Type: text/html; charset="iso-8859-1" RE: 8.1.6: possible to set role in db's logon trigger?

Hi Roy,

Note 122230.1 will answer your first question about session id's.

Lisa Koivu
Oracle Dogbone Administrator
Fairfield Resorts, Inc.
5259 Coconut Creek Parkway
Ft. Lauderdale, FL, USA  33063





-----Original Message-----
From: Pardee, Roy E [mailto:roy.e.pardee@lmco.com]
Sent: Monday, January 13, 2003 10:44 AM
To: Multiple recipients of list ORACLE-L
Subject: 8.1.6: possible to set role in db's logon trigger?


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

------_=_NextPart_001_01C2BB22.B92FE080-- -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Koivu, Lisa INET: Lisa.Koivu@efairfield.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@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).