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.
- Logon Triggers.
- Listener restrictions enforced by parameters
- Shutdown Listener/Listener port change than usual port
- 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.!!
No comments:
Post a Comment