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
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.
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.
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
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.
Related LinksImplementing Database in MySQL
Programming in MySQL
Backup Basics in MySQL
MySQL Access Privileges
Regular Expressions in MySQL
Date and Time in MySQL
Event in MySQL
PurePerl MySQL API Prepared Statements
More Related Links
PurePerl MySQL Command Line Tool
Major in Website Design
Perl Course - Optimized
Web Development Course