ITPub博客

首页 > 数据库 > MySQL > mysql用户权限管理

mysql用户权限管理

原创 MySQL 作者:linxueguo 时间:2021-09-06 09:32:41 0 删除 编辑

一、用户管理

用户权限相关的表

1 --user

user 表是 MySQL 中最重要的一个权限表,用来记录允许连接到服务器的账号信息。需要注意的是,在 user 表里启用的所有权限都是全局级的,适用于所有数据库。

mysql> select Host,User,Select_priv,Insert_priv,Update_priv,Delete_priv,Create_priv,Drop_priv from user;

 

2 --db

db 表比较常用,是 MySQL 数据库中非常重要的权限表,表中存储了用户对某个数据库的操作权限

mysql> select Host,Db,User,Select_priv,Insert_priv,Update_priv,Delete_priv,Create_priv,Drop_priv from db;

 

3 --tables_priv

tables_priv 表用来对单个表进行权限设置

mysql> select Host,Db,User,Table_name,Grantor,Table_priv,Column_priv from tables_priv;

 

4 --columns_priv

columns_priv 表用来对单个数据列进行权限设置

mysql> select Host,Db,User,Table_name,Column_name,Column_priv from columns_priv;

5 --procs_priv

procs_priv 表可以对存储过程和存储函数进行权限设置

mysql> select Host,Db,User,Table_name,Column_name,Column_priv from columns_priv;

 

创建用户

MySQL 在安装时,会默认创建一个名为 root 的用户,该用户拥有超级权限,可以控制整个 MySQL 服务器。为了避免有人恶意使用 root 用户控制数据库,我们通常创建一些具有适当权限的用户。

MySQL 提供了以下 3 种方法创建用户。

使用 CREATE USER 语句创建用户

mysql.user 表中添加用户

使用 GRANT 语句创建用户

新创建的用户拥有的权限很少,它们只能执行不需要权限的操作。如登录 MySQL 、使用 SHOW 语句查询所有存储引擎和字符集的列表等。如果两个用户的用户名相同,但主机名不同, MySQL 会将它们视为两个用户,并允许为这两个用户分配不同的权限集合。

1 CREATE USER 语句

可以使用 CREATE USER  语句来创建 MySQL 用户,并设置相应的密码。其基本语法格式如下:

CREATE USER < 用户 > [ IDENTIFIED BY [ PASSWORD ] 'password' ] [ , 用户 [ IDENTIFIED BY [ PASSWORD ] 'password' ]]

例子:

mysql> CREATE USER 'test1'@'%' identified by 'test1','test2'@'%' identified by 'test2';

 

在实际应用中,我们应避免明文指定密码,可以通过 PASSWORD 关键字使用密码的哈希值设置密码。

mysql> SELECT password('test3');

 

mysql> create user 'test3'@'%' identified by password '*F357E78CABAD76FD3F1018EF85D78499B6ACC431';

使用test3/test3 登录成功

 

 

2、 mysql.user 表中添加

可以使用 INSERT 语句将用户的信息添加到 mysql.user 表中,但必须拥有对 mysql.user 表的 INSERT 权限。通常 INSERT 语句只添加 Host User  authentication_string 3 个字段的值。

MySQL 5.7 user 表中的密码字段从 Password 变成了 authentication_string ,如果你使用的是 MySQL 5.7 之前的版本,将 authentication_string 字段替换成 Password 即可。

使用 INSERT  语句创建用户的代码如下:

INSERT INTO mysql.user(Host, User,  authentication_string, ssl_cipher, x509_issuer, x509_subject) VALUES ('hostname', 'username', PASSWORD('password'), '', '', '');

由于 mysql 数据库的 user 表中, ssl_cipher x509_issuer x509_subject 3 个字段没有默认值,所以向 user 表插入新记录时,一定要设置这 3 个字段的值,否则 INSERT 语句将不能执行。

例子:

mysql> INSERT INTO mysql.user(Host, User, authentication_string, ssl_cipher, x509_issuer, x509_subject) VALUES ('%', 'test4', PASSWORD('test4'), '', '', '');

 

使用 insert 语句添加用户后需要刷新权限才会生效

mysql> flush privileges;

3、 使用 GRANT 语句 (推荐该方法)

虽然 CREATE USER INSERT INTO 语句都可以创建普通用户,但是这两种方式不便授予用户权限。于是 MySQL 提供了 GRANT 语句。

使用 GRANT 语句创建用户的基本语法形式如下 :

GRANT priv_type ON database.table TO user [IDENTIFIED BY [PASSWORD] 'password']

其中:

priv_type 参数表示新用户的权限;

database.table 参数表示新用户的权限范围,即只能在指定的数据库和表上使用自己的权限;

user 参数指定新用户的账号,由用户名和主机名构成;

IDENTIFIED BY 关键字用来设置密码;

password 参数表示新用户的密码,新建用户必须同时指定密码。

例子:

mysql> grant all privileges on *.* to 'test5'@'%' identified by 'test5';

 

Grant 语句也可以用于授权、修改用户密码。

修改用户

MySQL 中,我们可以使用 RENAME USER 语句修改一个或多个已经存在的用户账号。

语法格式如下:

RENAME USER < 旧用户 > TO < 新用户 >

其中:

< 旧用户 > :系统中已经存在的 MySQL 用户账号。

< 新用户 > :新的 MySQL 用户账号。

使用 RENAME USER 语句时应注意以下几点:

RENAME USER 语句用于对原有的 MySQL 用户进行重命名。

若系统中旧账户不存在或者新账户已存在,该语句执行时会出现错误。

使用 RENAME USER 语句,必须拥有 mysql 数据库的 UPDATE 权限或全局 CREATE USER 权限。

例子:

mysql> rename user 'test1'@'%' to 'test1s'@'%';

 

删除用户

MySQL 数据库中,可以使用 DROP USER 语句删除用户,也可以直接在 mysql.user 表中删除用户以及相关权限。

1. 使用 DROP USER 语句删除普通用户

使用 DROP USER 语句删除用户的语法格式如下:

DROP USER < 用户 1> [ , < 用户 2> ]

其中,用户用来指定需要删除的用户账号。

使用 DROP USER 语句应注意以下几点:

DROP USER 语句可用于删除一个或多个用户,并撤销其权限。

使用 DROP USER 语句必须拥有 mysql 数据库的 DELETE 权限或全局 CREATE USER 权限。

DROP USER 语句的使用中,若没有明确地给出账户的主机名,则该主机名默认为“ % ”。

注意:用户的删除不会影响他们之前所创建的表、索引或其他数据库对象,因为 MySQL 并不会记录是谁创建了这些对象。

例子:

mysql> drop user test1s;

mysql> drop user test;

 

mysql> drop user 'test'@'localhost';

 

2. 使用 DELETE 语句删除普通用户

可以使用 DELETE 语句直接删除 mysql.user 表中相应的用户信息,但必须拥有 mysql.user 表的 DELETE 权限。其基本语法格式如下:

DELETE FROM mysql.user WHERE Host='hostname' AND User='username';

Host User 这两个字段都是 mysql.user 表的主键。因此,需要两个字段的值才能确定一条记录。

二、权限管理

权限查看

MySQL 中,可以通过查看 mysql.user 表中的数据记录来查看相应的用户权限,也可以使用 SHOW GRANTS 语句查询用户的权限。

1 、使用 select 查看权限

mysql 数据库下的 user 表中存储着用户的基本权限,可以使用 SELECT 语句来查看 , 相关的表如下

SELECT * FROM mysql.user;

Select * from mysql.db;

Select * from tables_priv;

Select * from columns_priv;

Select * from procs_priv;

查询上述表,必须拥有对相关表的查询权限。

常用查询:

mysql> select Host,User,Select_priv,Insert_priv,Update_priv,Delete_priv,Create_priv,Drop_priv from user;

mysql> select Host,Db,User,Select_priv,Insert_priv,Update_priv,Delete_priv,Create_priv,Drop_priv from db;

mysql> select Host,Db,User,Table_name,Grantor,Table_priv,Column_priv from tables_priv;

mysql> select Host,Db,User,Table_name,Column_name,Column_priv from columns_priv;

mysql> select Host,Db,User,Table_name,Column_name,Column_priv from columns_priv;

可根据需要调整。

新创建的用户只有登录 MySQL 服务器的权限,没有任何其它权限,不能查询 user 表。

2 、使用 show grant 查看权限

使用 SHOW GRANTS FOR 语句查看权限。其语法格式如下:

> SHOW GRANTS FOR 'username'@'hostname';

其中,username 表示用户名, hostname 表示主机名或主机 IP ,未指定hostname 的情况下, mysql 默认主机名为 %

例子:

mysql> show grants for test2;

mysql> show grants for test2@'localhost';

 

授权

MySQL 中,拥有 GRANT 权限的用户才可以执行 GRANT 语句,其语法格式如下:

GRANT priv_type [(column_list)] ON database.table

TO user [IDENTIFIED BY [PASSWORD] 'password']

[, user[IDENTIFIED BY [PASSWORD] 'password']] ...

[WITH with_option [with_option]...]

其中:

priv_type 参数表示权限类型;

columns_list 参数表示权限作用于哪些列上,省略该参数时,表示作用于整个表;

database.table 用于指定权限的级别;

user 参数表示用户账户,由用户名和主机名构成,格式是“ 'username'@'hostname' ”;

IDENTIFIED BY 参数用来为用户设置密码;

password 参数是用户的新密码。

 

WITH 关键字后面带有一个或多个 with_option 参数。这个参数有 5 个选项,详细介绍如下:

GRANT OPTION :被授权的用户可以将这些权限赋予给别的用户;

MAX_QUERIES_PER_HOUR count :设置每个小时可以允许执行 count 次查询;

MAX_UPDATES_PER_HOUR count :设置每个小时可以允许执行 count 次更新;

MAX_CONNECTIONS_PER_HOUR count :设置每小时可以建立 count 个连接 ;

MAX_USER_CONNECTIONS count :设置单个用户可以同时具有的 count 个连接。

 

MySQL 中可以授予的权限有如下几组:

列权限,和表中的一个具体列相关。例如,可以使用 UPDATE 语句更新表 students name 列的值的权限。

表权限,和一个具体表中的所有数据相关。例如,可以使用 SELECT 语句查询表 students 的所有数据的权限。

数据库权限,和一个具体的数据库中的所有表相关。例如,可以在已有的数据库 mytest 中创建新表的权限。

用户权限,和 MySQL 中所有的数据库相关。例如,可以删除已有的数据库或者创建一个新的数据库的权限。

 

对应地,在 GRANT 语句中可用于指定权限级别的值有以下几类格式:

* :表示当前数据库中的所有表。

*.* :表示所有数据库中的所有表。

db_name.* :表示某个数据库中的所有表, db_name 指定数据库名。

db_name.tbl_name :表示某个数据库中的某个表或视图, db_name 指定数据库名, tbl_name 指定表名或视图名。

db_name.routine_name :表示某个数据库中的某个存储过程或函数, routine_name 指定存储过程名或函数名。

TO 子句: 如果权限被授予给一个不存在的用户,MySQL 会自动执行一条 CREATE USER 语句来创建这个用户,但同时必须为该用户设置密码

例子

1 、授权用户权限

mysql>  select Host,User,Select_priv,Insert_priv,Update_priv,Delete_priv,Create_priv,Drop_priv from user;

 

2 、授权数据库权限

mysql> grant select on testdb.* to test6;

3 、授权表权限

mysql> grant select on testdb.tb1 to test3@'%';

 

4 、授权字段权限

mysql> grant select (id,vname) on testdb.tb1 to test5@'%';

mysql> grant select (id) on testdb.tb1 to test2@'%';

5 、授权过程(函数)权限

 

回收权限

MySQL 中,可以使用 REVOKE 语句删除某个用户的某些权限(此用户不会被删除),在一定程度上可以保证系统的安全性

使用 REVOKE 语句删除权限的语法格式有两种形式,如下所示:

1 )第一种

删除用户 某些特定的权限 ,语法格式如下:

REVOKE priv_type [(column_list)]...
ON database.table
FROM user [, user]...

REVOKE 语句中的参数与 GRANT 语句的参数意思相同。其中:

priv_type 参数表示权限的类型;

column_list 参数表示权限作用于哪些列上,没有该参数时作用于整个表上;

user 参数由用户名和主机名构成,格式为 “username'@'hostname'”

例子:

 

mysql> revoke SELECT (id) ON `testdb`.`tb1` from 'test2'@'%';

 

2 )第二种

删除特定 用户的所有权限 ,语法格式如下:

REVOKE ALL PRIVILEGES, GRANT OPTION FROM user [, user] ...

例子:

 

mysql> revoke all privileges on *.* from 'test5'@'%';

删除用户权限需要注意以下几点:

REVOKE 语法和 GRANT 语句的语法格式相似,但具有相反的效果。

要使用 REVOKE 语句,必须拥有 MySQL 数据库的全局 CREATE USER 权限或 UPDATE 权限。 USAGE 表示没有任何权限。

 

三、密码修改

MySQL 中,只有 root 用户可以通过更新 MySQL 数据库来更改密码 ,主要有以下三种方式。

1 Set 语句

基本语法:

SET PASSWORD FOR 'username'@'hostname' = PASSWORD ('newpwd');

注意:新密码必须使用 PASSWORD() 函数来加密,如果不使用 PASSWORD() 加密,也会执行成功,但是用户会无法登录。

如果是普通用户修改密码,可省略 FOR 子句来更改自己的密码。语法格式如下:

SET PASSWORD = PASSWORD('newpwd');

例子:

mysql> set password for test4@'localhost'=password('test4');

 

2 Update 语句

使用 root 用户登录 MySQL 服务器后,可以使用 UPDATE 语句修改 MySQL 数据库的 user 表的 authentication_string 字段,从而修改普通用户的密码。 UPDATA 语句的语法如下:

UPDATE MySQL.user SET authentication_string = PASSWORD("newpwd") WHERE User = "username" AND Host = "hostname";

注意,执行 UPDATE 语句后,需要执行 FLUSH PRIVILEGES 语句重新加载用户权限。

例子:

mysql> update user set authentication_string=password('test4new') where user='test4' and host='localhost';

 

 

 

3 Grant 语句

可以在全局级别使用 GRANT USAGE (表示不变更权限)   语句指定某个账户的密码而不影响账户当前的权限。需要注意的是,使用 GRANT 语句修改密码,必须拥有 GRANT 权限。一般情况下最好使用该方法来指定或修改密码。语法格式如下:

GRANT USAGE ON *.* TO 'user'@’hostname’ IDENTIFIED BY 'newpwd';

例子:
mysql> grant usage on *.* to test4@localhost identified by 'test4';

 

4 mysqladmin 工具

root 用户可以使用 mysqladmin 命令来修改密码, mysqladmin 的语法格式如下:

mysqladmin -u username -h hostname -p password "newpwd"

语法参数说明如下:

usermame 指需要修改密码的用户名称,在这里指定为 root 用户;

hostname 指需要修改密码的用户主机名,该参数可以不写,默认是 localhost

password 为关键字,而不是指旧密码;

newpwd 为新设置的密码,必须用双引号括起来。如果使用单引号会引发错误,可能会造成修改后的密码不是你想要的。
执行完上面的语句,root 用户的密码将被修改为 “newpwd”

例子:

[root@lxgmc2 ~]# mysqladmin -u root -p password "rootnew";

 

5 root 忘记密码重置

/etc/my.cnf 增加配置 skip-grant-tables=1 ,启用无密码登录

重启mysql 服务并登录

service mysqld restart

通过上述方法(set update grant) 修改 root 密码,修改完成后关闭无密码登录,重启 mysql

 


来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/31374736/viewspace-2790490/,如需转载,请注明出处,否则将追究法律责任。

上一篇: 没有了~
请登录后发表评论 登录
全部评论

注册时间:2016-07-16

  • 博文量
    10
  • 访问量
    3357