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
          mseberg

          Hello;

           

          This works on my test system

           

          CREATE OR REPLACE TRIGGER block_tools_from_primary

            AFTER LOGON ON DATABASE

          DECLARE

            v_prog sys.v_$session.program%TYPE;

            V_ROLE VARCHAR(30);

          BEGIN

            SELECT program INTO v_prog

            FROM sys.v_$session

            WHERE audsid = USERENV('SESSIONID')

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

            AND ROWNUM = 1; 

            

            SELECT DATABASE_ROLE INTO V_ROLE FROM V$DATABASE; 

           

            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

            THEN

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

            END IF;

            END IF;

          END;

          /

          SHOW ERRORS

           

          You would have to change to match your tool list.

           

          Best Regards

           

          mseberg

          • 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
              mseberg

              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:\Users\hemant>cd\wor*

                C:\working_monitoring>echo %PATH%
                C:\Users\hemant\product\11.2.0\client_1\bin;....etc

                C:\working_monitoring>cd\users\hemant\product\11.2.0\client_1\bin

                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.

                C:\Users\hemant\product\11.2.0\client_1\BIN>cd\working_monitoring

                C:\working_monitoring>myclient

                SQL*Plus: Release 11.2.0.1.0 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 11.2.0.3.0 - 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);

                PROGRAM
                ------------------------------------------------
                myclient.exe

                1 row selected.

                SQL>

                 

                 

                Hemant K Chitale

                • 5. Re: Restrict logins to standby
                  mseberg

                  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

                   

                  mseberg

                   

                  Message was edited by: mseberg