This discussion is archived
8 Replies Latest reply: Dec 13, 2012 10:38 AM by EdStevens RSS

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

user10744231 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Oracle ACE Director
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points