MySQL Forums
Forum List  »  Security

Column info is available when a column is secured
Posted by: Gail Badner
Date: April 25, 2005 06:35PM

I noticed that a user that does not have access to all columns in a table can find out about the secured columns using the DESC command, SHOW COLUMNS command, and by executing SELECT * FROM table.

For example, suppose table 'a' has two columns, i and j, and joe@localhost only has SELECT access to column j.

If joe@localhost executes "DESC a;" or "SHOW COLUMNS FROM a;", the following is returned:
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| i | int(11) | YES | | NULL | |
| j | int(11) | YES | | NULL | |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)

This seems inconsistent with security at the database and table levels. If a user does not have access to a database/table, that database/table will not be returned bySHOW DATABASES/TABLES. If a user does not have at least SELECT access to a column, shouldn't DESC and SHOW COLUMNS FROM exclude information about that column?

If joe@localhost executes "SELECT * FROM a;", the following error is returned:
ERROR 1143: SELECT command denied to user 'joe'@'localhost' for column 'i' in table 'a'
The same error is returned when column i is referenced in a WHERE, GROUP BY, and ORDER BY clause.

When column i is referenced in a HAVING clause, the error that is returned does not expose the fact that joe does not have SELECT access to the column. Instead, the error returned by "SELECT j FROM a HAVING i=1;" is:
ERROR 1054: Unknown column 'i' in 'having clause'

How much should a user be able to find out about a column if they do not have at least SELECT access to that column?
1) Should the column be described by "DESC a;" ?
2) Should an error from referencing the column say that the column is unknown (error 1054), or should it say that access to the column is denied(error 1143)?
3) Should executing "SELECT * FROM a;" return an error, or should it return results for all columns except those that are secured? I'd rather see an error returned, since returning results could cause inconsistencies with applications that explicitly bind parameters for each returned column.

I also noticed some other inconsistencies regarding tables and databases.

The error returned from attempting to use a non-existent database depends on whether the user has access to all databases. For example, if root@localhost has SELECT privilege on all databases, then executing "USE abc;" when database "abc" does not exist results in:
ERROR 1049: Unknown database 'abc'
If joe@localhost does not have SELECT privilege on all databases, the following error is returned instead:
ERROR 1044: Access denied for user 'joe'@'localhost' to database 'abc'
Shouldn't error 1049 be returned in both cases?

If a user tries to use an existing database and does not have at least SELECT access to anything in that database, should the error message say that the database is unknown (error 1049) or should it say that access to the database is denied (error 1044)?

Similarly, the error returned from attempting to SELECT from a non-existent table depends on whether the user has access to all tables in the database being used. For example, if root@localhost has SELECT privilege on all tables in database grant_test, then executing 'SELECT * FROM junk;" when table "junk" does not exist results in:
ERROR 1146: Table 'grant_test.junk' doesn't exist
If the joe@localhost does not have SELECT privilege on all tables, the following error is returned instead:
ERROR 1142: SELECT command denied to user 'joe'@'localhost' for table 'junk'
Shouldn't error 1046 be returned in both cases?

If a user tries SELECT from an existing table and does not have at least SELECT access to any columns in the table, should the error message say that the table does not exist (error 1146) or should it say that access to the table is denied (error 1142)?

Which of these should be considered bugs? Please let me know and I'll submit them.

Thanks,
Gail

Options: ReplyQuote


Subject
Views
Written By
Posted
Column info is available when a column is secured
6911
April 25, 2005 06:35PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.