MySQL 的用户管理与授权管理

一、用户管理
1. Mysql的用户通常分为两类：
            root           MySQL数据库的管理员用户
            普通用户   
  
2. 用户帐号：
            username@主机
            usernaem@网络
                    可以使用通配符， %和_    %：表示任意字符
 eg:

root@localhost --> root用户只能通过本机来登录
root@192.168.0.0/24 --> root用户只能通过192.168.0.0网段的主机登录
cacti_user@'192.168.0.%'

3. 创建用户：

> CREATE USER 'user_name'@'host' [IDENTIFIED BY 'password'] ;

4. 为用户更改密码：            

# mysqladmin -u root passwrord 'new_password' -p
# mysqladmin -u root -h this_host_name password 'new_password'

> SET PASSWORD FOR user_name@'host_name'=PASSWORD('password');

5. 删除用户：         

> DROP USER 'user'@'host';
> drop user ''@'localhost'; 删除匿名用户
> DELETE FROM user WHERE HOST='::1';

6. 不用输入密码登录mysql：  

# cd ~
# vi .my.cnf
[mysql]
user=root
host=localhost
password=redhat

二、权限管理 
1. 授权：

> GRANT  ALL ON *.* TO user_name  IDENTIFIED BY 'redhat';
> GRANT  ALL ON *.* TO user_name@'host_name' IDENTIFIED BY 'redhat';
> GRANT SELECT,UPDATE,DELETE ON test.player TO astd@'192.168.0.%' IDENTIFIED BY '123456';
> GRANT ALL PRIVILEGES ON player.* TO haha@localhost IDENTIFIED BY 'centos' WITH GRANT OPTION;

> FLUSH PRIVILEGES; 刷新授权表

收回某个用户的某些权限：    

> REVOKE  privilege_list  ON  db.tables FROM 'user'@'host';

2.查看某个用户的授权情况： 

mysql> SHOW GRANTS FOR astd@'192.168.0.%';
+---------------------------------------------------------------------------------------------------------------+
| Grants for astd@192.168.0.%                                                                                   |
+---------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'astd'@'192.168.0.%' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' |
| GRANT SELECT, UPDATE, DELETE ON `test`.`player` TO 'astd'@'192.168.0.%'                                       |
+---------------------------------------------------------------------------------------------------------------+

3. 与mysql用户授权相关的表

+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| columns_priv              |
| db                        |
| event                     |
| func                      |
| general_log               |
| host                      |
| ndb_binlog_index          |
| plugin                    |
| proc                      |
| procs_priv                |
| proxies_priv              | --> 与代理相关的授权
| servers                   |
| slow_log                  |
| tables_priv               |
| time_zone                 |
| user                      |
+---------------------------+

GRANT的语法：

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 BY [PASSWORD] 'password'
      | IDENTIFIED WITH auth_plugin [AS 'auth_string']
    ]

ssl_option:
    SSL
  | X509
  | CIPHER 'cipher'
  | ISSUER 'issuer'
  | SUBJECT 'subject'

with_option:
    GRANT OPTION
  | MAX_QUERIES_PER_HOUR count 平均每个小时最多可以发起多少次查询请求
  | MAX_UPDATES_PER_HOUR count
  | MAX_CONNECTIONS_PER_HOUR count
  | MAX_USER_CONNECTIONS count 一个用户最多允许同时发起的连接请求次数

Global level
     Global privileges apply to all databases on a given server. These privileges are stored in the mysql.user table. GRANT ALL ON *.* and REVOKE ALL ON *.* grant and revoke only global privileges.

>GRANT ALL ON *.* TO 'someuser'@'somehost';
>GRANT SELECT, INSERT ON *.* TO 'someuser'@'somehost';

Database level
     Database privileges apply to all objects in a given database. These privileges are stored in the mysql.db and mysql.host tables. GRANT ALL ON db_name.* and REVOKE ALL ON db_name.* grant and revoke only database privileges.

>GRANT ALL ON mydb.* TO 'someuser'@'somehost';
>GRANT SELECT, INSERT ON mydb.* TO 'someuser'@'somehost';

Table level
     Table privileges apply to all columns in a given table. These privileges are stored in the mysql.tables_priv table. GRANT ALL ON db_name.tbl_name and REVOKE ALL ON db_name.tbl_name grant and revoke only table privileges.  

>GRANT ALL ON mydb.mytbl TO 'someuser'@'somehost';
>GRANT SELECT, INSERT ON mydb.mytbl TO 'someuser'@'somehost';

    If you specify tbl_name rather than db_name.tbl_name, the statement applies to tbl_name in the default database.
Column level
     Column privileges apply to single columns in a given table. These privileges are stored in the mysql.columns_priv table. When using REVOKE, you must specifythe same columns that were granted. The column or columns for which the privileges are to be granted must be enclosed within parentheses.

>GRANT SELECT (col1), INSERT (col1,col2) ON mydb.mytbl TO 'someuser'@'somehost';

Routine level
     The CREATE ROUTINE, ALTER ROUTINE, EXECUTE, and GRANT OPTION privileges apply to stored routines (functions and procedures). They can be granted at the global and database levels. Also, except for CREATE ROUTINE, these privileges can be granted at the routine level for individual routines and are stored in the mysql.procs_priv table.

>GRANT CREATE ROUTINE ON mydb.* TO 'someuser'@'somehost';
>GRANT EXECUTE ON PROCEDURE mydb.myproc TO 'someuser'@'somehost';