slt mysql_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';