Solved

Restrict login access in Oracle 10g?

Posted on 2009-05-19
25
1,227 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
  • 15
  • 8
25 Comments
 
LVL 1

Accepted Solution

by:
waseemq earned 500 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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
 

Author Comment

by:cdion
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility


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
Comment Utility
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
Comment Utility
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
Comment Utility
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:cdion
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility

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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Create exception section and add code there
0
 

Author Comment

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

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

762 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

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now