4 Replies Latest reply: Oct 3, 2012 1:31 PM by mtefft RSS

    Procedures/packages security. Hide source code?

    David de Vega
      Hi.

      I was wondering if it is possible to hide the source code of a procedure for a user or avoid him to recreate it, but let him to execute it.

      Im doing a sandbox procedure who edit and run an external job. It works fine but I see a bunch of security holes. If any DB user who can execute that procedure can edit it too, they could run any shell command by changing the external job attributes. I could use credentials (11.2.0.2) and somehow limit the system user to specific commands but I want to fix the security hole by limiting the DB user instead limiting system user.

      This is the external job
      BEGIN
      DBMS_SCHEDULER.CREATE_JOB(
      job_name => 'test',
      job_type => 'EXECUTABLE',
      job_action => '/bin/sh',
      number_of_arguments => 2
      );
      
      DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE (
      job_name => 'test',
      argument_position => 1,
      argument_value => '-c'
      );
      
      DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE (
      job_name => 'test',
      argument_position => 2,
      argument_value => '/bin/date>>/tmp/date.log'
      );
      END;
      / 
      And then, the procedure.
      CREATE OR REPLACE PROCEDURE TEST_DATE_FILE ( new_file IN varchar2 )
      IS BEGIN
      DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE (
      job_name => 'test',
      argument_position => 2,
      argument_value => '/bin/date>>/tmp/' || new_file
      );
      
      DBMS_SCHEDULER.RUN_JOB(
                                      job_name =>             'test',
                                      use_current_session =>  TRUE
                                      );
      END;
      /
      --
      SQL> conn scott/tiger
      Conectado.
      SQL> exec TEST_DATE_FILE('newfile.log');

      Procedimiento PL/SQL terminado correctamente.

      SQL> CREATE OR REPLACE PROCEDURE SYS.TEST_DATE_FILE ( new_file IN varchar2 )
      2 IS BEGIN
      3 DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE (
      4 job_name => 'test',
      5 argument_position => 2,
      6 argument_value => '/bin/rm -rf /tmp/*'
      7 );
      8
      9 DBMS_SCHEDULER.RUN_JOB(
      10 job_name => 'test',
      11 use_current_session => TRUE
      12 );
      13 END;
      14 /

      Procedimiento creado.

      SQL> exec TEST_DATE_FILE ('');

      Procedimiento PL/SQL terminado correctamente.

      --

      It is possible to hide the source code of the procedure who edits and run the external procedure?
      There is any workaround or any idea of how can I prevent this?

      Regards

      Edited by: elvegaa_esp on 17-may-2012 2:41

      Edited by: elvegaa_esp on 17-may-2012 2:43
        • 1. Re: Procedures/packages security. Hide source code?
          693860
          Hi,

          You can wrap your source code. See this link for info : http://docs.oracle.com/cd/B28359_01/appdev.111/b28370/wrap.htm

          Eric
          • 2. Re: Procedures/packages security. Hide source code?
            HuaMin Chen
            ops$tkyte@ORA817DEV.US.ORACLE.COM> host wrap iname=test.sql oname=test_wrap.sql

            PL/SQL Wrapper: Release 8.1.7.2.0 - Production on Mon Jan 07 12:44:21 2002
            Copyright (c) Oracle Corporation 1993, 2000. All Rights Reserved.
            Processing test.sql to test_wrap.sql

            ops$tkyte@ORA817DEV.US.ORACLE.COM> @test_wrap.sql
            ops$tkyte@ORA817DEV.US.ORACLE.COM> create or replace procedure p wrapped
            2 0
            3 abcd
            4 abcd
            5 abcd
            6 abcd
            7 abcd
            8 abcd
            9 abcd
            10 abcd
            11 abcd
            12 abcd
            13 abcd
            14 abcd
            15 abcd
            16 abcd
            17 abcd
            18 3
            19 7
            20 8106000
            21 1
            22 4
            23 0
            24 4
            25 2 :e:
            26 1P:
            27 1DBMS_OUTPUT:
            28 1PUT_LINE:
            29 1Hello world:
            30 0
            31
            ...
            86 /

            Procedure created.

            and now you don't

            ops$tkyte@ORA817DEV.US.ORACLE.COM> select text from user_source where name = 'P';

            TEXT
            ----------------------------------------------------------------------------------------------------
            -------------------------------
            procedure p wrapped
            0
            abcd
            abcd
            abcd
            abcd
            abcd
            abcd
            abcd
            abcd
            abcd
            abcd
            abcd
            abcd
            abcd
            abcd
            abcd
            3
            7
            8106000
            1
            4
            0
            4
            2 :e:
            1P:
            1DBMS_OUTPUT:
            1PUT_LINE:
            1Hello world:
            0
            ...


            ops$tkyte@ORA817DEV.US.ORACLE.COM>

            Edited by: HuaMin Chen on May 29, 2012 11:26 AM
            • 3. Re: Procedures/packages security. Hide source code?
              David de Vega
              Thanks for your help.
              Regards
              • 4. Re: Procedures/packages security. Hide source code?
                mtefft
                You are not really securing this.

                1. Do not grant EXECUTE on DBMS_SCHEDULER to PUBLIC or to your users.
                2. GRANT EXECUTE ON DBMS_SCHEDULER TO [the schema that owns your procedure]
                3. GRANT EXECUTE ON [your procedure] TO[whoever needs it]

                Now it should not matter if they can see the code or not. They can't write a procedure that calls DBMS_SCHEDULER.