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
Introduction
This series is based on MySQL 5.
Prerequisite
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.
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 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.
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.
Chrys
Related Links
Major in Website DesignWeb Development Course
HTML Course
CSS Course
ECMAScript Course
NEXT