Experiment 4 Database Security Technology

[PURPOSE]

1. Understand the concept of security.

2. Master the independent access control mechanism (DAC) based on understanding users and related concepts ).

3. Familiar with ms SQL Server security technology.

[Lab nature]

Comprehensive Experiment

[Experiment Guide]

1Security Overview

Database Security refers to protecting the database and preventing Data leaks and damages caused by illegal use. In the database system, the primary measure to ensure data security is access control, that is, to allow different users to perform operations on different data objects, and to control users to access only others (operation power) the data to be accessed. The access control mechanism is divided into independent access control (DAC) and forced Access Control (MAC), which mainly includes two parts:

First, define user permissions and Register User Permissions in the data dictionary;

Second, check valid permissions.

Common Database Security Control technologies include:

(1) user identification.

(2) independent access control (DAC): users have different access permissions for different data objects, and different users have different permissions for the same object, in addition, users can grant their access permissions to other users. Therefore, autonomous access control is flexible.

(3) Mandatory Access Control (MAC): Each data object is labeled with a certain level of confidentiality, and each user is also granted a certain level of license. Only users with valid licenses can access any object. Therefore, mandatory access control is relatively strict.

(4) view: we can define different views for different users for access permission control, data Objects are restricted to a certain extent, that is, the view mechanism is used to hide the data to be kept confidential to users who do not have access to the data, so as to automatically provide a certain degree of security protection for the data. The view mechanism indirectly defines user permissions that support access predicates.

(5) Audit

(6) encryption: Data Encryption technology can be used for highly sensitive data, such as financial data, military data, and State secrets.

2,Ms SQL ServerSecurity Control Technology

For details, refer to "manage SQL Server" in "Books Online"> "Security Management"

The security of ms SQL Server is built on both authentication and access permit mechanisms, that is, the user has to go through two security phases: authentication and authorization (permission verification ). In the authentication phase, the user is identified by a login account (LOGIN) and only the user's ability to connect to the ms SQL server instance is verified. If the authentication succeeds, you can connect to the ms SQL server instance. Then the user needs to access the database on the server. Therefore, the user must be granted the access permission to the account mapped to the user login in each database. The permission verification stage controls the activities that a user can perform in the ms SQL Server database.

Ms SQL Server Security modes include login (LOGIN), Database User, permission, role and so on. User and login are two different concepts. All database users must match a login. Ms SQL Server Authentication works in one of two security authentication modes:

(1) Windows Authentication Mode (Windows Authentication): Windows Authentication mode allows users to connect through Microsoft Windows NT 4.0 or Windows 2000 user accounts.

(2) hybrid mode (Windows Authentication and SQL Server Authentication): hybrid mode allows users to use Windows authentication or SQL Server authentication to connect to the SQL server instance.

For the sake of flexibility, the hybrid mode is generally used. To set the ms SQL Server Authentication mode, you can use "Enterprise Manager" to select a server. Right-click the server's pop-up menu and choose "properties". 1. Click the "Security" page.

 

Figure 1

3Define Login and user

Method 1: graphic interface: Use "Enterprise Manager ". Choose "security"> "login" on the server, right-click "Add new login", enter the logon name logqixin, and select "SQL Server Authentication", 2.

 

Figure 2

On the "database access" Page, make sure to allow access to database "test", 3.

 

Figure 3

In this case, the Enterprise Manager automatically adds logqixin to the "user" of the database Qixin. Log on to logqixin and access the database Qixin with the same name.

If you create a database (such as Qixin) User separately, choose Enterprise Manager> Server> database> User and choose create database user from the shortcut menu. (Note: before that, you must create a login, and then create a user associated with the login .)

 

Figure 4

Method 2: Use the system stored procedure

(1) Add logon, 5.

 

Figure 5

(2) Add a user, 6.

 

Figure 6

4Complete authorization/Revoke permissions. Observe the changes before and after authorization (in table "department" and user"LogqixinFor example ).

(1) Authorization

Method 1: graphic interface, choose "Enterprise Manager"> "server"> "Database"> "user"> "logqixin"> right-click "all tasks"> "manage permissions ", 7. Figure 8.

 

Figure 7

 

Figure 8

Method 2: grant command.

(2) log on to "sa" (connection 1) and "logtest" (connection 2) to connect to the Database "Qixin" and "9" respectively.

 

Figure 9

(3) query in connection 2, 10.

 

Figure 10

(4) perform authorization on connection 1, and perform authorization on connection 11.

 

Figure 11

(5) The query results in connection 2 are obviously different, as shown in Figure 12.

 

Figure 12

(6) execute the revoke permission on connection 1 and then execute the query. The result is 10.

5Design security mechanisms to allow users toTest"You can only query40Employees over the age of years.

(1) If the user "test" does not exist, define the user "test" according to the aforementioned method ".

Exec sp_addlogin 'test', '', 'qixin'
Exec sp_adduser 'test', 'test'

(2) create a view of employees who are over 40 years old Based on the table "employees.

Create view employee _ age
Select * from employee
Where age> = 40

(3) grant the "Technical Department" Employee View query permission to the user "test ".

Grant select on employee _ age to test

6Design roles"Student", You can view"Employees"Employee ID and name.

Role: a set of permissions. The Database Administrator assigns the database permission to the role and then the role to the database user or login account, so that the database user or login account has the corresponding permissions.

Ms SQL Server provides a fixed server role and database role, users can modify the permissions of the fixed database role, you can also create a new database role, assign permissions to the new role. In addition to predefined roles, you can also create a role ).PublicA role is a special database role. Each database user belongs to it.

(1) define the role "student ".

Exec sp_addrole 'student'

(2) create a view of employees who are based on the table "employees" and only contain "employees.

Create view employee _ role
Select employee ID, name from employee

(3) grant the "student" role the employee View query permission that only contains the employee ID and name ".

Grant select on employee _ role to student

(4) Add the user "test" as a member to the role "student". In this way, the user "test" can only view the employee ID and name of the "employee.

-- Add user test to role student
Exec sp_addlogin 'test'
Exec sp_adduser 'test', 'test'
Exec sp_addrolemember 'student ', 'test'