Forum Stats

  • 3,855,314 Users
  • 2,264,499 Discussions
  • 7,905,967 Comments

Discussions

Categories

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
Tagged:
· Share on TwitterShare on Facebook

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 !
    · Share on TwitterShare on Facebook
  • 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.
    · Share on TwitterShare on Facebook
This discussion has been closed.