Skip to Main Content
  • Questions
  • Limit client connectivity sessions using trigger

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Srirams.

Asked: January 10, 2017 - 3:46 pm UTC

Last updated: January 12, 2017 - 1:23 am UTC

Version: 12.1.0.2.0

Viewed 1000+ times

You Asked

Hi,

I need to limit/allow client connectivity sessions (ie: toad) connecting to database.

My synario: user should not have more than 2 toad connections to database. I need to restrict the client connectivity.
Please let me know is this possible to limit session using trigger?

Thanks,

and Connor said...

You *could* do something using a trigger, but it can be subverted without too much trouble, eg here's one to get you started

CREATE OR REPLACE TRIGGER SYS.SECURE__ACCESS
after logon on database
declare
  l_program v$session.program%type;
  l_osuser  v$session.osuser%type;
  l_machine v$session.machine%type;
  l_user    varchar2(30) := user;

  l_instance v$instance.instance_name%type := sys_context('USERENV','INSTANCE_NAME');

begin
    if l_user = 'MY_USER' then

      select osuser, machine, program
      into   l_osuser, l_machine, l_program
      from   v$session
      where  sid = sys_context('USERENV','SID');

      if ( lower(l_program) like '%toad%' ) 
         and lower(l_machine) not like '%special%'  
      then

        select count(*)
        into   l_is_dba
        from   dba_role_privs
        where  grantee = upper(l_osuser)
        and    granted_role = 'DBA';

        if l_is_dba = 0 then
          raise_application_error(-20999,'This account is limited etc etc');
        end if;
      end if;

    end if;

exception
  when no_data_found then null;
  when too_many_rows then null;
end;
/




but of course a savvy user will rename their toad.exe to something else.

I've seen solutions more complex than this, namely, submit a job on login which will then look for the typical open cursors that TOAD will have, and then kill that session...but that seems overkill

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

A reader, January 11, 2017 - 10:32 am UTC

Hi Connor,

Thanks for the information.

Thanks,

How about a PROFILE like this

Rajeshwaran Jeyabal, January 11, 2017 - 2:31 pm UTC

rajesh@ORA12C> create profile temp_profile limit
  2  sessions_per_user 2;

Profile created.

rajesh@ORA12C> alter user demo profile temp_profile;

User altered.

rajesh@ORA12C> conn demo/demo@ora12c
Connected.
demo@ORA12C>


with a second sql*plus connect to "demo" like this.

C:\Users\179818>sqlplus demo/demo@ora12c

SQL*Plus: Release 12.1.0.2.0 Production on Wed Jan 11 19:53:14 2017

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Last Successful login time: Wed Jan 11 2017 19:53:03 +05:30

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

demo@ORA12C>


with a third sql*plus connect to "demo" error ed like this.

C:\Users\179818>sqlplus /nolog

SQL*Plus: Release 12.1.0.2.0 Production on Wed Jan 11 19:53:20 2017

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

idle> conn demo/demo@ora12c
ERROR:
ORA-02391: exceeded simultaneous SESSIONS_PER_USER limit


idle>

Connor McDonald
January 12, 2017 - 1:23 am UTC

(I think) the questioner wanted to restrict only people using TOAD, not sessions in general.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library