Broad Network

MySQL Access Privilege Overview

MySQL Access Privileges - Part 1

Division 7

Forward: In this part of the series we learn the basics of MySQL Access Privileges.

By: Chrysanthus Date Published: 8 Aug 2012


This is part 1 of my series MySQL Access Privileges. The root (DataBase Administrator) user has the right to do anything in a MySQL server. For example: he/she has the right to delete any row in any table; insert any row in any table; update (modify) any row in any table; and the right to do many more things. With MySQL, such rights are called privileges. In computing, the word, access, means “to read or write something”. In this part of the series we learn the basics of MySQL Access Privileges. It is the root user who gives privileges to any other user. It is possible (but not straight forward) for the root user to know the password of all other users. Under normal circumstances he would not know, but if he wants to know he would know, especially when a new user is signing up (registering) to the database (server).

This series is based on MySQL 5.

You should have covered the series in this blog beginning with the following titles, before reaching here:

- Database
- Normalization
- Referential Integrity in Database
- Implementing Database in MySQL
- Introduction to MySQL Programming for Events

To reach any of the series, just type the title and my name Chrys in the Search Box of this page and click Search.

To create a database, to create a table, to do anything on the table, etc. you use a SQL statement. SQL statements exist in groups. The groups I will mention in this series are:  Data Definition Statements, Data Manipulation Statements, MySQL Transactional and Locking Statements, Database Administration Statements, Utility Statements. We shall look at some of these statements as regards, MySQL privileges, below. Note: at the installation of a MySQL server and/or the creation of any database, the root user has all the privileges. He/She is the one who normally installs the MySQL server. His user name is “root” by default; he/she can change this name if he/she wants to. He/she determines his/her own password.

Statements have their names. Below I give statement names and the privileges that allow you to use the statements. In the next part of the series, we shall learn how to code privileges. Remember, the root user is the one who gives different privileges to different users. Note: A user can still give some of his own privileges to another user.

Some Privileges and Data Definition Statements
Here is a list of some statements (and) privileges. The privilege for a statement allows the user to use the statement (on the item or object concerned).

alter table – ALTER
create database, create table – CREATE
create temporary table - CREATE TEMPORARY
drop database, drop table – DROP
create trigger, drop trigger (table) – TRIGGER

So in the above list, the privileges are: ALTER, CREATE, CREATE TEMPORARY, DROP. On the left of each privilege above, you have the statement or statements concerned.

Some Privileges and Data Manipulation Statements
Here is a list of some statements (and) privileges under data manipulation:

delete (rows) - DELETE
insert (rows) – INSERT
select (columns) – SELECT
update (table) - UPDATE

Some Privileges and MySQL Transactional and Locking Statements
Here is a list of some statements (and) privileges under Transaction and Locking:

Lock tables - LOCK TABLES

Some Privileges and Database Administration Statements
Here is a list of some statements (and) privileges under Database Administration:

show databases - SHOW DATABASES
create user - CREATE USER
flush (table, log) – RELOAD

Non-Isolating Privileges
Some privileges cannot work alone. For example, the ALTER privilege works with the INSERT and CREATE privileges. In other words, you cannot have the right to alter a table without having the right to insert a row or without even having the right to create the table.

Non-Statement Name Privileges
It is not every privilege that allow the execution of a statement like the ones above that have the names of their corresponding statements. We shall see some of these non-statement name privileges in the coming parts of the series.

That is it for this part of the series. We stop here and continue in the next part.


Related Links

Major in Website Design
Web Development Course
HTML Course
CSS Course
ECMAScript Course


Become the Writer's Fan
Send the Writer a Message