Here is a trigger that we use to change the optimizer goal at logon based on the operating system account. You would be looking into the value of the v$session.program column instead of the OSUSER used below. Instead of the 'alter session set optimizer.......' code you would use any of the standard security recommendations that have already been posted for you. I saw some recommending the use of a select-only role. You can issue a 'set role selectonlyrole'. I would use a password-restricted role and then use 'set role selectonlyrole identified by yoursecretpassword'. This would prevent users from undoing your trigger changes post-logon.
HTH
========================= snippet begins here
CREATE OR REPLACE TRIGGER change_optimizer_on_startup
AFTER LOGON ON DATABASE
DECLARE
p_osuser_txt V$SESSION_CONNECT_INFO.OSUSER%TYPE;
BEGIN
BEGIN
SELECT OSUSER
INTO p_osuser_txt
FROM V$SESSION
WHERE AUDSID = (SELECT USERENV('SESSIONID') FROM DUAL);
EXCEPTION
WHEN OTHERS THEN
p_osuser_txt:=null;
END;
IF p_osuser_txt IN ('psoftfs','CompOps') THEN
BEGIN
EXECUTE IMMEDIATE 'alter session set optimizer_mode=rule';
EXCEPTION
WHEN OTHERS THEN
SYS.DBMS_SYSTEM.KSDWRT(2,TO_CHAR(SYSDATE,'dd-mon-rr hh:mm:ss')||SQLERRM);
END;
END IF;
END;
/
================== snippet ends here
-----Original Message-----
From: Larry Hahn [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, August 22, 2001 6:07 PM
To: Multiple recipients of list ORACLE-L
Subject: RE: User access within/outside of app
Tony,
8.1.7 EE on Sun Solaris
Larry
--- "Aponte, Tony" <[EMAIL PROTECTED]> wrote:
> What version are you working with?
>
> -----Original Message-----
> Sent: Tuesday, August 21, 2001 11:21 AM
> To: Multiple recipients of list ORACLE-L
>
>
> List,
>
> We have purchased a system where users login through
> an ODBC connection using a generic Oracle userid.
> This
> userid has full rights to do insert, update, delete,
> select on any table in the schema. The app asks for
> another username and password which checks the
> application security table, which limits what areas
> of
> the apps they can access.
>
> Although this may work fine for the app, the users
> also have the ability to use Access and other ODBC
> compliant programs to look at the data. When doing
> so,
> they use the same ODBC DSN and, what do you know,
> they
> have capabilities beyond their wildest imagination.
>
> This is obviously not a situation I want to
> implement.
> I am looking for a way to allow a user into the app
> to
> do their normal work, but only allow read access for
> anything outside the app.
>
> Any suggestions or ideas would be more than welcome.
>
> Thanks,
>
> Larry Hahn
> Journal Sentinel, Inc.
>
> __________________________________________________
> Do You Yahoo!?
> Make international calls for as low as $.04/minute
> with Yahoo! Messenger
> http://phonecard.yahoo.com/
> --
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.com
> --
> Author: Larry Hahn
> INET: [EMAIL PROTECTED]
>
> Fat City Network Services -- (858) 538-5051 FAX:
> (858) 538-5051
> San Diego, California -- Public Internet
> access / Mailing Lists
>
--------------------------------------------------------------------
> 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).
>
=====
Larry Hahn
DBA
Journal Sentinel,Inc
__________________________________________________
Do You Yahoo!?
Make international calls for as low as $.04/minute with Yahoo! Messenger
http://phonecard.yahoo.com/
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Larry Hahn
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
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).
