MySQL中如何知道哪些用户具有哪些库表的权限?

本文最后更新于2015年5月1日,已超过 1 年没有更新,如果文章内容失效,还请反馈给我,谢谢!

MySQL中如何知道哪些用户具有哪些库表的权限?
参考解答:
SELECT host,db,user,password from mysql.user;
SELECT host,db,user FROM mysql.db WHERE db='db_name';
SELECT host,db,user,Table_name FROM mysql.tables_priv WHERE db='db_name';
SELECT host,db,user,Table_name,Column_name FROM mysql.columns_priv WHERE db='db_name';
SELECT host,db,user,Proc_priv FROM mysql.procs_priv WHERE db='db_name';
mysql> show tables from mysql;
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| columns_priv              |
| db                        |
| event                     |
| func                      |
| general_log               |
| help_category             |
| help_keyword              |
| help_relation             |
| help_topic                |
| host                      |
| ndb_binlog_index          |
| plugin                    |
| proc                      |
| procs_priv                |
| servers                   |
| slow_log                  |
| tables_priv               |
| time_zone                 |
| time_zone_leap_second     |
| time_zone_name            |
| time_zone_transition      |
| time_zone_transition_type |
| user                      |
+---------------------------+
23 rows in set (0.00 sec)

mysql> desc mysql.db;
+-----------------------+---------------+------+-----+---------+-------+
| Field                 | Type          | Null | Key | Default | Extra |
+-----------------------+---------------+------+-----+---------+-------+
| Host                  | char(60)      | NO   | PRI |         |       |
| Db                    | char(64)      | NO   | PRI |         |       |
| User                  | char(16)      | NO   | PRI |         |       |
| Select_priv           | enum('N','Y') | NO   |     | N       |       |
| Insert_priv           | enum('N','Y') | NO   |     | N       |       |
| Update_priv           | enum('N','Y') | NO   |     | N       |       |
| Delete_priv           | enum('N','Y') | NO   |     | N       |       |
| Create_priv           | enum('N','Y') | NO   |     | N       |       |
| Drop_priv             | enum('N','Y') | NO   |     | N       |       |
| Grant_priv            | enum('N','Y') | NO   |     | N       |       |
| References_priv       | enum('N','Y') | NO   |     | N       |       |
| Index_priv            | enum('N','Y') | NO   |     | N       |       |
| Alter_priv            | enum('N','Y') | NO   |     | N       |       |
| Create_tmp_table_priv | enum('N','Y') | NO   |     | N       |       |
| Lock_tables_priv      | enum('N','Y') | NO   |     | N       |       |
| Create_view_priv      | enum('N','Y') | NO   |     | N       |       |
| Show_view_priv        | enum('N','Y') | NO   |     | N       |       |
| Create_routine_priv   | enum('N','Y') | NO   |     | N       |       |
| Alter_routine_priv    | enum('N','Y') | NO   |     | N       |       |
| Execute_priv          | enum('N','Y') | NO   |     | N       |       |
| Event_priv            | enum('N','Y') | NO   |     | N       |       |
| Trigger_priv          | enum('N','Y') | NO   |     | N       |       |
+-----------------------+---------------+------+-----+---------+-------+
22 rows in set (0.00 sec)

mysql> desc mysql.tables_priv;
+-------------+-------------+------+-----+-------------------+-----------------------------+
| Field       | Type        | Null | Key | Default           | Extra                       |
+-------------+-------------+------+-----+-------------------+-----------------------------+
| Host        | char(60)    | NO   | PRI |                   |                             |
| Db          | char(64)    | NO   | PRI |                   |                             |
| User        | char(16)    | NO   | PRI |                   |                             |
| Table_name  | char(64)    | NO   | PRI |                   |                             |
| Grantor     | char(77)    | NO   | MUL |                   |                             |
| Timestamp   | timestamp   | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| Table_priv  | set('Select','Insert','Update','Delete','Create','Drop','Grant','References','Index','Alter','Create View','Show view','Trigger') | NO   | | | |
| Column_priv | set('Select','Insert','Update','References')                                                                                      | NO   | | | |
+-------------+-------------+------+-----+-------------------+-----------------------------+
8 rows in set (0.00 sec)

mysql> desc mysql.columns_priv;
+-------------+----------------------------------------------+------+-----+-------------------+-----------------------------+
| Field       | Type                                         | Null | Key | Default           | Extra                       |
+-------------+----------------------------------------------+------+-----+-------------------+-----------------------------+
| Host        | char(60)                                     | NO   | PRI |                   |                             |
| Db          | char(64)                                     | NO   | PRI |                   |                             |
| User        | char(16)                                     | NO   | PRI |                   |                             |
| Table_name  | char(64)                                     | NO   | PRI |                   |                             |
| Column_name | char(64)                                     | NO   | PRI |                   |                             |
| Timestamp   | timestamp                                    | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| Column_priv | set('Select','Insert','Update','References') | NO   |     |                   |                             |
+-------------+----------------------------------------------+------+-----+-------------------+-----------------------------+
7 rows in set (0.00 sec)

mysql> desc mysql.procs_priv;
+--------------+----------------------------------------+------+-----+-------------------+-----------------------------+
| Field        | Type                                   | Null | Key | Default           | Extra                       |
+--------------+----------------------------------------+------+-----+-------------------+-----------------------------+
| Host         | char(60)                               | NO   | PRI |                   |                             |
| Db           | char(64)                               | NO   | PRI |                   |                             |
| User         | char(16)                               | NO   | PRI |                   |                             |
| Routine_name | char(64)                               | NO   | PRI |                   |                             |
| Routine_type | enum('FUNCTION','PROCEDURE')           | NO   | PRI | NULL              |                             |
| Grantor      | char(77)                               | NO   | MUL |                   |                             |
| Proc_priv    | set('Execute','Alter Routine','Grant') | NO   |     |                   |                             |
| Timestamp    | timestamp                              | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+--------------+----------------------------------------+------+-----+-------------------+-----------------------------+
8 rows in set (0.00 sec)
参考链接:

==

MySQL中查看当前的连接信息

结论:使用status命令

==

MySQL的string函数

==

字符串比较函数

==

MySQL的正则表达式

==

如何获取MySQL的字符串长度?

搜索关键字:MySQL string length

参考解答:
  • LENGTH() returns the length of the string measured in bytes.
  • CHAR_LENGTH() returns the length of the string measured in characters.

This is especially relevant for Unicode, in which most characters are encoded in two bytes. Or UTF-8, where the number of bytes varies. For example:

select length(_utf8 '€'), char_length(_utf8 '€')
--> 3, 1

As you can see the Euro sign occupies 3 bytes (it’s encoded as 0xE282AC in UTF-8) even though it’s only one character.

=

  • You are looking for CHAR_LENGTH() to get the number of characters in a string.
  • For multi-byte charsets LENGTH() will give you the number of bytes the string occupies, while CHAR_LENGTH() will return the number of characters.
参考链接:

声明: 除非注明,ixyzero.com文章均为原创,转载请以链接形式标明本文地址,谢谢!
https://ixyzero.com/blog/archives/2120.html

2 thoughts on “MySQL中如何知道哪些用户具有哪些库表的权限?”

  1. MySQL中字段的「字节长度」和「字符长度」
    https://stackoverflow.com/questions/1870937/mysql-how-to-select-data-by-string-length
    `
    You are looking for CHAR_LENGTH() to get the number of characters in a string.

    For multi-byte charsets LENGTH() will give you the number of bytes the string occupies, while CHAR_LENGTH() will return the number of characters.

    CHAR_LENGTH() –获取字符串的字符个数
    LENGTH() –对于多字节的字符集而言,它会返回字符串实际占用的字节数
    `

发表评论

电子邮件地址不会被公开。 必填项已用*标注