Finding Updateable Columns

A customer says: "I am building a form that presents columns in a table as fields in the form. I want to use the end user's database account to discover which columns are updateable and present the corresponding fields appropriately to the end user: I will gray-out the fields that are not updateable. How do I discover the updateable columns in a table?"

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!