Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Alberto.

Asked: June 15, 2018 - 10:18 pm UTC

Last updated: June 19, 2018 - 3:30 am UTC

Version: 12c

Viewed 10K+ times! This question is

You Asked

CREATE OR REPLACE TRIGGER LOG_T_LOGON
AFTER LOGON ON DATABASE
DECLARE
    osUser VARCHAR2(30);
    machine VARCHAR2(100); 
    prog VARCHAR2(100);
    ip_user VARCHAR2(15);
BEGIN
    SELECT OSUSER, MACHINE, PROGRAM, ora_client_ip_address
    INTO osUser, machine, prog, ip_user
    FROM v$session
    WHERE SID = SYS_CONTEXT('USERENV', 'SID');

    IF (osUser = 'APuente' AND prog = 'SQL Developer')THEN
        RAISE_APPLICATION_ERROR(-20000,'Denied!  You are not allowed to logon from host '||prog|| ' using '|| osUser);
    END IF;
END;
/


I have this code to prevent that the user Apuente connect with SQL Developer. How can i do that? beacuse this code don't clock user

Thanks in advance

and Connor said...

Write a program to capture the details you are after so you can compare, eg

SQL> create table t (
  2    o varchar2(30),
  3    m varchar2(30),
  4    p varchar2(30),
  5    i varchar2(30)
  6  );

Table created.

SQL>
SQL> CREATE OR REPLACE TRIGGER LOG_T_LOGON
  2  AFTER LOGON ON DATABASE
  3  DECLARE
  4      osUser VARCHAR2(30);
  5      machine VARCHAR2(100);
  6      prog VARCHAR2(100);
  7      ip_user VARCHAR2(15);
  8  BEGIN
  9      SELECT OSUSER, MACHINE, PROGRAM, ora_client_ip_address
 10      INTO osUser, machine, prog, ip_user
 11      FROM v$session
 12      WHERE SID = SYS_CONTEXT('USERENV', 'SID');
 13
 14      insert into t values ( osUser, machine, prog, ip_user) ; commit;
 15
 16  --    IF (osUser = 'APuente' AND prog = 'SQL Developer')THEN
 17  --        RAISE_APPLICATION_ERROR(-20000,'Denied!  You are not allowed to logon from host '||prog|| ' using '|| osUser);
 18  --    END IF;
 19  END;
 20  /

Trigger created.

--
-- then after logging in SQL Developer as user SCOTT with windows user HAMCDC
--
SQL> select * from t;

O                              M                              P                              I
------------------------------ ------------------------------ ------------------------------ -------------
hamcdc                         gtx                            SQL Developer                  127.0.0.1
hamcdc                         gtx                            SQL Developer                  127.0.0.1


So make sure you are not confusing OSUSER with database user.

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Alberto Puente, June 18, 2018 - 4:18 pm UTC

First of all thanks for the answer.

In your example i want to deny the access to de hamcdc(osuser) user.

I did this moditifation in the if block
    
IF (upper(osUser) like '%APUENTE%' AND upper(prog) like '%SQL DEVELOPER%')THEN
 insert into t values ( osUser, machine, prog, ip_user) ; commit;
 RAISE_APPLICATION_ERROR(-20000,'Denied!  You are not allowed to logon from host '||prog|| ' using '|| osUser);
END IF;


And every connection i do with Sql Developer insert a line on t table but don't deny the access.

Regards
Connor McDonald
June 19, 2018 - 3:30 am UTC

Does that user have DBA privs? Because if not, your logic is fine. Here's an example using a simple check for 'SCOTT'

SQL> CREATE OR REPLACE TRIGGER LOG_T_LOGON
  2      AFTER LOGON ON DATABASE
  3      DECLARE
  4          osUser VARCHAR2(30);
  5          machine VARCHAR2(100);
  6          prog VARCHAR2(100);
  7          ip_user VARCHAR2(15);
  8      BEGIN
  9         SELECT OSUSER, MACHINE, PROGRAM, ora_client_ip_address
 10         INTO osUser, machine, prog, ip_user
 11         FROM v$session
 12         WHERE SID = SYS_CONTEXT('USERENV', 'SID');
 13
 14         IF user = 'SCOTT' THEN
 15             RAISE_APPLICATION_ERROR(-20000,'Denied!  You are not allowed to logon from host '||prog);
 16         END IF;
 17     END;
 18     /

Trigger created.

SQL> conn scott/tiger
ERROR:
ORA-04088: error during execution of trigger 'SYS.LOG_T_LOGON'
ORA-00604: error occurred at recursive SQL level 1
ORA-20000: Denied!  You are not allowed to logon from host sqlplus.exe
ORA-06512: at line 13


Warning: You are no longer connected to ORACLE.
SQL>



More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library