mysql数据库用户与权限管理学习笔记

发布时间:2019-12-08编辑:脚本学堂
分享下mysql数据库中用户与用户权限管理的方法,掌握下mysql对用户与权限的一些控制方法,有需要的朋友参考下。
如果你手动地修改授权表(使用INSERT、UPDATE或DELETE等等),你应该执行mysqladmin flush-privileges或mysqladmin reload告诉服务器再装载授权表,否则你的更改将不会生效,除非你重启服务器。

如果你直接更改了授权表但忘记重载,重启服务器后你的更改方生效。这样可能让你迷惑为什么你的更改没有什么变化!

四、MySQL用户账户管理
4.1 创建用户与授权:
4.1.1 创建用户:CREATE USER
基本语法:
CREATE USER username@host [IDENTIFIED BY 'password']

例子:
mysql> CREATE USER barlow@'%' IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.34 sec)

4.2.2 创建用户并授权:GRANT
基本语法:
GRANT priv_type[(column_list)] ON [object_type] priv_level TO username@'%' [IDENTIFIED BY [PASSWORD] 'password'];
■ priv_type:ALL或上面的权限表格中的权限。
■ priv_level:  *| *.*| db_name.*| db_name.tbl_name| tbl_name| db_name.routine_name

例子:
mysql> GRANT CREATE,INSERT,SELECT,UPDATE,DELETE ON testdb.* TO barlow@'%';
Query OK, 0 rows affected (0.21 sec)

mysql> SHOW GRANTS FOR 'barlow'@'%';
+-------------------------------------------------------------------------------------------------------+
| Grants for barlow@%                                                                                   |
+-------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'barlow'@'%' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' |
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE ON `testdb`.* TO 'barlow'@'%'                            |
+-------------------------------------------------------------------------------------------------------+
2 rows in set (0.01 sec)
通过上图可以看到,用户只拥有明确授权的权限。

4.3 用户管理
4.3.1 删除用户:
基本语法:
DROP USER 'username'@'host'

4.3.2 重命名用户:
基本语法:
RENAME USER old_name TO new_name

4.3.3 收回已授予的用户权限
基本语法:
REVOKE  priv_type [(column_list)]   [, priv_type [(column_list)]] ... ON [object_type] priv_level  FROM user [, user] ...

例子:
mysql> mysql> REVOKE INSERT ON testdb.* FROM  barlow@'%';
Query OK, 0 rows affected (0.01 sec)

可以看出,barlow@'%'用户已经没有了INSERT权限。                      

4.3.4 修改用户密码:
方法一:SET PASSWORD

基本语法:
SET PASSWORD FOR 'user_name'@'host' = PASSWORD('new_password');

例子:
mysql> SET PASSWORD FOR 'barlow'@'%' = PASSWORD('987654');
Query OK, 0 rows affected (0.07 sec)
说明:管理员可以修改任何用户的密码,但普通用户只能修改自己的密码。

用户修改自己的密码语法(也可以使用上述语法修改):
SET PASSWORD = PASSWORD('new_password');方法二:直接update mysql.user表的password字段实现修改密码:基本语法:mysql> use mysql
mysql> UPDATE user SET Password = PASSWORD('new_password') WHERE User='user_name' AND Host='host';
mysql> FLUSH PRIVILEGES;

例子:
mysql> use mysql
Database changed
mysql> UPDATE user SET Password = PASSWORD('redhat') WHERE User='barlow' AND Host='%';
Query OK, 1 row affected (0.03 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.08 sec)

4.4 找回root用户的密码:
如果root用户的密码忘记后,可以通过如下方法找回:
■ 停止mysqld服务
■ 启动mysqld_safe时传递两个参数:--skip-grant-tables --skip-networking
■ 启动mysqld服务
■ 使用直接update mysql.user表的password字段实现修改root用户密码

例子:
停止服务,修改mysqld_safe传递参数:
[root@localhost ~]# service mysqld stop
Shutting down MySQL........ SUCCESS!
[root@localhost ~]# vim /etc/init.d/mysqld

登录mysql修改密码:
[root@localhost ~]# service mysqld start
Starting MySQL....................... SUCCESS!
[root@localhost ~]# mysql            ##注意,这里已经不需要登录密码了
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 1
Server version: 5.6.13 Source distribution
Copyright (c) 2000, 2013, 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> UPDATE mysql.user SET Password = PASSWORD('123456') WHERE User='root';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 3  Changed: 0  Warnings: 0
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.03 sec)

恢复mysqld_safe传递参数:
[root@localhost ~]# service mysqld stop
Shutting down MySQL........ SUCCESS!
[root@localhost ~]# vim /etc/init.d/mysqld
$bindir/mysqld_safe --datadir="$datadir" --pid-file="$mysqld_pid_file_path" $other_args >/dev/null 2>&1 &
[root@localhost ~]# service mysqld start
Starting MySQL...... SUCCESS!
[root@localhost ~]# mysql   ##再次登录,提示需要密码
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
[root@localhost ~]# mysql -u root –p   ##使用新密码正常登录
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 2
Server version: 5.6.13 Source distribution
Copyright (c) 2000, 2013, 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.