Monday, January 11, 2010

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!

41 comments:

  1. mysql> show full columns from city where Privileges like "%select%";
    +-------------+----------+-------------------+------+-----+---------+----------------+---------------+---------+
    | Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment |
    +-------------+----------+-------------------+------+-----+---------+----------------+---------------+---------+
    | ID | int(11) | NULL | NO | PRI | NULL | auto_increment | select | |
    | Name | char(35) | latin1_swedish_ci | NO | | | | select | |
    | CountryCode | char(3) | latin1_swedish_ci | NO | | | | select | |
    | District | char(20) | latin1_swedish_ci | NO | | | | select | |
    | Population | int(11) | NULL | NO | | 0 | | select,update | |
    +-------------+----------+-------------------+------+-----+---------+----------------+---------------+---------+
    5 rows in set (0.00 sec)

    mysql> show full columns from city where Privileges like "%update%";
    +------------+---------+-----------+------+-----+---------+-------+---------------+---------+
    | Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment |
    +------------+---------+-----------+------+-----+---------+-------+---------------+---------+
    | Population | int(11) | NULL | NO | | 0 | | select,update | |
    +------------+---------+-----------+------+-----+---------+-------+---------------+---------+
    1 row in set (0.01 sec)

    ReplyDelete
  2. If you lend someone $20 and never see that person again, it was probably worth it.............................................

    ReplyDelete
  3. 偉大的致富萬能之鑰,正是幫你充分掌握自己心志所必須的自律自制 ..................................................

    ReplyDelete
  4. 快樂與滿足的秘訣,就在全心全意投注於現在的每一分,每一秒上..................................................

    ReplyDelete
  5. 要在憂患恥辱的環境裡,創造我們自力更生的新生活。..................................................

    ReplyDelete
  6. 婚姻對男人來說是賭他的自由,對女人而言卻是賭她的幸福。.................................................................

    ReplyDelete
  7. 愛,拆開來是心和受兩個字。用心去接受對方的一切,用心去愛對方的所有。......................................................................

    ReplyDelete
  8. 一個人的際遇在第一次總是最深刻的,有時候甚至會讓人的心變成永遠的絕緣。......................................................................

    ReplyDelete
  9. 在莫非定律中有項笨蛋定律:「一個組織中的笨蛋,恆大於等於三分之二。」............................................................

    ReplyDelete
  10. Are you fighting in Final Fantasy XIV for FFXIV Gil or Final Fantasy XIV Gil?
    Can you suffer yourself being called newbie in FFXIV Gil game?
    Are you seeking unofficial Buy FFXIV Gil cheats or Final Fantasy XIV guides in order to make Final Fantasy XIV Gil faster?
    Can you get millions of Cheap FFXIV Gil in one day?
    Even if you know how to farm Buy Final Fantasy XIV Gil you have to prepare enough Final Fantasy XIV Power Leveling first to buy height class Final Fantasy XIV Items, to upgrade your Final Fantasy XIV characters.
    FF14 Gil Then why not Buy FFXIV Gil from us?
    In Final Fantasy XIV it's the fastest way FF14 Gil,for you to get rich. We are online 24 hours a day ready and 7 days one week to power up your FFXIV Gil accounts with FFXIV Gil. Here is the best place for the Final Fantasy XIV Online players to buy your Final Fantasy XIV Gil.
    We are the professional website in FFXIV GIL sale.FFXIV GIL here,We are professional FFXIV Power Leveling online. We update price every single day to make sure we are the lowest in the market.(we don't compare price with scam sites which uses unbelievable low price to deceive.)
    Our slogan:Cheapest price, Fastest delivery, Best service! Final Fantasy Xiv Gil
    In the 2 years we are in this field , FFXIV Gilbuilt many business with tens of thousands of customers. They are very satisfied with our service.So if you want to get a log of Cheap FFXIV Gil ,no doubt ,come to our website to buy. Our customer service is ready for you on line now!(WWW,GM MMO,COM Sell FFXIV Gil)

    ReplyDelete