Q
Problem solve Get help with specific problems with your technologies, process and projects.

Trigger to restrict user after a certain number of logons

I want to create a trigger that restricts a user's logon after the number of sessions reaches a point. If, let's say my max number of sessions allowed is 20 sessions and user 'SCOTT' has 14 sessions already, then whenever SCOTT tries to logon into database (or create a new session), the trigger should check the most obsolete session created by SCOTT and kill it so that SCOTT can continue to log on. I have created a sample trigger, however it doesn't meet my expectation. Please find the sample script as below:

 
 CREATE OR REPLACE TRIGGER my_after_logon
    AFTER LOGON ON DATABASE
 DECLARE
    usernm   VARCHAR2(50);
    sid      NUMBER;
    serial   NUMBER;
    v_count  NUMBER;
 BEGIN
    -- Get username
    SELECT user INTO usernm FROM dual;
    -- Get number of current sessions
    SELECT count(*) into v_count FROM v$session;
    IF v_count > 4 and usernm='SCOTT' THEN
        -- Get the obsolete session and
        -- Get SID and SERIAL# of the obsolete
 session
        SELECT sid,serial# INTO sid,serial from
 v$session
           WHERE username=usernm and
 to_char(logon_time,'dd-mon-yyyy hh24:mi:ss')=
                 (select
 min(to_char(logon_time,'dd-mon-yyyy hh24:mi:ss'))
 from v$session where username='DSS')
 ;
              --IF usernm='SCOTT' THEN
                 EXECUTE IMMEDIATE 'ALTER SYSTEM KILL
 SESSION '''||sid||','||serial||'''';
              --END IF;
    END IF;
 END;
 /
 
I created the trigger in SYS account. Could you please advise me on how the trigger should be?

Your code has "where username='DSS'". Why the DSS user? Shouldn't this user be SCOTT? That is your most likely error in the logic.

For More Information


Dig Deeper on Oracle database design and architecture

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close