CODEWITHSUNDEEP
mysql
Jannes Braet
Jannes Braet

Reputation: 207

mysql INFORMATION_SCHEMA.SCHEMA_PRIVILEGES explanation?

hello in mysql these are the columns in the INFORMATION_SCHEMA.SCHEMA_PRIVILEGES table

+----------------+--------------+------+-----+---------+-------+
| Field          | Type         | Null | Key | Default | Extra |
+----------------+--------------+------+-----+---------+-------+
| GRANTEE        | varchar(81)  | NO   |     |         |       |
| TABLE_CATALOG  | varchar(512) | YES  |     | NULL    |       |
| TABLE_SCHEMA   | varchar(64)  | NO   |     |         |       |
| PRIVILEGE_TYPE | varchar(64)  | NO   |     |         |       |
| IS_GRANTABLE   | varchar(3)   | NO   |     |         |       |
+----------------+--------------+------+-----+---------+-------+

so i am wondering what all those columns stands for i know what the third and fourth column stands for but i don't have any idea what the other columns do coul anyone explain me them bit? i have been searching on the net but i haven't found anythin only some example values for the column.

Upvotes: 3

Views: 4251

Answers (1)

RolandoMySQLDBA
RolandoMySQLDBA

Reputation: 44343

According to the MySQL Documentation, these values are just copied from the mysql.db table. All this does is provide an alternative way to find all users who have been granted a database-level privilege.

Here is a breakdown of what each column represents

  • GRANTEE is mysql user that had the privilege granted (user@host from mysql.db)
  • TABLE_CATALOG is a field defined for this table to be SQL-92 compatiple
  • TABLE_SCHEMA is the database whose grants are allowed (db column in mysql.db)
  • PRIVILEGE_TYPE is the individual database-level privilege granted in mysql.db
    • Select_priv
    • Insert_priv
    • Update_priv
    • Delete_priv
    • Create_priv
    • Drop_priv
    • References_priv
    • Index_priv
    • Alter_priv
    • Create_tmp_table_priv
    • Lock_tables_priv
    • Create_view_priv
    • Show_view_priv
    • Create_routine_priv
    • Event_priv
    • Trigger_priv
  • IS_GRANTABLE tells you if the user was defined WITH GRANTS (Grant_priv column of mysql.db) . In other words, the value tells whether or not the mysql user can give away the specific database-level privilege to other mysql users

Upvotes: 5

Related Questions