Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1260
  • Last Modified:

Restrict login access in Oracle 10g?

I have a login used for an application that some team members also need to use via sqlplus. I'd like to restrict access for some of the team members. Is it possible to restrict access to login as a particular user based on the client ID logging in through sqlplus? Using Oracle 10g.
0
cdion
Asked:
cdion
  • 15
  • 8
3 Solutions
 
waseemqCommented:
See Metalink Note# 281229.1
How to Restrict Access to the Database With Specific Tools or Applications

Script to create the trigger under SYS user to forbid access by SQL*Plus:
   ------------------------------------------------------------------------

   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  program 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 user_rec.program IN ('sqlplus.exe')
        THEN
            RAISE_APPLICATION_ERROR(-20001, 'You are not allowed to login');
        END IF;
        CLOSE user_prog;
    END;
   /


HTH

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

Thanks.
0
 
waseemqCommented:
Yes you can use OSUSER instead of program.

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 user_rec.OSUSER IN ('abc_username')
        THEN
            RAISE_APPLICATION_ERROR(-20001, 'You are not allowed to login');
        END IF;
        CLOSE user_prog;
    END;
   /

0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
cdionAuthor Commented:
I'm receiving errors when trying to run that code. It says the declaration of user_prog is incomplete or malformed.
0
 
cdionAuthor Commented:
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
0
 
waseemqCommented:
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


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


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;
   /
0
 
cdionAuthor Commented:
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
0
 
cdionAuthor Commented:
If I run the cursor statement by itself it runs okay. but within the trigger sql, that's when it's failing
0
 
cdionAuthor Commented:
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.
0
 
cdionAuthor Commented:
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
0
 
cdionAuthor Commented:
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
0
 
waseemqCommented:
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;



0
 
waseemqCommented:
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;
0
 
cdionAuthor Commented:
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;
0
 
cdionAuthor Commented:
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.
0
 
waseemqCommented:

CREATE OR REPLACE TRIGGER logon
   AFTER LOGON
   ON DATABASE

BEGIN
      FOR user_rec IN (SELECT * FROM v$session WHERE upper(username) in ('USER1','USER2') )
          LOOP
                  RAISE_APPLICATION_ERROR(-20001, 'You are not allowed to login. Your session has been terminated.');
          END LOOP;
END logon;
0
 
cdionAuthor Commented:
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;
/
0
 
cdionAuthor Commented:
What's strange is that it works okay in my dev DB, but not in my Test DB. Same code.
0
 
cdionAuthor Commented:
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?
0
 
waseemqCommented:
Create exception section and add code there
0
 
cdionAuthor Commented:
Still working on this. Please don't close. Had trouble adding code to issue kill statement for session. Thanks.
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

  • 15
  • 8
Tackle projects and never again get stuck behind a technical roadblock.
Join Now