GRANT OPTION and ALL Privileges in MySQL

MySQL Access Privileges - Part 5

Foreword: In this part of the series we look at the GRANT OPTION, in depth, and then we look at the ALL privilege.

By: Chrysanthus Date Published: 18 Jul 2015

Introduction

This is part 5 of my series, MySQL Access Privileges. In this part of the series we look at the GRANT OPTION, in depth, and then we look at the ALL privilege. I assume you have read the previous parts of the series, before reaching here, as this is a continuation.

GRANT OPTION Privilege
If you have the GRANT OPTION privilege, it means you can grant one or two or more or all of your privileges (rights) to another user. You can only give to the user, privileges that you have. For the user who received your privileges, if you gave him the GRANT OPTION privilege, then he too can give the privileges that he has (you have given him) to some other person. He can also give the GRANT OPTION privilege to that other person and the receiver would use it in the same way.

GRANT OPTION at all Levels
The GRANT OPTION privilege exists at all levels. The GRANT OPTION privilege exists at the global level. The GRANT OPTION privilege exists officially at the database level. The GRANT OPTION privilege exists officially at the table and routine levels. Note: column privileges can be given at the table level.

If you give all the table privileges to a user, then you have indirectly given the user the column privileges. So, if you give all the table privileges and the GRANT OPTION privilege to a user, you have indirectly given the user the GRANT OPTION privilege at the column level. So the GRANT OPTION privilege exists at all levels.

GRANT OPTION Example
The following grant statement grants a user, John, table level privileges at the table level including the GRANT OPTION:

grant INSERT, SELECT, UPDATE, TRIGGER on supermarket.products to john with GRANT OPTION;

With this statement, John can now grant the table privileges, INSERT, SELECT, UPDATE and TRIGGER for the table, products, to his own user that he creates.

WARNING
Granting a user you do not trust, privileges, is a dangerous thing to do, because the user can use the privileges against you. Giving the user the GRANT OPTION is even worse, because the third person (your user’s user) that you do not even know may be your enemy.

So give privileges, with care, and give the GRANT OPTION privilege with even more care.

The ALL Privilege
You can give all the privileges you have at a particular level to another user. You use the ALL privilege for this purpose. To give all your privileges at the database level to the user, John, you would type:

    grant ALL on supermarket.* to john;

where supermarket is the database of interest. To grant all your privileges at the table level, you would type:

    grant ALL on supermarket.products to john;

where products is the table of interest.

Note: the ALL privilege does not include the GRANT OPTION privilege. This is because granting the right to someone to give an unknown person your rights, is very delicate (discussed above).

If you want the GRANT OPTION to be included at the level, you will type:

    grant ALL on supermarket.products to john with GRANT OPTION;

Now, ALL and ALL PRIVILEGES are synonyms. So you may fine ALL PRIVILEGES instead of ALL in some forums.

Problem with ALL and *.* in grant Statement
Remember, we have global level privileges and database level privileges. If you want to give the global level privileges we have learned in this series, for every accessible item, at the global level, in the server, you would type:

    grant CREATE USER, FILE, RELOAD, SHOW DATABASES, SHUTDOWN on *.* to john;

where John is the user who receives the privileges.

If you want to give the database level privileges we have learned in this series, for all databases, at the database level, in the server, you would type:

    grant CREATE, DROP, GRANT OPTION on *.* to john;

Note that *.* has been used for the above two statements. So how do you interpret the following?

    grant ALL on *.* to john;

The first statement intends to give global privileges for global level items; the second statement intends to give database privileges for databases (items). Well, the global level items, e.g. MySQL produced files, and databases, can be considered to be at the same level. However, the inner contents of a database are of a lower level than the database (seen as a whole); the tables and routines of the same level are of a lower level than the database; the columns and the triggers are of a lower level than the tables. A trigger can be considered as part of a table, just like a column is part of a table; however a trigger is built independently from the table.

Looking at the global privileges (e.g. SHUTDOWN) above, we can say that the server itself, the MySQL produced files, and the databases (seen from the top or outside) are of the same level. A set of privileges exists for an item at a level. At the global level, you have more than one item (of the same level). After that you have the database that has contents, and these contents have their own contents. The content: table and procedure are of the same level. We can say the contents: column and trigger are of the same level. You can have many tables and procedures, which are at the same level in a database.

So in the first statement *.* refer to the global level items (MySQL server itself, files, databases). In the second statement, since the privileges are database privileges, then *.* refer to the databases.

Also note that there is no privilege that exists officially at the global level and also exists officially at the database level.

Basic Privilege for the Simple Internet User
The simple Internet user can only see (select) data in certain columns in certain tables of an Internet database. So all simple (ordinary) Internet users would have the same user name and password (and same hostname). All of them (too many) can have the following mysql statements:

    create user InternetUser identified by 'pswrd';

    grant SELECT on *.* to InternetUser;

These two statements may suffice, but the second one has a problem. In the second statement, they have been given the SELECT privilege at the database level. This privilege will act on all the columns, in all the tables, in all the databases. Allowing the Internet public user to see all the data in all the columns in all the tables in your databases is not safe. So the second statement should be replaced by statements for specific columns, in specific tables, in pacific databases, like in:

    grant SELECT (productName, productCategory, leastSellingPrice) on supermarket.products to InternetUser;

For you to run (type in) the first statement you need to have the global CREATE USER privilege. In my opinion, you should be the root. You need the GRANT OPTION privilege for the second statement (if you are not the root).

Warning: Never give the Internet user, the GRANT OPTION privilege.

Well, this tutorial that appeared at the beginning to be short has taken this long. This is because of the interwoven nature of the privilege system in MySQL.  - And we stop here; see you in the next part of the series.

Chrys

Broad Network

Related Articles

GRANT OPTION and ALL Privileges in MySQL

MySQL Access Privileges - Part 5

Introduction

Related Links

Comments