Mysql

From http://www.devshed.com/c/a/MySQL/The-MySQL-Grant-Tables/2/

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 moc.evitcaretni413|jw#moc.evitcaretni413|jw;

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 moc.evitcaretni413|jw#moc.evitcaretni413|jw;

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 moc.evitcaretni413|jw#moc.evitcaretni413|jw

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 moc.evitcaretni413|eromlig#moc.evitcaretni413|eromlig;

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 moc.evitcaretni413|eromlig#moc.evitcaretni413|eromlig;

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.

Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-Share Alike 2.5 License.