首页 > 代码库 > Mysql数据库理论基础之十 ---- 用户和权限管理、管理员密码重置
Mysql数据库理论基础之十 ---- 用户和权限管理、管理员密码重置
一、简介
由MySQL AB公司开发，是最流行的开放源码SQL数据库管理系统，主要特点：
1、是一种数据库管理系统
2、是一种关联数据库管理系统
3、是一种开放源码软件，且有大量可用的共享MySQL软件
4、MySQL数据库服务器具有快速、可靠和易于使用的特点
5、MySQL服务器工作在客户端/服务器模式下，或嵌入式系统中
InnoDB存储引擎将InnoDB表保存在一个表空间内，该表空间可由数个文件创建。这样，表的大小就能超过单独文件的最大容量。表空间可包括原始磁盘分区，从而使得很大的表成为可能。表空间的最大容量为64TB。
二、MySQL用户账号、權限管理
2.1. MySQL用户账号包括：用户名@主机名
用户名：16个字符以内
主机有以下几种表现方式
主机名： mysql, www.magedu.com
IP地址: 172.16.90.111
网络地址：172.16.0.0/255.255.0.0
通配符：%，_
%：任意字符 172.16.%.% ,%.magedu.com
_：任意一个 172.16_.%.%
2.2. 权限级别： 全局级别、 库级别、 表级别、 列级别、 存储过程 和存储函数级别
全局级别：SELECT * FROM db\G; 查询全局库级别的权限
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
**************************************************************************
CREATE USER username@host [IDENTIFIED BY ‘password‘] 创建用户
DROP USER ‘username‘@‘host‘; 刪除用戶
RENAME USER old_name TO new_name; 重命名用戶
SHOW GRANTS FOR ‘username‘@‘host‘; 查看用户权限列表
FLUSH PRIVILEGES ; 刷新权限列表
GRANT PRIVILEGES ON [object_type] db.* TO ‘username‘@‘host‘; 给用户增加权限
REVOKE SELECT ON db.* FROM ‘username‘@‘host‘; 取消用户的SELECT权限
2.3. 权限对应的作用范围明细：
2.4. 创建用户及权限：
2.4.1. 创建用户，并通过IDENTIFIED BY ‘password‘，设定密码
CREATE USER username@host [IDENTIFIED BY ‘password‘]
Usage:
mysql> CREATE USER test@localhost IDENTIFIED BY ‘test‘; #创建用户test本地数据库账号，密码test。
Query OK, 0 rows affected (0.00 sec)
mysql> FLUSH PRIVILEGES ; #刷新权限列表
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW GRANTS FOR test@localhost\G; #查看数据库账户test@localhost的权限列表
*************************** 1. row ***************************
Grants for test@localhost: GRANT USAGE ON *.* TO ‘test‘@‘localhost‘ IDENTIFIED BY PASSWORD ‘*94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29‘
1 row in set (0.00 sec)
2.4.2. 测试登录
重新打开另一个客户端，登录以test账号登录mysql
[root@lamp ~]# mysql -utest -p #登录mysql数据库以test用户，输入用户密码
mysql> SHOW DATABASES; #登录成功，查看该账号下的数据库
+--------------------+
| Database |
+--------------------+
| information_schema |
| test |
+--------------------+
2 rows in set (0.00 sec)
2.4.3. 权限设定GRANT
GRANT PRIVILEGES ON [object_type] db.* TO ‘username‘@‘host‘;
#指定权限PRIVILEGES
ON指定对象名称db.*
object_type指定对象类型:
TABLE 表
| FUNCTION 函数
| PROCEDURE
TO username@host 指定用户。
with_option:
GRANT OPTION
| MAX_QUERIES_PER_HOUR count
| MAX_UPDATES_PER_HOUR count
| MAX_CONNECTIONS_PER_HOUR count
| MAX_USER_CONNECTIONS count
object_type对象类型有：TABLE（表） FUNCTION（函数） PROCEDURE(程序、库)
Usage：GRANT EXECUTE ON FUNCTION db.abc TO ‘username‘@‘host‘; #授权给username@host用户,对
db数据库的abc函数有执行权限。
GRANT UPDATE(Age) ON db.testtb TO ‘username‘@‘host‘; #授权给username@host用户对db数据库的，testtb表的Age字段具有UPDATE权限。
2.4.3.1.测试开通创建库权限
A连接：
mysql> GRANT CREATE ON test01.* TO ‘test‘@‘%‘;
Query OK, 0 rows affected (0.01 sec)
B连接：（A连接命令执行前）
mysql> CREATE DATABASE test01;
ERROR 1007 (HY000): Can‘t create database ‘test‘; database exists
B连接：（A连接命令执行后）
mysql> CREATE DATABASE test01;
Query OK, 1 row affected (0.00 sec)
mysql> use test01
Database changed
mysql> CREATE TABLE testdb (ID INT UNSINED AUTO_INCREMENT NOT NULL,Name CHAR(20),PRIMARY KEY (ID));
Query OK, 0 rows affected (0.02 sec)
2.4.3.2.测试开通创建表权限，权限设定后需重连
A连接：
mysql> GRANT INSERT ON test01.* TO ‘test‘@‘%‘;
Query OK, 0 rows affected (0.01 sec)
mysql> GRANT SELECT ON test01.* TO ‘test‘@‘%‘;
Query OK, 0 rows affected (0.01 sec)
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.01 sec)
mysql> SHOW GRANTS FOR ‘test‘@‘%‘; #查看权限正常
+----------------------------------------------------------+
| Grants for test@% |
+----------------------------------------------------------+
| GRANT USAGE ON *.* TO ‘test‘@‘%‘ |
| GRANT CREATE ON `test`.* TO ‘test‘@‘%‘ |
| GRANT SELECT, INSERT, CREATE ON `test01`.* TO ‘test‘@‘%‘ |
+----------------------------------------------------------+
3 rows in set (0.00 sec)
[root@lamp ~]# tail /mydata/data/lamp.err #查看日志正常
170612 9:31:34 InnoDB: Completed initialization of buffer pool
170612 9:31:34 InnoDB: highest supported file format is Barracuda.
170612 9:31:35 InnoDB: Waiting for the background threads to start
170612 9:31:36 InnoDB: 1.1.8 started; log sequence number 5241255
170612 9:31:36 [Note] Server hostname (bind-address): ‘0.0.0.0‘; port: 3306
170612 9:31:36 [Note] - ‘0.0.0.0‘ resolves to ‘0.0.0.0‘;
170612 9:31:36 [Note] Server socket created on IP: ‘0.0.0.0‘.
170612 9:31:36 [Note] Event Scheduler: Loaded 0 events
170612 9:31:36 [Note] /usr/local/mysql/bin/mysqld: ready for connections.
Version: ‘5.5.28-log‘ socket: ‘/tmp/mysql.sock‘ port: 3306 Source distribution
B连接：（A连接命令执行前）
mysql> USE test01
Database changed
mysql> INSERT INTO testdb (Name) VALUES (‘TOM‘);
ERROR 1142 (42000): INSERT command denied to user ‘test‘@‘localhost‘ for table ‘testtb‘
B连接：（A连接命令执行且重新连接会话后）
mysql> use test01
Database changed
mysql> INSERT INTO testdb (Name) VALUES (‘TOM‘);
Query OK, 1 row affected (0.01 sec)
2.4.3.3.测试开通新增表中字段的权限，权限设定后需重连
A连接：
mysql> GRANT ALTER ON test01.* TO ‘test‘@‘%‘;
Query OK, 0 rows affected (0.00 sec)
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
B连接：（A连接命令执行前）
mysql> ALTER TABLE testdb ADD Age TINYINT UNSIGNED;
ERROR 1142 (42000): ALTER command denied to user ‘test‘@‘localhost‘ for table ‘testdb‘
B连接：（A连接命令执行且重新连接会话后）
mysql> use test01;
Database changed
mysql> ALTER TABLE testdb ADD Age TINYINT UNSIGNED;
Query OK, 1 row affected (0.03 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM testdb;
+----+------+------+
| ID | Name | Age |
+----+------+------+
| 1 | TOM | NULL |
+----+------+------+
1 row in set (0.00 sec)
2.4.3.4.测试开通新增表中字段的权限，权限设定后需重连
A连接：
mysql> GRANT UPDATE(Age) ON test01.testdb TO ‘test‘@‘%‘;
Query OK, 0 rows affected (0.00 sec)
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW GRANTS FOR ‘test‘@‘%‘;
+-----------------------------------------------------------------+
| Grants for test@% |
+-----------------------------------------------------------------+
| GRANT USAGE ON *.* TO ‘test‘@‘%‘ |
| GRANT CREATE ON `test`.* TO ‘test‘@‘%‘ |
| GRANT SELECT, INSERT, CREATE, ALTER ON `test01`.* TO ‘test‘@‘%‘ |
| GRANT UPDATE (Age) ON `test01`.`testdb` TO ‘test‘@‘%‘ |
+-----------------------------------------------------------------+
4 rows in set (0.00 sec)
B连接：（A连接命令执行且重新连接会话后）
mysql> use test01;
mysql> UPDATE testdb SET Age=30 WHERE ID=1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> SELECT * FROM testdb;
+----+------+------+
| ID | Name | Age |
+----+------+------+
| 1 | TOM | 30 |
+----+------+------+
1 row in set (0.00 sec)
mysql> UPDATE testdb SET Name=‘Jack‘ WHERE ID=1; #只有更新Age的權限，所以出錯
ERROR 1143 (42000): UPDATE command denied to user ‘test‘@‘localhost‘ for column ‘Name‘ in table ‘testdb‘
2.4.3.5.测试开通全局的权限，权限设定后需重连 XXX測試失敗
A连接：
mysql> SET GLOBAL tx_isolation=‘READ-UNCOMMITTED‘;
ERROR 1227 (42000): Access denied; you need (at least one of) the SUPER privilege(s) for this operation
mysql> show grants for ‘test‘@‘%‘;
+-----------------------------------------------------------------+
| Grants for test@% |
+-----------------------------------------------------------------+
| GRANT SUPER ON *.* TO ‘test‘@‘%‘ |
| GRANT CREATE ON `test`.* TO ‘test‘@‘%‘ |
| GRANT SELECT, INSERT, CREATE, ALTER ON `test01`.* TO ‘test‘@‘%‘ |
| GRANT UPDATE (Age) ON `test01`.`testdb` TO ‘test‘@‘%‘ |
+-----------------------------------------------------------------+
4 rows in set (0.00 sec)
B连接：（A连接命令执行且重新连接会话后）
mysql> use test01;
mysql> set GLOBAL tx_isolation = ‘READ-UNCOMMITTED‘;
ERROR 1227 (42000): Access denied; you need (at least one of) the SUPER privilege(s) for this operation
mysql>
2.4.3.6.测试撤銷授權
mysql> help revoke
Name: ‘REVOKE‘
Syntax:
REVOKE
priv_type [(column_list)]
[, priv_type [(column_list)]] ...
ON [object_type] priv_level
FROM user [, user] ...
REVOKE ALL PRIVILEGES, GRANT OPTION
FROM user [, user] ...
REVOKE PROXY ON user
FROM user [, user] ...
mysql> show grants for ‘test‘@‘%‘;
+-----------------------------------------------------------------+
| Grants for test@% |
+-----------------------------------------------------------------+
| GRANT SUPER ON *.* TO ‘test‘@‘%‘ |
| GRANT CREATE ON `test`.* TO ‘test‘@‘%‘ |
| GRANT SELECT, INSERT, CREATE, ALTER ON `test01`.* TO ‘test‘@‘%‘ |
| GRANT UPDATE (Age) ON `test01`.`testdb` TO ‘test‘@‘%‘ |
+-----------------------------------------------------------------+
4 rows in set (0.00 sec)
mysql> revoke SELECT ON test.* FROM ‘test‘@‘%‘;
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for ‘test‘@‘%‘;
+-----------------------------------------------------------------+
| Grants for test@% |
+-----------------------------------------------------------------+
| GRANT SUPER ON *.* TO ‘test‘@‘%‘ |
| GRANT CREATE ON `test`.* TO ‘test‘@‘%‘ |
| GRANT SELECT, INSERT, CREATE, ALTER ON `test01`.* TO ‘test‘@‘%‘ |
| GRANT UPDATE (Age) ON `test01`.`testdb` TO ‘test‘@‘%‘ |
+-----------------------------------------------------------------+
4 rows in set (0.00 sec)
2.4.3.7.测试开通SSL連接的权限，权限设定后需重连
2.4.3.8.测试开通其它的权限，权限设定后需重连
mysql> help drop user #刪除用戶
Name: ‘DROP USER‘
Syntax:
DROP USER user [, user] ...
mysql> help rename user #用戶重命名
Name: ‘RENAME USER‘
Syntax:
RENAME USER old_user TO new_user
[, old_user TO new_user] ...
2.5. MySQL数据库ROOT用户密码忘记解决方案步骤：
2.3.1. 先关闭mysqld进程，并修改配置文件/etc/my.cnf
[root@lamp ~]# service mysqld stop #先停止mysqld进程
Shutting down MySQL.. [ OK ]
[root@lamp ~]# vim /etc/init.d/mysqld #修改mysqld的启动脚本，修改内容如下红色框内
查找start: /start
新增跳过权限表及禁止网络登录： --skip-grant-tables --skip-networking
2.3.2. 启动mysqld服务，并登入修改mysql.user中的密码
[root@lamp ~]# service mysqld start #启动mysqld进程
Starting MySQL.. [ OK ]
[root@lamp ~]# mysql #此时登录mysql即可不需要用户名和密码
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.28-log Source distribution
Copyright (c) 2000, 2012, 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> USE mysql
Database changed
mysql> SELECT User,Host,Password FROM user; #查询user表的三个字段，是需要密码登录的
+------+-----------+-------------------------------------------+
| User | Host | Password |
+------+-----------+-------------------------------------------+
| root | localhost | *A198E6EEE923DA319BBF86C99624479A198E6EEE9 |
| root | lamp | *A198E6EEE9823DA319BBF86C99624479A198E6EEE9 |
| root | 127.0.0.1 | *A198E6EEE9DA319BBF86C99624479A198E6EEE9 |
| root | ::1 | *A198E6EEE93DA319BBF86C99624479A198E6EEE9 |
| test | localhost | *94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29 |
+------+-----------+-------------------------------------------+
5 rows in set (0.00 sec)
mysql> UPDATE user SET Password=PASSWORD(‘redhat‘) WHERE User=‘root‘;
#此时由于跳过了grant权限列表，所以只能通过修改user表的Password字段的值来修改用户密码。
Query OK, 0 rows affected (0.00 sec)
Rows matched: 4 Changed: 0 Warnings: 0
mysql> SELECT User,Host,Password FROM user; #查询user表的三个字段，是需要密码登录的
+------+-----------+-------------------------------------------+
| User | Host | Password |
+------+-----------+-------------------------------------------+
| root | localhost | *84BB5DF4823DA319BBF86C99624479A198E6EEE9 |
| root | lamp | *84BB5DF4823DA319BBF86C99624479A198E6EEE9 |
| root | 127.0.0.1 | *84BB5DF4823DA319BBF86C99624479A198E6EEE9 |
| root | ::1 | *84BB5DF4823DA319BBF86C99624479A198E6EEE9 |
| test | localhost | *94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29 |
+------+-----------+-------------------------------------------+
5 rows in set (0.00 sec)
mysql>\q
2.3.3. 关闭mysqld服务，并登入删除之前增加的安全启动参数
[root@lamp ~]# service mysqld stop #停止mysqld进程
Shutting down MySQL. [ OK ]
[root@lamp ~]# vim /etc/init.d/mysqld #修改启动脚本，把之前修改的内容去掉后保存退出
2.3.4. 启动mysqld服务，并使用修改后的密码登录
[root@lamp ~]# service mysqld start #启动mysqld进程
Starting MySQL.. [ OK ]
[root@lamp ~]# mysql #此时直接登录mysql提示输入用户及密码
ERROR 1045 (28000): Access denied for user ‘root‘@‘localhost‘ (using password: NO)
[root@lamp ~]# mysql -uroot -p #指定通过root账号登录 -p指定需要输入密码登录
Enter password:
---end---11---
本文出自 “风过无痕” 博客，请务必保留此出处http://wangfx.blog.51cto.com/1697877/1935056
Mysql数据库理论基础之十 ---- 用户和权限管理、管理员密码重置