mysql 创建用户并赋权,收回权限

1.创建用户

语法:

CREATE USER user [IDENTIFIED BY [PASSWORD] 'password']  [, user [IDENTIFIED BY [PASSWORD] 'password']] ...

例子: CREATE USER 'test001' IDENTIFIED BY  '123456';

2.授权

语法:

GRANT priv_type [(column_list)] [, priv_type [(column_list)]] ...
    ON [object_type] {tbl_name | * | *.* | db_name.*}
    TO user [IDENTIFIED BY [PASSWORD] 'password']

priv_type:

例子:grant all on *.* to 'test001'@'%' identified by '123456'; 如果后面加上 with grant option 则表示创建的该用户也可授权

3.收回权限

语法:

REVOKE priv_type [(column_list)] [, priv_type [(column_list)]] ...
    ON [object_type] {tbl_name | * | *.* | db_name.*}
    FROM user [, user] ...
例子:revoke all on *.* from 'test001'@'%';

4.修改用户密码

例子:set password for 'test001'@'%' = password('12345678');

5.查看用户权限

 show grants for 'test001'@'%';