Home » SQL & PL/SQL » SQL & PL/SQL » Logon Trigger Issue
Logon Trigger Issue [message #184365] Wed, 26 July 2006 06:17 Go to next message
KenJ
Messages: 69
Registered: July 2006
Location: London
Member
Hi All,

I am attempting to write a logon trigger to not allow access to certain programs (program names are stored in a table called RESTRICTED_PROGRAMS) unless the username has an entry in the 'EXEMPT_LOGINS' table. Here is the code:-

RESTRICTED_PROGRAMS

CREATE TABLE RESTRICTED_PROGRAMS(
PROGRAM_NAME VARCHAR2(64))
TABLESPACE DBA_ADMIN;

INSERT INTO RESTRICTED_PROGRAMS VALUES ('DBArt801.exe');
INSERT INTO RESTRICTED_PROGRAMS VALUES ('sqlplusw.exe');
INSERT INTO RESTRICTED_PROGRAMS VALUES ('jrew.exe');

COMMIT;

EXEMPT_LOGINS

CREATE TABLE EXEMPT_LOGINS(
LOGIN_NAME VARCHAR2(64))
TABLESPACE DBA_ADMIN;

INSERT INTO EXEMPT_LOGINS VALUES ('JONESK');

COMMIT;

Trigger itself

CREATE OR REPLACE TRIGGER logonauditing AFTER LOGON ON database
DECLARE
v_sid NUMBER(10);
v_serial NUMBER(10);
v_username VARCHAR2(64);
v_program VARCHAR2(64);
v_restricted_program VARCHAR2(64);
v_exempt_login VARCHAR2(30);
v_kill_statement VARCHAR2(128);

CURSOR c1 IS
SELECT sid, serial#, username, program
FROM v$session WHERE audsid = userenv('sessionid');
CURSOR c2 IS
SELECT *
FROM RESTRICTED_PROGRAMS;
CURSOR c3 IS
SELECT *
FROM EXEMPT_LOGINS;
BEGIN
OPEN c1;
FETCH c1 INTO v_sid, v_serial, v_username, v_program;
OPEN c2;
FETCH c2 INTO v_restricted_program;
OPEN c3;
FETCH c3 INTO v_exempt_login;

IF upper(v_username) <> upper(v_exempt_login)
AND upper(v_program) = upper(v_restricted_program)
THEN v_kill_statement := 'ALTER SYSTEM KILL SESSION '''||v_sid|| ', '||v_serial|| ''' IMMEDIATE';
EXECUTE IMMEDIATE v_kill_statement;
END IF;

CLOSE c1;
CLOSE c2;
CLOSE c3;
END;
/

The trigger is not firing. Has anyone else had to write a similar trigger or has some tips for me? I think I am using the cursors incorrectly. Maybe an 'EXISTS' clause would be more suitable for this trigger than cursors? I am using Oracle Standard Edition 10.2.0.2.0 running RHEL4.

Thanks in advance,

Ken
Re: Logon Trigger Issue [message #184376 is a reply to message #184365] Wed, 26 July 2006 06:37 Go to previous messageGo to next message
KenJ
Messages: 69
Registered: July 2006
Location: London
Member
CREATE OR REPLACE TRIGGER logonauditing
AFTER LOGON ON database
DECLARE
v_username VARCHAR2(64);
v_program VARCHAR2(64);
CURSOR c1 IS
SELECT username, program
FROM v$session WHERE audsid = userenv('sessionid');
BEGIN
OPEN c1;
FETCH c1 INTO v_username, v_program;
IF v_username = 'JONESK'
AND upper(v_program) like 'SQL%'
THEN RAISE_APPLICATION_ERROR(-20001, 'You are not allowed to login using SQLPLUS' );
END IF;
CLOSE c1;
END;

All,

This trigger works fine.

Ken
Re: Logon Trigger Issue [message #184379 is a reply to message #184365] Wed, 26 July 2006 06:41 Go to previous messageGo to next message
KenJ
Messages: 69
Registered: July 2006
Location: London
Member
All,

The dynamic kill satement can be replaced in the first trigger with the RAISE_APPLICATION_ERROR command from the second trigger to no avail.

i.e.

CREATE OR REPLACE TRIGGER logonauditing2 AFTER LOGON ON database
DECLARE
v_sid NUMBER(10);
v_serial NUMBER(10);
v_username VARCHAR2(64);
v_program VARCHAR2(64);
v_restricted_program VARCHAR2(64);
v_exempt_login VARCHAR2(30);
v_kill_statement VARCHAR2(128);
CURSOR c1 IS
SELECT sid, serial#, username, program
FROM v$session WHERE audsid = userenv('sessionid');
CURSOR c2 IS
SELECT *
FROM RESTRICTED_PROGRAMS;
CURSOR c3 IS
SELECT *
FROM EXEMPT_LOGINS;
BEGIN
OPEN c1;
FETCH c1 INTO v_sid, v_serial, v_username, v_program;
OPEN c2;
FETCH c2 INTO v_restricted_program;
OPEN c3;
FETCH c3 INTO v_exempt_login;
IF upper(v_username) <> upper(v_exempt_login)
AND upper(v_program) = upper(v_restricted_program)
THEN RAISE_APPLICATION_ERROR(-20001, 'You are not allowed to login using '|| v_program );
END IF;
CLOSE c1;
CLOSE c2;
CLOSE c3;
END;
/


Ken
Re: Logon Trigger Issue [message #184391 is a reply to message #184379] Wed, 26 July 2006 07:06 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Well, first you should put some debug code in there. I reckon that will show you that the trigger is firing.
Looking at your code, it looks like the problem is that you just get the first row from cursor C1, the first row from cursor C2 and the first row from cursor C3.

Given that your data setup shows you havin 3 rows in C2, you'll only notice the trigger if you happen to login from the program matching the first row your cursor returns.

You need to use C1 to get the session details, and then close it.

Open a version of C3 that you can pass a username into, to check if this user in on the Exempt_logins. If they are, just ignore the rest of the processing.

Open a version of C2 that you can pass a program into, to check if the program they are logging in from is in the baned list, and if it is, raise an error.

On a related note, you're only providing the illusion of security here. All I have to do is rename 'sqlplusw.exe' to 'notinyourtable.exe' and I'll be logged into your database as quick as you like.
Re: Logon Trigger Issue [message #184399 is a reply to message #184365] Wed, 26 July 2006 07:35 Go to previous messageGo to next message
KenJ
Messages: 69
Registered: July 2006
Location: London
Member
JRowbottom,

Thanks for the reply. I understand that users can use DBMS_APPLICATION_INFO to rename sqlplusw.exe to 'ANYTHINGTHEYLIKE' but I have been tasked with writing this trigger but I have also highlighted this issue.

I will digest what you have stated regarding the cursors and will rewrite the code to reflect this.

I will post results.

Thanks again,

Ken.
Re: Logon Trigger Issue [message #184413 is a reply to message #184399] Wed, 26 July 2006 08:27 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You misunderstand me slightly - the users don't need to use Dbms_Application_Info (they'd need to be already logged on for that). They just need to change the name of the executable on disc. That will then be passed to Oracle as the program name, and there's absolutely no way to stop it.

SQL> select username,program
  2  from   v$session 
  3  where  username = 'JOHN_DBA';

USERNAME                       PROGRAM
------------------------------ ------------------------------------------------
JOHN_DBA                       SQL Developer
JOHN_DBA                       notinyourlist.exe
JOHN_DBA                       SQL Developer


D:\Oracle\ora92\bin>dir *.exe
 Volume in drive D has no label.
 Volume Serial Number is B8D1-97E7

 Directory of D:\Oracle\ora92\bin

26/04/2002  20:00           114,960 csscan.exe
28/04/2002  11:07           385,296 EXP.EXE
28/04/2002  11:07           147,728 IMP.EXE
29/01/2002  14:37            28,672 launch.exe
25/04/2002  12:36            41,704 LMSGEN.EXE
28/04/2002  11:07            16,656 loadpsp.exe
25/04/2002  12:36            68,028 LXEGEN.EXE
25/04/2002  12:36           217,316 LXINST.EXE
26/04/2002  19:33           147,676 NAMESCTL.EXE
27/04/2002  00:58           700,416 notinyourlist.exe       <==
28/04/2002  11:08            20,480 ocopy.exe
26/04/2002  19:34           242,328 ONRSD.EXE
26/04/2002  19:33            20,480 OracleAdNetConnect.exe
26/04/2002  19:34            24,576 OracleAdNetTest.exe
28/04/2002  11:20           917,776 RMAN.EXE
27/04/2002  17:53            34,028 schema.exe
28/04/2002  11:07           364,816 SQLLDR.EXE
27/04/2002  01:07           459,024 sqlplus.exe
27/04/2002  00:58           700,416 sqlplusw.exe
28/04/2002  11:07            16,656 tkprof.exe
26/04/2002  19:34            38,848 TNSPING.EXE
03/04/2002  14:19           145,408 unzip.exe
28/08/2000  18:06            44,544 WFBLDR.EXE
08/02/2002  12:00            98,304 WFMLR.EXE
27/04/2002  17:52           278,128 xml.exe
27/04/2002  17:53            36,964 xmlcg.exe
03/04/2002  14:19           104,448 zip.exe
              27 File(s)      5,415,676 bytes
               0 Dir(s)  36,233,834,496 bytes free
Re: Logon Trigger Issue [message #184424 is a reply to message #184365] Wed, 26 July 2006 09:08 Go to previous messageGo to next message
KenJ
Messages: 69
Registered: July 2006
Location: London
Member
JRowbottom,

Thanks for all you help - here is the finished and tested code.

CREATE OR REPLACE TRIGGER logonauditing2 AFTER LOGON ON database
DECLARE
v_sid NUMBER(10);
v_serial NUMBER(10);
v_username VARCHAR2(64);
v_program VARCHAR2(64);
v_restricted_program VARCHAR2(64);
v_exempt_login VARCHAR2(30);
v_kill_statement VARCHAR2(128);
CURSOR c1 IS
SELECT sid, serial#, username, program
FROM v$session WHERE audsid = userenv('sessionid');
CURSOR c2 IS
SELECT *
FROM RESTRICTED_PROGRAMS;
CURSOR c3 IS
SELECT *
FROM EXEMPT_LOGINS;
BEGIN
OPEN c1;
FETCH c1 INTO v_sid, v_serial, v_username, v_program;
CLOSE c1;
OPEN c3;
FETCH c3 INTO v_exempt_login;
IF upper(v_username) = upper(v_exempt_login)
THEN NULL;
ELSE RAISE_APPLICATION_ERROR(-20002, 'You are not allowed to login using '|| v_program );
END IF;
CLOSE c3;
OPEN c2;
FETCH c2 INTO v_restricted_program;
IF upper(v_program) <> upper(v_restricted_program)
THEN NULL;
ELSE RAISE_APPLICATION_ERROR(-20001, 'You are not allowed to login using '|| v_program );
END IF;
CLOSE c2;
END;
/

I have taken your security issues on board and will mull them over.

Ken
Re: Logon Trigger Issue [message #184436 is a reply to message #184424] Wed, 26 July 2006 09:36 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I'm sorry, you seem to have missed the point of my code suggestions, and I strongly suspect that you haven't tested this piece of code.

This trigger you have written will only allow one user to log into the database, that user being the person whos name is returned first from the Exempt_Logins table.
That user will be allowed to log in from ANY program, except the program returned first from Restricted_Programs.

Try something along these lines
(untested code fragment)
CURSOR c1 IS
  SELECT username, program
  FROM   v$session 
  WHERE  audsid = userenv('sessionid');

CURSOR c2 (cp_program in  varchar2) IS
  SELECT count(*)
  FROM   RESTRICTED_PROGRAMS
  WHERE  program_name = cp_program;
  
CURSOR c3 (cp_user in  varchar2)IS
  SELECT count(*)
  FROM   EXEMPT_LOGINS
  WHERE  login_name = cp_user;
BEGIN
  OPEN c1;
  FETCH c1 INTO v_username, v_program;
  CLOSE c1;
  
  open c3 (v_username)
  fetch c3 into v_count;
  close c3;
  
  IF v_count > 0 then -- Username is on list of exempt users
    NULL;
  ELSE -- Need to check if they are logging in from a restricted program

    OPEN c2 (v_program);
    FETCH c2 INTO v_count;
    CLOSE c2;

    IF v_count = 0 then -- Program not on restricted list
      null;
    ELSE 
      RAISE_APPLICATION_ERROR(-20001, 'You are not allowed to login using '|| v_program );
    END IF;
  end if;


There are several performance enhancements still to make - replacing cursors with SELECT INTOs, changing the order of the neted cursors so that the most likely failures come first, but there'd be no fun in doing all the work....
Re: Logon Trigger Issue [message #184447 is a reply to message #184365] Wed, 26 July 2006 10:13 Go to previous messageGo to next message
KenJ
Messages: 69
Registered: July 2006
Location: London
Member
JRowbottom,

Many thanks for your suggestions. I will take what you have stated onboard and will post up the next effort.

You are correct in the assumption that the trigger was tested for one user. I was awaiting more information from the application team and was then going to implement into dev and test thoroughly. I won't do this now.

Thanks again,

Ken.
Re: Logon Trigger Issue [message #184691 is a reply to message #184365] Thu, 27 July 2006 08:29 Go to previous messageGo to next message
KenJ
Messages: 69
Registered: July 2006
Location: London
Member
JRowbottom,

I have rewritten the code to have an ALLOWED_PROGRAMS list. The entreies in thi list will not be known by the users and can therefore not be spoofed.

CREATE TABLE EXEMPT_LOGINS(
LOGIN_NAME VARCHAR2(64))
TABLESPACE DBA_ADMIN;

INSERT INTO EXEMPT_LOGINS VALUES ('USERNAME');

COMMIT;

CREATE TABLE ALLOWED_PROGRAMS(
PROGRAM_NAME VARCHAR2(64))
TABLESPACE DBA_ADMIN;

INSERT INTO RESTRICTED_PROGRAMS VALUES ('PROGRAM_NAME.exe');

COMMIT;

CREATE OR REPLACE TRIGGER "SYS"."LOGONAUDITING"
AFTER LOGON ON database
DECLARE
v_sid NUMBER(10);
v_serial NUMBER(10);
v_username VARCHAR2(64);
v_program VARCHAR2(64);
v_restricted_program VARCHAR2(64);
v_exempt_login VARCHAR2(30);
v_count number(2);
CURSOR c1 IS
SELECT username, program
FROM v$session
WHERE audsid = userenv('sessionid');
CURSOR c2 (cp_program in varchar2) IS
SELECT count(*)
FROM ALLOWED_PROGRAMS
WHERE program_name = cp_program;
CURSOR c3 (cp_user in varchar2)IS
SELECT count(*)
FROM EXEMPT_LOGINS
WHERE login_name = cp_user;
BEGIN
OPEN c1;
FETCH c1 INTO v_username, v_program;
CLOSE c1;
open c3 (v_username);
fetch c3 into v_count;
close c3;
IF v_count > 0 then -- Username is on list of exempt users
NULL;
ELSE -- Need to check if they are logging in from an allowed program
OPEN c2 (v_program);
FETCH c2 INTO v_count;
CLOSE c2;
IF v_count = 1 then -- Program on allowed list
null;
ELSE
RAISE_APPLICATION_ERROR(-20001, 'You are not allowed to login using '|| v_program );
END IF;
END IF;
END;


I believe that this is much more security conscious. What are your thoughts and thanks for your help.

Ken
Re: Logon Trigger Issue [message #184705 is a reply to message #184691] Thu, 27 July 2006 09:05 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
It's more secure, but if a user can connect to you database using application X, then if they rename sqlplusw.exe to X then they're still in..

But it's better.
Re: Logon Trigger Issue [message #184713 is a reply to message #184365] Thu, 27 July 2006 09:18 Go to previous messageGo to next message
KenJ
Messages: 69
Registered: July 2006
Location: London
Member
Yes but they will not know what to rename it to as the table ALLOWED_PROGRAMS will live in it's own locked schema.

Ken.
Re: Logon Trigger Issue [message #184719 is a reply to message #184713] Thu, 27 July 2006 09:45 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
They just have to look on their local disk, see what the application is called, and rename sqlplusw to that.
They know that the application they connect to the database with is allowed, so they just have to use that name.

This will only work for users allowed to connect to the database, but if a user isn't allowed to connect to the Db, they shouldn't have an account to start with.
Re: Logon Trigger Issue [message #184732 is a reply to message #184365] Thu, 27 July 2006 10:43 Go to previous messageGo to next message
KenJ
Messages: 69
Registered: July 2006
Location: London
Member
JRowbottom,

Agreed but they will not know the mechanism by which they are getting blocked so it would take some good guessing to start renaming executables to break into the db.

I do consider it as a security flaw but not too serious.

Ken.
Re: Logon Trigger Issue [message #184807 is a reply to message #184732] Fri, 28 July 2006 02:25 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Your error message ('You are not allowed to login using '|| v_program ) is a bit of a give away in my opinion Cool

There is quite a good thread on AskTom about this problem.

The best solution they hammer out is in the April 10 2005 post
Basically, you revoke everything from the user except create session and then use this process from the application
Quote:

***
Client connects to the database using a username and password creating a
session.
Client calls a server side function to obtain a key from the database server -
no parameters
Database responds '123,546,12Jan051735' -- This information happens to match the
session, serial#, logontime
Client uses a local function to transform the key and calculates
'127,503,,1JJa001144' -- an example transformation
Client request that key and tranformation be verified providing parameters
key='127,5036,12Jan051845', transkey = '127,503,,1JJa001144'
Database verifies that the key belongs to the session and that the
transformation is the expected one, it then enables a database role granting
access.
***

Trying to reproduce the conversation in another session will fail because the
key is derived from the database session and includes the logon time.

Using sqlplus it would be possible to call the get key function to obtain a key
but you would need to be able to do the transformation.

If instead, the sqlplus session called the procedure to verify the key and the
transformation directly (with information from a trace file) the key would not
match the sqlplus session.

The database and the application program need to have an identical
transformation function. i.e. They need to be able to modify the application


To validate that they are using the correct app before enabling the role they need. Add a logoff trigger to revoke the role and it looks pretty good.
Re: Logon Trigger Issue [message #211877 is a reply to message #184365] Tue, 02 January 2007 17:47 Go to previous messageGo to next message
luiscali
Messages: 1
Registered: July 2006
Location: Guatemala
Junior Member

I trying to use this issue with forms 6i, but the fields program or module has no value. I test with sqlplus or toad using the developer's home, but when i connect using the database home it's works.

The developer release of SQL*Plus: Release 8.0.6.0.0
The oracle SQL*Plus: Release 8.1.7.0.0

I have to install a patch to fix it?

Please help me... thanks
Re: Logon Trigger Issue [message #211900 is a reply to message #211877] Wed, 03 January 2007 00:50 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
luiscali wrote on Wed, 03 January 2007 00:47

The developer release of SQL*Plus: Release 8.0.6.0.0
The oracle SQL*Plus: Release 8.1.7.0.0



Cool! That's the way to go: 1) use one version to develop and another version for production and 2) make sure you use ancient versions for both.
Previous Topic: querying based on the string data in the column
Next Topic: Select daylight saving values
Goto Forum:
  


Current Time: Sat Dec 03 03:39:34 CST 2016

Total time taken to generate the page: 0.09536 seconds