Summary of MySQL Privilege System
MySQL Access Privileges - Part 7
Division 7
Forward: In this part of the series we look at a summary of all what we have learned in MySQL Access Privileges.
By: Chrysanthus Date Published: 8 Aug 2012
Introduction
What is a Privilege in MySQL
In MySQL a privilege is a right for a user to perform certain task. The root has all the privileges. The root can give (copy) some of his privileges to other users who can also give some of their privileges to other (new) users.
Privileges are granted with the grant statement. Privileges are revoked with the revoke statement.
Privileges that have SQL Statements and privileges that do not
Many privileges have corresponding SQL statements and the role of these privileges is to allow you to use their corresponding SQL statements. Some privileges do not have corresponding SQL statements:
Privileges with Corresponding SQL statements in this series are: CREATE USER, SHOW DATABASES, CREATE, DROP, ALTER, CREATE, DELETE, INDEX, INSERT, SELECT, TRIGGER, UPDATE, LOCK TABLES, GRANT OPTION.
Privileges without Corresponding SQL statements in this series are: FILE, RELOAD, SHUTDOWN.
Privileges Levels
The server System has a structure with levels and privileges are made for those levels. That is, a privilege is design to act at a particular level. You have the global level (topmost); next down, you have the database level; next down you have the table level; next down you have the column level. You also have the routing level. The routing and table levels are of the same position (height) - seen from the top.
Global Privileges in this series are: CREATE USER, FILE, RELOAD, SHOW DATABASES and SHUTDOWN. Officially GRANT OPTION is not in this group, but it works at this level in my own version of MySQL.
Database Privileges in this series are: CREATE, DROP, and GRANT OPTION.
Table Privileges in this series are: ALTER, CREATE, DELETE, DROP, GRANT OPTION, INDEX, INSERT, SELECT, TRIGGER, and UPDATE.
Routine Privileges in this series are: ALTER ROUTINE, CREATE ROUTINE, EXECUTE, and GRANT OPTION.
Column privileges are: INSERT, SELECT and UPDATE.
Some lower level privileges can be given at higher levels but they will act at the lower level.
The ALL privilege grants all the privileges a user has at a particular level, except GRANT OPTION.
The GRANT OPTION privilege (if you have it) allows you to grant any, some or all of your privileges to another user.
In simple terms, the grant statement is:
grant Privileges On Item To user_account_name
You need the GRANT OPTION privilege to use this statement.
The revoke statement comes in two forms: the first form is:
revoke privileges on items from user_account_name;
You need the GRANT OPTION privilege in order to use this first revoke statement. The second form is:
revoke ALL PRIVILEGES, GRANT OPTION from user_account_name
The first revoke statement revokes particular privileges of a user from items in a level. The second revoke statement revokes all privileges of a user from all levels from all items. To use the second revoke statement, you need the CREATE USER privilege.
Transactions
If you want a transaction, you need to lock the tables in the transaction with the Lock Tables statement. At the end of the transaction you unlock the tables with the unlock table statement. To lock tables you need the SELECT and the LOCK TABLES privileges.
MySQL User
After a user has just been created, he has no privileges, and so he cannot access data in a database. He has to be given privileges before he can access data with the privileges. To access data in a database server, you must login to the server first; then you can access data, if you have the privileges to do so.
That is it. We have come to the end of this series. I hope you appreciated it.
Chrys
Related Links
Major in Website DesignWeb Development Course
HTML Course
CSS Course
ECMAScript Course