1, Data security control

1. Access rights in relational database system

2. Authorization: Grant and withdrawal

① GRANT (GRANT permission)

Grant < < Permission > [< Permission >]
On < object type > < object name > [< object type > < object name >]
To < user > [< user >]
[WITH GRANT OPTION];

[WITH GRANT OPTION] users who have obtained certain permissions can also grant such permissions to other users

Because we need to authorize many user permissions, we first create some users, but a login name only corresponds to one user, so we need to create several login names.

If it is created with a login name, an error will be reported

create login U_1 with password='123456';--Create login
create user U1 for login U_1; --Create user name
create login U_2 with password='123456';
create user U2 for login U_2; 
create login u_3 with password='123456';
create user U3 for login U_3; 
create login U_4 with password='123456';
create user U4 for login U_4; 
create login U_5 with password='123456';
create user U5 for login U_5; 
create login U_6 with password='123456';
create user U6 for login U_6; 
create login U_7 with password='123456';
create user U7 for login U_7; 

[example 4.1] grant the permission to query the Student table to user U1

GRANT SELECT
ON Student  --ON TABLE Student Will report an error should be SQL and T-SQL Differences between
TO U1

[example 4.2] grant all permissions on Student table and Course table to users U2 and U3

--standard SQL
GRANT ALL PRIVILEGES
ON TABLE Student,Course
TO U2,U3;

--T-SQL Separate authorization
 GRANT ALL PRIVILEGES
 ON  Student
 TO U2,U3;
 GRANT ALL PRIVILEGES
 ON  Course
 TO U2,U3;

[example 4.3] authorize all users to query the table SC (no problem is displayed)

GRANT SELECT
ON SC
TO PUBLIC

[example 4.4] grant the permission to query the Student table and modify the Student number to user U4 (indicate the corresponding attribute column name)

GRANT UPDATE(Sno),SELECT
ON Student
TO U4

[example 4.5] grant the INSERT permission to the table SC to the U5 user, and allow him to grant this permission to other users

GRANT INSERT
ON SC
TO U5
WITH GRANT OPTION

② REVOKE (REVOKE permission)

[example 4.8] withdraw the permission of user U4 to modify student ID (from)

REVOKE UPDATE(Sno)
ON Student
FROM U4

[example 4.9] withdraw the query authority of all users on table SC

REVOKE SELECT
ON SC
FROM PUBLIC

[example 4.10] withdraw the INSERT permission of user U5 on SC table

REVOKE INSERT
ON SC
FROM U5

It will fail to retract because he can grant this permission to other users (need to use CASCADE)

REVOKE INSERT
ON SC
FROM U5 CASCADE

2, Database role

ROLE:
A named set of permissions related to database operations
Advantage: a simplified role is a collection of permissions. You can create a role for a group of users with the same permissions.

1. Role creation

CREATE ROLE <Role name>

2. Authorize roles

 GRANT  <jurisdiction>[,<jurisdiction>]... 
 ON <object type>Object name  
 TO <role>[,<role>]... 

3. Grant a role to other users or users

GRANT  <Role 1>[,<Role 2>]...
TO  <Role 3>[,<User 1>]... 
[WITH ADMIN OPTION]

4. Withdrawal of role permissions

REVOKE <jurisdiction>[,<jurisdiction>]...
 
ON <object type> <Object name>
 
FROM <role>[,<role>]...

[example 4.11] grant a group of permissions to a user through roles.

CREATE ROLE R1
GRANT SELECT,UPDATE,INSERT
ON Student
TO R1

[example 4.12] permission modification of role

GRANT DELETE
ON Student
TO R1

[example 4.13] permission modification of role

REVOKE SELECT
ON Student
FROM R1

3, Audit

Mandatory access control

1. In mandatory access control, all entities managed by database management system are divided into subject and object

(1) A subject is an active entity in a system
Actual users managed by the database management system
(2) Object is a passive entity in the system
File, basic table, index, view

2. For subject and object, DBMS assigns a sensitivity Label to each instance (value)
Top Secret (TS)
Secret (S)
Trusted (C)
Public (P)
TS>=S>=C>=P

3. Mandatory access control rules

(1) Only when the license level of the subject is greater than or equal to the secret level of the object can the subject read the corresponding object
(2) Only when the license level of the subject is less than or equal to the secret level of the object can the subject write the corresponding object

4, View mechanism

Hide the data to be kept secret from users who have no right to access these data, and provide a certain degree of security protection for the data

[example 4.14] establish the view of computer department students, grant the SELECT permission of the view to Wang Ping, and grant all operation permissions on the view to Zhang Ming

CREATE VIEW CS_Student
AS
SELECT *
FROM Student
WHERE Sdept='CS'
GRANT SELECT
FROM CS_Student
TO Wang Ping
GRANT ALL PRIVILEGES
FROM CS_Student
TO Zhang Ming

5, Audit

1. Audit function

The audit function automatically records all user operations on the database and puts them into the audit log

Auditors use the audit log to monitor various behaviors in the database and find out the person, time and content of illegally accessing data.

2.AUDIT statement and NOAUDIT statement

AUDIT statement: AUDIT function settings
NOAUDIT statement: cancel the audit function

[example 4.15] audit the operation of modifying SC table structure or SC table data

AUDIT ALTER,UPDATE
ON SC

[example 4.16] cancel all audits of SC table

NOAUDIT ALTER,UPDATE
ON SC

These statements cannot be used by SQL SERVER..