This is a straight-forward set of queries on the data dictionary, which is presented in the information_schema database. The only thing to note is that a user's access to table columns is determined at 4 levels: server level, database level, table level, and column level. These privileges are reported in the information_schema tables USER_PRIVILEGES, SCHEMA_PRIVILEGES, TABLE_PRIVILEGES, and COLUMN_PRIVILEGES. The access I have is the union of all the privileges I have been granted. So, if I have been granted access to all tables in the world database with, say:
GRANT UPDATE ON world.* TO 'user';
, and also to the world.city table with:
GRANT UPDATE ON world.city TO 'user';
, the second GRANT doesn't really matter at all, as the first GRANT already gives me UPDATE access to all the tables in the world database, regardless of access given at the finer level of just one table. So, to find if I have access to a given column, I need to look in up to 4 data dictionary tables, but once I find a suitable privilege, I need look no further to be sure I have that access.
If I'm working interactively, I can always find out the total privileges I have by executing the command SHOW GRANTS, but it's a different matter to write a program that responds to this information dynamically.
Following are a couple of stored functions to do the job. Note that both functions use SECURITY INVOKER, as we are interested in the access allowed to the invoker of the function, not the definer. Note also that both functions attempt to avoid redundant work: once a result is known, a return is made without further database queries. (And by the way, don't forget to grant execute on these functions to the users you want to have access to them.)
I. Function 'updateable'
Takes as input database, table, and column names, and returns a boolean result to tell the user whether the column in question is updateable.
II. Function 'updatelist'
Takes as input a database and table name, and a flag. If the flag is TRUE, returns the columns in the table that are updateable for this user; if FALSE, returns the columns that are not updateable for this user. In both cases, the return is a string containing a comma-separated list of column names.
I. Function 'updateable' definition:
DROP FUNCTION IF EXISTS Updateable;
DELIMITER ;;
CREATE FUNCTION Updateable(db VARCHAR(64),
tab VARCHAR(64),
col VARCHAR(64)) RETURNS BOOL
DETERMINISTIC
SQL SECURITY INVOKER
BEGIN
# Given a database, table, and column in parameters (db, tab, col),
# return TRUE if the function invoker can update that column,
# FALSE otherwise.
DECLARE userId VARCHAR(81);
DECLARE rowCount INT;
# Put userId into form 'user'@'host' (with quotes) for matches
# with information_schema GRANTEE column values.
SET userId = CONCAT("'",
REPLACE(CURRENT_USER(), "@", "'@'"),
"'");
# Look for UPDATE privilege server-wide.
SELECT COUNT(*)
INTO rowCount
FROM information_schema.USER_PRIVILEGES
WHERE GRANTEE = userId
AND PRIVILEGE_TYPE = 'UPDATE';
IF rowCount > 0 THEN RETURN TRUE; END IF;
# Look for database-level privilege.
SELECT COUNT(*)
INTO rowCount
FROM information_schema.SCHEMA_PRIVILEGES
WHERE GRANTEE = userId
AND TABLE_SCHEMA = db
AND PRIVILEGE_TYPE = 'UPDATE';
IF rowCount > 0 THEN RETURN TRUE; END IF;
# Look for table-level privilege.
SELECT COUNT(*)
INTO rowCount
FROM information_schema.TABLE_PRIVILEGES
WHERE GRANTEE = userId
AND TABLE_SCHEMA = db
AND TABLE_NAME = tab
AND PRIVILEGE_TYPE = 'UPDATE';
IF rowCount > 0 THEN RETURN TRUE; END IF;
# Look for column-level privilege.
SELECT COUNT(*)
INTO rowCount
FROM information_schema.COLUMN_PRIVILEGES
WHERE GRANTEE = userId
AND TABLE_SCHEMA = db
AND TABLE_NAME = tab
AND COLUMN_NAME = col
AND PRIVILEGE_TYPE = 'UPDATE';
IF rowCount > 0 THEN RETURN TRUE; END IF;
# No UPDATE privilege found: return FALSE.
RETURN FALSE;
END;;
DELIMITER ;
II. Function 'updatelist' definition:
DROP FUNCTION IF EXISTS UpdateList;
DELIMITER ;;
CREATE FUNCTION UpdateList(db VARCHAR(64),
tab VARCHAR(64),
upd BOOL) RETURNS MEDIUMTEXT
DETERMINISTIC
SQL SECURITY INVOKER
BEGIN
# Given database and table name in parameters (db, tab),
# and an update flag in parameter (upd):
# if upd is TRUE, return a list of the names of columns
# that the invoker can update;
# if upd is FALSE, return a list of the names of columns
# that the invoker cannot update.
DECLARE userId VARCHAR(81);
DECLARE rowCount INT;
DECLARE allColumnsUpdateable BOOL DEFAULT FALSE;
DECLARE result MEDIUMTEXT DEFAULT '';
# Put userId into form 'user'@'host' (with quotes) for matches
# with information_schema GRANTEE column values.
SET userId = CONCAT("'",
REPLACE(CURRENT_USER(), "@", "'@'"),
"'");
# Big IF-THEN structure to find if all columns are updateable.
# Look for UPDATE privilege server-wide.
SELECT COUNT(*)
INTO rowCount
FROM information_schema.USER_PRIVILEGES
WHERE GRANTEE = userId
AND PRIVILEGE_TYPE = 'UPDATE';
IF rowCount > 0 THEN
SET allColumnsUpdateable = TRUE;
ELSE
# Look for database-level privilege.
SELECT COUNT(*)
INTO rowCount
FROM information_schema.SCHEMA_PRIVILEGES
WHERE GRANTEE = userId
AND TABLE_SCHEMA = db
AND PRIVILEGE_TYPE = 'UPDATE';
IF rowCount > 0 THEN
SET allColumnsUpdateable = TRUE;
ELSE
# Look for table-level privilege.
SELECT COUNT(*)
INTO rowCount
FROM information_schema.TABLE_PRIVILEGES
WHERE GRANTEE = userId
AND TABLE_SCHEMA = db
AND TABLE_NAME = tab
AND PRIVILEGE_TYPE = 'UPDATE';
IF rowCount > 0 THEN
SET allColumnsUpdateable = TRUE;
END IF;
END IF;
END IF;
IF upd THEN
# Get updateable column list.
IF allColumnsUpdateable THEN
# As determined above, report all columns.
SELECT GROUP_CONCAT(COLUMN_NAME)
INTO result
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = db
AND TABLE_NAME = tab;
ELSE
# Report column-level privilege, or empty string for none.
SELECT IFNULL(GROUP_CONCAT(COLUMN_NAME), '')
INTO result
FROM information_schema.COLUMN_PRIVILEGES
WHERE GRANTEE = userId
AND TABLE_SCHEMA = db
AND TABLE_NAME = tab
AND PRIVILEGE_TYPE = 'UPDATE';
END IF;
ELSE
# Get non-updateable column list.
IF allColumnsUpdateable THEN
SET result = '';
ELSE
SELECT IFNULL(GROUP_CONCAT(COLUMN_NAME), '')
INTO result
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = db
AND TABLE_NAME = tab
AND COLUMN_NAME NOT IN (
SELECT COLUMN_NAME
FROM information_schema.COLUMN_PRIVILEGES
WHERE GRANTEE = userId
AND TABLE_SCHEMA = db
AND TABLE_NAME = tab
AND PRIVILEGE_TYPE = 'UPDATE');
END IF;
END IF;
RETURN result;
END;;
DELIMITER ;
You could write similar functions to discover which columns are insertable or selectable for a user.
Enjoy!
