用户权限表的一些说明

jikcheng

 mysql.user 这张表是针对用户对整个数据库的权限的一个描述。
当执行赋予权限的语句：
(root@localhost) [(none)]> grant select on *.*  to root@192.168.154.190 ;    
Query OK, 0 rows affected (0.00 sec)
赋予权限后：
(root@localhost) [(none)]> select * from mysql.user where host='192.168.154.190'\G
*************************** 1. row ***************************
                  Host: 192.168.154.190
                  User: root
              Password: *23AE809DDACAF96AF0FD78ED04B6A265E05AA257
           Select_priv: Y
           Insert_priv: N
           Update_priv: N
           Delete_priv: N
           Create_priv: N
             Drop_priv: N
           Reload_priv: N
         Shutdown_priv: N
          Process_priv: N
             File_priv: N
            Grant_priv: N
       References_priv: N
            Index_priv: N
            Alter_priv: N
          Show_db_priv: N
            Super_priv: N
 Create_tmp_table_priv: N
      Lock_tables_priv: N
          Execute_priv: N
       Repl_slave_priv: N
      Repl_client_priv: N
      Create_view_priv: N
        Show_view_priv: N
   Create_routine_priv: N
    Alter_routine_priv: N
      Create_user_priv: N
            Event_priv: N
          Trigger_priv: N
Create_tablespace_priv: N
              ssl_type:
            ssl_cipher:
           x509_issuer:
          x509_subject:
         max_questions: 0
           max_updates: 0
       max_connections: 0
  max_user_connections: 0
                plugin: mysql_native_password
 authentication_string:
      password_expired: N
1 row in set (0.00 sec)

mysql.db表
是数据库对于用户的权限。
(root@localhost) [mysql]>  select * from mysql.db where host='192.168.154.190'\G
*************************** 1. row ***************************
                 Host: 192.168.154.190
                   Db: world
                 User: root
          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
1 row in set (0.00 sec)

(root@localhost) [mysql]>

tables_priv 表
针对表的权限赋予那些权限到哪些用户上去。
root@localhost) [mysql]> select * from mysql.tables_priv;
+---------------+-------+------+------------+----------------+---------------------+------------+-------------+
| Host          | Db    | User | Table_name | Grantor        | Timestamp           | Table_priv | Column_priv |
+---------------+-------+------+------------+----------------+---------------------+------------+-------------+
| 192.168.154.1 | mysql | root | user       | root@localhost | 0000-00-00 00:00:00 | Select     |             |
+---------------+-------+------+------------+----------------+---------------------+------------+-------------+
columns_priv表
查看当前那些列权限给了那些用户。


procs_priv 表
存储过程权限表，那些用户拥有对于存储过程的权限。



赋予权限：
grant [select]| [update] | [insert] | [delete]  on [database].[tables] to 'user'@'host'
例：赋予数据库的全部权限
grant all privileges on *.* to root@localhost;
回收权限：
revoke [all privileges] | [select] | [delete] | [update] from 'user'@'host'
例：回收全部权限：
revoke all privileges ,grant option from root@localhost;

[此贴子已经被作者于2014-11-10 17:20:40编辑过]