本文最后更新于：2024年3月18日凌晨

SQL 用户与权限

用户管理

查看用户

用户信息表。

1	`SELECT * FROM mysql.user`

添加用户

1 2	`CREATE USER '用户名' IDENTIFIED BY ['PASSWORD'] [WITH] [DBAA\|RESOURCE\|CONNECT]; CREATE USER test IDENTIFIED BY '123456'`

必须拥有mysql数据库的全局CREATE USER权限，或拥有INSERT权限。
要在纯文本中指定密码，需忽略PASSWORD关键词，要把密码指定为由PASSWORD(）函数返回的混编值，需包含关键字PASSWORD
只有系统的超级用户才有权创建一个新的数据库用户。
新创建的数据库用户有三种权限：CONNECT,RESOURCE和DBA
CREATE USER命令中如果没有指定创建的新用户的权限，默认该用户拥有CONNECT权限，拥有CONNECT权限的用户不能创建新用户，不能创建模式，也不能创建基本表，只能登录数据库，由数据库管理员或其他用户授予他应有的权限，根据获得的授权情况他可以对数据库对象进行权限范围内的操作。
拥有RESOURCE权限的用户能创建基本表和视图，成为所创建对象的属主，但不能创建模式，不能创建新的用户，数据库对象的属主可以使用GRANT语句把该对象上的存取权限授予其他用户。

拥有DBA权限的用户时系统中的超级用户，可以创建新的用户，创建模式，创建基本表和视图等，DBA拥有对所有数据库对象的存取权限，还可以把这些权限授予一般用户。

	CREATE USER	CREATE SCHEMA	CREATE TABLE	登录数据库，执行数据查询和操纵
DBA	可以	可以	可以	可以
RESOURCE	不可以	不可以	可以	可以
CONNECT	不可以	不可以	不可以	可以，但必须拥有相应权限

重命名用户

1 2	`RENAME USER old_user TO new_user RENAME USER test TO test2`

设置用户名密码

1 2	`SET PASSWORD = PASSWORD('密码') -- 为当前用户设置密码。 SET PASSWORD FOR 用户名 = PASSWORD('密码') -- 为指定用户设置密码。`

修改密码

1	`UPDATE mysql.user SET AUTHENTICATION_STRING=PASSWORD('123456') WHERE USER='root';`

删除用户

1
2
3

DROP USER 用户名。

DROP USER test

赋予用户所有权限

1 2	`GRANT ALL PRIVILEGES ON . TO 'root'@'%' WITH GRANT OPTION; FLUSH PRIVILEGES; # 刷新权限。`

权限管理

查看权限

查看指定用户的权限。

1 2	`SHOW GRANTS FOR 用户名。 SHOW GRANTS FOR root@localhost;`

查看当前用户权限。

1
2
3

SHOW GRANTS;
SHOW GRANTS FOR CURRENT_USER;
SHOW GRANTS FOR CURRENT_USER();

刷新权限

1	`FLUSH PRIVILEGES`

GRANT

GRANT语句的一般格式为：

GRANT <权限> [,<权限>]...
ON <对象类型> <对象名> [,<对象类型> <对象名>]...
TO <用户>[,<用户>]...
[WITH GRANT OPTION];

其语义为：将对指定操作对象的指定操作权限授予指定的用户，发出该GRANT语句的可以是数据库管理员，也可以是该数据库对象创建者（即属主owner)，还可以是已经拥有该权限的用户，接收权限的用户可以是一个或多个具体用户，也可以是PUBLIC，即全体用户。
如果指定了WITH GRANT OPTION子句，则获得某种权限的用户还可以把这种权限再授予其他的用户，如果没有指定WITH GRANT OPTION子句，则获得某种权限的用户只能使用该权限，不能传播该权限。
SQL标准允许具有WITH GRANT OPTION的用户把相应权限或其子集传递授予其他用户，但不允许循环授权，即被授权者不能把权限再授回给授权者或其祖先。
权限选项
- all privileges：表示所有权限。
  - *.* ：表示所有库的所有表。
  - 库名。表名 ：表示某库下面的某表。

[例4.1]：把查询Student表的权限授给用户U1

1
2
3

GRANT SELECT
ON TABLE Student
TO U1;

[例4.2]：把对Student表和Course表的全部操作权限授予用户U2和U3

1
2
3

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

[例4.3]：把对表SC的查询权限授予所有用户。

1
2
3

GRANT SELECT
ON TABLE SC
TO PUBLIC;

[例4.4]：把查询Student表和修改学生学号的权限授给用户U4

1
2
3

GRANT UPDATE (Sno),SELECT
ON TABLE Student
TO U4

这里，实际上要授予U4用户的是对基本表Student的SELECT权限和对属性列Sno的UPDATE权限，对属性列授权时必须明确指出相应的属性列名。

[例4.5]：把对表SC的INSERT权限授予U5用户，并允许将此权限再授予其他用户。

GRANT INSERT
ON TABLE SC
TO U5
WITH GRANT OPTION

执行此SQL语句后，U5不仅拥有了对表SC的INSERT权限，还可以传播此权限，即由U5用户发上述GRANT命令给其他用户，例如U5可以将此权限授予U6（如下例）

[例4.6]

GRANT INSERT
ON TABLE SC
TO U6
WITH GRANT OPTINO

同样，U6还可以将此权限授予U7（如下例）

[例4.7]

1
2
3

GRANT INSERT
ON TABLE SC
TO U7;

因为U6未给U7传播的权限，因此U7不能再传播此权限。

REVOKE

授予用户的权限可以由数据库管理员或其他授权者用REVOKE语句收回，REVOKE语句的一般格式为：

1
2
3

REVOKE <权限> [,<权限>]...
ON <对象类型> <对象名> [,<对象类型> <对象名>]...
FROM <用户> [,<用户>]...[CASCADE|RESTRICT];

[例4.8]：把用户U4修改学生学号的权限收回。

1
2
3

REVOKE UPDATE(Sno)
ON TABLE Student
FROM U4;

[例4.9]：收回所有用户对表SC的查询权限。

1
2
3

REVOKE SELECT
ON TABLE SC
FROM PUBLIC;

[例4.10]：把用户U5对SC表的INSERT权限收回。

1
2
3

REVOKE INSERT
ON TABLE SC
FROM U5 CASCADE;

把用户U5的INSERT权限收回的同时，级联（CASCADE）收回了U6和U7的INSERT权限，否则系统将拒绝执行该命令，因为再例4.6中，U5将对SC表的INSERT权限授予了U6，而U6又将其授予了U7（例4.7)

注意:

这里默认值为CASCADE，有的数据库管理系统默认值为RESTRICR，将自动执行级联操作，如果U6或U7还从其他用户处获得对SC表的INSERT权限，则他们仍具有此权限，系统只收回直接或间接从U5处获得的权限。
SQL提供了非常灵活的授权机制，数据库管理员拥有对数据库中所有对象的所有权限，并可以根据实际情况将不同的权限授予不同的用户。
用户对自己建立的基本表和视图拥有全部的操作权限，并且可以用GRANT语句把其中某些权限授予其他用户，被授予的用户如果有"继续授权"的许可，还可以把获得的权限再授予其他用户。
所有授权出去的权力再必要时又都可以用REVOKE语句收回。
可见，用户可以"自主"地决定将数据的存取权限授予何人，决定是否也将”授权"的权限授予别人，，因此称这样的存取控制是自主存取控制。

权限列表

ALL [PRIVILEGES]    -- 设置除GRANT OPTION之外的所有简单权限。
ALTER    -- 允许使用ALTER TABLE
ALTER ROUTINE    -- 更改或取消已存储的子程序。
CREATE    -- 允许使用CREATE TABLE
CREATE ROUTINE    -- 创建已存储的子程序。
CREATE TEMPORARY TABLES        -- 允许使用CREATE TEMPORARY TABLE
CREATE USER        -- 允许使用CREATE USER, DROP USER, RENAME USER和REVOKE ALL PRIVILEGES
CREATE VIEW        -- 允许使用CREATE VIEW
DELETE    -- 允许使用DELETE
DROP    -- 允许使用DROP TABLE
EXECUTE        -- 允许用户运行已存储的子程序。
FILE    -- 允许使用SELECT...INTO OUTFILE和LOAD DATA INFILE
INDEX     -- 允许使用CREATE INDEX和DROP INDEX
INSERT    -- 允许使用INSERT
LOCK TABLES        -- 允许对您拥有SELECT权限的表使用LOCK TABLES
PROCESS     -- 允许使用SHOW FULL PROCESSLIST
REFERENCES    -- 未被实施。
RELOAD    -- 允许使用FLUSH
REPLICATION CLIENT    -- 允许用户询问从属服务器或主服务器的地址。
REPLICATION SLAVE    -- 用于复制型从属服务器（从主服务器中读取二进制日志事件）
SELECT    -- 允许使用SELECT
SHOW DATABASES    -- 显示所有数据库。
SHOW VIEW    -- 允许使用SHOW CREATE VIEW
SHUTDOWN    -- 允许使用MySQLadmin shutdown
SUPER    -- 允许使用CHANGE MASTER, KILL, PURGE MASTER LOGS和SET GLOBAL语句，mysqladmin debug命令;允许您连接（一次），即使已达到max_connections
UPDATE    -- 允许使用UPDATE
USAGE    -- "无权限”的同义词。
GRANT OPTION    -- 允许授予权限。

Software BackEnd Database 关系数据库 DCL

本博客所有文章除特别声明外，均采用 CC BY-SA 4.0 协议，转载请注明出处！

SQL 视图上一篇

SQL 函数下一篇