数据库实验二

最新推荐文章于 2023-11-04 23:14:43 发布

原创最新推荐文章于 2023-11-04 23:14:43 发布 · 290 阅读

1 ·

CC 4.0 BY-SA版权

文章标签：

#数据库

1.创建数据库demo，并插入数据

**
CREATE TABLE workers(
empno CHAR(3),
ename VARCHAR(5),
age int,
job VARCHAR(10),
sal INT,
dno CHAR(2)
);

insert into workers values (‘001’,‘王明’,35,‘MANAGER’,2300,‘01’);
insert into workers values (‘002’,‘李勇’,33,‘CLERK’,1300,‘01’);
insert into workers values (‘003’,‘刘星’,38,‘PRESIDENT’,4300,‘01’);
insert into workers values (‘004’,‘张新’,23, ‘MANAGER’,2500,‘02’);
insert into workers values (‘005’,‘周平’,23, ‘ANALYST’,2100,‘02’);
insert into workers values (‘006’,‘杨兰’,41, ‘MANAGER’,2900,‘03’);

CREATE TABLE department(
dno CHAR(2),
dname VARCHAR(8),
maname VARCHAR(8),
addr VARCHAR(15),
telephone VARCHAR(11)
);
INSERT INTO department VALUES (‘01’,‘aaa’,‘王明’,‘深圳’,‘123456789’);
INSERT INTO department VALUES (‘02’,‘bbb’,‘张新’,‘北京’,‘987654321’);

2.定义用户并且完成权限定义

（1）.创建用户test

CREATE USER ‘test’@‘localhost’ IDENTIFIED by ‘1234’;
GRANT SELECT ON department TO ‘test’@‘localhost’
GRANT SELECT ON workers TO ‘test’@‘localhost’

（2）.用户王明对两个表有 INSERT,DELETE 权力；

CREATE USER ‘王明’@‘localhost’
GRANT INSERT,DELETE ON department TO ‘王明’@‘localhost’
GRANT INSERT,DELETE ON workers TO ‘王明’@‘localhost’

（3）.用户李勇对两个表有 SELECT 和对工资字段具有跟新权力；

CREATE USER ‘李勇’@‘localhost’
GRANT SELECT ON workers TO ‘李勇’@‘localhost’

（4）.用户周平具有对两个表所有权力(读，插，改，删数据)，并具有给其他用户授权的权力；

CREATE USER ‘周平’@‘localhost’
GRANT ALL PRIVILEGES ON department TO ‘周平’@‘localhost’ WITH GRANT OPTION
GRANT ALL PRIVILEGES ON workers TO ‘周平’@‘localhost’ WITH GRANT OPTION

3.用户杨兰具有从每个部门职工中 SELECT 最高工资，最低工资，平均工资的权力，他不能查看每个人的工资。

CREATE USER ‘杨兰’@‘localhost’
CREATE VIEW workerssla AS SELECT department.dno,MAX(sal)maxg,MIN(sal)ming,AVG(sal)avgg
FROM department,workers WHERE workers.dno=department.dno GROUP BY workers.dno

GRANT SELECT ON workerssla TO ‘杨兰’@‘localhost’

6.把 (1)~(5) 的每一种情况，撤销各用户所授予的权力。

REVOKE SELECT ON workers FROM ‘王明’@‘localhost’
REVOKE SELECT ON department FROM ‘王明’@‘localhost’
REVOKE INSERT,DELETE ON workers FROM ‘李勇’@‘localhost’
REVOKE ALL PRIVILEGES ON workers FROM ‘周平’@‘localhost’
REVOKE ALL PRIVILEGES ON department FROM ‘周平’@‘localhost’
REVOKE SELECT ON workerssla FROM ‘杨兰’@‘localhost’
DROP VIEW workerssla