Method of encrypting stored procedure under oracle
- 2020-06-15 10:24:43
- OfStack
Method 1: X:\oracle\ora81\ wrap iname=XXX oname=XXX
Method 2:9 i encrypts the stored procedure using wrap under win2000. 10g can encrypt the procedure using es15EN_ddl.wrap or ES18en_ddl.create_wrapped.
11.5.1 Use wrap encrypted stored procedures under win2000
wrap.bat
rem Use: wrap file name to be encrypted
set NLS_LANG=AMERICAN_AMERICA.USACII7
wrap.exe iname=%1
pause
WRAP package (10.2 version)
PL/SQL program units often contain very sensitive and confidential information about company processes and trade secrets, making them similar to tables as protected groups of entities. To prevent unauthorized view of source code, we often use the wrap command-line utility, which can be confusing.
wrap can only be called after the PL/SQL script has been created; The utility packages the plaintext input into a file. However, in some cases, you may want to dynamically generate wrappers in PL/SQL code. In this case, the wrap utility cannot be called because the source file does not yet exist.
Since the Oracle database 10g version 2 comes with a package, you can use it to create code and package it. This package complements, not replaces, the wrap utility. The latter is still appropriate if you want to use the command line to quickly package a large number of source files.
For example, suppose you want to create a simple procedure, p1, in a packaged form.
create or replace procedure p1 as
begin
null;
end;
In PL/SQL cell, you can create this 1 process dynamically in packaging using the following command:
begin
dbms_ddl.create_wrapped
('create or replace procedure p1 as begin null; end;')
end;
/
Now you want to confirm the packaging process. You can select the source text from the dictionary.
SQL > select text from user_source where name = 'P1';
Text
-----------------------------------------------------------------
procedure p1 wrapped
a000000
369
abcd
abcd
... And so on...
Line 1 procedure p1 wrapped is the confirmation to create the package. If you use the DBMS_METADATA.GET_DDL () function to get DDL for the procedure, you will still see the source code packaged.
Sometimes you may have slightly different requirements; For example, you might want to generate PL/SQL code but don't want to create the process. In this case, you can save it in a file or table for later execution. But because the above method creates the process, it won't work here. So you need to call another function in the package:
SQL > select dbms_ddl.wrap
2 ('create or replace procedure p1 as begin null; end;')
3 from dual
4 /
DBMS_DDL.WRAP('CREATEORREPLACEPROCEDUREP1ASBEGINNULL;END;')
----------------------------------------------------------------------
create or replace procedure p1 wrapped
a000000
369
abcd
abcd
... and so on ...
The output of the WRAP function is a passable parameter that represents the packaged output of the PL/SQL code. This parameter can be saved in a pure file file or table and can be executed later. This is useful if your generated code is to be deployed elsewhere, and you must make sure that your code is secure.
If you can pass all the text of the stored code as an varchar2 data type (the size limit is 32K), this method works fine. If the PL/SQL code exceeds 32K, you must use a slightly different approach: accept a set variable as input.
Here you can use one supplied data type: varchar2 in the package DBMS_SQL. This is a collection data type (TABLE OF VARCHAR2), with each cell of the table receiving up to 32K text; Feel free to increase the number of cells contained in this table to suit your needs. For example, suppose you have to wrap a very long process called myproc, which is defined as follows:
create or replace procedure myproc as
l_key VARCHAR2(200);
begin
l_key := 'ARUPNANDA';
end;
Of course, this is not a very long process at all; But for the sake of demonstration, let's assume it's long. To create it as a package, you execute the following PL/SQL block:
1 declare
2 l_input_code dbms_sql.varchar2s;
3 begin
4 l_input_code (1) := 'Array to hold the MYPROC';
5 l_input_code (2) := 'create or replace procedure myproc as ';
6 l_input_code (3) := ' l_key VARCHAR2(200);';
7 l_input_code (4) := 'begin ';
8 l_input_code (5) := ' l_key := ''ARUPNANDA'';';
9 l_input_code (6) := 'end;';
10 l_input_code (7) := 'the end';
11 sys.dbms_ddl.create_wrapped (
12 ddl = > l_input_code,
13 lb = > 2,
14 ub = > 6
15 );
16* end;
Here we define a variable l_input_code to hold the plaintext code entered. In lines 4 through 10, we populate the lines with the code we want to package. In this example, Again for simplicity, I use very short lines. In fact, you might be using very long lines, up to 32KB in size. Again, I only used 7 units in the array; In fact, you might want to use several units to populate the entire code.
Lines 11 through 15 show how I call the procedure to create it as a package. In line 12, I pass the collection as an argument, DDL. However, pause 1 here - I've allocated 1 comment as the first unit of the array, possibly for the document. But it's not a valid syntax. Similarly, I assign another comment to the last unit of the array (7), which is not a valid syntax for the creation process. In order for the wrapper operation to process only valid lines, I specify the lowest (2) and highest (6) cells in lines 13 and 14 that store our collection of code. The parameter LB represents the lower bound of the array, in this case 2, and HB is the upper bound (6).
Using this approach, you can now create a process of any size in package form from your PL/SQL code.
============================================================
CREATE OR REPLACE PROCEDURE p_wraped_user AUTHID CURRENT_USER AS
--Created by xsb on 2006-11-10
--For: Batch encrypt all the code under this user, including stored procedure, function, package.
v_procs dbms_sql.varchar2a;
BEGIN
FOR n IN (SELECT DISTINCT NAME, TYPE
FROM user_source
WHERE NAME < > 'P_WRAPED_USER' AND
TYPE < > 'TYPE'
MINUS
SELECT DISTINCT NAME, TYPE
FROM user_source
WHERE line = 1 AND
instr(text, 'wrapped') > 0
--WHERE NAME = 'GET_CLERK' --AND
-- TYPE = 'PACKAGE BODY'
ORDER BY TYPE) LOOP
FOR i IN (SELECT rownum rn, text
FROM (SELECT decode(line, 1, 'create or replace ') || text text
FROM user_source
WHERE NAME = n.NAME AND
TYPE = n.TYPE
ORDER BY line)) LOOP
v_procs(i.rn) := i.text;
END LOOP;
dbms_ddl.create_wrapped(v_procs, 1, v_procs.COUNT);
v_procs.DELETE;
END LOOP;
END;
Method 2:9 i encrypts the stored procedure using wrap under win2000. 10g can encrypt the procedure using es15EN_ddl.wrap or ES18en_ddl.create_wrapped.
11.5.1 Use wrap encrypted stored procedures under win2000
wrap.bat
rem Use: wrap file name to be encrypted
set NLS_LANG=AMERICAN_AMERICA.USACII7
wrap.exe iname=%1
pause
WRAP package (10.2 version)
PL/SQL program units often contain very sensitive and confidential information about company processes and trade secrets, making them similar to tables as protected groups of entities. To prevent unauthorized view of source code, we often use the wrap command-line utility, which can be confusing.
wrap can only be called after the PL/SQL script has been created; The utility packages the plaintext input into a file. However, in some cases, you may want to dynamically generate wrappers in PL/SQL code. In this case, the wrap utility cannot be called because the source file does not yet exist.
Since the Oracle database 10g version 2 comes with a package, you can use it to create code and package it. This package complements, not replaces, the wrap utility. The latter is still appropriate if you want to use the command line to quickly package a large number of source files.
For example, suppose you want to create a simple procedure, p1, in a packaged form.
create or replace procedure p1 as
begin
null;
end;
In PL/SQL cell, you can create this 1 process dynamically in packaging using the following command:
begin
dbms_ddl.create_wrapped
('create or replace procedure p1 as begin null; end;')
end;
/
Now you want to confirm the packaging process. You can select the source text from the dictionary.
SQL > select text from user_source where name = 'P1';
Text
-----------------------------------------------------------------
procedure p1 wrapped
a000000
369
abcd
abcd
... And so on...
Line 1 procedure p1 wrapped is the confirmation to create the package. If you use the DBMS_METADATA.GET_DDL () function to get DDL for the procedure, you will still see the source code packaged.
Sometimes you may have slightly different requirements; For example, you might want to generate PL/SQL code but don't want to create the process. In this case, you can save it in a file or table for later execution. But because the above method creates the process, it won't work here. So you need to call another function in the package:
SQL > select dbms_ddl.wrap
2 ('create or replace procedure p1 as begin null; end;')
3 from dual
4 /
DBMS_DDL.WRAP('CREATEORREPLACEPROCEDUREP1ASBEGINNULL;END;')
----------------------------------------------------------------------
create or replace procedure p1 wrapped
a000000
369
abcd
abcd
... and so on ...
The output of the WRAP function is a passable parameter that represents the packaged output of the PL/SQL code. This parameter can be saved in a pure file file or table and can be executed later. This is useful if your generated code is to be deployed elsewhere, and you must make sure that your code is secure.
If you can pass all the text of the stored code as an varchar2 data type (the size limit is 32K), this method works fine. If the PL/SQL code exceeds 32K, you must use a slightly different approach: accept a set variable as input.
Here you can use one supplied data type: varchar2 in the package DBMS_SQL. This is a collection data type (TABLE OF VARCHAR2), with each cell of the table receiving up to 32K text; Feel free to increase the number of cells contained in this table to suit your needs. For example, suppose you have to wrap a very long process called myproc, which is defined as follows:
create or replace procedure myproc as
l_key VARCHAR2(200);
begin
l_key := 'ARUPNANDA';
end;
Of course, this is not a very long process at all; But for the sake of demonstration, let's assume it's long. To create it as a package, you execute the following PL/SQL block:
1 declare
2 l_input_code dbms_sql.varchar2s;
3 begin
4 l_input_code (1) := 'Array to hold the MYPROC';
5 l_input_code (2) := 'create or replace procedure myproc as ';
6 l_input_code (3) := ' l_key VARCHAR2(200);';
7 l_input_code (4) := 'begin ';
8 l_input_code (5) := ' l_key := ''ARUPNANDA'';';
9 l_input_code (6) := 'end;';
10 l_input_code (7) := 'the end';
11 sys.dbms_ddl.create_wrapped (
12 ddl = > l_input_code,
13 lb = > 2,
14 ub = > 6
15 );
16* end;
Here we define a variable l_input_code to hold the plaintext code entered. In lines 4 through 10, we populate the lines with the code we want to package. In this example, Again for simplicity, I use very short lines. In fact, you might be using very long lines, up to 32KB in size. Again, I only used 7 units in the array; In fact, you might want to use several units to populate the entire code.
Lines 11 through 15 show how I call the procedure to create it as a package. In line 12, I pass the collection as an argument, DDL. However, pause 1 here - I've allocated 1 comment as the first unit of the array, possibly for the document. But it's not a valid syntax. Similarly, I assign another comment to the last unit of the array (7), which is not a valid syntax for the creation process. In order for the wrapper operation to process only valid lines, I specify the lowest (2) and highest (6) cells in lines 13 and 14 that store our collection of code. The parameter LB represents the lower bound of the array, in this case 2, and HB is the upper bound (6).
Using this approach, you can now create a process of any size in package form from your PL/SQL code.
============================================================
CREATE OR REPLACE PROCEDURE p_wraped_user AUTHID CURRENT_USER AS
--Created by xsb on 2006-11-10
--For: Batch encrypt all the code under this user, including stored procedure, function, package.
v_procs dbms_sql.varchar2a;
BEGIN
FOR n IN (SELECT DISTINCT NAME, TYPE
FROM user_source
WHERE NAME < > 'P_WRAPED_USER' AND
TYPE < > 'TYPE'
MINUS
SELECT DISTINCT NAME, TYPE
FROM user_source
WHERE line = 1 AND
instr(text, 'wrapped') > 0
--WHERE NAME = 'GET_CLERK' --AND
-- TYPE = 'PACKAGE BODY'
ORDER BY TYPE) LOOP
FOR i IN (SELECT rownum rn, text
FROM (SELECT decode(line, 1, 'create or replace ') || text text
FROM user_source
WHERE NAME = n.NAME AND
TYPE = n.TYPE
ORDER BY line)) LOOP
v_procs(i.rn) := i.text;
END LOOP;
dbms_ddl.create_wrapped(v_procs, 1, v_procs.COUNT);
v_procs.DELETE;
END LOOP;
END;