Oracle Stored Procedure Encryption

Document directory

• 2.1 wrap
• 2.2 create_wrapped

Oracle Stored Procedure Encryption

The business secrets we include in Oracle stored procedures are sometimes unwilling to be seen by third-party personnel. They can be achieved through encryption of stored procedures. There are two methods to encrypt the stored procedure: Use the wrap tool or dbms_ddl package. Next we will introduce it in detail.   1. Wrap Tool WRAP is an operating system-level command provided by Oracle. The syntax is as follows: Wrap INAME =Input_file[Oname =Output_file] The parameter INAME is the name of the file to be encrypted, and the oname is the name of the encrypted file. If oname is omitted, an encrypted file name with the same name is automatically generated and suffixed with PLB. Let's demonstrate the usage of the wrap tool. First, create a file named test1. SQL: Create or replace function get_date_string return varchar2 Begin Return to_char (sysdate, 'dd-MON-YYYY '); End get_date_string; / It is saved in the root directory of drive D. Now I will encrypt it: D:/> dir test * the volume in drive D is the serial number of volume D, which is 15c2-d261   D:/directory 138 test1. SQL 1 file, 138 bytes 0 directories, 2,052,046,848 available bytes D:/> wrap INAME = D:/test1. SQL PL/SQL wrapper: Release 10.2.0.1.0-production on Saturday December 26 16:36:01 2009 Copyright (c) 1993,200 4, Oracle. All rights reserved. Processing D:/test1. SQL to test1.plb   D:/> dir test * The volume in drive D is d The serial number of the volume is 15c2-d261 D:/directory 350 test1.plb 138 test1. SQL 2 files in 488 bytes 0 directories, 2,052,046,848 available bytes Check the encrypted file test1.plb: Create or replace function get_date_string wrapped A000000 354 ABCD ABCD ABCD ABCD ABCD ABCD ABCD ABCD ABCD ABCD ABCD ABCD ABCD ABCD ABCD 8 71 AE P29rdhrzx0oro0ed/mmf8i12glkwg8ezgcflcnl + xlquyvsuovah8jbrppdhdornwlk9spte 58d0wdo4dgujuhslwmay/tkgcamhas7g1hohro/wthaectkod0xx9rbzc/xvn2dm6 + zzpxlp R1uqfbwu/sx2010pwujxpqzcvywg   / Now you can send the test1.plb file to the customer without worrying about the exposure of your source code. This tool is easy to use and convenient, especially for generating encrypted files in the stored procedure in batches. 2. dbms_ddl package From Oracle 10g Release 2, you can use the dbms_ddl package to dynamically encrypt the stored procedure. If you only want to encrypt the written source code, you can use the dbms_ddl.wrap function just like the wrap tool we mentioned earlier. If you want to encrypt the source code and create it in the database, then you can use dbms_ddl.create_wrapped. 2.1 wrap If you do not want to create this process, but want to obtain the encrypted creation script and send it to the client for execution, you can use the dbms_ddl.wrap function. It has the same functions as the "Wrap" tool. Let's take a look at the definition of this function: Function wrap (DDL varchar2) return varchar2; function wrap (DDL dbms_ SQL .varchar2s, LB pls_integer, UB pls_integer) return dbms_ SQL .varchar2s;   Function wrap (DDL dbms_ SQL .varchar2a, LB pls_integer, UB pls_integer) Return dbms_ SQL .varchar2a;   It is overloaded three times: the first function receives input of the varchar2 type, and the last two functions receive input of the varchar2 set (large DDL statement input is allowed ). The DDL syntax of the input parameter must be "create or replace ..." The DDL statement used to create a package, package, type, type, function, and process unit. If the program unit defined by the input parameter DDL cannot be encrypted or has a syntax error, a "malformed_wrap_input" exception is thrown. LB is the lowest element in the encryption set, and UB is the highest element in the encryption set. The returned value is the encrypted code. You can write it into a file or store it in a table. We first use the first wrap function to implement a simple application. I want to obtain the following encrypted code:   Create or replace procedure p2asbegin   Dbms_output.put_line ('yuechaotia '); End;   Use the wrap function to implement it: SQL> select dbms_ddl.wrap ('create or replace procedure P2 as begin dbms_output.put_line ("yuechaotian"); end; ') 2 from dual;   Dbms_ddl.wrap ('createorreplaceprocedurep2asbegindbms _ output. put_line ("yuechaotian"); end ;') -------------------------   Create or replace procedure P2 wrapped A000000 354 ABCD ABCD ABCD ABCD ABCD ABCD ABCD ABCD ABCD ABCD ABCD ABCD ABCD ABCD ABCD 7 41 7d Nd2ti3isthvviemtwiohec7rmcmwg5nnm7 + fmr2ywfwwfam4dcu4wdl + 0l64upuysv4osr3n Srmdbjasritqsogxjxddx9vk8ufbejiu9ktqjb/2rdmmf3htza =   The generated ciphertext can be copied directly and executed in the database:   SQL> Create or replace procedure P2 wrapped 2 a000000 3 354   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 ABCD 19 7 20 41 7d 21 nd2ti3isthvviemtwiohec7rmcmwg5nnm7 + fmr2ywfwwfam4dcu4wdl + 0l64upuysv4osr3n 22 srmdbjasritqsogxjxddx9vk8ufbejiu9ktqjb/2rdmmf3htza = 23/   The process has been created.   SQL> exec p2 Yuechaotian   The PL/SQL process is successfully completed.   You can also save it to a plain text file or table and execute it later. This method is useful if you need to deploy the generated code elsewhere and ensure code security. Note that the source code here is passed to the wrap function in the varchar2 type. Because the varchar2 type has a limited length (32767 bytes), for a long process (such as a package of 2 W rows), you have to use the other two overloaded wrap functions: Function wrap (DDL dbms_ SQL .varchar2s, LB pls_integer, UB pls_integer) return dbms_ SQL .varchar2s; function wrap (DDL dbms_ SQL .varchar2a, LB pls_integer, UB pls_integer)   Return dbms_ SQL .varchar2a; The input and output here are implemented using the associative array. The difference between the two is that the type dbms_ SQL .varchar2s is limited to 256 bytes per line, and the type dbms_ SQL .varchar2a is 32767 bytes. Let's take an example: Set serveroutput on size unlimiteddeclare l_source dbms_ SQL .varchar2a;   Rochelle wrap dbms_ SQL .varchar2a; Begin Rochelle source (1): = 'create or replace function get_date_string return varchar2 '; Rochelle source (2): = 'begin '; Rochelle source (3): = 'Return to_char (sysdate, "DD-MON-YYYY ");'; Rochelle source (4): = 'end get_date_string ;';   L_wrap: = SYS. dbms_ddl.wrap (DDL => l_source, Lb => 1, UB => l_source.count );   For I in 1 .. l_wrap.count Loop Dbms_output.put_line (l_wrap (I )); End loop; End; / Create or replace function get_date_string wrapped   A000000 B2 ABCD ABCD ABCD ABCD ABCD ABCD ABCD ABCD ABCD ABCD ABCD ABCD ABCD ABCD ABCD 8 6f AA Signature + onulxdqc1 0hvopp4ejpqs5zxskxpj6xl1 Fviexywcr3btzxtqcgyhfxrtqdvpztr/O + 9uz8l5oijdssrw Zpv6riszfyqeescbwefufyxd     PL/SQL procedure successfully completed. 2.2 create_wrapped In addition to the wrap function, the dbms_ddl package also contains three overloaded create_wrapped processes: Procedure create_wrapped (DDL varchar2 ); Procedure create_wrapped (DDL dbms_ SQL .varchar2s, LB pls_integer, UB pls_integer ); Procedure create_wrapped (DDL dbms_ SQL .varchar2a, LB pls_integer, UB pls_integer ); They have the same input and output parameters as wrap. The usage method is similar to the wrap function, but note that they are processes (rather than functions ). Unlike the wrap function, create_wrapped not only encrypts the source code, but also implements the encrypted ciphertext in the database. Let's take a look at an example. I want to create this process in an encrypted way in the database: Create or replace procedure p1asbegin   Dbms_output.put_line ('yuechaotia '); End; I can use the process dbms_ddl.create_wrapped: SQL> begin 2 dbms_ddl.create_wrapped 3 ('create or replace procedure p1   4 5 begin 6 dbms_output.put_line ("yuechaotian "); 7 end ;'); 8 end; 9/   The PL/SQL process is successfully completed.   SQL> set serveroutput on SQL> exec p1 Yuechaotian   The PL/SQL process is successfully completed. Look at the encrypted code:   -1. query SQL> set pagesize 1000sql> Col text format a100 from the user_source view.   SQL & gt; Set line 2000 SQL> select text from user_source where name = 'p1 ′;   Text ------------------------- Procedure P1 wrapped A000000 354 ABCD ABCD ABCD ABCD ABCD ABCD ABCD ABCD ABCD ABCD ABCD ABCD ABCD ABCD ABCD 7 4A 81 Avtlmidkjye1rqszxgcz9z7 + yewg5nnm7 + fmr2ywfwwff0jabh0odpc4rlbp2ejna + V + NHx Gsqhfmohoijjhhb6c3eqd9umxrec004mzbzodyo7n55mc3hyikbbolaf   -2. Use get_ddl to query SQL> set long 10000 SQL> select dbms_metadata.get_ddl ('Procedure ', 'p1') from dual;   Dbms_metadata.get_ddl ('Procedure ', 'p1 ′) ------------------------   Create or replace procedure "test". "P1" wrapped A000000 354 ABCD ABCD ABCD ABCD ABCD ABCD ABCD ABCD ABCD ABCD ABCD ABCD ABCD ABCD ABCD 7 4A 81 Avtlmidkjye1rqszxgcz9z7 + yewg5nnm7 + fmr2ywfwwff0jabh0odpc4rlbp2ejna + V + NHx Gsqhfmohoijjhhb6c3eqd9umxrec004mzbzodyo7n55mc3hyikbbolaf In this way, you create a stored procedure for encrypting source code in the database.