Mysql创建用户并授权命令

skyCai edited this page Dec 30, 2015 · 1 revision

1.新建用户

// 以root用户登录Mysql
> mysql -uroot -p
> 密码
// 创建用户
mysql> insert into mysql.user(Host,User,Password) values ("localhost","sky",password("skycai"));
// 刷新系统权限表
mysql> flush privileges;

上面命令创建了名为sky同时密码为skycai的用户

2.授权

// 以root用户登录Mysql
> mysql -uroot -p
> 密码
// 先为用户创建一个数据库(testDB)
mysql> create database testDB;
// 授权sky用户拥有testDB数据库的所有权限
mysql> grant all privileges on testDB.* to sky@localhost identified by 'skycai';
// 刷新系统权限表
mysql> flush privileges;

若只想指定部分权限给用户,可以用下面方式来写:

// 将testDB的select跟update权限授权给sky
mysql> grant select,update on testDB.* to sky@localhost identified by 'skycai';
// 刷新系统权限
mysql> flush privileges;

3.删除用户

> mysql -uroot -p
> 密码
mysql> DELETE FROM user WHERE User="sky" and Host="localhost";
// 删除用户数据库
mysql> drop database testDB;

4.修改指定用户密码

> mysql -uroot -p
> 密码
mysql> update mysql.user set password=password('new password') where User="sky" and Host="localhost";
mysql> flush privileges;
You can’t perform that action at this time.
You signed in with another tab or window. Reload to refresh your session. You signed out in another tab or window. Reload to refresh your session.
Press h to open a hovercard with more details.