Solved

Restrict login access in Oracle 10g?

Posted on 2009-05-19
25
1,246 Views
Last Modified: 2013-12-18
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
Comment
Question by:cdion
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 15
  • 8
25 Comments
 
LVL 1

Accepted Solution

by:
waseemq earned 500 total points
ID: 24426859
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
 

Author Comment

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

Thanks.
0
 
LVL 1

Assisted Solution

by:waseemq
waseemq earned 500 total points
ID: 24435704
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
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 

Author Comment

by:cdion
ID: 24650507
I'm receiving errors when trying to run that code. It says the declaration of user_prog is incomplete or malformed.
0
 

Author Comment

by:cdion
ID: 24650566
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
 
LVL 1

Expert Comment

by:waseemq
ID: 24650754
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
 

Author Comment

by:cdion
ID: 24650812
yeah, that's what I did, but I don't see what the syntax errors are. Everything looks okay to me.
0
 
LVL 1

Expert Comment

by:waseemq
ID: 24651572


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
 

Author Comment

by:cdion
ID: 24651877
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
 

Author Comment

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

Author Comment

by:cdion
ID: 24652448
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
 

Author Comment

by:cdion
ID: 24652460
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
 

Author Comment

by:cdion
ID: 24682751
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
 
LVL 1

Expert Comment

by:waseemq
ID: 24684290
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
 
LVL 1

Expert Comment

by:waseemq
ID: 24684321
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
 

Author Comment

by:cdion
ID: 24684644
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
 

Author Comment

by:cdion
ID: 24684681
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
 
LVL 1

Assisted Solution

by:waseemq
waseemq earned 500 total points
ID: 24685024

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
 

Author Comment

by:cdion
ID: 24746688
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
 

Author Comment

by:cdion
ID: 24746736
What's strange is that it works okay in my dev DB, but not in my Test DB. Same code.
0
 

Author Comment

by:cdion
ID: 24747044
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
 
LVL 1

Expert Comment

by:waseemq
ID: 24747124
Create exception section and add code there
0
 

Author Comment

by:cdion
ID: 25188419
Still working on this. Please don't close. Had trouble adding code to issue kill statement for session. Thanks.
0

Featured Post

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
Suggested Courses

626 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question