CONNECT_TIME

I had to limit a SQL*Plus session’s connection time today while I was setting up a new environment so I decided to document how I did it. The example below was run on an Oracle 11.2.0.2.7 database. First I connected as SYS and limited CONNECT_TIME to 1 minute in the DEFAULT profile: 

SQL> conn / as sysdba

Connected.

SQL> alter profile default

  2  limit connect_time 1

  3  /

 

Profile altered.

 

SQL>

 

Then I set RESOURCE_LIMIT to TRUE so that limits would be enforced:

 

SQL> alter system set resource_limit = true

  2  /

 

System altered.

 

SQL>

 

I created a user and gave it the DEFAULT profile. Then I connected as that user, looked at CONNECT_TIME in USER_RESOURCE_LIMITS then checked the time at 10 second intervals:

 

SQL> create user andrew

  2  identified by reid

  3  profile default

  4  /

 

User created.

 

SQL> grant create session to andrew

  2  /

 

Grant succeeded.

 

SQL> conn andrew/reid

Connected.

SQL> select limit from user_resource_limits

  2  where resource_name = 'CONNECT_TIME'

  3  /

 

LIMIT

----------------------------------------

1

 

SQL> select to_char(sysdate,'hh24:mi:ss')

  2  time_now from dual

  3  /

 

TIME_NOW

--------

17:31:40

 

SQL> host sleep 10

 

SQL> select to_char(sysdate,'hh24:mi:ss')

  2  time_now from dual

  3  /

 

TIME_NOW

--------

17:31:50

 

SQL> host sleep 10

 

SQL> select to_char(sysdate,'hh24:mi:ss')

  2  time_now from dual

  3  /

 

TIME_NOW

--------

17:32:00

 

SQL> host sleep 10

 

SQL> select to_char(sysdate,'hh24:mi:ss')

  2  time_now from dual

  3  /

 

TIME_NOW

--------

17:32:10

 

SQL> host sleep 10

 

SQL> select to_char(sysdate,'hh24:mi:ss')

  2  time_now from dual

  3  /

 

TIME_NOW

--------

17:32:20

 

SQL> host sleep 10

 

SQL> select to_char(sysdate,'hh24:mi:ss')

  2  time_now from dual

  3  /

 

TIME_NOW

--------

17:32:30

 

SQL> host sleep 10

 

Once the session had been connected for 1 minute, the next SQL statement failed and the session was terminated with an ORA-02399:

 

SQL> select to_char(sysdate,'hh24:mi:ss')

  2  time_now from dual

  3  /

select to_char(sysdate,'hh24:mi:ss')

*

ERROR at line 1:

ORA-00604: error occurred at recursive SQL level 1

ORA-02399: exceeded maximum connect time, you are

being logged off

ORA-02399: exceeded maximum connect time, you are

being logged off

 

SQL>

 

… and a subsequent attempt to run the SQL showed that the session was no longer connected:

 

SQL> select to_char(sysdate,'hh24:mi:ss')

  2  time_now from dual

  3  /

select to_char(sysdate,'hh24:mi:ss')

*

ERROR at line 1:

ORA-01012: not logged on

Process ID: 884

Session ID: 61 Serial number: 2851

 

SQL>

Failed Login Attempts

(Tested on an Oracle 9.2.0.7.0 database.)

This can be used to limit the number of times a user can enter an incorrect password. First create a test user and show what profile he is using:

  1  grant create session to andrew

  2* identified by reid

SQL> /

Grant succeeded.

SQL> select profile from dba_users

  2  where username = 'ANDREW';

PROFILE

------------------------------

DEFAULT

SQL>

Then change that profile so that only two incorrect password attempts are allowed:

SQL> alter profile default

  2  limit failed_login_attempts 2;

Profile altered.

SQL>

To demonstrate the limit, the user must then try to login twice with the wrong password:

SQL> conn andrew/wrong_password

ERROR:

ORA-01017: invalid username/password; logon denied

Warning: You are no longer connected to ORACLE.

SQL> conn andrew/wrong_password

ERROR:

ORA-01017: invalid username/password; logon denied

SQL>

If he tries to login again he will see that the account is locked:

SQL> conn andrew/wrong_password

ERROR:

ORA-28000: the account is locked

SQL>

Note that this limit still applies even if resource_limit is set to false:

SQL> col value format a20

SQL> l

  1  select value from v$parameter

  2* where name = 'resource_limit'

SQL> /

VALUE

--------------------

FALSE

SQL>

Also note that the failed login attempts need to be consecutive. A successful login attempt sets the count of failed login attempts back to zero:

SQL> conn / as sysdba

Connected.

SQL> alter user andrew account unlock;

User altered.

SQL> conn andrew/wrong_password

ERROR:

ORA-01017: invalid username/password; logon denied

Warning: You are no longer connected to ORACLE.

SQL> conn andrew/reid

Connected.

SQL> conn andrew/wrong_password

ERROR:

ORA-01017: invalid username/password; logon denied

Warning: You are no longer connected to ORACLE.

SQL> conn andrew/reid

Connected.

SQL> conn andrew/wrong_password

ERROR:

ORA-01017: invalid username/password; logon denied

Warning: You are no longer connected to ORACLE.

SQL> conn andrew/reid

Connected.

SQL>