8 Replies Latest reply on Dec 13, 2012 6:38 PM by EdStevens

    Is it possible to restrict connect on sys account connecting as sysdba??

    user10744231
      We're trying to prevent users from logging into our database as sys user using TOAD. We can prevent database authenticated users from using TOAD, using the CONNECT command rule and a allow connect rule, but the rule doesn't work with sys, since sys is signed on using sysdba which uses OS authentication. We need to be able to access the database from other databases using the sys account, so we need the passwordfile. ANy help would be appreciated. Thank you...
        • 1. Re: Is it possible to restrict connect on sys account connecting as sysdba??
          mseberg
          Hello;

          I would test this, but you should be able to do this with a logon trigger.

          Sample/Example
          CREATE OR REPLACE TRIGGER
          block_tools_from_prod
            AFTER LOGON ON
          DATABASE
          DECLARE
            v_prog sys.v_$session.program%TYPE;
          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;  -- Parallel
          processes will have the same AUDSID's
           
            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, 'Development tools are not allowed here.');
            END IF;
          END;
          /
          SHOW ERRORS
          This has not been tested by me, note the don't block SYS line, you need to remove or change it.

          Pretty sure this will write to alert log.

          I would take away SYS from all but two users myself.


          Best Regards

          mseberg

          Edited by: mseberg on Dec 7, 2012 3:18 PM
          • 2. Re: Is it possible to restrict connect on sys account connecting as sysdba??
            EdStevens
            user10744231 wrote:
            We're trying to prevent users from logging into our database as sys user using TOAD. We can prevent database authenticated users from using TOAD, using the CONNECT command rule and a allow connect rule, but the rule doesn't work with sys, since sys is signed on using sysdba which uses OS authentication. We need to be able to access the database from other databases using the sys account, so we need the passwordfile. ANy help would be appreciated. Thank you...
            "We need to be able to access the database from other databases using the sys account

            Why? This sounds like a fundamental flaw in your model.
            • 3. Re: Is it possible to restrict connect on sys account connecting as sysdba??
              Srini Chavali-Oracle
              Change the password for sensitive accounts like SYS and SYSTEM and keep them confidential.

              HTH
              Srini
              • 5. Re: Is it possible to restrict connect on sys account connecting as sysdba??
                user10744231
                The primary database needs to connect to the physical standby database, and it does this thru the sys account.
                • 6. Re: Is it possible to restrict connect on sys account connecting as sysdba??
                  user10744231
                  Hello;

                  I would test this, but you should be able to do this with a logon trigger.

                  Sample/Example


                  CREATE OR REPLACE TRIGGER
                  block_tools_from_prod
                  AFTER LOGON ON
                  DATABASE
                  DECLARE
                  v_prog sys.v_$session.program%TYPE;
                  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; -- Parallel
                  processes will have the same AUDSID's

                  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, 'Development tools are not allowed here.');
                  END IF;
                  END;
                  /
                  SHOW ERRORS



                  This has not been tested by me, note the don't block SYS line, you need to remove or change it.

                  Pretty sure this will write to alert log.

                  I would take away SYS from all but two users myself.

                  Best Regards

                  mseberg

                  Edited by: mseberg on Dec 7, 2012 3:18 PM




                  The audit trigger doesn't work on OS authenticated logins (/ as sysdba). We tried the trigger method first, and then tried database vault.
                  • 7. Re: Is it possible to restrict connect on sys account connecting as sysdba??
                    user10744231
                    user10744231 wrote:
                    We're trying to prevent users from logging into our database as sys user using TOAD. We can prevent database authenticated users from using TOAD, using the CONNECT command rule and a allow connect rule, but the rule doesn't work with sys, since sys is signed on using sysdba which uses OS authentication. We need to be able to access the database from other databases using the sys account, so we need the passwordfile. ANy help would be appreciated. Thank you...

                    "We need to be able to access the database from other databases using the sys account

                    Why? This sounds like a fundamental flaw in your model.



                    The primary database needs to connect as sys to the physical standby database. That's the only reason we need to allow access from other databases using the sys account.
                    • 8. Re: Is it possible to restrict connect on sys account connecting as sysdba??
                      EdStevens
                      user10744231 wrote:
                      user10744231 wrote:
                      We're trying to prevent users from logging into our database as sys user using TOAD. We can prevent database authenticated users from using TOAD, using the CONNECT command rule and a allow connect rule, but the rule doesn't work with sys, since sys is signed on using sysdba which uses OS authentication. We need to be able to access the database from other databases using the sys account, so we need the passwordfile. ANy help would be appreciated. Thank you...

                      "We need to be able to access the database from other databases using the sys account

                      Why? This sounds like a fundamental flaw in your model.



                      The primary database needs to connect as sys to the physical standby database. That's the only reason we need to allow access from other databases using the sys account.
                      And why would the primary database need to connect to a physical standby via a dblink? Especially when one considers the physical standby isn't even open.

                      Still sounds like a fundamental design flaw.

                      Step back from your proposed solution and describe the *business problem* you are trying to solve.