The COLUMN_PRIVILEGES
table provides
information about column privileges. It takes its values from the
mysql.columns_priv
system table.
The COLUMN_PRIVILEGES
table has these
columns:
GRANTEE
The name of the account to which the privilege is granted, in
'
format.user_name
'@'host_name
'TABLE_CATALOG
The name of the catalog to which the table containing the column belongs. This value is always
def
.TABLE_SCHEMA
The name of the schema (database) to which the table containing the column belongs.
TABLE_NAME
The name of the table containing the column.
COLUMN_NAME
The name of the column.
PRIVILEGE_TYPE
The privilege granted. The value can be any privilege that can be granted at the column level; see Section 15.7.1.6, “GRANT Statement”. Each row lists a single privilege, so there is one row per column privilege held by the grantee.
In the output from
SHOW FULL COLUMNS
, the privileges are all in one column and in lowercase, for example,select,insert,update,references
. InCOLUMN_PRIVILEGES
, there is one privilege per row, in uppercase.IS_GRANTABLE
YES
if the user has theGRANT OPTION
privilege,NO
otherwise. The output does not listGRANT OPTION
as a separate row withPRIVILEGE_TYPE='GRANT OPTION'
.
Notes
COLUMN_PRIVILEGES
is a nonstandardINFORMATION_SCHEMA
table.
The following statements are not equivalent:
SELECT ... FROM INFORMATION_SCHEMA.COLUMN_PRIVILEGES
SHOW GRANTS ...