标题: 用户权限表的一些说明 [打印本页] 作者: jikcheng 时间: 2014-11-10 14:35 标题: 用户权限表的一些说明 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;