1. 创建和删除用户，mysql中的用户是由用户名和主机名来确定的
create user "user_name@host_name" identified by passwd; drop user user_name; ---------------也可以直接向mysql.user表添加记录---------------- insert into mysql.user(host,user,password) values ("host_name","user_name",password("pwd")); flush privileges;//使用这种方法必须添加用户，必须刷新权限加载到内存中
2.查看用户权限
show grants for user_name@host_name
3.修改用户密码
set password for user_name@'host_name'=password('...') or update mysql.user set password=password('...') where user='user_name'; flush privileges; ---------------修改ROOT用户密码也可以这样---------------------- mysqladmin -u root -p password 123456
4.查看所有用户
select host,user,password from mysql.user
5.授予权限
使用GRANT命令，其语法如下：
GRANT priv_type [(column_list)] [, priv_type [(column_list)]] ... ON [object_type] priv_level TO user_specification [, user_specification] ... [REQUIRE {NONE | ssl_option [[AND] ssl_option] ...}] [WITH with_option ...] GRANT PROXY ON user_specification TO user_specification [, user_specification] ... [WITH GRANT OPTION]
grant select,update,delete on test.* to user_name@host_name
MySQL数据库权限粒度可以分为全局、数据库、表、列、程序。
grant select,update on *.* to user_name@host_name //全局粒度 grant select,update on test.* to user_name@host_name //数据库级粒度 grant select,update on test.tab to user_name@host_name //表级粒度 grant select(name) on test.tab to user_name@host_name //列级粒度
6.回收权限
使用REVOKE命令，其语法如下
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] ...
在回收用户权限的时候，一定要对上GRANT时的权限粒度，否则回收权限不会成功，例如。
mysql> show grants for zhumuxian@localhost; +------------------------------------------------------------------------------------------------------------------+ | Grants for zhumuxian@localhost | +------------------------------------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'zhumuxian'@'localhost' IDENTIFIED BY PASSWORD '*00A51F3F48415C7D4E8908980D443C29C69B60C9' | | GRANT SELECT, UPDATE ON `mysql`.* TO 'zhumuxian'@'localhost' | +------------------------------------------------------------------------------------------------------------------+ 2 rows in set (0.00 sec)
这时使用不是数据库级别粒度回收是不会成功，接着看
mysql> revoke all privileges on *.* from zhumuxian@localhost; Query OK, 0 rows affected (0.00 sec) mysql> show grants for zhumuxian@localhost; +------------------------------------------------------------------------------------------------------------------+ | Grants for zhumuxian@localhost | +------------------------------------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'zhumuxian'@'localhost' IDENTIFIED BY PASSWORD '*00A51F3F48415C7D4E8908980D443C29C69B60C9' | | GRANT SELECT, UPDATE ON `mysql`.* TO 'zhumuxian'@'localhost' | +------------------------------------------------------------------------------------------------------------------+ 2 rows in set (0.00 sec)
再使用相同粒度的回收就可以了。
mysql> revoke select,update on mysql.* from zhumuxian@localhost; Query OK, 0 rows affected (0.00 sec) mysql> show grants for zhumuxian@localhost; +------------------------------------------------------------------------------------------------------------------+ | Grants for zhumuxian@localhost | +------------------------------------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'zhumuxian'@'localhost' IDENTIFIED BY PASSWORD '*00A51F3F48415C7D4E8908980D443C29C69B60C9' | +------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
如果你想回收某个用户所有的权限，不管什么权限级别，粒度，直接使用下面的语句即可。
revoke all privileges,grant option from zhumuxian@localhost
帐号安全管理
1.在Linux平台会有一个历史文件，它会记录mysql数据库的所有操作。
tail -20 ~/.mysql_history
这里最好使用/dev/null作为.mysql_history的软链接，这样所有的操作都被输出为空
$ ln -f -s /dev/null ~/.mysql_history
2.管理员口令丢失的处理
1.启动MySQL服务时加上--init-file,使其执行含有密码重置的脚本
首先停止Mysql服务，编写一个脚本updatePwd.txt,内容:
set password for root@localhost=password("xxxx");
进入mysql_safe模式，使用mysqld命令执行：
mysqld --init-file="./updatePwd.txt" //执行完这条语句，再关闭mysqld进程，停止mysql服务，接着以正常方式启动mysql即可
2.先停止mysql服务，然后以以下语句跳过权限验证：
mysqld --skip-grant-tables --skip-networking
然后使用以下语句登录mysql并修改root密码：
mysql -u root -p //此时密码为空 update mysql.user set password=password("xxxx") where user="root" flush privileges; --------然后重新启动mysql即可---------------