Oracle Stored Procedure Encryption

Introduction: Usually when you do the project, often encounter the Oracle stored procedures to the project site to test the system. In this case, if you want to keep your own stored procedures confidential, do not let others see the source code, you can encrypt the existing stored procedures to protect. As the name implies, is the encryption of Oracle stored procedure source code. Of course not all the time required, when some projects on the security requirements are relatively high when you can use, below I use the case to introduce the encryption method and experimental results.

Experimental environment

Operating system version

Red Hat Enterprise Linux Server release 6.5 (Santiago)

Database version

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0-64bit Production

DB Instance Name

Shdb

Wrap Path

Oracle has the Wrap command under the Oracle_home/bin directory.

/opt/oracle/product/11.2.0/db_1/bin/wrap

Experiment started.

1. Create a SQL file and create it in the Oracle User home directory

[Email protected] ~]$ vim Leonarding.sql

Create or Replace procedure Leo (i in number)

As

Begin

Dbms_output.put_line (' input parameter: ' | | To_char (i));

End

Save & Exit

[email protected] ~]$ LL

Total 3664228

Drwxr-xr-x 7 Oracle Oinstall 4096 Database

Drwxr-xr-x 2 Oracle Oinstall 4096 Jan 7 15:46 Desktop

-rw-r--r--1 Oracle Oinstall 16:49 Leonarding.sql has been generated

-rwxr-xr-x 1 root root 1395582860 Oct p13390677_linux-x86-64_11gr2040_database_1of2.zip

-rwxr-xr-x 1 root root 1151304589 Oct p13390677_linux-x86-64_11gr2040_database_2of2.zip

-rwxr-xr-x 1 root root 1205251894 Oct p13390677_linux-x86-64_11gr2040_grid.zip

2.Wrap Mode Encryption

Usage:

The use of wrap is still relatively simple, one command can be encrypted, the following is the format of the command

Wrap Iname=input_file [Oname=output_file]

Iname: The source file is specified

Oname: Specified is the converted encrypted file, the Oname option can be omitted if the Omit wrap command will automatically generate the file name exactly the same as the encrypted file, of course you can also specify your own naming method, as follows

[Email protected] ~]$ Wrap Iname=leonarding.sql

PL/SQL Wrapper:release 11.2.0.4.0-64bit Production on Thu Jan 14 16:57:46 2016

Copyright (c) 1993, Oracle. All rights reserved.

Processing Leonarding.sql to LEONARDING.PLB automatically generate an encrypted file with the exact same file name

[email protected] ~]$ LL

Total 3664232

Drwxr-xr-x 7 Oracle Oinstall 4096 Database

Drwxr-xr-x 2 Oracle Oinstall 4096 Jan 7 15:46 Desktop

-rw-r--r--1 Oracle Oinstall 322 Jan 16:57 LEONARDING.PLB

-rw-r--r--1 Oracle Oinstall 16:49 leonarding.sql

At this point the contents of the file leonarding.plb are encrypted, as shown below

[Email protected] ~]$ vim LEONARDING.PLB

Create or replace procedure Leo wrapped

a000000

354

Abcd

Abcd

Abcd

Abcd

Abcd

Abcd

Abcd

Abcd

Abcd

Abcd

Abcd

Abcd

Abcd

Abcd

Abcd

7

6f B6

Nkxs9ezosgeu6odhpiyrmqr6o5cwg5nnm7+fmr2ywfyfodexpcol0l6lmyeywlilw4/adcup

Wmayw55spqmpbbcgudsqevpneemcl8b4mjk+j00xi5sjpxaofg9si+ingm+ko88rncmuluoq

namhc8hasojodegcck+zyf0g4kam2x0svw==

Specify the encryption file name yourself

[Email protected] ~]$ RM LEONARDING.PLB

[Email protected] ~]$ Wrap Iname=leonarding.sql ONAME=LEO.PLB

PL/SQL Wrapper:release 11.2.0.4.0-64bit Production on Thu Jan 14 16:59:58 2016

Copyright (c) 1993, Oracle. All rights reserved.

Processing Leonarding.sql to LEO.PLB

[email protected] ~]$ LL

Total 3664232

Drwxr-xr-x 7 Oracle Oinstall 4096 Database

Drwxr-xr-x 2 Oracle Oinstall 4096 Jan 7 15:46 Desktop

-rw-r--r--1 Oracle Oinstall 16:49 leonarding.sql

-rw-r--r--1 Oracle Oinstall 322 Jan 16:59 LEO.PLB

Its default output is the source file name plus the PLB suffix, if you want to specify the suffix name, you can also write, wrap Iname=leonarding.sql oname=leo.out, of course, as long as the encrypted file can be correctly parsed by Oracle. Linux is not an extension concept.

NOTES: If you take an encrypted SQL file and use the command again to encrypt it, the resulting file is actually the same as the original, and you can try it yourself.

3. Compile the encrypted LEO.PLB stored procedure

[[email protected] ~]$ sqlplus SHDB/SHDB Login Database

Sql*plus:release 11.2.0.4.0 Production on Thu Jan 14 17:24:18 2016

Copyright (c) 1982, Oracle. All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0-64bit Production

With the partitioning, OLAP, Data Mining and Real application testing options

[Email protected]> @/home/oracle/leo.plb Compiling stored procedure

Procedure created.

4. Running the stored procedure Leo

[Email protected]> set serveroutput on;

[Email protected]> exec leo (100);

Input parameter:100

PL/SQL procedure successfully completed.

[Email protected]> exec leo (200);

Input parameter:200

PL/SQL procedure successfully completed.

PL/SQL Developer is also encrypted and can only see the stored procedure name

We look at the data dictionary and we can't see the content. So now we can use our stored procedures openly. Avoid the risk of information loss. And the process is irreversible.

Sql> Select Name,text from User_source where type= ' PROCEDURE ' and name= ' LEO ';

NAME TEXT

------------------------------ ------------

Leo procedure Leo Wrapped

a000000

354

Abcd

Abcd

Abcd

Abcd

Abcd

Abcd

Abcd

Abcd

Abcd

Abcd

Abcd

Abcd

Abcd

Abcd

Abcd

7

6c B2

Qz+tdob6646qjalbmmeknutfrc4wg5nnm7+fmr2ywfyfodexpcol0l6lmyeywlilw4/adcup

Wmayw55spqmpbbcgudsqevpneemcl8b4mjk+j00xi5sjpxaofg9si+ingm+tpk2xd0boxtos

Vznemccno8e+kr5u1bhsun+mpn8cdla=

PL/SQL Developer Test

Sql> exec Leo (888);

Input parameter:888

PL/SQL procedure successfully completed

Still so smooth, still so dry!!

Summary

To this we fully deduce the Oracle stored Procedure Wrap tool encryption method, in fact, Oracle also provides DBMS_DDL Subprograms encryption method, DBMS_DDL contains encryption stored procedures, functions, type description, type body, package description, package body, This subroutine provides the ability to dynamically generate PL/SQL cells. In fact, the inside is a wrap function and a create_wrapped stored procedure. Plus an exception-handling unit, Malformed_wrap_input.

Here are some things to note about wrap tools

Limitations of Wrap:

1. This method cannot encrypt the stored procedure name.

2. The trigger cannot be encrypted.

4. During the encryption process, you will not be able to check your syntax errors and check them at compile time.

5. He is up compatible, dependent on Oracle version, 10g can run on 11g, 11g can not run on 10g, because two is not a set of encryption algorithm

6. Only the following types can be encrypted, anonymous blocks cannot be encrypted

CREATE [OR REPLACE] FUNCTION function_name

CREATE [OR REPLACE] PROCEDURE procedure_name

CREATE [OR REPLACE] Package package_name

CREATE [OR REPLACE] Package BODY package_name

CREATE [OR REPLACE] TYPE type_name as OBJECT

CREATE [OR REPLACE] TYPE type_name under Type_name

CREATE [OR REPLACE] TYPE BODY type_name

Oracle Stored Procedure Encryption