Wrap tool for Oracle Stored Procedure Encryption
Oracle Stored Procedure encryption-wrap Tool
Dingchang Poetry
The three emperors and Five Emperors of morality are named Xia Hou Shang Zhou, And the qixiong of WUBA is busy during the spring and autumn periods,
Qingshi has several surnames, and there are countless Barren Hills in the north region. Our predecessors have planted seeds for future generations and said that they are fighting together.
Introduction: when you are working on a project, you may often bring the Oracle stored procedure to the project site to test the system. At this time, if you want to keep your stored procedures confidential and do not let others see the source code, you can encrypt and protect existing stored procedures. As the name implies, it is to encrypt the source code of the Oracle stored procedure. Of course, it is not always necessary. When some projects have high security requirements, they can be used. Next I will use cases to introduce this encryption method and experiment results.
Lab 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
Database instance name
Shdb
Wrap path
The Oracle oracle_home/bin directory contains the wrap command.
/Opt/oracle/product/11.2.0/db_1/bin/wrap
Lab started
1. Create an SQL file in the oracle user's home directory.
[Oracle @ zxt28 ~] $ 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
[Oracle @ zxt28 ~] $ Ll
Total 3664228
Drwxr-xr-x 7 oracle oinstall 4096 Aug 27 2013 database
Drwxr-xr-x 2 oracle oinstall 4096 Jan 7 Desktop
-Rw-r -- 1 oracle oinstall 128 Jan 14 16: 49leonarding. SQL has been generated
-Rwxr-xr-x 1 root 1395582860 Oct 17 2013 p13390677_linux-x86-64_11gR2040_database_1of2.zip
-Rwxr-xr-x 1 root 1151304589 Oct 17 2013 p13390677_linux-x86-64_11gR2040_database_2of2.zip
-Rwxr-xr-x 1 root 1205251894 Oct 16 2013 p13390677_linux-x86-64_11gR2040_grid.zip
2. Wrap Encryption
Usage:
The usage of wrap is relatively simple. You can encrypt a command. The following is the command format.
Wrap iname = input_file [oname = output_file]
Iname: Specifies the source file.
Oname: Specifies the encrypted file after conversion. The oname option can be omitted. If the wrap command is omitted, an encrypted file with the same file name will be automatically generated. Of course, you can also specify your own naming method, as shown below:
[Oracle @ zxt28 ~] $ 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,200 9, Oracle. All rights reserved.
Processing leonarding. SQL to leonarding. plb automatically generates the same encrypted file name
[Oracle @ zxt28 ~] $ Ll
Total 3664232
Drwxr-xr-x 7 oracle oinstall 4096 Aug 27 2013 database
Drwxr-xr-x 2 oracle oinstall 4096 Jan 7 Desktop
-Rw-r -- 1 oracle oinstall 322 Jan 14 16: 57leonarding. plb
-Rw-r -- 1 oracle oinstall 128 Jan 14 16: 49leonarding. SQL
The content of the file leonarding. plb is encrypted as follows:
[Oracle @ zxt28 ~] $ 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 name of the encrypted file
[Oracle @ zxt28 ~] $ Rm leonarding. plb
[Oracle @ zxt28 ~] $ 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,200 9, Oracle. All rights reserved.
Processingleonarding. SQL to leo. plb
[Oracle @ zxt28 ~] $ Ll
Total 3664232
Drwxr-xr-x 7 oracle oinstall 4096 Aug 27 2013 database
Drwxr-xr-x 2 oracle oinstall 4096 Jan 7 Desktop
-Rw-r -- 1 oracle oinstall 128 Jan 14 16: 49leonarding. SQL
-Rw-r -- 1 oracle oinstall 322 Jan 14 16: 59leo. plb
The default output is the source file name with the plb suffix. If you want to specify the suffix, you can also write it like this, wrap iname = leonarding. SQL oname = leo. out, of course, as long as the encrypted file can be correctly parsed by Oracle. Linux has no extension concept.
Notes: If you get an encrypted SQL file and use command encryption again, the obtained file is actually the same as the original one. You can try it on your own.
3. Compile the encrypted leo. plb Stored Procedure
[Oracle @ zxt28 ~] $ 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,201 3, Oracle. All rights reserved.
Connected:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0-64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SHDB @ shdb> @/home/oracle/leo. plb compilation Stored Procedure
Procedure created.
4. Run the Stored Procedure leo
SHDB @ shdb> set serveroutput on;
SHDB @ shdb> exec leo (100 );
Input parameter: 100
PL/SQL procedure successfully completed.
SHDB @ shdb> exec leo (200 );
Input parameter: 200
PL/SQL procedure successfully completed.
PL/SQL Developer is also encrypted. Only the stored procedure name is displayed, and SQL statements are not displayed.
We can see from the data dictionary that we can't see the content! So now we can use our stored procedures by the vendor. This avoids the risk of information loss. And this process is irreversible.
SQL> select name, text from user_source where type = 'processed' 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 Testing
SQL> exec leo (888 );
Input parameter: 888
PL/SQL procedure successfully completed
Still so smooth, still so cool !!
Summary
At this point, we fully interpret the wrap tool encryption method in the Oracle stored procedure. In fact, Oracle also provides the DBMS_DDL Subprograms encryption method. DBMS_DDL includes the encryption stored procedure, function, type description, and type body, package Description, package body. This subroutine provides the ability to dynamically generate PL/SQL units. In fact, it is a WRAP function and a CREATE_WRAPPED stored procedure. Add the MALFORMED_WRAP_INPUT unit for exception handling.
Below are some precautions for the wrap tool.
Limitations of wrap:
1. This method cannot encrypt the stored procedure name.
2. The trigger cannot be encrypted.
4. During the encryption process, Your syntax errors will not be checked, but will be checked during compilation.
5. It is compatible with the Oracle version. 10g can run on 11g, and 11g cannot run on 10g, because the two are not using a set of encryption algorithms.
6. Only the following types can be encrypted, and 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