Forum Stats

  • 3,855,318 Users
  • 2,264,499 Discussions
  • 7,905,968 Comments

Discussions

Logon Trigger - Question

719861
719861 Member Posts: 1
edited Aug 28, 2009 1:16PM in SQL & PL/SQL
Hi,

I'm a beginner on the Oracle technology and I'm trying add a step on my logon trigger that avoids that the same user connects twice to the database using PL/SQL Developer.

Here what I'm doing:

.
.
.
VUSER := UPPER(SYS_CONTEXT('USERENV', 'SESSION_USER'))

VPROGRAM := UPPER(SYS_CONTEXT('USERENV', 'MODULE'))
.
.
.
SELECT COUNT(*) INTO N_SESSION
FROM V$SESSION
WHERE USERNAME = UPPER(SYS_CONTEXT('USERENV', 'SESSION_USER'));

IF VUSER NOT IN ('SYS','SYSTEM') AND N_SESSION >= 2 AND VPROGRAM LIKE 'PLSQLDEV%' THEN
RAISE_APPLICATION_ERROR(-20003,
'Access to database ' ||
UPPER(SYS_CONTEXT('USERENV', 'DB_NAME')) ||
' denied for this application');
END IF;
.
.
.

For some reason, it's not working.

Do you guys know what I should do?

Thanks

Answers

  • MichaelS
    MichaelS Member Posts: 8,424 Bronze Crown
    The trigger works fine in principle:
    CREATE OR REPLACE TRIGGER after_logon_trg
    AFTER LOGON
    ON DATABASE
    DECLARE
    	n_session INTEGER;
    BEGIN
    	SELECT COUNT ( * )
    	  INTO n_session
    	  FROM v$session
    	 WHERE username = UPPER (SYS_CONTEXT (
    										 'USERENV',
    										 'SESSION_USER'
    									 ));
    
    	IF UPPER (SYS_CONTEXT (
    					 'USERENV',
    					 'SESSION_USER'
    				 )) NOT IN ('SYS', 'SYSTEM')
      AND n_session >= 2
      AND UPPER (SYS_CONTEXT (
    					 'USERENV',
    					 'MODULE'
    				 )) LIKE
    			'SQL%'
    	THEN
    		raise_application_error (
    			-20003,
    				'Access to database '
    			|| UPPER (SYS_CONTEXT (
    							 'USERENV',
    							 'DB_NAME'
    						 ))
    			|| ' denied for this application'
    		);
    	END IF;
    END after_logon_trg;
    /
    Session 1:
    SQL> connect scott/[email protected]
    Connect durchgeführt.
    
    
    Session 2:
    C:\>sqlplus scott/[email protected]
    
    SQL*Plus: Release 11.1.0.7.0 - Production on Fr Aug 28 19:05:25 2009
    
    Copyright (c) 1982, 2008, Oracle.  All rights reserved.
    
    ERROR:
    ORA-00604: Fehler auf rekursiver SQL-Ebene 1
    ORA-20003: Access to database ORACLE denied for this application
    ORA-06512: in Zeile 23
    But beware that for users with "ADMINISTER DATABASE TRIGGER" privilege the trigger will still allow the user to login !
  • Sven W.
    Sven W. Member Posts: 10,551 Gold Crown
    user6720599 wrote:
    Hi,

    I'm a beginner on the Oracle technology and I'm trying add a step on my logon trigger that avoids that the same user connects twice to the database using PL/SQL Developer.
    Why? An Oracle database is quite capable of handling multiple sessions.
This discussion has been closed.