Overview of MySQL Access Privilege

MySQL Access Privileges - Part 1

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

By: Chrysanthus Date Published: 18 Jul 2015

Introduction

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.

Prerequisite
This is part of the series titled, MySQL Course. At the bottom of this page you will find links to the different series you should have read before reaching here.

Recall
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. 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 go with 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.

Chrys

Broad Network

Related Articles

Overview of MySQL Access Privilege

MySQL Access Privileges - Part 1

Introduction

Related Links

Comments