< a > stored procedure encryption
In fact, with this more than 10-year SQL Server, I've become a true fan of the stored procedures. When dealing with business logic directly using SQL statements or stored procedures, I basically choose the latter without hesitation.
The reasons are as follows:
1, the use of stored procedures, at least in the prevention of illegal injection (inject) to provide better protection . At the very least, the stored procedure performs the precompilation first, which, if it is not executed because of an illegal parameter, provides a layer of natural barrier to some extent.
I remember a privilege control system that was introduced about eight or nine years ago by piecing together an SQL statement and finally getting a shape like "where 1=1 and Dataid in (1,2) and modelid in (2,455) and Shopid in (111) and Departid in (1,3) and ([Name] like%myword%) "To get the result set that meets the criteria.
Note: This parameter is entered through the address bar of the Web application of the address bar or the WinForm UI interface, so the malicious injection will cost a certain amount of maintenance. Because some commonly used keywords (or sensitive words) are difficult to distinguish between malicious and not malicious.
2. You can provide better security by using stored procedures instead of directly accessing base tables . You can control how the data is modified at the row or column level. As opposed to table access, you can confirm that a user with permission to execute has executed the appropriate stored procedure. This is the only way to access the data server. Therefore, any peeping person will not be able to see your SELECT statement. In other words, each application can only have the appropriate stored procedure to access the base table, rather than "Sleect *".
3, stored procedures can be encrypted . (This is very practical, imagine that your database server is hosted or rented, you can feel at ease every day to sleep soundly.) What if your competitor "accidentally" boarded your SQL Server, or injected your stored procedures, and then injected malicious SQL into your business logic, and it happened that you didn't make a backup five minutes ago? ）
(Note: you should back up the original stored procedure before you encrypt the stored procedure, and the encryption should be completed before deployment to the production environment.) )
The encryption of stored procedures is very simple, let's look at an example:
Insert Test Table
Copy Code code as follows:
Use TESTDB2
Go
/********** Test Table *****************/
SET ansi_padding on
Go
CREATE TABLE [dbo]. [Tb_demo] (
[ID] [int] not NULL,
[Submitdate] [DateTime] Null
[Commment] [nvarchar] (MB) NULL,
)
Go
SET ansi_padding off
Go
Insert into [Tb_demo]
Select 1024, GETDATE (), REPLICATE (' A ', 100);
WAITFOR DELAY ' 00:00:04 ';
Insert into [Tb_demo]
Select 1024, GETDATE (), REPLICATE (' B ', 50);
Go
To insert a stored procedure:
Copy Code code as follows:
/*************** create an unencrypted stored procedure *******************/
Create Procedure cpp_test_original
As
SELECT * FROM [Tb_demo]
Go
/*************** create an encrypted stored procedure *******************/
Create Procedure cpp_test_encryption
With encryption
As
----can be replaced with arbitrary logic.
Execute cpp_test_original
Go
Unencrypted stored procedure:
Encrypted stored procedures:
At this point, at least, the contents of the stored procedure are not easily visible (although decryption is possible). Using this, we can encrypt some of the key stored procedures. at this point, however, the stored procedure can still be execute, ALTER, and drop.
< two > Security context
In addition to encrypting the contents of the SQL text, we can also use the EXECUTE as clause to set the security context of the stored procedure to meet different security level requirements.
If you are not interested in these, please pass the underlined paragraph directly.
(For detailed usage of the EXECUTE as clause, see msdn:http://msdn.microsoft.com/zh-cn/library/ms188354.aspx)
Here, what we need to know is:
1 . In SQL Server, you can define execution contexts for the following user-defined modules: functions (except for inline table-valued functions), procedures, queues, and triggers.
By specifying the context of the execution module, you can control which user account the database engine uses to authenticate permissions on the objects referenced by the module. This helps people to more flexibly and forcefully manage the permissions in the chain of objects that are formed by user-defined modules and their referenced objects. You must and only grant the user permissions to the module itself, without giving the user explicit permissions on the referenced object. Only the user who is running the module must have permissions on the object that the module accesses.
The corresponding parameters are also different for functions, procedures, queues, and triggers. The parameters corresponding to the stored procedure include (CALLER | SELF | OWNER | ' User_name').
CALLER The statement within the specified module executes in the context of the module caller. The user executing the module must have the appropriate permissions not only for the module itself, but also for any database objects referenced by the module. CALLER is the default value for all modules except queues, as is the behavior of SQL Server 2005. CALLER cannot be specified in the CREATE queue or ALTER queue statement.
self EXECUTE as SELF is equivalent to the EXECUTE as user_name, which specifies that the user is the user who created or changed the module. The actual user ID of the user who created or changed the module is stored in the
sys.sql_modules or
sys.service_queues catalog view
execute_as_principal_id< The/c13> column. SELF is the default value for the queue.
owner Specifies that the statements within the module are executed in the context of the module's current owner. If the module does not have a specified owner, the owner of the module schema is used. You cannot specify OWNER for DDL or login triggers. Note:
OWNER must be mapped to a separate account, not a role or group.
' user_name ' specifies that the statement within the module executes in the context of the user specified by user_name. Permissions to any object within the module are validated against user_name. You cannot specify user_name for DDL triggers or login triggers that have a server scope. Please use login_name instead. The user_name must exist in the current database and must be a separate account. User_name cannot be a group, role, certificate, key, or built-in account, such as NT AUTHORITY\LocalService, NT AUTHORITY\NetworkService, or NT AUTHORITY\LocalSystem. The user ID of the execution context is stored in the metadata and can be viewed in the execute_as_principal_id column of the sys.sql_modules or Sys.assembly_modules catalog view.
2. The ownership chain has the following limitations:
Applies only to DML statements:SELECT, INSERT, UPDATE, and DELETE.
The owner of the invocation and the invoked object must be the same.
Does not apply to dynamic queries within a module.
Let's look at an example:
The first step is to create a test stored procedure that deletes all the data from the table Tb_demo
Copy Code code as follows:
Use TESTDB2
Go
CREATE PROCEDURE dbo. [Cpp_del_all_tb_demo]
As
--Deletes all rows prior to the data feed
DELETE dbo. [Tb_demo]
Go
Step Two: Create an account Tonyzhang and assign the account EXEC permission to the stored procedure
Copy Code code as follows:
Use master
Go
CREATE LOGIN Tonyzhang with PASSWORD = ' 123b3b4 '
Use TESTDB2
Go
CREATE USER Tonyzhang
Go
GRANT EXEC on dbo. [Cpp_del_all_tb_demo] to Tonyzhang
Log in to SQL Server with this account and execute:
Copy Code code as follows:
EXECUTE dbo. cpp_del_all_tb_demo/** (4 row (s) affected) **/
Note: At this point, although Tonyzhang does not have any permissions other than executing the stored procedure [Cpp_del_all_tb_demo], it executes the stored procedure and deletes the table record.
If we modify the stored procedure to:
Copy Code code as follows:
Alter PROCEDURE dbo. [Cpp_del_all_tb_demo]
As
--Deletes all rows prior to the data feed
TRUNCATE TABLE dbo. [Tb_demo]
Go
At this point, and then log in as Tonyzhang and execute the stored procedure, you will be prompted:
This is because the owner right chain is limited to select, INSERT, UPDATE, and DELETE. Instead of including truncate, in other words, the system-mandated exec is only assigned to SELECT, INSERT, UPDATE, and DELETE
One might ask: How do we qualify permissions if dynamic statements are invoked inside stored procedures instead of explicit table names?
The third step: we set up a stored procedure, the function is to pass a parameter table name, query that table record number.
Copy Code code as follows:
CREATE PROCEDURE dbo. [Cpp_sel_countrowsfromanytable]
@SchemaAndTable nvarchar (255)
As
EXEC (' SELECT COUNT (1) from ' + @SchemaAndTable)
Go
Granted to Tonyzhang to execute permissions on the stored procedure:
Copy Code code as follows:
GRANT EXEC on dbo. [Cpp_sel_countrowsfromanytable] to Tonyzhang
Go
At this point, log in as Tonyzhang and execute the stored procedure, prompting:
Note that at this point, Tonyzhang has failed to execute because it has permission to execute the stored procedure, but has no SELECT permission on the parameter table.
Step Fourth: Modify the context of the stored procedure
Create a new account Jackwang, assign to table Tb_demo SELECT permission
copy code code as follows:
Use master
Go
CREATE LOGIN jackwang with PASSWORD = ' 123b3b4 '
US E Testdb2
Go
CREATE USER jackwang
GRANT SELECT on OBJECT::d bo.[ Tb_demo] to Jackwang
Go
/*******
Note: At this point, Jackwang can perform dbo.[ Tb_demo Select
*******/
Modify the stored performer
Copy code generation Code as follows:
use Testdb2
Go
Alter PROCEDURE dbo.[ Cpp_sel_countrowsfromanytable]
@SchemaAndTable nvarchar (255)
with EXECUTE as ' Jackwang '
as
EXE C (' SELECT COUNT (1) from ' + @SchemaAndTable "
Go
Note: This way, when we call the stored procedure [cpp_sel_countrowsfromanytable] , the stored procedure is automatically run as Jackwang.
At this point, we still log in as Tonyzhang, and then execute:
Summary:
This article starts a security trip to SQL Server code with a simple two example,
1. Encryption of stored procedures (Note:
you should back up the original stored procedure before you encrypt the stored procedure, and the encryption should be done before you deploy to the production environment.) )
2. The security context of the stored procedure. You can set more stringent data access levels through the context. (primarily access restrictions on
SELECT, INSERT, UPDATE, and DELETE statements)
The next section will cover the new Transparent encryption (TDE) features added to SQL Server 2008.
