9 Replies Latest reply: Feb 17, 2014 10:19 PM by 933257 RSS

    Restricting access to PROD but not DR.

    933257

      I am using Release 11.2.0.3.0 Production. I have below questions.

       

      1. Prod DB is frequently queried by Developers and QA guys with resource intensive queries resulting into production down? So we are thinking of restricting the access of those users from prod and only give access to functional users. And for the developer/qa guys , provide them access to DR(stand by).So how can i restrict the user access to prod but not to DR(Copy of Prod), how can this be achieved? Or any other way to achive this?

       

      2. Our prod DBAs, complaining that they are facing some issue(mighyt be performance related) due to sessions connected for long hours with Inactive state from developers/qa people. But my understanding was, it will simply eat some memory(very less) but wont put any CPU load, so need expert advice , how Inactive session could cause issue?

        • 1. Re: Restricting access to PROD but not DR.
          sb92075

          1) Firewall

          2) replace prod DBAs or ask them for SQL & results which shows their  position is correct & valid

          • 2. Re: Restricting access to PROD but not DR.
            933257

            Thanks.
            can you please elaborate ,how can i setup firewall for blocking the nonfunctional users to access prod but not DR.

            Also Is there some simple technique to block the access of specific set of non functional users depending on service name(PROD/DR) or server details, using some
            trigger etc.

            • 3. Re: Restricting access to PROD but not DR.
              sb92075

              933257 wrote:

               

              Thanks.
              can you please elaborate ,how can i setup firewall for blocking the nonfunctional users to access prod but not DR.

              Also Is there some simple technique to block the access of specific set of non functional users depending on service name(PROD/DR) or server details, using some
              trigger etc.

              >how can i setup firewall for blocking the nonfunctional users to access prod but not DR

              talk to the Firewall admin who hopefully has more of a clue than you do.

               

               

              I ASSUME that the DR instance is a carbon copy of the production database. RIGHT?

              so how or why do you expect them to contain different objects & metadata & to behave differently?

              Please join the real world at your earliest convenience.

              • 4. Re: Restricting access to PROD but not DR.
                cleavitt

                Personally, I think you should look into...

                 

                1) Why are your developers routinely writing queries so poorly that they crash the DB instance?  Your developers might need some training on database concepts and without it they will continue to write a lot of bad code which is a problem for everyone.

                2) Why is the DB instance crashing instead of telling the user that they are using too much of a given resource?  You may want to look into setting max limits on the size of the Temp and Undo tablespaces and also look into "resource groups" and "resource plans" in the Oracle documentation to limit your dev users resource usage.

                 

                You could write a login trigger to keep developers from logging in to PROD, but as sb points out, that login trigger will be copied to your DR instance as well.  If you go this route, the login trigger will need to include logic to check the hostname or instance name and only allow the developer users to connect it it's not PROD.  You could also code it to only apply to users that belong to a "developers" role so that it's less maintenance when developers are hired or leave.  The logic in a login trigger needs to be bulletproof so keep it simple and test extensively.

                • 5. Re: Restricting access to PROD but not DR.
                  933257

                  Thanks for the response.

                   

                  I was looking for the logon trigger alternative, My database is two node RAC, for logon trigger implementation i do have the OS users with me but need to derive the host name of the DB ,i was tryin to query the service_name of gv$session, but its showing sometimes value as 'SYS$USERS' and some times the actual host name, so how to get the actual host name always without fail? Also i am expecting the osuer as the individual USERID, but few cases, i am seing the OSUERS value a 'oracle' for invidula user login.

                  • 6. Re: Restricting access to PROD but not DR.
                    sb92075

                    933257 wrote:

                     

                    Thanks for the response.

                     

                    I was looking for the logon trigger alternative, My database is two node RAC, for logon trigger implementation i do have the OS users with me but need to derive the host name of the DB ,i was tryin to query the service_name of gv$session, but its showing sometimes value as 'SYS$USERS' and some times the actual host name, so how to get the actual host name always without fail?

                     

                    I contend you are diffing yourself a deep hole so let me assist with a shovel full or so.

                     

                    select machine from v$session where sid = 1;

                    • 7. Re: Restricting access to PROD but not DR.
                      933257

                      This query will give the machine name which is Clients m/c but not the DB host mahine which we need for the trigger.

                      • 8. Re: Restricting access to PROD but not DR.
                        sb92075

                        933257 wrote:

                         

                        This query will give the machine name which is Clients m/c but not the DB host mahine which we need for the trigger.

                         

                        I am sorry that your clue locker is 100% EMPTY!

                        • 9. Re: Restricting access to PROD but not DR.
                          rp0428

                          Use the SERVER_HOST environment variable.

                           

                          See SYS_CONTEXT in the SQL Language doc.

                          http://docs.oracle.com/cd/E11882_01/server.112/e26088/functions184.htm

                          The host name of the machine on which the instance is running.

                           

                          SELECT SYS_CONTEXT('USERENV', 'SERVER_HOST')   FROM DUAL;