5 Replies Latest reply on Sep 19, 2013 1:15 PM by mseberg

    Restrict logins to standby

    Catfive Lander

      11gR2, Linux, 2-node RAC replicating to a 2-node active data guard. On the standby site, we have created an 'reports' service, and we have a tnsnames entry which directs anyone connecting @reports to the standby site first, as per Oracle's maximum availability architecture documentation.


      Other users wanting to insert/update/delete live data (or, occasionally, query little bits of live data) connect to various other services which run preferentially on the primary site.


      My question is, what can I do to thwart a determined user who's smart enough to know how to craft a tnsnames.ora that directs to the primary site and who wants to run his reports there?


      The obvious concern is a "reporter" running a long multi-hour, temp-thrashing report on what's supposed to be the primary, production database. He obviously has the relevant select privileges in both the primary and the standby; we just don't want him exercising them on the primary, usually.


      I thought of a number of things:


      1. Create a profile, attached to the user, which says 'if estimated execution time > 30secs AND you're instance role is 'primary', then don't execute'. But I don't think you can do instance-role aware profiles like that.

      2. An after-logon trigger that says 'if username is X and instance role is primary, kick the user off', but don't like the idea of maintaining a list (or table) of users who are only allowed to connect to the standby

      3. OID-managed connections, so the REPORTS alias is defined correctly to go to standby first (but still wouldn't help thwart someone determined enough to edit their sqlnet.ora and craft their own tnsnames.ora)

      4. Something to do with Resource Manager... but we don't currently use it, and I wouldn't know where to begin


      General thoughts on what the best sort of approach would be?

        • 1. Re: Restrict logins to standby



          This works on my test system


          CREATE OR REPLACE TRIGGER block_tools_from_primary



            v_prog sys.v_$session.program%TYPE;

            V_ROLE VARCHAR(30);


            SELECT program INTO v_prog

            FROM sys.v_$session

            WHERE audsid = USERENV('SESSIONID')

            AND audsid != 0 -- Don't Check SYS Connections

            AND ROWNUM = 1; 




            IF V_ROLE = 'PRIMARY' THEN


            IF UPPER(v_prog) LIKE '%TOAD%' OR UPPER(v_prog) LIKE '%T.O.A.D%' OR -- Toad

            UPPER(v_prog) LIKE '%SQLNAV%' OR -- SQL Navigator

            UPPER(v_prog) LIKE '%PLSQLDEV%' OR -- PLSQL Developer

            UPPER(v_prog) LIKE '%BUSOBJ%' OR -- Business Objects

            UPPER(v_prog) LIKE '%EXCEL%' -- MS-Excel plug-in


            RAISE_APPLICATION_ERROR(-20000, 'These tools are not allowed here.');

            END IF;

            END IF;



          SHOW ERRORS


          You would have to change to match your tool list.


          Best Regards



          • 2. Re: Restrict logins to standby
            Hemant K Chitale

            >  IF UPPER(v_prog) LIKE


            However, it is known that a determined user can rename his program and avoid the trigger.  (You didn't include sqlplus in your list ?)



            Hemant K Chitale

            • 3. Re: Restrict logins to standby

              Are you making fun of me sir?

              • 4. Re: Restrict logins to standby
                Hemant K Chitale

                No.  It is very easy to rename -- for example sqlplus.exe to myclient.exe  on windows -- and login.  V$SESSION populates the session's PROGRAM as "myclient.exe" instead of sqlplus.exe


                See :



                C:\working_monitoring>echo %PATH%


                C:\Users\hemant\product\11.2.0\client_1\BIN>copy sqlplus.exe myclient.exe
                Access is denied.
                        0 file(s) copied.

                C:\Users\hemant\product\11.2.0\client_1\BIN>copy sqlplus.exe c:\working_monitoring\myclient.exe
                        1 file(s) copied.



                SQL*Plus: Release Production on Thu Sep 19 10:33:31 2013

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

                Enter user-name: hemant@myremotedb
                Enter password:

                Connected to:
                Oracle Database 11g Enterprise Edition Release - 64bit Production
                With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
                Data Mining and Real Application Testing options

                SQL> select program from v$session where sid=select distinct sid from v$mystat;
                select program from v$session where sid=select distinct sid from v$mystat
                ERROR at line 1:
                ORA-00936: missing expression

                SQL> select program from v$session where sid=(select distinct sid from v$mystat);


                1 row selected.




                Hemant K Chitale

                • 5. Re: Restrict logins to standby

                  Point taken. But you could counter by changing the block to only allow certain names of programs. IF UPPER(v_prog) NOT LIKE ...The determined user would have a much harder time. And if they still don't behave you can add a block to trap for that user along with the program they try to run. In any event its an idea.


                  Also rename does not seem to change the module


                  set linesize 200


                  select program, module from v$session where username = 'BIGSHOW';


                  PROGRAM MODULE 

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

                  myclient.exe SQL*Plus 

                  1 row selected.



                  At some point this becomes a cat and mouse game. I did try using a CONSUMER_GROUP and v$session, but it does not seem to work.


                  Best Regards




                  Message was edited by: mseberg