Encryption stored procedure in oracle
Encryption stored procedure in oracle
Method 1: X: \ oracle \ ora81 \ bin \ wrap iname = XXX oname = XXX
Method 2: 9i uses wrap to encrypt the stored procedure in win2000. For 10 Gb, you can use the encrypted package dbms_ddl.wrap or dbms_ddl.create_wrapped.
11.5.1 use wrap to encrypt the stored procedure in win2000
Wrap. bat
Rem usage: the name of the file to be encrypted in wrap
Set NLS_LANG = AMERICAN_AMERICA.USACII7
Wrap.exe iname = % 1
Pause
11.5.2 WRAP package (Version 10.2)
PL/SQL program units often contain sensitive and confidential information about company processes and trade secrets, which makes them similar to tables and become protected entity groups. To prevent unauthorized access to source code, we often need to use the wrap command line utility, which can make the program messy.
Wrap can be called only after PL/SQL scripts are created. The utility packs the input plaintext into a file. However, in some cases, you may want to dynamically generate packaging in PL/SQL code. In this case, the wrap Utility cannot be called because no source file exists.
Since Oracle Database 10g 2nd provides a supply package, you can use it to create code and package it. This package supplements (rather than substitutes) the wrap utility. The latter is still suitable for scenarios where you want to use command lines to quickly package a large number of source files.
For example, suppose you want to create a simple process p1 in the form of packaging.
Create or replace procedure p1
Begin
Null;
End;
In PL/SQL units, you can use the following command to dynamically create a process by packaging:
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
...... Wait ......
The first line of procedure p1 wrapped is to confirm the creation process as a package. If you use the DBMS_METADATA.GET_DDL () function to obtain the DDL of the process, the source code is still packaged.
Sometimes you may have slightly different requirements. For example, you may want to generate PL/SQL code, but do not want to create a process. In this case, you can save it in a file or table for later execution. However, because the above method is created, this method does not work here. Therefore, 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 passed parameter, which represents the packaging output result of PL/SQL code. This parameter can be saved in a pure file or table and can be executed later. This method is useful if the generated code is to be deployed elsewhere and must be secure.
If you can pass all the text of the stored code as a varchar2 data type (the size limit is 32 K), this method works normally. If the PL/SQL code exceeds 32 KB, you must use a slightly different method: accept a set variable as the input.
Here you can use a supplied data type: varchar2 in the package DBMS_ SQL. This is a set data type (table of VARCHAR2). Each unit OF a TABLE receives up to 32 kb of text. You can increase the number OF units contained in the TABLE to meet your needs. For example, suppose you have to wrap a very long process named myproc, its definition is as follows:
Create or replace procedure myproc
Rochelle key VARCHAR2 (200 );
Begin
Rochelle key: = 'aruploand ';
End;
Of course, this is not a very long process at all; but for the sake of demonstration, suppose it is very long. To create it as a package, you need to execute the following PL/SQL blocks:
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 ';
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 Rochelle input_code to save the input plaintext code. In rows 4th to 10th, we fill these rows with the code to be packaged. In this example, for simplicity, I use very short rows. In fact, you may need to use a very long row with a size of up to 32 KB. Similarly, I only use seven units in the array; in fact, you may need to use several units to fill in all the code.
Lines 11th to 15th indicate how I can call this process to create it as a package. In row 12th, I pass the set as a parameter DDL. However, pause here-I have allocated a comment as the first unit of the array, which may be used in the document. But it is not a valid syntax. Similarly, I allocate another comment to the last unit (7) of the array, which is not a valid syntax for the creation process. To make the packaging operation only process valid rows, I specify the minimum (2) and maximum (6) units for the set that stores our code in rows 13th and 14th. The LB parameter indicates the lower bound of the array. In this example, it is 2, and HB is the upper bound (6 ).
Using this method, you can create any size from your PL/SQL code in the packaging method.
========================================================== ================================
Create or replace procedure p_wraped_user AUTHID CURRENT_USER
-- Created by xsb on 2006-11-10
-- For: encrypt all the codes of a user in batches, including stored procedures, functions, and packages.
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 _ cler' -- AND
-- TYPE = 'package body'
Order by type) LOOP
FOR I IN (SELECT rownum rn, text
FROM (SELECT decode (line, 1, 'create or replace ') | 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;
