MySQL Privilege System Summary

MySQL Access Privileges - Part 7

Foreword: 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: 18 Jul 2015

Introduction

This is part 7 of my series, MySQL Access Privileges. In this part of the series we look at a summary of all what we have learned in MySQL Access Privileges. I assume you have read the previous parts of the series, before reaching here; this is a continuation.

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 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.

Privilege Levels
The server System has a structure with levels and privileges are made for these 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 - 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.

Grant and Revoke Statement Syntaxes
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 and unlock 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 access data, if you have the privileges to do so. The root has all privileges at all levels, as soon as he has installed the MySQL server.

That is it. We have come to the end of this series. I hope you appreciated it.

Chrys

Broad Network

Related Articles

MySQL Privilege System Summary

MySQL Access Privileges - Part 7

Introduction

Related Links

Comments