Start：
--------------------------------------------------------------------------------
On the network, you see the methods of encrypting and decrypting SQL Server 2000 and SQL Server 2005 stored procedures, and then you analyze the code and find that they all have the same principle. I later wrote two stored procedures, a cryptographic stored procedure (Sp_encryptobject), and a decryption stored procedure (Sp_encryptobject) based on the actual application environment, which can be applied to stored procedures, functions, views in SQL Server. and triggers.
Feel that these two stored procedures are interesting, to share with you, if you have seen similar, it is better to relive it.
Stored procedures for encryption (Sp_encryptobject):
--------------------------------------------------------------------------------
Stored procedure (sp_encryptobject) encryption is in the stored procedure, function, view of the "as" position before the "with encryption", if it is a trigger, in the "for" position before the "with encryption".
If the trigger is {after | INSTEAD of} needs to modify the following code "for" Location:
Copy Code code as follows:
If ObjectProperty (object_id (@Object), ' Execisaftertrigger ') =0 set @Replace = ' as '; else Set @Replace = ' for ';
Stored Procedure Completion code:
Copy Code code as follows:
Use master
Go
If object_id (' [Sp_encryptobject] ') is not null
Drop Procedure [Sp_encryptobject]
Go
CREATE PROCEDURE Sp_encryptobject
(
@Object sysname= ' All '
)
As
/*
When @object=all, encrypt all functions, stored procedures, views, and triggers
Call Method:
. Execute sp_encryptobject ' All '
. Execute sp_encryptobject ' objectname '
*/
Begin
SET NOCOUNT ON
If @Object <> ' all '
Begin
If not exists (select 1 to sys.objects a where a.object_id=object_id (@Object) and A.type in (' P ', ' V ', ' TR ', ' FN ', ' IF ', ' TF ') )
Begin
--sql Server 2008
RAISERROR 50001 N ' Invalid encrypted Object! The encrypted object must be a function, a stored procedure, a view, or a trigger. '
--sql Server 2012
--throw 50001, N ' Invalid encrypted Object! The encrypted object must be a function, a stored procedure, a view, or a trigger. ', 1
Return
End
if exists (select 1 from Sys.sql_modules a where a.object_id=object_id (@Object) and a.definition is null)
Begin
--sql Server 2008
RAISERROR 50001 N ' object has been encrypted! '
--sql Server 2012
--throw 50001, N ' object has been encrypted! ', 1
Return
End
End
declare @sql nvarchar (max), @C1 nchar (1), @C2 nchar (1), @type nvarchar (m), @Replace nvarchar (50)
Set @C1 =nchar (13)
Set @C2 =nchar (10)
DECLARE cur_object
Cursor FOR
Select object_name (a.object_id) as Objectname,a.definition
From Sys.sql_modules A
INNER JOIN sys.objects B on b.object_id=a.object_id
and B.is_ms_shipped=0
And NOT EXISTS (select 1
From Sys.extended_properties X
where x.major_id=b.object_id
and X.minor_id=0
and X.class=1
and X.name= ' Microsoft_database_tools_support '
)
where B.type in (' P ', ' V ', ' TR ', ' FN ', ' IF ', ' TF ')
and (b.name= @Object or @Object = ' all ')
and B.name <> ' Sp_encryptobject '
And a.definition is not NULL
ORDER BY case
When b.type = ' V ' then 1
When b.type = ' TR ' then 2
When B.type in (' FN ', ' IF ', ' TF ') then 3
Else 4 end,b.create_date,b.object_id
Open Cur_object
FETCH NEXT from Cur_object into @Object, @sql
While @ @fetch_status =0
Begin
Begin Try
If ObjectProperty (object_id (@Object), ' Execisaftertrigger ') =0 set @Replace = ' as '; else Set @Replace = ' for ';
if (patindex ('% ' + @C1 + @C2 + @Replace + @C1 + @C2 + '% ', @sql) >0)
Begin
Set @sql =replace (@sql, @C1 + @C2 + @Replace + @C1 + @C2, @C1 + @C2 + ' with encryption ' + @C1 + @C2 + @Replace + @C1 + @C2)
End
else if (patindex ('% ' + @C1 + @Replace + @C1 + '% ', @sql) >0)
Begin
Set @sql =replace (@sql, @C1 + @Replace + @C1, @C1 + ' with encryption ' + @C1 + @Replace + @C1)
End
else if (patindex ('% ' + @C2 + @Replace + @C2 + '% ', @sql) >0)
Begin
Set @sql =replace (@sql, @C2 + @Replace + @C2, @C2 + ' with encryption ' + @C2 + @Replace + @C2)
End
else if (patindex ('% ' + @C2 + @Replace + @C1 + '% ', @sql) >0)
Begin
Set @sql =replace (@sql, @C2 + @Replace + @C1, @C1 + ' with encryption ' + @C2 + @Replace + @C1)
End
else if (patindex ('% ' + @C1 + @C2 + @Replace + '% ', @sql) >0)
Begin
Set @sql =replace (@sql, @C1 + @C2 + @Replace, @C1 + @C2 + ' with encryption ' + @C1 + @C2 + @Replace)
End
else if (patindex ('% ' + @C1 + @Replace + '% ', @sql) >0)
Begin
Set @sql =replace (@sql, @C1 + @Replace, @C1 + ' with encryption ' + @C1 + @Replace)
End
else if (patindex ('% ' + @C2 + @Replace + '% ', @sql) >0)
Begin
Set @sql =replace (@sql, @C2 + @Replace, @C2 + ' with encryption ' + @C2 + @Replace)
End
Set @type =
Case
When object_id (@Object, ' P ') >0 then ' Proc '
When object_id (@Object, ' V ') >0 then ' View '
When object_id (@Object, ' TR ') >0 then ' Trigger '
When object_id (@Object, ' FN ') >0 or object_id (@Object, ' IF ') >0 or object_id (@Object, ' TF ') >0 then ' Function '
End
Set @sql =replace (@sql, ' Create ' + @type, ' Alter ' + @type)
Begin Transaction
EXEC (@sql)
Print N ' has finished encrypting object (' + @type + '): ' + @Object
Commit Transaction
End Try
Begin Catch
Declare @Error nvarchar (2047)
Set @Error = ' Object: ' + @Object + @C1 + @C2 + ' Error: ' +error_message ()
Rollback Transaction
Print @Error
Print @sql
End Catch
FETCH NEXT from Cur_object into @Object, @sql
End
Close Cur_object
Deallocate Cur_object
End
Go
exec sp_ms_marksystemobject ' sp_encryptobject '--identified as a system object
Go
If SQL Server 2012, please modify the code in the two locations below. In SQL Server 2012, it is recommended that you use throw instead of RAISERROR.
Decryption Method：
Decryption process, the most important use of the different or method:
[Character 1] after the function fn_x (x) encryption into [encrypted character 1], if we know [encrypted character 1], in turn look [character 1], you can do this:
[character 1] = [character 2] ^ fn_x ([character 2]) ^ [Encrypted character 1]
Here's a simple example:
Copy Code code as follows:
--Create an encryption function (fn_x)
If object_id (' fn_x ') is not null drop function fn_x
Go
Create function fn_x
(
@x nchar (1)
) returns NCHAR (1)
As
Begin
Return (NCHAR (65535-unicode (@x)))
End
Go
DECLARE @nchar_1_encrypt nchar (1), @nchar_2 nchar (1)
--Encrypt the character ' A ' and deposit it in the variable @nchar_1_encrypt
Set @nchar_1_encrypt =dbo.fn_x (N ' A ')
--The character @nchar_2 of the reference
Set @nchar_2 = ' x '
--figure out the characters before @nchar_1_encrypt encryption
Select NCHAR (Unicode (@nchar_2) ^unicode (dbo.fn_x (@nchar_2)) ^unicode (@nchar_1_encrypt) as [@nchar_1]
/*
@nchar_1
--------------------
A
*/
[note]: Can be decrypted from SQL Server 2000 through SQL Server 2012 using XOR or method
Stored procedures for decryption (Sp_decryptobject):
Copy Code code as follows:
Use master
Go
If object_id (' [Sp_decryptobject] ') is not null
Drop Procedure [Sp_decryptobject]
Go
CREATE PROCEDURE Sp_decryptobject
(
@Object sysname,--object name to decrypt: function, stored procedure, view, or trigger
@MaxLength int=4000--assessing the length of content
)
As
SET NOCOUNT ON
/* 1. Decryption */
If not exists (select 1 to sys.objects a where a.object_id=object_id (@Object) and A.type in (' P ', ' V ', ' TR ', ' FN ', ' IF ', ' TF ') )
Begin
--sql Server 2008
RAISERROR 50001 N ' Invalid Object! The object to decrypt must be a function, a stored procedure, a view, or a trigger. '
--sql Server 2012
--throw 50001, N ' Invalid Object! The object to decrypt must be a function, a stored procedure, a view, or a trigger. ', 1
Return
End
if exists (select 1 from Sys.sql_modules a where a.object_id=object_id (@Object) and a.definition are NOT null)
Begin
--sql Server 2008
RAISERROR 50001 N ' object is not encrypted! '
--sql Server 2012
--throw 50001, N ' Invalid Object! The object to decrypt must be a function, a stored procedure, a view, or a trigger. ', 1
Return
End
declare @sql nvarchar (max)--decrypted SQL statement
, @imageval nvarchar (max)--encrypted string
, @tmpStr nvarchar (max)--Temporary SQL statement
, @tmpStr_imageval nvarchar (max)--Temporary SQL statement (after encryption)
, @type char (2)--Object type (' P ', ' V ', ' TR ', ' FN ', ' IF ', ' TF ')
, @objectID int--Object ID
, @i int--while Recycle
, @Oject1 nvarchar (1000)
Set @objectID =object_id (@Object)
Set @type = (select A.type from sys.objects a where a.object_id= @objectID)
DECLARE @Space4000 nchar (4000)
Set @Space4000 =replicate ('-', 4000)
/*
The following SQL statement will be constructed @tmpStr
-------------------------------------------------------------------------------
ALTER TRIGGER TR_NAME on TABLE_NAME with encryption for update as return/**/
ALTER PROC Proc_name with encryption as select 1 as Col/**/
ALTER VIEW view_name with encryption as select 1 as Col/**/
Alter function fn_name () returns int with encryption as begin return (0) end/**/
*/
Set @Oject1 =quotename (Object_schema_name (@objectID)) + '. ' +quotename (@Object)
Set @tmpStr =
Case
When @type = ' P ' then N ' Alter Procedure ' + @Oject1 + ' with encryption as select 1 as Column1 '
When @type = ' V ' then N ' Alter View ' + @Oject1 + ' with encryption as select 1 as Column1 '
When @type = ' FN ' then N ' Alter Function ' + @Oject1 + ' () returns int with encryption as begin return (0) end '
When @type = ' IF ' then N ' Alter Function ' + @Oject1 + ' () returns table with encryption as return (Select a.name from Sys.types A) '
When @type = ' TF ' then N ' Alter Function ' + @Oject1 + ' () returns @t table (name nvarchar) with encryption as Begin return E nd '
Else ' Alter Trigger ' + @Oject1 + ' on ' +quotename (Object_schema_name (@objectID)) + '. ' + (select Top (1) quotename (object_name (parent_id)) from Sys.triggers a where a.object_id= @objectID) + ' with encryption for Update as Return '
End
Set @tmpStr = @tmpStr + '/* ' + @Space4000
Set @i=0
While @i < (ceiling (@MaxLength *1.0/4000)-1)
Begin
Set @tmpStr = @tmpStr + @Space4000
Set @i=@i+1
End
Set @tmpStr = @tmpStr + ' * * '
------------
Set @imageval = (select Top (1) a.imageval from Sys.sysobjvalues a where a.objid= @objectID and A.valclass=1)
BEGIN Tran
EXEC (@tmpStr)
Set @tmpStr_imageval = (select Top (1) a.imageval from Sys.sysobjvalues a where a.objid= @objectID and A.valclass=1)
Rollback Tran
-------------
Set @tmpStr =stuff (@tmpStr, 1,5, ' create ')
Set @sql = '
Set @i=1
While @i<= (datalength (@imageval)/2)
Begin
Set @sql = @sql +isnull (nchar (Unicode (substring (@tmpStr, @i,1)) ^ Unicode (substring (@tmpStr_imageval, @i,1)) ^unicode ( SUBSTRING (@imageval, @i,1)))
Set @i+=1
End
/* 2. Print */
DECLARE @patindex int
While @sql > '
Begin
Set @patindex =patindex ('% ' +char () +char + '% ', @sql)
If @patindex >0
Begin
Print substring (@sql, 1, @patindex-1)
Set @sql =stuff (@sql, 1, @patindex +1, ")
End
Else
Begin
Set @patindex =patindex ('% ' +char + '% ', @sql)
If @patindex >0
Begin
Print substring (@sql, 1, @patindex-1)
Set @sql =stuff (@sql, 1, @patindex, ")
End
Else
Begin
Set @patindex =patindex ('% ' +char (%) + '% ', @sql)
If @patindex >0
Begin
Print substring (@sql, 1, @patindex-1)
Set @sql =stuff (@sql, 1, @patindex, ")
End
Else
Begin
Print @sql
Set @sql = '
End
End
End
End
Go
exec sp_ms_marksystemobject ' sp_decryptobject '--identified as a system object
Go
If SQL Server 2012, please modify the code in the two locations below. method is similar to the previous encryption process:
Build test Environment：
--------------------------------------------------------------------------------
In a test environment (Db:test), execute the above encryption stored procedure (Sp_encryptobject) and decrypt the stored procedure (sp_encryptobject) and create two more tables: TableA & TableB
Copy Code code as follows:
Use test
Go
--Create TABLE: TableA & TableB
If object_id (' Mytablea ') is not null drop table Mytablea
If object_id (' Mytableb ') is not null drop table Mytableb
Go
CREATE TABLE Mytablea (id int identity,data nvarchar (m), constraint Pk_mytablea primary key (ID))
CREATE TABLE Mytableb (id int, data nvarchar, constraint Pk_mytableb primary key (ID))
Go
Next, we want to create 6 unencrypted objects (the object type contains ' P ', ' V ', ' TR ', ' FN ', ' IF ', ' TF '):
1. View (MyView):
Copy Code code as follows:
If object_id (' MyView ') is not null drop view MyView
Go
CREATE VIEW MyView
As
SELECT * from TableA;
Go
2. Trigger (Mytrigger):
Copy Code code as follows:
If object_id (' Mytrigger ') is not null drop Trigger Mytrigger
Go
Create Trigger Mytrigger
On TableA
For update
As
Insert into TableB (id,data) Select A.id,a.data from Inserted a
Go
3. Stored procedure (MYPROC):
Copy Code code as follows:
If object_id (' MyProc ') is not null drop proc MyProc
Go
Create proc MyProc
(
@data nvarchar (50)
)
As
INSERT into TableA (data) values (@data)
Go
4. User-defined table-valued function (TF) (MYFUNCTION_TF):
Copy Code code as follows:
If object_id (' MYFUNCTION_TF ') is not null drop function MYFUNCTION_TF
Go
Create function MYFUNCTION_TF
(
)
Returns @t table
(
ID int,
Data nvarchar (50)
)
As
Begin
Insert @t (id,data) Select Id,data from TableA
Return
End
Go
5. Inline table-valued function (IF) (myfunction_if):
Copy Code code as follows:
If object_id (' myfunction_if ') is not null drop function myfunction_if
Go
Create function myfunction_if
(
)
Returns table
As
Return (the Select top (3) ID, the data from TableA ORDER by id DESC)
Go
6. Scalar function (FN) (MYFUNCTION_FN):
Copy Code code as follows:
If object_id (' Myfunction_fn ') is not null drop function Myfunction_fn
Go
Create function Myfunction_fn
(
)
Returns nvarchar (50)
As
Begin
Return (select TOP (1) data to TableA ORDER by id DESC)
End
Go
When the above 1-6-step script is executed, we query the system view Sys.sql_modules to see the defined information before it is unencrypted:
Copy Code code as follows:
Select B.name as Object,b.type,a.definition
From Sys.sql_modules A
INNER JOIN sys.objects B on b.object_id=a.object_id
where B.create_date>=convert (Date,getdate ())
ORDER BY b.object_id
Encryption Test：
--------------------------------------------------------------------------------
I then encrypt them by calling the encrypted stored procedure (sp_encryptobject):
Copy Code code as follows:
Use test
Go
exec sp_encryptobject ' all '
Go
When we look back at the system view sys.sql_modules, we find that the definition column returns a null value, stating that the definition has been encrypted:
Decryption Test：
--------------------------------------------------------------------------------
The decryption process, which must be connected to SQL Server at the DAC, is an example where we start the DAC from the SSMS (SQL Server Management Studio) query Editor, as shown in the figure:
Decrypting a stored procedure (Sp_decryptobject) to decrypt only one stored procedure, function, view, or trigger at a time:
Copy Code code as follows:
Use test
Go
EXEC sp_decryptobject Mytrigger
Go
When the definition content is longer than 4000, we can specify @maxlength values, such as:
Copy Code code as follows:
EXEC sp_decryptobject fn_my,20000
Go
Here (fn_my) is a function that defines more than 8000 of the content:
... ...
Summary：
--------------------------------------------------------------------------------
Although, the above script, I have been tested in SQL Server 2008 R2 and SQL Server 2012, I cannot avoid some unknown errors. If you are testing the above script yourself, please do not do it in the production environment. If you are in the application process, encounter any problems or have any comments and suggestions can send email contact me or thread, thank you very much!
