MySQL 权限管理

创建用户：
mysql> create user root@'%' identified by 'mysqlpass123';
Query OK, 0 rows affected (0.01 sec)

授权：
mysql> grant all on *.* to 'root'@'%';
Query OK, 0 rows affected (0.00 sec)

刷新权限：
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

删除用户：
mysql> drop user 'root'@'127.0.0.1';
Query OK, 0 rows affected (0.00 sec)

mysql> delete from user where host='::1';
Query OK, 1 row affected (0.01 sec)

grant all privileges on *.* to 'root'@'%' IDENTIFIED by '123456';
grant all privileges on slt_tppaml.* to 'slt_tppaml_w'@'%' IDENTIFIED by 'master_w';
grant Select on slt_tppaml.* to 'slt_tppaml_r'@'%' IDENTIFIED by 'readonly';
flush privileges;

mysql设置密码及修改密码的方法

1.设置新密码:(用户初始没有密码)
# mysqladmin -uroot password '123456'
修改密码:
# mysqladmin -uroot -p123456 password '888888'

2.用root 进入mysql后
mysql>set password for 'user'@'localhost' =password('mypass');
mysql>flush privileges;

例：将root@localhost帐号密码设置为mysql!123。
mysql>set password for 'root'@'localhost'=password('mysql!123');

3.使用GRANT语句
mysql>grant all on *.* to 'root'@'localhost' IDENTIFIED BY '你的密码' with grant option ;
mysql>flush privileges;

4.进入mysql库修改user表
mysql>use mysql;
mysql>update user set password=password('你的密码') where user='root';
mysql>flush privileges;

通过命令行执行密码修改：
# /usr/local/mysql/bin/mysql -uroot -p'mysqlpass' -e "update mysql.user set password=password('12345678') where host='127.0.0.1';"

mysql 5.7.17 修改用户密码：
-------------------------------------
mysql> update mysql.user set authentication_string=password('666666') where user='wk';
Query OK, 1 row affected, 1 warning (0.06 sec)
Rows matched: 1  Changed: 1  Warnings: 1

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

 

MySQL用户管理

查看用户权限：
mysql> show grants for root@localhost;
 
授权：
mysql权限分为：全局级别、数据库级别、表级别、列级别，根据需要指定相应级别。

赋予root超级用户权利权限：
mysql> grant all privileges on *.* to root@'%' identified by '111111';
 
创建test全局权限并有授予别人的权限：
mysql> grant all privileges on *.* to test@'%' identified by '222222' with grant option;
 
创建test用户并赋予cacti库所有权限：
mysql> grant all privileges on cacti.* to test@'%' identified by '222222';
 
创建test用户并赋予cacti库某些权限：
mysql> grant select,delete,update,create,drop on cacti.* to test@'%' identified by '222222';
 
创建test用户并赋予cacti库host表权限：
mysql> grant all privileges on cacti.host to test@'%' identified by '222222';
 
撤销用户权限：
mysql> revoke all on *.* from test;

mysql> revoke select on *.* from test@'%';

 
REVOKE语句只是取消用户的权限，没有彻底删除用户，如果需要，可以用delete语句删除用户。
mysql> delete from mysql.user where user='test';
mysql> flush privileges;
 
多条件匹配删除：
mysql> delete from mysql.user where user='test' and host='localhost';