MySQL权限系统分析

shell> MySQL -h host_name -u user_name -pyour_pass

shell> MySQL -h localhost -u joe
shell> MySQL -h localhost
shell> MySQL -u joe
shell> MySQL

+-----------+----------+-
| Host      | User     | …
+-----------+----------+-
| localhost | root     | … ...
| localhost |          | … ...
| %         | jeffrey  | … ...
| %         | root     | … ...
+-----------+----------+-

+----------------+----------+-
| Host           | User     | …
+----------------+----------+-
| %              | jeffrey  | …
| thomas.loc.gov |          | …
+----------------+----------+-

+----------------+----------+-
| Host           | User     | …
+----------------+----------+-
| thomas.loc.gov |          | …
| %              | jeffrey  | …
+----------------+----------+-

mysql> SELECT CURRENT_USER();
+----------------+
| CURRENT_USER() |
+----------------+
| @localhost     |
+----------------+

user表：         global privileges
 |
db表：           database privileges
 |
table_priv表：   table privileges
 |
column_priv表：  column privileges

shell> mysql
RROR 2003: Can't connect to MySQL server on 'host_name' (111)
shell> mysql
ERROR 2002: Can't connect to local MySQL server through socket '/tmp/mysql.sock' (111)

shell> netstat -ln | grep mysql

GRANT ALL PRIVILEGES ON *.* TO 'monty'@'localhost' IDENTIFIED BY 'some_pass' 
WITH GRANT OPTION;

GRANT ALL PRIVILEGES ON *.* TO 'monty'@'%' IDENTIFIED BY 'some_pass' 
WITH GRANT OPTION;

GRANT RELOAD,PROCESS ON *.* TO 'admin'@'localhost';

GRANT USAGE ON *.* TO 'dummy'@'localhost';

-- 第1个账户可以访问bankaccount数据库，但只能从本机访问。
GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
ON bankaccount.*
TO 'custom'@'localhost'
IDENTIFIED BY 'obscure';

-- 第2个账户可以访问expenses数据库，但只能从主机whitehouse.gov访问。
GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
ON expenses.*
TO 'custom'@'whitehouse.gov'
IDENTIFIED BY 'obscure';

-- 第3个账户可以访问customer数据库，但只能从主机server.domain访问。
GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
ON customer.*
TO 'custom'@'server.domain'
IDENTIFIED BY 'obscure';

-- t1能看到testdb的所有表，能查询不能写入
grant select on testdb.* to 't1'@'%' identified by '333';


-- t1只能看到testtbl1，能查询不能写入
grant select on testdb.testtbl1 to 't2'@'%' identified by '333';

-- t1 t2的user表中全是N
-- t1在DB表中记录权限，t2没有数据
mysql> select * from db where user = 't1'\G
*************************** 1. row ***************************
                 Host: %
                   Db: testdb
                 User: t1
          Select_priv: Y
          Insert_priv: N
          Update_priv: N
          Delete_priv: N
          Create_priv: N
            Drop_priv: N
           Grant_priv: N
      References_priv: N
           Index_priv: N
           Alter_priv: N
Create_tmp_table_priv: N
     Lock_tables_priv: N
     Create_view_priv: N
       Show_view_priv: N
  Create_routine_priv: N
   Alter_routine_priv: N
         Execute_priv: N
           Event_priv: N
         Trigger_priv: N

-- t2在tables_priv表中记录权限，t1没有数据
mysql> select * from tables_priv where user='t2'\G
*************************** 1. row ***************************
       Host: %
         Db: testdb
       User: t2
 Table_name: testtbl1
    Grantor: aliyun_root@127.0.0.1
  Timestamp: 0000-00-00 00:00:00
 Table_priv: Select
Column_priv:

GRANT ALL ON customer.* TO 't3'@'localhost'
IDENTIFIED BY '333'
WITH MAX_QUERIES_PER_HOUR 20
     MAX_UPDATES_PER_HOUR 10
     MAX_CONNECTIONS_PER_HOUR 5
     MAX_USER_CONNECTIONS 2;
     
select max_questions,max_updates,max_connections,max_user_connections from user where user='t3';
+---------------+-------------+-----------------+----------------------+
| max_questions | max_updates | max_connections | max_user_connections |
+---------------+-------------+-----------------+----------------------+
|            20 |          10 |               5 |                    2 |
+---------------+-------------+-----------------+----------------------+

GRANT USAGE ON *.* TO 'francis'@'localhost' WITH MAX_QUERIES_PER_HOUR 100;

GRANT USAGE ON *.* TO 'francis'@'localhost' WITH MAX_CONNECTIONS_PER_HOUR 0;

mysql> grant usage on *.* to 't3'@'localhost' with MAX_USER_CONNECTIONS 1;
Query OK, 0 rows affected, 2 warnings (0.00 sec)

#mysql -ut3 -S /mysqldata/tmp3148/mysql.sock -p333
mysql>

#mysql -S /home/mysql/data3148/tmp/mysql.sock -ut3 -p333
ERROR 1226 (42000): User 't3' has exceeded the 'max_user_connections' resource (current value: 1)

SET PASSWORD FOR 'jeffrey'@'%' = PASSWORD('biscuit');

GRANT USAGE ON *.* TO 'jeffrey'@'%' IDENTIFIED BY 'biscuit';

mysql> SET PASSWORD FOR 't3'@'%' = PASSWORD('123');
ERROR 1044 (42000): Access denied for user 't3'@'localhost' to database 'mysql'

-- 修改自己的密码可省略
mysql> SET PASSWORD = PASSWORD('123');
Query OK, 0 rows affected, 1 warning (0.01 sec)

-- root登陆
create table tbl1(a int, b int, c int, d int);
insert into tbl1 values (1,2,3,4);
select * from tbl1;
+------+------+------+------+
| a    | b    | c    | d    |
+------+------+------+------+
|    1 |    2 |    3 |    4 |
+------+------+------+------+

grant select(a) on testdb.tbl1 to t4 identified by '333';
grant insert(a) on testdb.tbl1 to t6 identified by '333';

-- t4登陆：只能查询a列
mysql -ut4 -S /mysqldata/tmp3148/mysql.sock -p333
mysql> select * from tbl1;
ERROR 1142 (42000): SELECT command denied to user 't4'@'localhost' for table 'tbl1'
mysql> select a from tbl1;
+------+
| a    |
+------+
|    1 |
+------+
mysql> show grants;
+-------------------------------------------------+
| Grants for t4@%                                 |
+-------------------------------------------------+
| GRANT USAGE ON *.* TO 't4'@'%'                  |
| GRANT SELECT (a) ON `testdb`.`tbl1` TO 't4'@'%' |
+-------------------------------------------------+

-- t6登陆：只能插入a列
mysql -ut6 -S /mysqldata/tmp3148/mysql.sock -p333
mysql> insert into tbl1 (a) values (1);
Query OK, 1 row affected (0.01 sec)
mysql> insert into tbl1 (b) values (1);
ERROR 1143 (42000): INSERT command denied to user 't6'@'localhost' for column 'b' in table 'tbl1'
mysql> show grants;
+-------------------------------------------------+
| Grants for t6@%                                 |
+-------------------------------------------------+
| GRANT USAGE ON *.* TO 't6'@'%'                  |
| GRANT INSERT (a) ON `testdb`.`tbl1` TO 't6'@'%' |
+-------------------------------------------------+

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] ...]

object_type =
    TABLE
  | FUNCTION
  | PROCEDURE

with_option =
    GRANT OPTION
  | MAX_QUERIES_PER_HOUR count
  | MAX_UPDATES_PER_HOUR count
  | MAX_CONNECTIONS_PER_HOUR count
  | MAX_USER_CONNECTIONS count
  
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] ...

FILEPROCESS
RELOAD
REPLICATION CLIENT 
REPLICATION SLAVE
SHOW DATABASES
SHUTDOWN
SUPER