Restricting Users During Maintenance Window.!!

Hi Everyone!!

In this article, will narrate few options that we can use to block users to get into databases while DBAs work on maintenance tasks. Allowing users might get into locking/contention issues that might slow down the maintenance as well users dissatisfaction issues during to performance.

1. Logon Triggers. 
2. Listener restrictions enforced by parameters
3. Shutdown Listener/Listener port change than usual port
4. Restricted Session Privilege

Option 1.  Beauty of Logon Triggers

Logon triggers can be effectively used to perform this task beautifully. You can either restrict at whole DB level or individual user level(multiple triggers). Sample codes are given below. 

CREATE OR REPLACE TRIGGER "SYS"."XX_MAINT_AFTERLOGON" 

AFTER LOGON on DATABASE

BEGIN

IF (SYS_CONTEXT ('USERENV', 'SERVER_HOST') NOT in ('eecql0113gs8.qadomain.com','plaxoemd01.domain.com','plxdoemd01.domain.com' ))

then

RAISE_APPLICATION_ERROR

(-20001, 'You are not allowed to login now, Maintenance ends 3:30 AM!!!'|| SYS_CONTEXT ('USERENV', 'SERVER_HOST'));

END IF;

END;

/

Change OPS$USERX with the username that you want to block,

 CREATE OR REPLACE TRIGGER "SYS"."XX_USERX_AFTERLOGON" 

AFTER LOGON on OPS$USERX.SCHEMA

BEGIN

IF (SYS_CONTEXT ('USERENV', 'SERVER_HOST') NOT in ('ip-10-11-24-29') )

then

RAISE_APPLICATION_ERROR

(-20001, 'You are not allowed to logon from this Host, Use DR DB!!!'|| SYS_CONTEXT ('USERENV', 'SERVER_HOST'));

END IF;

END;

/

Option 2: Listener restrictions enforced by parameters

You can use listeners sqlnet.ora parameter. You must put all invited nodes in one line; likewise for excluded nodes.You should always enter localhost as an invited node.

TCP.INVITED_NODES takes precedence over the TCP.EXCLUDED_NODES

Update $ORACLE_HOME/NETWORK/ADMIN/SQLNET.ORA with below parameters and restart the listener.

tcp.validnode_checking = YES

tcp.invited_nodes = (localhost, plxdoemv01, 10.122.238.44) #{list of IP addresses}

tcp.excluded_nodes = (10.130.28.64)  #{list of IP addresses}

Option 3: Shutdown Listener/Listener port change than usual port

If maintenance task can be done on the server without listener up for remote connections, that can be safely used as well.  Or other option would be reconfigure the listener to 1522 from 1521 temporarily on listener.ora file and restarting will be another choice, you might have to add corresponding tnsnames.ora 

Option 4: Restricted Session privilege

You can use simple restrict session privilege method to achieve the same. 

SQL> alter system enable restricted session ;

SQL> select logins from v$instance ;

LOGINS

----------

RESTRICTED

SQL>

SQL> alter system disable restricted session ;

once DB is under restricted session mode, none can login other than some who has restricted privilege. 

Syntax to grant the user restricted session privilege. 

SQL>  grant restricted session to user1;    -- to grant

SQL> revoke restricted session from user1;  -- to revoke

Hope this helps someone.!!