mysql怎么管理权限 mysql权限管理的语句

如何设置mysql用户的权限

用户管理

营口网站制作公司哪家好，找创新互联！从网页设计、网站建设、微信开发、APP开发、响应式网站建设等网站项目制作，到程序开发，运营维护。创新互联于2013年成立到现在10年的时间，我们拥有了丰富的建站经验和运维经验，来保证我们的工作的顺利进行。专注于网站建设就选创新互联。

mysqluse mysql;

查看

mysql select host,user,password from user ;

创建

mysql create user zx_root IDENTIFIED by 'xxxxx'; //identified by 会将纯文本密码加密作为散列值存储

修改

mysqlrename user feng to newuser；//mysql 5之后可以使用，之前需要使用update 更新user表

删除

mysqldrop user newuser; //mysql5之前删除用户时必须先使用revoke 删除用户权限，然后删除用户，mysql5之后drop 命令可以删除用户的同时删除用户的相关权限

更改密码

mysql set password for zx_root =password('xxxxxx');

mysql update mysql.user set password=password('xxxx') where user='otheruser'

查看用户权限

mysql show grants for zx_root;

赋予权限

mysql grant select on dmc_db.* to zx_root;

回收权限

mysql revoke select on dmc_db.* from zx_root; //如果权限不存在会报错

mysql 权限设置

背景

在了解动态权限之前，我们先回顾下 MySQL 的权限列表。

权限列表大体分为服务级别和表级别，列级别以及大而广的角色（也是MySQL 8.0 新增）存储程序等权限。我们看到有一个特殊的 SUPER 权限，可以做好多个操作。比如 SET 变量，在从机重新指定相关主机信息以及清理二进制日志等。那这里可以看到，SUPER 有点太过强大，导致了仅仅想实现子权限变得十分困难，比如用户只能 SET 变量，其他的都不想要。那么 MySQL 8.0 之前没法实现，权限的细分不够明确，容易让非法用户钻空子。

那么 MySQL 8.0 把权限细分为静态权限和动态权限，下面我画了两张详细的区分图，图 1 为静态权限，图 2 为动态权限。

图 1- MySQL 静态权限的权限管理图

图 2-动态权限图

那我们看到其实动态权限就是对 SUPER 权限的细分。 SUPER 权限在未来将会被废弃掉。

我们来看个简单的例子，

比如， 用户 'ytt2@localhost', 有 SUPER 权限。

mysql show grants for ytt2@'localhost';+---------------------------------------------------------------------------------+| Grants for ytt2@localhost                                                       |+---------------------------------------------------------------------------------+| GRANT INSERT, UPDATE, DELETE, CREATE, ALTER, SUPER ON *.* TO ytt2@localhost |+---------------------------------------------------------------------------------+1 row in set (0.00 sec)

但是现在我只想这个用户有 SUPER 的子集，设置变量的权限。那么单独给这个用户赋予两个能设置系统变量的动态权限，完了把 SUPER 给拿掉。

mysql grant session_variables_admin,system_variables_admin on *.* to ytt2@'localhost';Query OK, 0 rows affected (0.03 sec)mysql revoke super on *.* from ytt2@'localhost';Query OK, 0 rows affected, 1 warning (0.02 sec)

我们看到这个 WARNINGS 提示 SUPER 已经废弃了。

mysql show warnings;

+---------+------+----------------------------------------------+

| Level   | Code | Message                                      |

+---------+------+----------------------------------------------+

| Warning | 1287 | The SUPER privilege identifier is deprecated |

+---------+------+----------------------------------------------+

1 row in set (0.00 sec)`

mysql show grants for ytt2@'localhost';

+-----------------------------------------------------------------------------------+

| Grants for ytt2@localhost                                                         |

+-----------------------------------------------------------------------------------+

| GRANT INSERT, UPDATE, DELETE, CREATE, ALTER ON *.* TO ytt2@localhost          |

| GRANT SESSION_VARIABLES_ADMIN,SYSTEM_VARIABLES_ADMIN ON *.* TO ytt2@localhost |

+-----------------------------------------------------------------------------------+

2 rows in set (0.00 sec)

当然图 2 上还有其它的动态权限，这里就不做特别说明了。

简述构成mysql权限控制步骤

1、mysql的权限是，从某处来的用户对某对象的权限。

2、mysql的权限采用白名单策略，指定用户能做什么，没有指定的都不能做。

3、权限校验分成两个步骤：

a、能不能连接，检查从哪里来，用户名和密码，常见错误 ERROR 1045 (28000): Access denied for user ...

b、能不能执行操作，粒度从粗到细，分别为：mysql.user, mysql.db, msql.tables_priv, mysql.columns_priv, mysql.proc_priv。

需要注意的是，这些表各有分工，但是在权限分配上有一定的重合。

可以这样理解，mysql 先检查对大范围是否有权限，如果没有再到小范围里去检查。比如：先检查对这个数据库是否有select权限，如果有，就允许执行。如果没有，再检查对表是否有select权限，一直到最细粒度，也没有权限，就拒绝执行。举例来说：要检查张三能否控制一个团，我只要先检查张三能否控制一个军，如果可以，就是有权限，如果不行，再检查张三能否控制一个师。因此，粒度控制越细，权限校验的步骤越多，性能越差，需要考虑。

4、mysql服务启动之后，就会把权限有关的表的数据读到内存中，对权限做的修改，是否会即时生效？要看情况，手动修改表数据，需要 flush privileges

5、创建用户 create user，修改密码 set password，注意 alter user只是设置密码过期，可以登录，但是不能执行任何操作，必须从新设置密码，删除用户 drop user

6、host+user 标示唯一的一个用户，也就是说都叫张三，从不同地方来的张三是两个用户，他们有不同的权限。

7、那么问题来了，表中有两条记录：'root'@'192.168.1.101' 和 'root'@'%', 现在root来登录，mysql 怎么匹配呢？认为是哪个root呢？

mysql 对用户进行了排序，先对host排序，再对user排序，小范围在前面，大范围在后面，从上往下匹配。

8、权限授予，grant 权限 on 对象 to 用户@哪里来 identified by 密码

9、收回权限，revoke 权限 on 对象 from 用户@哪里来，注意revoke 必须要与grant 对应，也就是说，只能收回授予的权限。

10、那么问题来了，我授予张三 select的权限，现在revoke all privileges 也不能收回张三select的权限，因为没有对张三 grant all privileges，怎么解决这个问题？

使用 revoke all privileges，grant option from user

11、权限级别：从某台主机来的某个用户，对某个数据库中某个表的某些列的某部分记录，是否有权限。

12、全局：对象是mysql服务的所有数据库，包含服务级的管理权限，比如showdown

13、数据库：对象是某一个数据库

14、表：对象是数据库中某个表

15、列：对象是表中的某个列，比如：grant select (name) on xxx to xxx

16、程序：对象是存储过程和方法。

17、information_scheme，数据库和表是存放数据的，那么谁来存放 数据库和表这些信息呢？ information_scheme 就是记录数据库和表的，需要注意的是，infromation_scheme没有对应的物理文件，它是mysql在内存中维护的。

18、权限设定原则：

a、尽量缩小权限

b、按业务，分离用户，不同的业务对应不同的用户

c、避免权限粒度太细，因为mysql权限检查，会影响性能。

19、文件泄密，linux下mysql客户端执行的操作记录在文件 ~/.mysql_history中，输出重定向/dev/null

20、密码丢失怎么办？

a、mysql启动，增加选项重置密码

b、mysql启动，增加选项不检查权限，登陆后修改密码，退出重启启动。

mysql中怎样设置用户和管理员的权限?

MySQL管理员应该知道如何设置MySQL用户账号，指出哪个用户可以连接服务器，从哪里连接，连接后能做什么。MySQL 3.22.11开始引入两条语句使得这项工作更容易做：GRANT语句创建MySQL用户并指定其权限，而REVOKE语句删除权限。两条语句扮演了MySQL数据库的前端角色，并提供与直接操作这些表的内容不同的另一种方法。CREATE和REVOKE语句影响4个表： 中.国站长站

还有第5个授权表（host），但它不受GRANT和REVOKE的影响。 中.国.站长站

当你对一个用户发出一条GRANT语句时，在user表中为该用户创建一条记录。如果语句指定任何全局权限（管理权限或适用于所有数据库的权限），这些也记录在user表中。如果你指定数据库、表和列级权限，他们被分别记录在db、tables_priv和columns_priv表中。

站.长站

用GRANT和REVOKE比直接修改授权表更容易些，然而，建议你阅读一下《MySQL安全性指南》。这些表异常重要，而且作为一名管理员，你应该理解它们如何超越GRANT和REVOKE语句的功能水平。

站.长站

在下面的章节中，我们将介绍如何设置MySQL用户账号并授权。我们也涉及如何撤权和从授权表中删除用户。 Chinaz

你可能也想考虑使用MySQLACCESS和mysql_setpermission脚本，它是MySQL分发的一部分，它们是Perl脚本，提供GRANT语句的另一种选择设置用户账号。MySQL_setpermission需要安装DBI支持。

Chinaz.com

1、创建用户并授权 Chinaz.com

GRANT语句的语法看上去像这样：

以下为引用的内容：

GRANT privileges (columns)

ON what

TO user IDENTIFIED BY "password"

WITH GRANT OPTION

要使用该语句，你需要填写下列部分：

中国.站.长站

privileges 站长.站

授予用户的权限，下表列出可用于GRANT语句的权限指定符：

中国站.长.站

上表显示在第一组的权限指定符适用于数据库、表和列，第二组数管理权限。一般，这些被相对严格地授权，因为它们允许用户影响服务器的操作。第三组权限特殊，ALL意味着“所有权限”，UASGE意味着无权限，即创建用户，但不授予权限。 Www^Chinaz^com

columns

中国.站长站

权限运用的列，它是可选的，并且你只能设置列特定的权限。如果命令有多于一个列，应该用逗号分开它们。

中国站长.站

what

站长.站

权限运用的级别。权限可以是全局的（适用于所有数据库和所有表）、特定数据库（适用于一个数据库中的所有表）或特定表的。可以通过指定一个columns字句是权限是列特定的。 Www@Chinaz@com

user 中国站长_站,为中文网站提供动力

权限授予的用户，它由一个用户名和主机名组成。在MySQL中，你不仅指定谁能连接，还有从哪里连接。这允许你让两个同名用户从不同地方连接。MySQL让你区分他们，并彼此独立地赋予权限。

中国.站长站

MySQL中的一个用户名就是你连接服务器时指定的用户名，该名字不必与你的Unix登录名或Windows名联系起来。缺省地，如果你不明确指定一个名字，客户程序将使用你的登录名作为MySQL用户名。这只是一个约定。你可以在授权表中将该名字改为nobody，然后以nobody连接执行需要超级用户权限的操作。

[中国站长站]

password Www_Chinaz_com

赋予用户的口令，它是可选的。如果你对新用户没有指定IDENTIFIED BY子句，该用户不赋给口令（不安全）。对现有用户，任何你指定的口令将代替老口令。如果你不指定口令，老口令保持不变，当你用IDENTIFIED BY时，口令字符串用改用口令的字面含义，GRANT将为你编码口令，不要象你用SET PASSWORD 那样使用password()函数。 中国站长_站,为中文网站提供动力

WITH GRANT OPTION子句是可选的。如果你包含它，用户可以授予权限通过GRANT语句授权给其它用户。你可以用该子句给与其它用户授权的能力。 [中国站长站]

用户名、口令、数据库和表名在授权表记录中是大小写敏感的，主机名和列名不是。 Www~Chinaz~com

一般地，你可以通过询问几个简单的问题来识别GRANT语句的种类：

中国.站长站

谁能连接，从那儿连接？

Chinaz.com

用户应该有什么级别的权限，他们适用于什么？

中国站长_站,为中文网站提供动力

用户应该允许管理权限吗？ 中国站长.站

下面就讨论一些例子。 站.长站

1.1 谁能连接，从那儿连接？

Chinaz@com

你可以允许一个用户从特定的或一系列主机连接。有一个极端，如果你知道降职从一个主机连接，你可以将权限局限于单个主机：

GRANT ALL ON samp_db.* TO boris@localhost IDENTIFIED BY "ruby"

中国站.长.站

GRANT ALL ON samp_db.* TO fred@res.mars.com IDENTIFIED BY "quartz" 中国站长.站

(samp_db.*意思是“samp_db数据库的所有表)另一个极端是，你可能有一个经常旅行并需要能从世界各地的主机连接的用户max。在这种情况下，你可以允许他无论从哪里连接：

中国站长.站

GRANT ALL ON samp_db.* TO max@% IDENTIFIED BY "diamond" 站.长站

“%”字符起通配符作用，与LIKE模式匹配的含义相同。在上述语句中，它意味着“任何主机”。所以max和max@%等价。这是建立用户最简单的方法，但也是最不安全的。 Www@Chinaz@com

取其中，你可以允许一个用户从一个受限的主机集合访问。例如，要允许mary从snake.net域的任何主机连接，用一个%.snake.net主机指定符：

[中国站长站]

GRANT ALL ON samp_db.* TO mary@.snake.net IDENTIFIED BY "quartz";

中国站长_站,为中文网站提供动力

如果你喜欢，用户标识符的主机部分可以用IP地址而不是一个主机名来给定。你可以指定一个IP地址或一个包含模式字符的地址，而且，从MySQL 3.23，你还可以指定具有指出用于网络号的位数的网络掩码的IP号： Chinaz~com

GRANT ALL ON samp_db.* TO boris@192.168.128.3 IDENTIFIED BY "ruby"

中国站长.站

GRANT ALL ON samp_db.* TO fred@192.168.128.% IDENTIFIED BY "quartz" Chinaz@com

GRANT ALL ON samp_db.* TO rex@192.168.128.0/17 IDENTIFIED BY "ruby" [中国站长站]

第一个例子指出用户能从其连接的特定主机，第二个指定对于C类子网192.168.128的IP模式，而第三条语句中，192.168.128.0/17指定一个17位网络号并匹配具有192.168.128头17位的IP地址。 中国.站.长站

如果MySQL抱怨你指定的用户值，你可能需要使用引号（只将用户名和主机名部分分开加引号）。 Chinaz^com

GRANT ALL ON samp_db.president TO "my friend"@"boa.snake.net"

1.2 用户应该有什么级别的权限和它们应该适用于什么？ 站.长站

你可以授权不同级别的权限，全局权限是最强大的，因为它们适用于任何数据库。要使ethel成为可做任何事情的超级用户，包括能授权给其它用户，发出下列语句： 中.国.站长站

GRANT ALL ON *.* TO ethel@localhost IDENTIFIED BY "coffee" WITH GRANT OPTION Chinaz~com

ON子句中的*.*意味着“所有数据库、所有表”。从安全考虑，我们指定ethel只能从本地连接。限制一个超级用户可以连接的主机通常是明智的，因为它限制了试图破解口令的主机。

Www~Chinaz~com

有些权限（FILE、PROCESS、RELOAD和SHUTDOWN）是管理权限并且只能用"ON *.*"全局权限指定符授权。如果你愿意，你可以授权这些权限，而不授权数据库权限。例如，下列语句设置一个flush用户，他只能发出flush语句。这可能在你需要执行诸如清空日志等的管理脚本中会有用： 站.长站

GRANT RELOAD ON *.* TO flushl@localhost IDENTIFIED BY "flushpass" 中国站.长.站

一般地，你想授权管理权限，吝啬点，因为拥有它们的用户可以影响你的服务器的操作。 Www~Chinaz~com

数据库级权限适用于一个特定数据库中的所有表，它们可通过使用ON db_name.*子句授予： Chinaz^com

GRANT ALL ON samp_db TO bill@racer.snake.net INDETIFIED BY "rock" Chinaz@com

GRANT SELECT ON samp_db TO ro_user@% INDETIFIED BY "rock"

中国.站.长站

第一条语句向bill授权samp_db数据库中所有表的权限，第二条创建一个严格限制访问的用户ro_user（只读用户），只能访问samp_db数据库中的所有表，但只有读取，即用户只能发出SELECT语句。

你可以列出一系列同时授予的各个权限。例如，如果你想让用户能读取并能修改现有数据库的内容，但不能创建新表或删除表，如下授予这些权限： Www@Chinaz@com

GRANT SELECT,INSERT,DELETE,UPDATE ON samp_db TO bill@snake.net INDETIFIED BY "rock"

中国.站.长站

对于更精致的访问控制，你可以在各个表上授权，或甚至在表的每个列上。当你想向用户隐藏一个表的部分时，或你想让一个用户只能修改特定的列时，列特定权限非常有用。如：

GRANT SELECT ON samp_db.member TO bill@localhost INDETIFIED BY "rock"

中.国.站.长.站

GRANT UPDATE (expiration) ON samp_db. member TO bill@localhost 中国站长_站,为中文网站提供动力

第一条语句授予对整个member表的读权限并设置了一个口令，第二条语句增加了UPDATE权限，当只对expiration列。没必要再指定口令，因为第一条语句已经指定了。

Www^Chinaz^com

如果你想对多个列授予权限，指定一个用逗号分开的列表。例如，对assistant用户增加member表的地址字段的UPDATE权限，使用如下语句，新权限将加到用户已有的权限中：

[中国站长站]

GRANT UPDATE (street,city,state,zip) ON samp_db TO assistant@localhost

中国.站.长站

通常，你不想授予任何比用户确实需要的权限宽的权限。然而，当你想让用户能创建一个临时表以保存中间结果，但你又不想让他们在一个包含他们不应修改内容的数据库中这样做时，发生了要授予在一个数据库上的相对宽松的权限。你可以通过建立一个分开的数据库（如tmp）并授予开数据库上的所有权限来进行。例如，如果你想让来自mars.net域中主机的任何用户使用tmp数据库，你可以发出这样的GRANT语句：

中.国站长站

GRANT ALL ON tmp.* TO ""@mars.net

Chinaz^com

在你做完之后，用户可以创建并用tmp.tbl_name形式引用tmp中的表（在用户指定符中的""创建一个匿名用户，任何用户均匹配空白用户名）。 中.国站长站

1.3 用户应该被允许管理权限吗？

中.国.站.长.站

你可以允许一个数据库的拥有者通过授予数据库上的所有拥有者权限来控制数据库的访问，在授权时，指定WITH GRANT OPTION。例如：如果你想让alicia能从big.corp.com域的任何主机连接并具有sales数据库中所有表的管理员权限，你可以用如下GRANT语句：

[中国站长站]

GRANT ALL ON sales.* TO alicia@%.big.corp.com INDETIFIED BY "applejuice" WITH GRANT OPTION 中国站.长.站

在效果上WITH GRANT OPTION子句允许你把访问授权的权利授予另一个用户。要注意，拥有GRANT权限的两个用户可以彼此授权。如果你只给予了第一个用户SELECT权限，而另一个用户有GRANT加上SELECT权限，那么第二个用户可以是第一个用户更“强大”。 中国站长_站,为中文网站提供动力

2 撤权并删除用户

Chinaz_com

要取消一个用户的权限，使用REVOKE语句。REVOKE的语法非常类似于GRANT语句，除了TO用FROM取代并且没有INDETIFED BY和WITH GRANT OPTION子句： 中国站.长站

REVOKE privileges (columns) ON what FROM user 中国.站.长站

user部分必须匹配原来GRANT语句的你想撤权的用户的user部分。privileges部分不需匹配，你可以用GRANT语句授权，然后用REVOKE语句只撤销部分权限。

中.国站长站

REVOKE语句只删除权限，而不删除用户。即使你撤销了所有权限，在user表中的用户记录依然保留，这意味着用户仍然可以连接服务器。要完全删除一个用户，你必须用一条DELETE语句明确从user表中删除用户记录：

中国站.长.站

%mysql -u root MySQL

MySQLDELETE FROM user

-WHERE User="user_name" and Host="host_name";

MySQLFLUSH PRIVILEGES; 中.国站长站

DELETE语句删除用户记录，而FLUSH语句告诉服务器重载授权表。（当你使用GRANT和REVOKE语句时，表自动重载，而你直接修改授权表时不是。 [中国站长站]

MySQL操作基础之DCL语句，用户权限管理

DCL 语句主要是DBA 用来管理系统中的对象权限时所使用的，一般的开发人员很少使用。

示例：创建只能本地登录（localhost）的账号zhangsan，密码为mypass

创建用户并授权，语法格式（8.0以前版本）：

示例：

8.0以前版本创建用户授权一步到位。

8.0以后的版本，创建用户后，再进行授权操作。

格式：

示例：

有两种方式查看。

第一种方式：

第二种方式：

g 相当于’;’

G使每个字段打印到单独的行，也有’;’的作用

只能查出哪个数据库的哪张表的权限，如查userwordpress在mysql数据库的user表的权限，显示都是N（no），没有权限，如果查root用户就都是Y（yes）选择了。

修改密码的两种方式：

1）Alter user

2）set password

3）8.0以前版本grant，8.0以后版本无法使用

修改host，访问IP：