MySQL用户与权限管理_MySQL

root@localhost[(none)]> help Account Management
For more information, type 'help ', where  is one of the following
topics:
You asked for help about help category: "Account Management"
 CREATE USER
 DROP USER
 GRANT
 RENAME USER
 REVOKE
 SET PASSWORD

--创建用户的语法
root@localhost[(none)]> help create user;
Name: 'CREATE USER'
Description:
Syntax:
CREATE USER user_specification [, user_specification] ...

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

create user命令会创建一个新帐户，同时也可以为其指定密码。该命令将添加一条记录到user表。
该命令仅仅授予usage权限。需要再使用grant命令进行进一步授权。也可以使用grant命令直接来创建账户见后续的相关演示。
下面是mysql官方手册对usage的解释。
The USAGE privilege specifier stands for “no privileges.” It is used at the global level with
GRANT to modify account attributes such as resource limits or SSL characteristics without affecting
existing account privileges.

--当前演示环境
root@localhost[(none)]> show variables like 'version';
+---------------+------------+
| Variable_name | Value |
+---------------+------------+
| version | 5.5.39-log |
+---------------+------------+

--创建新用户(未指定密码)
root@localhost[(none)]> create user 'fred'@'localhost';
Query OK, 0 rows affected (0.00 sec)

--指定密码创建新用户,%表示任意，即frank可以从任意主机访问数据库
root@localhost[(none)]> create user 'frank'@'%' identified by 'frank';
Query OK, 0 rows affected (0.00 sec)

--查看刚刚添加的账户
root@localhost[(none)]> select host,user,password from mysql.user where user like 'fr%';
+-----------+-------+-------------------------------------------+
| host | user | password |
+-----------+-------+-------------------------------------------+
| % | frank | *63DAA25989C7E01EB96570FA4DBE154711BEB361 |
| localhost | fred | |
+-----------+-------+-------------------------------------------+

--grant命令语法
root@localhost[mysql]> help grant
Name: 'GRANT'
Description:
Syntax:
GRANT
 priv_type [(column_list)]
 [, priv_type [(column_list)]] ...
 ON [object_type] priv_level
 TO user_specification [, user_specification] ...
 [REQUIRE {NONE | ssl_option [[AND] ssl_option] ...}]
 [WITH with_option ...]

GRANT PROXY ON user_specification
 TO user_specification [, user_specification] ...
 [WITH GRANT OPTION]

object_type:
 TABLE
 | FUNCTION
 | PROCEDURE

priv_level:
 *
 | *.*
 | db_name.*
 | db_name.tbl_name
 | tbl_name
 | db_name.routine_name

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

如何授权
 a、需要指定授予哪些权限
 b、权限应用在那些对象上(全局，特定对象等)
 c、授予给哪个帐户
 d、可以指定密码(可选项,用此方式会自动创建用户)

授权权限的范围：
 ON　*.*
 ON db_name.*
 ON db_name.table_name
 ON db_name.table_name.column_name
 ON db_name.routine_name

--权限一览表，我们直接查询root账户所有的权限，如下
--mysql的权限相对于oracle而言，相对简单，而且也没有涉及到角色方面的定义与配置
root@localhost[(none)]> select * from mysql.user where user='root' and host='localhost'\G 
*************************** 1. row ***************************
 Host: localhost
 User: root
 Password: 
 Select_priv: Y
 Insert_priv: Y
 Update_priv: Y
 Delete_priv: Y
 Create_priv: Y
 Drop_priv: Y
 Reload_priv: Y
 Shutdown_priv: Y
 Process_priv: Y
 File_priv: Y
 Grant_priv: Y
 References_priv: Y
 Index_priv: Y
 Alter_priv: Y
 Show_db_priv: Y
 Super_priv: Y
 Create_tmp_table_priv: Y
 Lock_tables_priv: Y
 Execute_priv: Y
 Repl_slave_priv: Y
 Repl_client_priv: Y
 Create_view_priv: Y
 Show_view_priv: Y
 Create_routine_priv: Y
 Alter_routine_priv: Y
 Create_user_priv: Y
 Event_priv: Y
 Trigger_priv: Y
Create_tablespace_priv: Y
 ssl_type: 
 ssl_cipher: 
 x509_issuer: 
 x509_subject: 
 max_questions: 0
 max_updates: 0
 max_connections: 0
 max_user_connections: 0
 plugin: 
 authentication_string: 
1 row in set (0.00 sec)

--说明，本文中描述的mysql提示符为user@hostname[(dbname)]，不同的帐户，不同的主机登录会显示不同。
--其次，不同的提示符下所代表的用户身份及权限。
--查看当前的连接用户
root@localhost[(none)]> select current_user();
+----------------+
| current_user() |
+----------------+
| root@localhost |
+----------------+

--查看当前帐户的权限
root@localhost[(none)]> show grants; --该账户用于最高权限，带有WITH GRANT OPTION
+---------------------------------------------------------------------+
| Grants for root@localhost |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION |
+---------------------------------------------------------------------+

suse11b:~ # mysql -ufred -p
Enter password: 

fred@localhost[(none)]> show grants;
+------------------------------------------+
| Grants for fred@localhost |
+------------------------------------------+
| GRANT USAGE ON *.* TO 'fred'@'localhost' |
+------------------------------------------+

--下面使用root账户给fred赋予权限all privileges
root@localhost[(none)]> grant all privileges on *.* to 'fred'@'localhost';
Query OK, 0 rows affected (0.01 sec)

root@localhost[(none)]> flush privileges;
Query OK, 0 rows affected (0.00 sec)

fred@localhost[(none)]> show grants;
+---------------------------------------------------+
| Grants for fred@localhost |
+---------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'fred'@'localhost' |
+---------------------------------------------------+

fred@localhost[(none)]> use tempdb

fred@localhost[tempdb]> create table tb_isam(id int,value varchar(20)) engine=myisam;
Query OK, 0 rows affected (0.10 sec)

fred@localhost[tempdb]> insert into tb_isam values (1,'jack'),(2,'robin');
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0

fred@localhost[tempdb]> commit;

--下面的授权收到了错误提示，不能授权
fred@localhost[tempdb]> grant select on tempdb.* to 'frank'@'%';
ERROR 1044 (42000): Access denied for user 'fred'@'localhost' to database 'tempdb'

--下面从root session来给之前创建的frank授权
--授予frank在数据库tempdb上所有对象的select权限
root@localhost[(none)]> grant select on tempdb.* to 'frank'@'%';
Query OK, 0 rows affected (0.00 sec)

--更新cache中的权限
root@localhost[(none)]> flush privileges;
Query OK, 0 rows affected (0.00 sec)

--从另外的主机使用frank账户登录
suse11a:~ # mysql -ufrank -p -h172.16.6.89
Enter password: 

--此时frank，此时已经可以访问了tempdb上的表tb_isam
frank@172.16.6.89[(none)]> select * from tempdb.tb_isam;
+------+-------+
| id | value |
+------+-------+
| 1 | jack |
| 2 | robin |
+------+-------+

frank@172.16.6.89[(none)]> show grants;
+------------------------------------------------------------------------------------------------------+
| Grants for frank@% |
+------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'frank'@'%' IDENTIFIED BY PASSWORD '*63DAA25989C7E01EB96570FA4DBE154711BEB361' |
| GRANT SELECT ON `tempdb`.* TO 'frank'@'%' --可以看到多出了select权限 |
+------------------------------------------------------------------------------------------------------+

--下面是一个授予最大权限的例子，授予的同时会自动创建用户，由于我们没有设置密码，所以password列查询

撤销权限使用的是revoke关键字，撤销与授权的权限方式基本类似，
其次有哪些权限可以授予，相应地就有哪些权限可以撤销，原来的to子句呢则变成了from子句。
如下面的示例
mysql> revoke SELECT (user, host), UPDATE (host) ON `tempdb`.`tb_user` from 'frank'@'%'; 
mysql> revoke all privileges, grant option from 'frank'@'%';

root@localhost[(none)]> revoke SELECT (user, host), UPDATE (host) ON `tempdb`.`tb_user` from 'frank'@'%'; 
Query OK, 0 rows affected (0.00 sec)

-- Author : Leshami
-- Blog : http://blog.csdn.net/leshami

root@localhost[(none)]> revoke all privileges, grant option from 'frank'@'%';
Query OK, 0 rows affected (0.01 sec)

root@localhost[(none)]> flush privileges;
Query OK, 0 rows affected (0.00 sec)

root@localhost[(none)]> show grants for 'frank'; --查看revoke之后仅拥有最基本权限
+------------------------------------------------------------------------------------------------------+
| Grants for frank@% |
+------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'frank'@'%' IDENTIFIED BY PASSWORD '*63DAA25989C7E01EB96570FA4DBE154711BEB361' |
+------------------------------------------------------------------------------------------------------+

使用drop user命令删除用户
--查看当前系统中已存在的用户
root@localhost[(none)]> select user,host,Password from mysql.user;
+-------+-----------+-------------------------------------------+
| user | host | Password |
+-------+-----------+-------------------------------------------+
| root | localhost | |
| root | suse11b | |
| root | 127.0.0.1 | |
| root | ::1 | |
| | localhost | |
| | suse11b | |
| fred | localhost | |
| frank | % | *63DAA25989C7E01EB96570FA4DBE154711BEB361 |
| jack | localhost | |
+-------+-----------+-------------------------------------------+

--使用drop user命令删除用户
root@localhost[(none)]> drop user 'frank'@'%';
Query OK, 0 rows affected (0.00 sec)

root@localhost[(none)]> drop user 'fred'@'localhost';
Query OK, 0 rows affected (0.00 sec)

root@localhost[(none)]> select user,host,Password from mysql.user where user like 'fr%';
Empty set (0.00 sec)

--如何重命名帐户，使用rename user命令
root@localhost[(none)]> rename user 'jack'@'localhost' to 'jason'@'localhost';
Query OK, 0 rows affected (0.00 sec)

root@localhost[(none)]> select user,host,Password from mysql.user where user like 'j%';
+-------+-----------+----------+
| user | host | Password |
+-------+-----------+----------+
| jason | localhost | |
+-------+-----------+----------+

--对于用户的删除也可以直接从mysql.user进行删除相应的记录，但不推荐直接操作mysql系统表