14 Replies Latest reply: Mar 21, 2013 11:54 PM by Billy~Verreynne RSS

    Source code

    969952
      Hi All,

      I have some procedures,Functions,Packages,triggers in different schemas.

      I would like to hide the source code of these procedures,Functions,Packages,triggers. Is there any script to hide the source code of these DB objects at once instead of doing one by one using "WRAP " for each object one by one.

      Could any one please suggest me if available or how can we approach to resolve this issue in another way?

      Thanks.
        • 1. Re: Source code
          Marwim
          Hello,

          why do you want to hide the source code? Wrapping the code is just a smal nuisance to someone who wants to see your code.

          The best way is to put your code in a schema and grant other users only execution rights on the procedures. Now make sure that the users cannot query dba_source. As long as the users don't know the password of the procedure schema they cannot look at your code.

          Regards
          Marcus
          • 2. Re: Source code
            BEDE
            You usually wrap package bodies. You don't wrap package specifications.
            Thus, to wrap the package body my_pkg.pkb, on the OS command prompt you issue:

            wrap iname= my_pkg.pkb oname= my_pkg_w.pkb

            you thus obtain my_pkg_w.pkb that is the wrapped source, that you will execute in sqlplus or your favourite PL/SQL development tool.
            • 3. Re: Source code
              969952
              Thanks for your quick response.

              As per the requirement we would like to hide the source codes. So that no one can have any idea the respective object is existed or not? if we move this objects into another schema and giving execution options then they can utilize the functionality. But we don't want to give them the Source code as well as any execute privileges.

              If we hide these codes then later we can retrieve the code whenever it is required.

              One more thing is : if we hide the source code in Development environment are the USERS can access the data ( export data from Production to DEV) then they will use it rite?

              I would like to stop that process as well. Is there any utility to follow which will satisfy the above two conditions? Please advise.

              Thanks.
              • 4. Re: Source code
                JustinCave
                966949 wrote:
                As per the requirement we would like to hide the source codes. So that no one can have any idea the respective object is existed or not? if we move this objects into another schema and giving execution options then they can utilize the functionality. But we don't want to give them the Source code as well as any execute privileges.
                I don't understand. It sounds like you are trying to configure things so that
                - Users have the ability to EXECUTE a particular piece of code
                - Users cannot determine whether that piece of code actually exists

                Those are logically mutually exclusive. Clearly, if I am able to execute a piece of code, I am able to determine that this piece of code exists.
                One more thing is : if we hide the source code in Development environment are the USERS can access the data ( export data from Production to DEV) then they will use it rite?
                I can't guess what you are trying to ask here.


                Justin
                • 5. Re: Source code
                  Marwim
                  But we don't want to give them the Source code
                  This makes no sense. You have to give them code, either wrapped or unwrapped. When the code is wrapped, then it cannot be read directly, but unwrapping the code is not really difficult. That's why I asked why you want to hide the code.

                  Your customer is usually not allowed to extract and use your code, because it is protected by IP regulations as long as the code is not part of the contract. If the customer does not care about prohibitions then it will not stop him from unwrapping your code.
                  as well as any execute privileges.
                  How is the customer supposed to use your code without execution privileges?

                  Regards
                  Marcus
                  • 6. Re: Source code
                    969952
                    Hi,

                    I am trying to hide the source which not visible or accessible by any user as of now. Code which have written already. so would like to keep hide the code and bring it back once requirement comes.


                    Thanks.
                    • 7. Re: Source code
                      JustinCave
                      OK. So the bit about hiding whether the object exists or not is irrelevant? And the second requirement you were trying to explain is also irrelevant?

                      As it appears that you're already aware, you can wrap the code. That doesn't stop someone from unwrapping it. If that's enough for you and you are just looking for ways to script the process, you can always use the dbms_ddl.create_wrapped procedure, pulling the code from the data dictionary. Of course, you'd need to ensure that you've already got clear-text versions of all your code in your source control repository. Otherwise, you won't be able to get back to the original source when you want to make a code change (well, short of grabbing an unwrapping utility and dealing with whatever gets lost in the wrapping and unwrapping process).

                      Justin
                      • 8. Re: Source code
                        Billy~Verreynne
                        966949 wrote:

                        I would like to hide the source code of these procedures,Functions,Packages,triggers. Is there any script to hide the source code of these DB objects at once instead of doing one by one using "WRAP " for each object one by one.

                        Could any one please suggest me if available or how can we approach to resolve this issue in another way?
                        I don't get the reason behind wanting to wrap Oracle PL/SQL code. What makes you think your code is so awesome, that it needs to be wrapped?

                        Let's say for argument sake that your code is indeed awesome - doing some real funky stuff using innovative concepts that no one else have yet thought off.

                        How would you want to protect that? By trying to obfuscate the code? Not a sensible choice. If you want to protect your innovation and your IP rights, it needs to be done via patents, copyrights and license agreements.

                        Let's say for argument sake you have secrets (like crypto keys) in code, and want to protect that. That is a basic design flaw (ask Sony about their LV0 keys). As trying to keep a secret, secret, is a fundamental problem. Especially when the secret needs to be shipped with code and data to users - where the code and data are used in the "wild". Environments that you never can control. Environments that fail to keep your secrets, secret.

                        As I said at the start - I have not yet seen any valid reasons for wanting to wrap/obfuscate PL/SQL code. As there are better legal methods, with penalties, that are far better suited at protecting your code in the wild.
                        • 9. Re: Source code
                          969952
                          DECLARE
                             v_procedure   VARCHAR2 (32767);
                          BEGIN
                             v_procedure :=
                                   'CREATE OR REPLACE PROCEDURE EMC.generate_032113 AS'
                          ||'BEGIN'
                          ||'DELETE' 
                          ||'FROM'
                          ||' notes_staging_031513;'
                          ||'   COMMIT;'
                          ||'INSERT INTO' 'notes_staging_031513'
                                  'SELECT' ||'*'|| 'FROM''note_staging_031513';
                          ||'COMMIT;'
                          ||'END generate_032113;'
                          Getting compilation errors. Can you please correct this. AM not able to find where exactly am doing mistake.

                          Thanks.
                          • 10. Re: Source code
                            JustinCave
                            I'm assuming at some point that you are trying to execute the DDL statement that you are building.

                            Step 1 would be to print out the statement that you create and look at it. That should show you where the syntax errors are. If it doesn't, manually try to execute the generated SQL statement and see what line the compilation errors are on.

                            The bits that jump out at me are the lack of whitespace in the generated string. You're concatenating together the AS, BEGIN, DELETE, and FROM keywords so that the statement becomes
                            CREATE OR REPLACE PROCEDURE EMC.generate_032113 ASBEGINDELETEFROM ...
                            Oracle has no idea that you intended that to be 4 separate keywords.

                            There may be other syntax errors as well.

                            Justin
                            • 11. Re: Source code
                              969952
                              Yes. You are correct am getting error exactly at "BEGINDELETEFROM" how can I mention those clauses in the code? please advise.
                              • 12. Re: Source code
                                SomeoneElse
                                   v_procedure :=
                                         'CREATE OR REPLACE PROCEDURE EMC.generate_032113 AS'
                                ||'BEGIN'
                                ||'DELETE' 
                                ||'FROM'
                                What on earth? Why would you create a stored procedure from inside an anonymous pl/sql block?
                                • 13. Re: Source code
                                  JustinCave
                                  966949 wrote:
                                  Yes. You are correct am getting error exactly at "BEGINDELETEFROM" how can I mention those clauses in the code? please advise.
                                  Include spaces in the string that you generate
                                  l_sql := 
                                    'CREATE OR REPLACE PROCEDURE procedure_name ' ||
                                    'AS ' ||
                                    'BEGIN ' ||
                                  Justin
                                  • 14. Re: Source code
                                    Billy~Verreynne
                                    966949 wrote:
                                    DECLARE
                                    v_procedure   VARCHAR2 (32767);
                                    BEGIN
                                    v_procedure :=
                                    'CREATE OR REPLACE PROCEDURE EMC.generate_032113 AS'
                                    ||'BEGIN'
                                    ||'DELETE' 
                                    ||'FROM'
                                    ||' notes_staging_031513;'
                                    ||'   COMMIT;'
                                    ||'INSERT INTO' 'notes_staging_031513'
                                    'SELECT' ||'*'|| 'FROM''note_staging_031513';
                                    ||'COMMIT;'
                                    ||'END generate_032113;'
                                    Getting compilation errors. Can you please correct this. AM not able to find where exactly am doing mistake.
                                    If you want to go down the silly bugger road of dynamic code, then use a code template, substitution variables, and a constant as the code template. And no concatenation.

                                    E.g.
                                    declare
                                      PROC_TEMPLATE constant varchar2(32767) :=
                                    'create or replace procedure $PROCNAME is
                                    begin
                                      delete from $TABLE1 where day < sysdate-7;
                                      .. more code using $ tokens as substitution variables..
                                    end;';
                                    
                                      procDDL varchar2(32767);
                                    begin
                                      procDDL := replace( PROC_TEMPLATE, '$PROCNAME', 'MyStupidDynamicProcedure' );
                                      procDDL := replace( procDDL, '$TABLE', 'FOO' );
                                      .. etc.. 
                                    
                                      execute immediate procDDL;
                                    end;
                                    Not using concatenation makes it easy to copy and paste the code template into SQL-Plus/SQL-Developer for testing. Using substitution variables increases the readability, maintainability and flexibility of the code.

                                    Using concatenation after concatenation, as your sample code does, is a flawed approach. As is the approach to dynamically creating procedures in this fashion.