MySQL用户管理

最新推荐文章于 2024-08-11 00:05:10 发布

bug--maker

最新推荐文章于 2024-08-11 00:05:10 发布

阅读量211

点赞数

分类专栏： Linux运维笔记文章标签： MySQL用户管理用户权限授权移除用户权限密码忘记处理

本文链接：https://blog.csdn.net/qq_36294875/article/details/80260713

版权

Linux运维笔记专栏收录该内容

80 篇文章 2 订阅

订阅专栏

MySQL用户和权限管理
RDBMS:需要确保用户的请求是接收认证并且确认合法之后才能够进行的合法访问用户是访问系统资源的凭证;
MySQL使用的是虚拟用户进行登陆的,这些用户不能够用于登录操作系统,类似于vsftpd;
用户名使用username@hostname组成,仅仅用于验证登录系统,并且密码使用自己的PASSWORD()函数进行加密;
用户访问数据库或者表的权限,需要单独进行授权,
MySQL服务器在启动的时候,回读取user,db,host,tables_priv coumns_priv,procs_priv,这几张表,并且在内存中生成授权表,任何一个SQL语句的执行,都是需要检查授权表的,这些信息用于生成授权表,并且常驻内存;
- user:用户帐号,全局权限,非权限字段;
- db:数据库级别的权限定义;
- host:废弃;
- tables_priv:表级别权限;
- columns_priv:表示列级别权限;
- procs_priv:表示存储过程和存储函数相关的权限;
- proxies_priv:表示代理用户级别的权限;
用户的定义:
用户=用户名@主机,用于名称需要限制在16个字符以内,通常在连接数据库时,主机名称回进行反解,这样会降低服务器性能,但是可以使用选项--skip-name-resolve;来略过主机名称反解,可以提高服务器性能;
主机名称允许为:
- IP地址:172.25.23.2;
- 网络地址:172.25.23.0/24;
- 支持使用通配符号:%;
查看库级别的权限

mysql> SELECT * FROM db\G;
*************************** 1. row ***************************
                 Host: %
                   Db: test
                 User: 
          Select_priv: Y
          Insert_priv: Y
          Update_priv: Y
          Delete_priv: Y
          Create_priv: Y
            Drop_priv: Y
           Grant_priv: N
      References_priv: Y
           Index_priv: Y
           Alter_priv: Y
Create_tmp_table_priv: Y
     Lock_tables_priv: Y
     Create_view_priv: Y
       Show_view_priv: Y
  Create_routine_priv: Y
   Alter_routine_priv: N
         Execute_priv: N
           Event_priv: Y
         Trigger_priv: Y

常见的权限:
- EVENT :表示用于创建事件调度器的;
- CREATE_TABLESPACE:用于创建表空间;
- PROCESS:表示用于创建进程;
- PROXY:表示代理用户的创建;
- RELOAD:表示重载授权表;
- SHUTDOWN:用于表示关闭服务的;
- CREATE_ROUTINE:表示存储函数;
- EXCUTE:表示执行存储过程或者存储函数;
- CREATE VIEW:表示创建视图;
- SHOW VIEW:表示显示视图;
- CREATE_TEMPORARY_TABLES:用于创建临时表的权限;
- TRIGGER:表示触发器,是用来创建主动数据库的创建,主动数据库是用来自我完成一些维护工作,例如在执行INSERT DELETE UPDATE时,同时进行日志或者其他信息的记录;
- 临时表:表示的是内存表,大小是有限的,Heap 16M;
- SUPER:可以用于改变主服务器的指向,修改全局变量的值,kill进程或者线程;
管理用户
创建用户

mysql> CREATE USER cactiusers@'%' IDENTIFIED BY 'cactiuser';
Query OK, 0 rows affected (0.00 sec)

因为用户的授权表需要载入内存,所以新创建的用户需要使用

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

通知MySQL服务器重新读取授权表,并且重新生成权限,但是使用mysql> FLUSH PRIVILEGES会自动刷新刷新授权表,但是使用CREATE USER会自动触发授权表;
创建用户的另一种方式

mysql> CREATE USER 'finley'@'localhost' IDENTIFIED BY 'lixun';
Query OK, 0 rows affected (0.00 sec)

还有最后一种方式,直接往数据库里面插入数据,这个是必须刷新授权表的

mysql> INSERT INTO user (User) VALUES('xiaoxiao');
Query OK, 1 row affected, 3 warnings (0.00 sec)

mysql> FLUSH PRIVILEGES;;
Query OK, 0 rows affected (0.00 sec)

查看用户的授信息

mysql> show grants for cactiuser@%;
----------------------------------------------------------------------------------+
| Grants for cactiuser@%                                                                                   |
+----------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'cactiuser'@'%' IDENTIFIED BY PASSWORD '*43DD7940383044FBDE5B177730FAD3405BC6DAD7' |
+----------------------------------------------------------------------------------------------------------+

默认创建的用户具有简单的权限,连接数据库并且查看数据库的权限

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.00 sec)

用户授权
USAGE:表示创建会话,连接MySQL服务器,认证通过的权限,并且可以访问公共的数据库,但是不能够创建数据库;
用户的权限类别就是前面提到的权限;
额外的选项:

with_option:
    GRANT OPTION
  | MAX_QUERIES_PER_HOUR count //每小时最多多少查询请求;
  | MAX_UPDATES_PER_HOUR count //每小时多少更新数据;
  | MAX_CONNECTIONS_PER_HOUR count //每小时只允许连接多少次请求;
  | MAX_USER_CONNECTIONS count //每一个用户帐号最多允许登录几次;
  不进行限定时,将数值修改为0;

授权新用户具有创建数据库的权限

mysql> GRANT CREATE ON cactidb.* TO 'cactiuser'@'%';
Query OK, 0 rows affected (0.00 sec)

验证新用户创建数据库的权限

mysql> CREATE DATABASE cactidb;
Query OK, 1 row affected (0.03 sec)

当然这里只能够创建指定的数据库;

mysql> CREATE DATABASE cactidb1;
ERROR 1044 (42000): Access denied for user 'cactiuser'@'%' to database 'cactidb1'

同时在这个数据库里面创建表是成功的

mysql> CREATE TABLE testdb (
    -> ID INT UNSIGNED AUTO_INCREMENT NOT NULL,
    -> Name CHAR(20),
    -> PRIMARY KEY(ID));
Query OK, 0 rows affected (0.00 sec)

这里是不能够插入数据,接下来需要,授权具有插入数据的权限

mysql> INSERT INTO testdb (Name) VALUES('Tom');
ERROR 1142 (42000): INSERT command denied to user 'cactiuser'@'localhost' for table 'testdb'

授权具有插入数据的权限,对于INSERT来说默认在两个权限上面生效,其中一个是表级别,另一个是字段级别,这里授权表级别的数据插入权限;

mysql> GRANT INSERT ON  cactidb.* TO 'cactiuser'@'%';
Query OK, 0 rows affected (0.00 sec)

cactiuaer尝试插入数据,仍然是是失败的

mysql> INSERT INTO testdb (Name) VALUES('Tom');
ERROR 1142 (42000): INSERT command denied to user 'cactiuser'@'localhost' for table 'testdb'

这个需要cacti用户重新连接MySQL服务器

mysql> USE cactidb;
Database changed
mysql> INSERT INTO testdb (Name) VALUES('Tom');
Query OK, 1 row affected (0.01 sec)

再次授权,具有Alter权限

mysql> GRANT ALTER ON   cactidb.* TO 'cactiuser'@'%';
Query OK, 0 rows affected (0.00 sec)

修改表,并且添加字段

mysql> DESC testdb;
+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| ID    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| Name  | char(20)         | YES  |     | NULL    |                |
+-------+------------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

mysql> ALTER TABLE testdb ADD Age TINYINT UNSIGNED;
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

查看添加的表的结构
用于授权用户只能够更新某个字段的权限

mysql> GRANT UPDATE(Age) ON   cactidb.testdb TO 'cactiuser'@'%';
Query OK, 0 rows affected (0.81 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

验证更新的权限,

mysql> use cactidb;
Database changed

mysql> UPDATE testdb SET Age=30 WHERE ID=1;
Query OK, 1 row affected (0.25 sec)
Rows matched: 1  Changed: 1  Warnings: 0

尝试更改非法的字段数据,这里是明确拒绝的;

mysql> UPDATE testdb SET Name='jerry' WHERE ID=1;
ERROR 1143 (42000): UPDATE command denied to user 'cactiuser'@'localhost' for column 'Name' in table 'testdb'

查看自己获得的权限
授权具有SUPER权限

mysql> GRANT SUPER ON *.* TO 'cactiuser'@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.29 sec)

在cactiuser上面尝试修改事务的隔离级别

mysql> SET GLOBAL tx_isolation='READ-UNCOMMITTED';
Query OK, 0 rows affected (0.00 sec)

如果超级用户在给某个用户授权时,允许了逐级授权,那么得到授权的用户仍然是可以进行筑级授权的,这个是比较危险的;
删除用户

DROP USER 'username'@'hostname'

用户重命名
取消授权
取消cactiuser的SELECT权限

mysql> REVOKE SELECT ON cactidb.* FROM 'cactiuser'@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

cactiuser用户无法进行SELECT权限

mysql> SELECT * FROM testdb;
ERROR 1142 (42000): SELECT command denied to user 'cactiuser'@'localhost' for table 'testdb'

管理员密码的恢复
首先停止MySQL服务器

[root@server60 ~]# service mysqld stop 
Shutting down MySQL.                                       [  OK  ]

编辑配置脚本,添加--skip-grant-tables --skip-networking表示跳过授权表
然后启动服务,移除默认的密码文件

[root@server60 ~]# service mysqld start
Starting MySQL..                                           [  OK  ]
[root@server60 ~]# mv ~/.my.cnf /mnt/

使用mysql命令直接进行连接

[root@server60 ~]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.12-log Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

然后需要修改用户的密码,这里如果使用下面的方式会出现错误;

mysql> SELECT User,Host,Password FROM user;
+------------+--------------+-------------------------------------------+
| User       | Host         | Password                                  |
+------------+--------------+-------------------------------------------+
| root       | localhost    | *28C1E2BE21B45562A34B6CC34A19CFAFC2F88F96 |
| root       | server60.com | *28C1E2BE21B45562A34B6CC34A19CFAFC2F88F96 |
| root       | 127.0.0.1    | *28C1E2BE21B45562A34B6CC34A19CFAFC2F88F96 |
| cactiusers | %            | *43DD7940383044FBDE5B177730FAD3405BC6DAD7 |
| finley     | localhost    | *B313F6B1C5E18A1F402D945FE0A9AD24284FF825 |
| xiaoxiao   |              |                                           |
| cactiuser  | %            | *43DD7940383044FBDE5B177730FAD3405BC6DAD7 |
+------------+--------------+-------------------------------------------+
7 rows in set (0.00 sec)

修改密码出现下面的错误

mysql> SET PASSWORD FOR 'root'@'localhost'=PASSWORD('westos');
ERROR 1290 (HY000): The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement

手动更改上面文件,来修改用户的密码

mysql> UPDATE user SET Password=PASSWORD('redhat') WHERE User='root';
Query OK, 3 rows affected (0.01 sec)
Rows matched: 3  Changed: 3  Warnings: 0

接下来停止Mysql服务器,然后恢复里面的选项

[root@server60 ~]# /etc/init.d/mysqld stop 
Shutting down MySQL.                                       [  OK  ]
[root@server60 ~]# vim /etc/init.d/mysqld

这里写图片描述

然后启动mysqld服务,并且使用新的秘密进行登陆

[root@server60 ~]# /etc/init.d/mysqld start
Starting MySQL..                                           [  OK  ]
[root@server60 ~]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.12-log Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>