FAQ
Hi all,

I want to prevent certain OS users from logging in database using certain DB
accounts (say: ORAUSR1). The following code works only if ORAUSR1 is NOT a
DBA account. Can anybody please help?

We have to grant the DBA role to the schema owner of our ERP system, but
the password for this account is well-known and changing it is not advisable
as many applications are using this account with hard-code the password.

CREATE OR REPLACE TRIGGER logonauditing AFTER LOGON ON database
DECLARE

machinename VARCHAR2(64);
osuserid VARCHAR2(30);
v_sid NUMBER(10);
v_serial NUMBER(10);
v_killsession varchar2(500);
CURSOR c1 IS
SELECT sid, serial#, osuser, machine
FROM v$session WHERE audsid = userenv('sessionid');
BEGIN
OPEN c1;
FETCH c1 INTO v_sid, v_serial, osuserid, machinename;
if upper(user) in ('ORAUSER1','ORAUSER2') and osuserid not in
('OSUSER1','OSUSER2') then
v_killsession := 'alter system kill session ' ||''''|| v_sid
','|| v_serial ||'''';
execute immediate v_killsession;
-- same if I try "raise_application_error( ....)"
END IF;

END;

Search Discussions

  • David Sharples at Apr 4, 2006 at 4:42 pm
    you cant stop dba accounts from logging into the database. The reason being
    is that if you wrote a login trigger that didnt work then no-one could ever
    log into change it

    Revoke dba from the user and grant him the priveleges he needs
    On 04/04/06, TU Lijie wrote:

    Hi all,

    I want to prevent certain OS users from logging in database using certain
    DB accounts (say: ORAUSR1). The following code works only if ORAUSR1 is NOT
    a DBA account. Can anybody please help?

    We have to grant the DBA role to the schema owner of our ERP system, but
    the password for this account is well-known and changing it is not advisable
    as many applications are using this account with hard-code the password.
    --
    http://www.freelists.org/webpage/oracle-l
  • Rjamya at Apr 4, 2006 at 4:47 pm
    me thinks this fact is well documented.

    Raj

    Got RAC?

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedApr 4, '06 at 4:34p
activeApr 4, '06 at 4:47p
posts3
users3
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase