Wrap tool for Oracle Stored Procedure Encryption

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