数据库原理与应用
1 / 6
引入

谁能看成绩，谁能改成绩？

引例　学校的教务数据库

教务数据库里存着全校学生的成绩，也存着老师的工资。学生只能查成绩，不能改；辅导员要看学生信息，却不该看到老师的工资；任课老师要录成绩……

同一个数据库，不同的人能做的事不一样。这一章就学：怎样让 MySQL 照着演示里这张权限表办事。

重点数据库安全性：保护数据库，防止不合法的使用造成数据泄露、更改或破坏。

数据库自身的安全手段有好几种：用户标识与鉴定、存取权限控制、视图、数据加密、安全审计。本章在 MySQL 中做前两件：管用户、管权限。

动手看谁能做什么？点一行看看
查成绩改成绩查学生信息查教师工资
学生✓✗✗✗
辅导员✓✗✓✗
任课教师✓✓✓✗
教务员✓✓✓✓
每个人一行，每件事一列。点任意一行，看 MySQL 要用什么办法做到。
1

两道关：先认人，再看权限

引例　进实验楼要刷两次卡

在楼门口刷一次校园卡，证明你是本校的人；到了某间机房门口，还要再刷一次，看你有没有这间房的权限。只过了大门，照样进不了机房。

MySQL 也是这样把关的：登录一次，之后每条语句都查一次。

重点登录时检查一次「你是谁」；之后每执行一条语句，都检查一次「你能做什么」。
第一关

登录：用户名、主机、口令三样都对得上，才能连进来。对不上：报错 1045，连接被拒绝。

第二关

权限：每条语句执行前，先查有没有这项权限。没有权限：报错 1142，这条语句不执行。

用户和权限都记在系统库 mysql 的几张表里：用户在 mysql.user，库、表、字段的权限分别在 mysql.db、mysql.tables_priv、mysql.columns_priv。后面学的语句，其实都在改这几张表。

动手看一次访问要过两道关
口令 权限
xiaoli想查教师表
第一关登录
第二关查权限
teaching.t
xiaoli>SELECT * FROM teaching.t;
选好口令和权限，点「发起访问」。
2

用户：账户 = 用户名 + 主机

引例　新来的助教小李

学期初来了一位新助教小李，要用教务数据库。第一件事是给他开个账号，就像入职先办一张工卡：卡上写着名字，也写着在哪儿能刷——只在办公室，还是哪个校区都行。

开账号、改口令、改名、销号，就是这一步的五条语句。

重点账户写成 '用户名'@'主机'。新建的用户只能登录，还用不了任何数据。
主机怎么写
写法意思
'localhost'只能在数据库服务器这台电脑上登录
'%'任何电脑都能登录
不写 @主机等于写 '%'
管用户的五条语句
  1. 创建
    CREATE USER 'xiaoli'@'localhost' IDENTIFIED BY 'xl123';
  2. 查看
    SELECT User, Host FROM mysql.user;
  3. 改口令
    ALTER USER 'xiaoli'@'localhost' IDENTIFIED BY 'xl456';
  4. 改名
    RENAME USER 'xiaoli'@'localhost' TO 'lijing'@'localhost';
  5. 删除
    DROP USER 'lijing'@'localhost';

注意用户名、主机、口令都要加单引号。不要用 INSERT、UPDATE 直接改 mysql.user 表来建用户、改口令。

动手看主机决定能从哪里登录
'xiaoli'@
服务器本机
机房另一台电脑
动手看执行语句，看 mysql.user 怎样变
root>
SELECT User, Host, authentication_string FROM mysql.user;
UserHost口令（加密后保存）
3

授权：GRANT 权限 ON 范围 TO 用户

引例　工卡办好了，可门都打不开

小李的账号建好了，可他一登录，什么表都查不了——还没给他开通权限。教务处决定：teaching 库的表他都能查，但只能修改成绩表。

「能做什么」「在哪儿做」「给谁」，正好是一条 GRANT 的三段。

重点ON 后面写什么，权限就管多大：*.* 所有库 › 库名.* 一个库 › 库名.表名 一张表 › 字段。
一条 GRANT 分三段
GRANTSELECT, UPDATE做什么 ONteaching.sc管多大 TO'xiaoli'@'localhost'给谁;
范围的四种写法
ON 后面写管多大记在
*.*所有库（全局）mysql.user
teaching.*teaching 库的全部表mysql.db
teaching.sc只有成绩表 scmysql.tables_priv
SELECT(tno, tn)只有 tno、tn 两列mysql.columns_priv

注意先 CREATE USER，再 GRANT，否则报错 1410。末尾加 WITH GRANT OPTION，对方还能把这些权限再授给别人。

动手看拼一条 GRANT，看它管多大
做什么
管多大
MySQL 服务器*.*
teaching 库teaching.*
s学生
c课程
t教师
tnotnprofsal
sc选课
其他库
…
…
4

查看与收回：SHOW GRANTS 和 REVOKE

引例　小李调岗了

学期末小李改带实验课，不再管成绩了。账号不用删，只要把「修改成绩」这项权限关掉。先看看他现在有哪些权限，再收回多余的那项。

查看用 SHOW GRANTS，收回用 REVOKE。

重点REVOKE 和 GRANT 的写法一样，只是把 TO 换成 FROM。
查看权限
  1. 查看
    SHOW GRANTS FOR 'xiaoli'@'localhost';

结果中一行就是一条授权。第一行 USAGE 表示「只能登录」，每个用户都有。

授予和收回，只差两个词
GRANT UPDATE ON teaching.sc TO 'xiaoli'@'localhost';
一次全部收回
  1. 全收回
    REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'xiaoli'@'localhost';

注意在哪一层授的，就在哪一层收：在 teaching.sc 上授的权限，要写 ON teaching.sc 才收得回。

动手看收回一项，SHOW GRANTS 就少一项
root>SHOW GRANTS FOR 'xiaoli'@'localhost';
5

角色：把一组权限打包，分给很多人

引例　新学期来了 30 位老师

每位老师都要同样的 4 项权限。一位位 GRANT，要写 120 条；下学期再加一项，又得改 30 遍。能不能先做一张「任课教师」权限模板，人来了直接套上？

这张模板就是角色。

重点建角色 › 给角色授权 › 分给用户 › 激活。忘了激活，用户还是没有权限。
四个步骤（和演示同步）
  1. 1 建角色
    CREATE ROLE 'teacher';
  2. 2 授权
    GRANT SELECT, INSERT, UPDATE, DELETE ON teaching.sc TO 'teacher';
  3. 3 分给用户
    GRANT 'teacher' TO 'teacher2'@'localhost', 'teacher3'@'localhost';
  4. 4 激活
    SET DEFAULT ROLE 'teacher' TO 'teacher2'@'localhost', 'teacher3'@'localhost';
  5. 撤销
    REVOKE 'teacher' FROM 'teacher2'@'localhost';

注意第 3 步没有 ON。用户登录后执行 SELECT CURRENT_ROLE();，结果是 NONE 就说明角色还没激活。

动手看一步步把角色分给两位老师
root>
角色teacher
还没有角色
SELECT ON teaching.scINSERT ON teaching.scUPDATE ON teaching.scDELETE ON teaching.sc
teacher2没有角色
teacher3没有角色
teacher2 登录后执行SELECT * FROM teaching.sc;报错 1142