Tables_priv and columns_priv - MySQL

The tables_priv and columns_priv grant tables is two of the more recent additions to the MySQL database server. They are intended to provide the user with even greater control over the user's actions while connected to the server. Both are very similar to the db grant table, but with an even more specified range of purpose; a given table contained within a given database. Whereas the superuser could previously limit a user's actions within a database via the db grant table, the superuser can now limit a user's actions on a per-table basis and per-column basis. Understandably, this provides the superuser with a very flexible array of options to work with.

Before we look further into each table, please read carefully the following characteristics:

• Wildcards are permitted within the host field of both tables, but are not permitted within the Db, Table_name and Column_name fields.
• Both tables are sorted similarly to the db table, but is much easier since only the host column can hold wildcards.
• The privilege fields are declared as 'SET' fields.
• The tables_priv and columns_priv tables should ONLY be modified via GRANT/REVOKE commands. Attempts to insert data into these tables using 'INSERT' commands will result in a problematic server!
• The table_priv column within the tables_priv table allows the following: 'Select', 'Insert', 'Update', 'Delete', 'Create', 'Drop', 'Grant', 'References', 'Index', 'Alter'.
• The column_priv column within the tables_priv table allows the following: 'Select', 'Insert', 'Update', 'References'.
• The type column within the columns_priv table allows the following: 'Select', 'Insert', 'Update', 'References'.

Note:

• 'References' is not yet implemented.
• 'usage' simply means a user with no privileges.

The tables_priv grant table

The following is a diagram of the tables_priv table:

Column definitions:

• Host - For what host does this apply?
• Db - For what db connected from the above host does this apply?
• User - For what user from the above host does this apply?
• Table_name - For which table within the above Db does this apply?
• Table_priv - What privileges are allowed for this table?
• Column_priv - What privileges are allowed for the columns contained within this table?
• Timestamp - When was this privilege granted?
• Grantor - Who granted the 'User' this privilege?

Perhaps the only way to truly understand how the tables_priv table is used is through examples. Let's take a look at a few of them.

Example #1:

%>GRANT SELECT ON italy TO wj@314interactive.com;

What does this accomplish?
The above command allows user 'wj' from host '314interactive.com' to perform a 'SELECT' statement on the table 'italy'. Remember that this table would be referred to only if there was a 'N' within the 'SELECT' column of the 'db' or 'host' table regarding the given database/host and given username. If there was a 'Y' within the 'SELECT' column of the 'db' or 'host' table regarding the given database/host and given username, then there would be no need to control the tables_priv table.

Example #2:

%>GRANT SELECT, INSERT ON oats.italy TO wj@314interactive.com;

What does this accomplish?
The above command allows user 'wj' from host '314interactive.com' to perform 'SELECT' and 'INSERT' statements on the table 'italy' residing within the 'oats' database.

Example #3:

%>REVOKE SELECT on oats.italy from wj@314interactive.com

What does this accomplish?
The above command revokes 'SELECT' privileges from user 'wj' from host '314interactive.com' pertaining to the table 'italy' contained within the database 'oats'.

It is important to understand that the information contained within the tables_priv only comes into effect when the host/db tables deny the user the necessary privileges to perform the requested function. If the given privilege were 'Y' within the host/db table, then there would be no need to even look at the tables_priv table.

Example #4: ( A slight bit more complicated)

%>GRANT SELECT(id,name,address,phone),update(address,phone) ON
company.customers TO gilmore@314interactive.com;

What does this accomplish?
The above command grants SELECT privileges for the 'id', 'name', 'address', and 'phone' columns, and UPDATE privileges for the 'address' and 'phone' columns within the 'customers' table, contained within the 'company' database.
What does this affect?
This command modifies both the tables_priv table and the columns_priv tables. This is because it refers to both the table and specific columns residing within the table.

Example #5:

%>REVOKE UPDATE(address,phone) ON company.customers FROM gilmore@314interactive.com;

What does this accomplish?
This revokes UPDATE privileges for the address and phone columns contained within the 'customers' table residing within the company database.
What does this affect?
Since the command makes direct references to the columns contained within the given table, the columns_priv table is updated as well as the tables_priv table.

Although stated previously within this article, it is of enough importance that it should be repeated; Grant tables are only used if needed. For example, if the table of higher precedence provides adequate privileges, than the lower table precedences will not be consulted. If the higher-precedence table contains 'N' within the requested command, then the lower-precedence table will be consulted. Simple as that.

Note from Monty: GRANT will create a new user if the user didn't exist for before and that one can add a password for a new user with the IDENTFIED BY 'password' syntax.

I have compiled a short list of references pertaining to the MySQL grant tables on the following page. Please feel free to review each.

>>> More MySQL Articles          >>> More By W.J. Gilmore

blog comments powered by Disqus