1. The experiment purpose

Master the definition and maintenance method of autonomous access control authority .

2. The contents and requirements of the experiment

Define users 、 role , Assign permissions to users and roles 、 Recycling permissions , Log in to the database with the corresponding user name to verify whether the permission allocation is correct .

3. The experimental steps

（1） Create user ：

① For purchasing 、 Manager of sales and customer management  David、Tom、Jerry  Create a user representation , Require the right to create users or roles .

Source code ：

CREATE USER 'David'@'localhost' IDENTIFIED BY '123456';

CREATE USER 'Tom'@'localhost' IDENTIFIED BY '123456';

CREATE USER 'Jerry'@'localhost' IDENTIFIED BY '123456';

GRANT CREATE USER,
 CREATE ROLE ON *.* TO 'David'@'localhost' WITH GRANT OPTION;

GRANT CREATE USER,
 CREATE ROLE ON *.* TO 'Tom'@'localhost' WITH GRANT OPTION;

GRANT CREATE USER,
 CREATE ROLE ON *.* TO 'Jerry'@'localhost' WITH GRANT OPTION;

Results screenshots ：

② For purchasing 、 Sales and customer management  Marry、Jack、Mike  Create user ID and user password .

Source code ：

CREATE USER 'Marry'@'localhost' IDENTIFIED BY '123456';

CREATE USER 'Jack'@'localhost' IDENTIFIED BY '123456';

CREATE USER 'Mike'@'localhost' IDENTIFIED BY '123456';

Results screenshots ：

（2） Create roles and assign permissions ：

① Each department creates a query role , And assign corresponding query permissions . Purchase corresponds to ： Parts list 、 Supplier list 、 Parts supply contact list

Sales correspondence ： Order list and order details

The customer corresponds to ： Customer watch 、 Country table 、 District Table

Source code ：

CREATE ROLE 'SELECT_David'@'localhost';

GRANT SELECT
	ON test.part TO 'SELECT_David'@'localhost';

GRANT SELECT
	ON test.supplier TO 'SELECT_David'@'localhost';

GRANT SELECT
	ON test.partsupp TO 'SELECT_David'@'localhost';

CREATE ROLE 'SELECT_Tom'@'localhost';

GRANT SELECT
	ON test.orders TO 'SELECT_Tom'@'localhost';

GRANT SELECT
	ON test.lineitem TO 'SELECT_Tom'@'localhost';

CREATE ROLE 'SELECT_Jerry'@'localhost';

GRANT SELECT
	ON test.customer TO 'SELECT_Jerry'@'localhost';

GRANT SELECT
	ON test.nation TO 'SELECT_Jerry'@'localhost';

GRANT SELECT
	ON test.region TO 'SELECT_Jerry'@'localhost';

Results screenshots ：

② Create an employee role for each department , Have the ability to view this department 、 Insert permissions .

Source code ：

CREATE ROLE 'WORKERS_Marry'@'localhost';

GRANT SELECT,INSERT
	ON test.part TO 'WORKERS_Marry'@'localhost';

GRANT SELECT,INSERT
	ON test.supplier TO 'WORKERS_Marry'@'localhost';

GRANT SELECT,INSERT
	ON test.partsupp TO 'WORKERS_Marry'@'localhost';

CREATE ROLE 'WORKERS_Jack'@'localhost';

GRANT SELECT,INSERT
	ON test.orders TO 'WORKERS_Jack'@'localhost';

GRANT SELECT,INSERT
	ON test.lineitem TO 'WORKERS_Jack'@'localhost';

CREATE ROLE 'WORKERS_Mike'@'localhost';

GRANT SELECT,INSERT
	ON test.customer TO 'WORKERS_Mike'@'localhost';

GRANT SELECT,INSERT
	ON test.nation TO 'WORKERS_Mike'@'localhost';

GRANT SELECT,INSERT
	ON test.region TO 'WORKERS_Mike'@'localhost';

Results screenshots ：

③ Create a manager role for each department , The corresponding role has full control over the information of the Department , The manager has the right to assign authority to the staff of his department

Source code ：

CREATE ROLE 'MANAGER_David'@'localhost';

GRANT ALL PRIVILEGES ON test.part TO 'MANAGER_David'@'localhost' WITH GRANT OPTION;

GRANT ALL PRIVILEGES ON test.supplier TO 'MANAGER_David'@'localhost' WITH GRANT OPTION;

GRANT ALL PRIVILEGES ON test.partsupp TO 'MANAGER_David'@'localhost' WITH GRANT OPTION;

CREATE ROLE 'MANAGER_Tom'@'localhost';

GRANT ALL PRIVILEGES ON test.orders TO 'MANAGER_Tom'@'localhost' WITH GRANT OPTION;

GRANT ALL PRIVILEGES ON test.lineitem TO 'MANAGER_Tom'@'localhost' WITH GRANT OPTION;

CREATE ROLE 'MANAGER_Jerry'@'localhost';

GRANT ALL PRIVILEGES ON test.customer TO 'MANAGER_Jerry'@'localhost' WITH GRANT OPTION;

GRANT ALL PRIVILEGES ON test.nation TO 'MANAGER_Jerry'@'localhost' WITH GRANT OPTION;

GRANT ALL PRIVILEGES ON test.region TO 'MANAGER_Jerry'@'localhost' WITH GRANT OPTION;

Results screenshots ：

（3） Assign permissions to users

Need to activate the role ！！！

set default role all to David;

① Assign authority to each department manager .

Source code ：

GRANT 'MANAGER_David'@'localhost' TO 'David'@'localhost';

GRANT 'MANAGER_Tom'@'localhost' TO 'Tom'@'localhost';

GRANT 'MANAGER_Jerry'@'localhost' TO 'Jerry'@'localhost';

  Results screenshots ：

② Assign authority to the staff of each department .

Source code ：

GRANT 'WORKERS_Marry'@'localhost' TO 'Marry'@'localhost';

GRANT 'WORKERS_Jack'@'localhost' TO 'Jack'@'localhost';

GRANT 'WORKERS_Mike'@'localhost' TO 'Mike'@'localhost';

Results screenshots ：

（4） Recycling Roles or user permissions

① Withdraw the customer information viewing permission of the role of customer manager .

Source code ：

REVOKE SELECT
	ON TABLE test.customer
FROM
	'MANAGER_Jerry'@'localhost';
REVOKE SELECT
	ON TABLE test.nation
FROM
	'MANAGER_Jerry'@'localhost';
REVOKE SELECT
	ON TABLE test.region
FROM
	'MANAGER_Jerry'@'localhost';

  Results screenshots ：

② Recycling  Mike  Employee authority of the customer department .

because Mike The permissions are WORKERS_Mike Granted , Cannot view permissions by itself , Therefore, it is necessary to recover WORKERS_Mike The right to withdraw Mike Authority .

Source code ：

REVOKE ALL PRIVILEGES
	ON TABLE test.customer
FROM
	'WORKERS_Mike'@'localhost';
REVOKE ALL PRIVILEGES
	ON TABLE test.nation
FROM
	'WORKERS_Mike'@'localhost';
REVOKE ALL PRIVILEGES
	ON TABLE test.region
FROM
	'WORKERS_Mike'@'localhost';

Results screenshots ：

（5） Verify the correctness of permission assignment

① With  David  Login to the database , Verify the authority of the purchasing department manager .

This part of the experiment is carried out on the command line , The code and results are as follows .

Screenshot of source code and results ：

Use on the command line

mysql -u David -p

Command and enter the password 123456 Sign in David User's identity

Use

SELECT *

FROM test.customer;

Command query customer table , An error at this time David The user does not have permission to query the customer table

Use

SELECT *

FROM test.part;

Command to query the parts table , At this point, the query is successful , Prove that the permission is granted correctly

② Recycling  Mike  Employee authority of the customer department .

Screenshot of source code and results ：

With Mike Identity login database , Using a database test, Report errors , Prove that after the last recycling permission ,Mike You no longer have permission to use this database .