Granting Privileges at their Levels in MySQL
MySQL Access Privileges - Part 3
Division 7
Forward: In this part of the series, we look at privilege levels and how privileges are granted in that light.
By: Chrysanthus Date Published: 8 Aug 2012
Introduction
Privilege Levels
The Server System is structured in categories. You have the global level, the database level, the table level and the column (table column) level; coming down in that order. Routines (stored procedures and functions) are of the table level.
Global privileges are privileges that concern the whole server. Database privileges are privileges that concern the databases. Remember, you can have more than one database in a server. Table privileges are privileges that concern tables. Routine privileges are privileges that concern routines (stored procedures and functions) and are of the same level as the table privileges. Column privileges are privileges that concern columns of tables and they are of the column level.
Note: all privileges are granted using the Grant Statement. Also note that routines are stored procedures and functions (see later).
Global Privileges
Global privileges are administrative and it is normally the root user who has them. He can give any of them to you if he wants to; however, he will hardly do that. He can only do that if he has a very good reason for doing so.
Global privileges taught in this series are: CREATE USER, FILE, RELOAD, SHOW DATABASES and SHUTDOWN.
The CREATE USER privilege is used to create a new user account. A user must have an account before he can do anything in the server (a server has databases). The FILE privilege gives you the right to read and write certain files that concern the server in some directories. The RELOAD privilege enables the use of the FLUSH statement (see later). The SHOW DATABASES privilege allows you to see the names of all the databases in the server. If you do not have this privilege, you can only see the names of databases that concern you. Note that the root can access all databases in the server. The SHUTDOWN privilege allows you to shutdown (close and stop from functioning) the MySQL server (not the computer).
As you can see, the ordinary user should not have these privileges, because any abuse from him can cause a lot of damage.
For the root to grant any of these privileges such as the FILE privilege, he would type at the mysql prompt:
grant FILE on *.* to 'user_name'@'host_name';
Here, *.* means everything accessible in the server. Only the root should access everything. If there is more than one privilege, separate them with commas.
Database privileges apply to a database. Database privileges of this series are: CREATE, DROP, and GRANT OPTION. CREATE is the right (privilege) to create a database. DROP is the right to drop a database. The user who has the GRANT OPTION privilege has the right to give his database privileges to another user of the server; he is not obliged to give all or any of his privileges, but he has the right to do so. So, if you are an ordinary user and you have the GRANT OPTION privilege, you can give one or more of your privileges to another user. In this series, privileges, means rights. The database privileges, studied in these tutorials that you can grant to another user are: CREATE, DROP, and GRANT OPTION.
Assume that you are an ordinary user and you have the CREATE, DROP, and GRANT OPTION privileges for the database called, myDb. You can give the DROP privilege to the user, john@localhost for the database as follows:
grant DROP on myDb.* to 'john'@'localhost';
Now, in *.*, you can interpret the first asterisk as all databases; it actually means all databases and other things like files, seen at the global level. In the above statement we have, myDb, in the position of the first asterisk; this means you are referring to a particular database called, myDb (and NOT all the databases and other global level items). In the statement, since the position of the first asterisk has, myDb, it means the second asterisk is referring to all tables and all routines in the myDb database. If you have more than one privilege in a statement, separate them with commas. The following statement illustrates this:
grant CREATE, DROP on myDb.* to 'john'@'localhost';
Now, consider the following statement:
grant CREATE, DROP, GRANT OPTION on *.* to 'john'@'localhost';
Assume that you are an ordinary user who was given the CREATE, DROP, and GRANT OPTION privileges, which we know are the database level privileges. At all times, John can only receive the privileges from you that you have. Here, CREATE means John can create his own databases. *.* refers to his own databases and your own databases. DROP means he can drop his own databases and your own databases. John cannot drop any database that does not belong to you or himself, because you are the one who gave him the privileges. GRANT OPTION here means he can in future give the privileges that you have given to him, to some other person. If you want him to be able to drop only his own databases and not your own then you will start by giving him the CREATE and GRANT OPTION privilege as follows:
grant CREATE, GRANT OPTION on *.* to 'john'@'localhost';
After he creates a database, you execute the following statement:
grant DROP on hisdB.* to 'john'@'localhost';
where hisdB is the name of each database he creates. In this way he will be able to drop his own database but not yours.
Table privileges apply to all columns in a given table. The syntax to grant table level privileges is:
grant privileges on db_name.tbl_name to 'john'@'localhost';
where db_name is a database name and tbl_name is the table name. The database name followed by the dot in the statement, directs mysql to the table, whose name follows the dot in the statement.
Table level privileges are: ALTER, CREATE, DELETE, DROP, GRANT OPTION, INDEX, INSERT, SELECT, TRIGGER, and UPDATE.
ALTER is to alter a table. CREATE is to create a table. DELETE is to delete rows in a table. DROP is to drop a table. GRANT OPTION is to grant table privileges. INDEX is for creating and dropping indexes. INSERT is for inserting rows in the table. SELECT is for selecting rows of the table. TRIGGER is for creating or dropping or executing a trigger associated with the table. UPDATE is for updating rows in the table.
You can give privileges (that you have) for a table as in the following example:
grant INSERT, SELECT, UPDATE, TRIGGER on supermarket.products to 'john'@'localhost';
There are 4 privileges in the statement. The name of the database is, supermarket. The name of the table is, products. Remember, there is a dot between the database name position and the table name position.
Note that some privileges such as CREATE have the same name and purpose at different levels, but they act on the objects at their respective levels.
Column privileges apply to single columns in a given table. Each privilege to be granted at the column level must be followed by the column or columns, enclosed within parentheses. The column privileges are INSERT, SELECT and UPDATE. This means, if you have these column privileges, you can insert data for column cells in a table; you can select data from certain columns cells of a table; and you can update data of certain columns cells in a table. The privileges can be granted as in the following statement:
grant INSERT, SELECT on supermarket.products to 'john'@'localhost';
This statement grants the privileges only of the products table. If you want the privileges of all the tables in the supermarket database to be granted, then you would type:
grant INSERT, SELECT on supermarket.* to 'john'@'localhost';
Note the use of the asterisk at the position of the table in the statement (the dot before it is always there). In “supermarket.*” the asterisk means all tables of the supermarket.
Assume the products table has the columns: productName, productCategory, and leastSellingPrice. You can give a clerk the INSERT, SELECT privileges as follows:
grant INSERT (productName, productCategory), SELECT (leastSellingPrice) on supermarket.* to 'clerk'@'localhost';
You should not grant the clerk the INSERT or UPDATE column privilege on the leastSellingPrice column, because he can take advantage of that and embezzle money in the long run. So, you type column names for a particular privilege in parentheses, separating them with commas. In some situations you have to precede the column name with the name of the table and a dot.
Each level has its own privileges relevant to that level. You can give privileges of a lower level at a higher level. However, the privileges will still act on the lower level objects that are under the higher-level (object in question). Consider the following statement:
grant DELETE, INSERT, SELECT, UPDATE on hisdB.* to 'john'@'localhost';
The privileges here are table level privileges that have been given at the database level (hisDb – do not forget the dot after the database position in the statement). So the privileges will act on the tables inside the hisdB database and not on the hisdB empty database. A database level privilege (e.g. DROP (database)) acts on a database whether or not it has tables. Lower privileges, given at a higher level, act on the lower objects inside the higher-level object and not at the higher level.
Effects of giving Lower Level Privileges at Higher Levels
Here is a quotation from the MySQL Manual: “The privileges for a database, table, column, or routine are formed additively as the logical OR of the privileges at each of the privilege levels. For example, if a user has a global SELECT privilege, the privilege cannot be denied by an absence of the privilege at the database, table, or column level.”
The quotation means for example, that if you give a user the INSERT privilege at the database level; you do not have to give the INSERT privilege at the table and column levels again. So the user can go ahead and insert data in a table (rows) that belongs to the database, without being denied permission. He can also go ahead and insert data in the columns of a table of the database. If you do not want the user to insert data into all the columns of the table, then give him column privileges at the column level choosing the columns.
This is a quotation from the MySQL manual: “MySQL enables you to grant privileges on databases or tables that do not exist. For tables, the privileges to be granted must include the CREATE privilege. This behavior is by design, and is intended to enable the database administrator to prepare user accounts and privileges for databases or tables that are to be created at a later time.”
The quotation means that, once there is a new user, he can be granted privileges to a database and the database’s objects (tables) that he is still to create.
That is it for this part of the series. Strictly speaking, each level has its own privileges; however, you need to know how to use the privileges. Do not forget that you can only give to another user, privileges that you own. Time to take a break. We stop here and continue in the next part of the series.
Chrys
Related Links
Major in Website DesignWeb Development Course
HTML Course
CSS Course
ECMAScript Course
NEXT