MySQL 学习<十三> MySQL账户管理

账户管理

MySQL提供许多语句用来管理用户帐号，这些语句可以用来包括登录和退出MYSQL服务器、创建用户、删除用户、密码管理、权限管理；MySQL数据库的安全性，需要通过帐户管理来保证。

登陆和退出服务器

mysql命令的常用参数如下：

-h：主机名或ip，默认是localhost，最好指定-h参数

-u：用户名

-p：密码，注意：该参数后面的字符串和-p不能有空格

-P：端口号，默认为3306

数据库名：可以在命令最后指定数据库名

-e：执行SQL语句，如果指定该参数，将在登录后执行-e后面的命令或sql语句并退出

新建普通用户

创建用户，必须要有相应的权限来执行创建操作。MySQL中有两种方式创建新用户：一种是使用CREATE USER或GRANT语句；另一种是直接操作MySQL授权表。

1.使用CREATE USER语句创建新用户

基本语法格式如下：

CREATE USER user_specification
　　[,user_specification]...

user_specification:
　　user@host
　　[
　　　　IDENTIFIED BY [PASSWORD] 'password'
　　　　|IDENTIFIED WITH auth_plugin [AS 'auth_string']
　　]

1).user：用户的名称

2).host：允许登录的用户主机名称

3).IDENTIFIED BY：用来设置用户的密码

4).[PASSWORD]：表示使用哈希值设置密码，可选

5).‘password’：用户登录的普通明文密码

6).IDENTIFIED WITH：为用户指定一个身份验证插件

7).auth_plugin：插件名

8).auth_string：该参数将传递给身份验证插件，由该插件解释该参数的意义

例如，创建一个用户名为‘test’， 密码是‘password’，主机名为localhost，命令如下：

如果只指定用户名部分，主机名部分则默认为‘%’（即对所有的主机开放权限）。 

如果指定用户登录不需要密码，可以省略IDENTIFIED BY部分。

2.使用GRANT语句创建新用户

GRANT USER语句可以用来创建帐户，通过该语句可以在user表中添加一条新记录；
比起CREATE USER语句创建的新用户，还需要使用GRANT语句赋予用户权限；
使用GRANT语句创建新用户时必须有GRANT权限。

基本语法如下：

GRANT priv_type [(column_list)] [, priv_type [(column_list)]] ...

ON [object_type] {tbl_name | * | *.* | db_name.*}

TO user [IDENTIFIED BY [PASSWORD] 'password']

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

[REQUIRE

NONE |

[{SSL| X509}]

[CIPHER 'cipher' [AND]]

[ISSUER 'issuer' [AND]]

[SUBJECT 'subject']]

[WITH with_option [with_option] ...]

使用GRANT语句创建一个新用户testUser，密码为testpwd，并授予用户对所有数据表的SELECT和UPDATE权限：

查看账户权限信息：

3.直接操作MYSQL用户表

不管是CREATE USER还是GRANT USER，在创建用户时，实际上都是在user表中添加一条新记录。

使用INSERT语句向mysql.user表INSERT一条记录来创建一个新用户。

插入的时候必须要有INSERT权限。

基本语法格式如下：

INSERT INTO mysql.user(host,user,password,[privilegelist])

VALUES ('host','username',password('password'),privilegevaluelist)

例如，使用INSERT创建一个新账户，其用户名称为customer1，主机名称为localhost，密码为customer1：

INSERT INTO user (Host,User,Password) VALUES('localhost','customer1',PASSWORD('customer1'));

删除普通用户

使用DROP USER语句删除用户，也可以直接通过DELETE从mysql.user表中删除对应的记录来删除用户；DROP USER语句用于删除一个或多个MySQL帐户。要使用DROP USER，必须拥有MySQL数据库的全局CREATE USER 权限或DELETE权限。

1.使用DROP USER删除用户

基本语法如下：

DROP USER  user  [,user];

例如，删除刚才创建的用户’test‘@'localhost'：

2.使用DELETE语句删除用户:
DELETE FROM mysql.user WHERE `Host`='hostname' and `User`='sername'

root用户修改自己的密码

1.使用mysqladmin命令在命令行指定新密码:
mysqladmin -u username -h localhost -p password"newpwd";

2.修改mysql数据库的user表:
UPDATE mysql.user SET `Password` =password('rootpwd') WHERE `User`='root' and `Host`='localhost'
password('')函数用来加密用户密码。执行UPDATE之后需要执行flush privileges语句重新加载用户权限

3、使用SET语句修改root用户的密码：

SET PASSWORD=PASSWORD("ROOTPWD")

SET PASSWORD语句可以用来重新设置其他用户的登录密码或者自己使用的帐户密码，新密码必须用PASSWORD函数加密。执行之后需要使用执行flush privileges语句或者重启MySQL重新加载用户权限。

root用户修改普通用户的密码

1.使用SET语句修改普通用户的密码：
SET PASSWORD FOR 'USER'@'HOST' =PASSWORD("ROOTPWD")；

2.使用update语句修改普通用户的密码：
UPDATE mysql.user SET `Password` =password('rootpwd') WHERE `User`='username' and `Host`='hostname'；
执行完毕之后需要使用flush privileges语句或者重启MySQL重新加载用户权限

3.用GRANT语句修改普通用户密码：
GRANT USAGE ON *.* TO 'someuser'@'%' IDENTIFIED BY 'somepwd'；

注意：使用GRANT语句和MYSQLADMIN设置密码，他们均会加密密码，这种情况下，不需要使用PASSWORD()函数。

普通用户修改密码

使用SET语句修改自己的密码：
SET password=password('newpassword');

root用户密码丢失的解决办法

使用--skip-grant-tables选项启动MYSQL服务
使用--skip-grant-tables选项启动MYSQL时，服务器将不加载权限判断，任何用户都能访问数据库。

1.LINUX下：
使用mysqld_safe来启动MYSQL服务，也可以使用/etc/init.d/mysql命令来启动mysql
mysqld_safe --skip-grant-tables user=mysql

或者
/etc/init.d/mysql start-mysqld --skip-grant-tables
启动MYSQL服务后，就可以使用root用户登录了

2.windows下：

如果已经将MySQL的目录添加到环境变量中，可以直接使用MySQLd、MySQL-nt、命令启动MySQL服务，否则需要先在命令下切换到MySQL的bin目录;

使用

mysqld --skip-grant-tables

或者

mysqld-nt --skip-grant-tables

启动过后就可以使用root用户登录了；

登录成功后，可以使用UPDATE语句或者MySQLadmin命令重新设置root密码，设置密码的命令如下：

UPDATE mysql.user SET `Password` =password(’newpwd')  WHERE `User`='root' and `Host`='localhost'；

修改完成后，必须使用FLUSH PRIVILEGES语句加载权限表。

权限管理

权限管理主要是对登录到MySQL的用户进行验证

MySQL各种权限

用户权限信息被存储在mysql数据库中的user、db、host、tables_priv、columns_priv和procs_priv表中；

对于GRANT和REVOKE语句，priv_type可以被指定为以下任何一种：

权限	意义
ALL [PRIVILEGES]	设置除GRANT OPTION之外的所有简单权限
ALTER	允许使用ALTER TABLE
ALTER ROUTINE	更改或取消已存储的子程序
CREATE	允许使用CREATE TABLE
CREATE ROUTINE	创建已存储的子程序
CREATE TEMPORARY TABLES	允许使用CREATE TEMPORARY TABLE
CREATE USER	允许使用CREATE USER, DROP USER, RENAME USER和REVOKE ALL PRIVILEGES。
CREATE VIEW	允许使用CREATE VIEW
DELETE	允许使用DELETE
DROP	允许使用DROP TABLE
EXECUTE	允许用户运行已存储的子程序
FILE	允许使用SELECT...INTO OUTFILE和LOAD DATA INFILE
INDEX	允许使用CREATE INDEX和DROP INDEX
INSERT	允许使用INSERT
LOCK TABLES	允许对您拥有SELECT权限的表使用LOCK TABLES
PROCESS	允许使用SHOW FULL PROCESSLIST
REFERENCES	未被实施
RELOAD	允许使用FLUSH
REPLICATION CLIENT	允许用户询问从属服务器或主服务器的地址
REPLICATION SLAVE	用于复制型从属服务器（从主服务器中读取二进制日志事件）
SELECT	允许使用SELECT
SHOW DATABASES	SHOW DATABASES
SHOW VIEW	允许使用SHOW CREATE VIEW
SHUTDOWN	允许使用mysqladmin shutdown
SUPER	允许使用CHANGE MASTER, KILL, PURGE MASTER LOGS和SET GLOBAL语句，mysqladmin debug命令；允许您连接（一次），即使已达到max_connections。
UPDATE	允许使用UPDATE
USAGE	“无权限”的同义词
GRANT OPTION	允许授予权限

授权

授权就是为某个用户授予权限,授予的权限可以分为多个层级：

1.全局层级

全局权限适用于一个给定服务器中的所有数据库。这些权限存储在mysql.user表中。GRANT ALL ON *.*和REVOKE ALL ON *.*只授予和撤销全局权限。

2. 数据库层级

数据库权限适用于一个给定数据库中的所有目标。这些权限存储在mysql.db和mysql.host表中。GRANT ALL ONdb_name.*和REVOKE ALL ON db_name.*只授予和撤销数据库权限。

3.表层级

表权限适用于一个给定表中的所有列。这些权限存储在mysql.talbes_priv表中。GRANT ALL ON db_name.tbl_name和REVOKE ALL ON db_name.tbl_name只授予和撤销表权限。

4. 列层级

列权限适用于一个给定表中的单一列。这些权限存储在mysql.columns_priv表中。当使用REVOKE时，您必须指定与被授权列相同的列。

5.子程序层级

CREATE ROUTINE, ALTER ROUTINE, EXECUTE和GRANT权限适用于已存储的子程序。这些权限可以被授予为全局层级和数据库层级。而且，除了CREATE ROUTINE外，这些权限可以被授予为子程序层级，并存储在mysql.procs_priv表中。

当后续目标是一个表、一个已存储的函数或一个已存储的过程时，object_type子句应被指定为TABLE、FUNCTION或PROCEDURE。使用GRANT语句创建一个新用户grantUser，密码为“grantpwd”;用户对所有的数据有查询、插入权限，并授予GRANT权限。

收回权限

收回权限就是取消已经赋予用户的某些权限。收回用户不必要的权限可以在一定程度上保证系统的安全性。
使用REVOKE收回权限之后，用户帐户的记录将从db、host、tables_priv、columns_priv表中删除，但是用户帐号记录依然在user表中保存。基本语法如下：

REVOKE priv_type [(column_list)] [, priv_type [(column_list)]] ...

ON [object_type] {tbl_name | * | *.* | db_name.*}

FROM user [, user] ...

REVOKE ALL PRIVILEGES, GRANT OPTION FROM user [, user] ...

使用REVOKE语句，必须拥有mysql数据库的全局CREATE权限或UPDATE权限。

查看权限

SHOW GRANT语句可以显示用户的权限信息，其基本语法如下：
SHOW GRANT FOR 'username'@'hostname';