Restrict login access in Oracle 10g?

Are you pulling user_rec from a table? Is it possible for me to use the OS user instead of sqlplus.exe?

Thanks.

I'm receiving errors when trying to run that code. It says the declaration of user_prog is incomplete or malformed.

Also, even though it fails on creation, no one can log into the database. Instead users receive this error:
ORA-04098: trigger 'SYSTEM.ON_LOGON' is invalid and failed re-validation

Login as sysdba
-- try to recompile the trigger first
  SQL> ALTER TRIGGER on_logon COMPILE ;
  -- If recompilation fails or if you are still not able to connect, disable  the trigger

  SQL> ALTER TRIGGER on_logon DISABLE ;

And fix syntax errors

yeah, that's what I did, but I don't see what the syntax errors are. Everything looks okay to me.

CREATE OR REPLACE TRIGGER on_logon
   AFTER LOGON
   ON DATABASE
   DECLARE
    --Declare a cursor to find out the program
    --the user is connecting with.
    CURSOR user_prog IS
          SELECT  OSUSER FROM v$session  
          WHERE   audsid=sys_context('USERENV','SESSIONID');
   
    --Assign the cursor to a PL/SQL record.
    user_rec user_prog%ROWTYPE;
    BEGIN
        OPEN user_prog;
        FETCH user_prog INTO user_rec;
        IF upper(user_rec.OSUSER) = upper('abc_username')
        THEN
            RAISE_APPLICATION_ERROR(-20001, 'You are not allowed to login');
        END IF;
        CLOSE user_prog;
    END;
   /

It doesn't like something in the declaration. Still failing:

 PLS-00341: declaration of cursor 'USER_PROG' is incomplete or malformed
 PL/SQL: SQL Statement ignored
 PL/SQL: ORA-00942: table or view does not exist
 PL/SQL: Item ignored
 PL/SQL: SQL Statement ignored
 PLS-00320: the declaration of the type of this expression is incomplete or malformed
 PL/SQL: Statement ignored
 PLS-00320: the declaration of the type of this expression is incomplete or malformed

If I run the cursor statement by itself it runs okay. but within the trigger sql, that's when it's failing

Okay, I figured out why I was receiving those errors. I needed to explicitly grant select rights on the V$session view to system. Even though it already has select_catalog_role, but it seemed to create it now.

it created it, however, all users now receive this error when logging in:


ERROR:
ORA-00604: error occurred at recursive SQL level 1
ORA-01001: invalid cursor
ORA-06512: at line 10

I figured out why all users were receiving that error. I changed this section:
          SELECT  OSUSER FROM v$session  
          WHERE   audsid=sys_context('USERENV','SESSIONID');

to be           SELECT  OSUSER FROM v$session  
          WHERE   username in ('user1','user2');

Which are the application IDs that the team should not be logging in as. So, now all users can log in successfully, except anyone logging in with user1 or user2. Even though the cursor is supposed to be checking for those particular OS Ids:

user_rec user_prog%ROWTYPE;
BEGIN
FETCH user_prog INTO user_rec;
IF user_rec.osuser in ('osd1', 'osid2')
THEN
RAISE_APPLICATION_ERROR(-20001, 'You are not allowed to login. Your session has been terminated.');
END IF;
END logon;
/

When any user logs in with the 2 application IDs this error is received:
ERROR:
ORA-00604: error occurred at recursive SQL level 1
ORA-01001: invalid cursor
ORA-06512: at line 19

Try this (I have not got a chance to test it)

CREATE OR REPLACE TRIGGER logon
   AFTER LOGON
   ON DATABASE

BEGIN
      FOR user_rec IN (SELECT * FROM v$session WHERE username in ('user1','user2') )
          LOOP
            IF user_rec.osuser in ('osd1', 'osid2')
            THEN
                  RAISE_APPLICATION_ERROR(-20001, 'You are not allowed to login. Your session has been terminated.');
            END IF;
          END LOOP;
END logon;

Replace
IF user_rec.osuser in ('osd1', 'osid2')
with
IF user_rec.osuser in ('user1','user2')

or you can get rid of this if statement.
FOR user_rec IN (SELECT * FROM v$session WHERE username in ('user1','user2') )
          LOOP
                  RAISE_APPLICATION_ERROR(-20001, 'You are not allowed to login. Your session has been terminated.');
          END LOOP;

This one below worked, but what's weird is it works for when the user logs in as user1, but not as user2.

CREATE OR REPLACE TRIGGER logon
   AFTER LOGON
   ON DATABASE

BEGIN
      FOR user_rec IN (SELECT * FROM v$session WHERE username in ('user1','user2') )
          LOOP
            IF user_rec.osuser in ('osd1', 'osid2')
            THEN
                  RAISE_APPLICATION_ERROR(-20001, 'You are not allowed to login. Your session has been terminated.');
            END IF;
          END LOOP;
END logon;

Well, it did work. Only the user who I didn't want to login as that application ID wasn't able to. But then suddenly it stopped everyone from logging in again. Under any id.

That worked in a dev db. I implemented into test & it worked for a day, then suddenly it started kicking everyone out that tries to use those IDs. Here's my code:

CREATE OR REPLACE TRIGGER SYSTEM.LOGON
AFTER LOGON
ON DATABASE
BEGIN
      FOR user_rec IN (SELECT * FROM v$session WHERE osuser in ('user1', 'user2', 'user3' ,'user4') )
          LOOP
            IF user_rec.username in ('AppID1', 'AppID2')
            THEN
                  RAISE_APPLICATION_ERROR(-20001, 'You are not allowed to login as AppID1 or AppID2. Your session has been terminated.');
            END IF;
          END LOOP;
END logon;
/

What's strange is that it works okay in my dev DB, but not in my Test DB. Same code.

I see what happened. When those restricted users log in with the application ID, their session remains open on the DB side, although they are not able to continue their session. And for some reason that throws off the trigger to affect every other user logging in with the application ID. I'll have to add some code to disconnect their session. Can I add that after the application error is raised?

Create exception section and add code there

Still working on this. Please don't close. Had trouble adding code to issue kill statement for session. Thanks.