MySQL Database Administration 101_MySQL

This tutorial will go over some of the fundamentals of MySQL Database Administration and Security.

If you aren’t familiar with MySQL, creating a database and table is a simple process that requires a user with multiple privileges to execute a SQL Statement. The syntax would look something like this:

CREATE DATABASE shop;			
USE shop;			
CREATE TABLE clients(				
id INT NOT NULL AUTO_INCREMENT,				
firstName VARCHAR(30) NOT NULL,				
lastName VARCHAR(30) NOT NULL,				
dept VARCHAR(10) NOT NULL,				
PRIMARY KEY (id)			
);		

For any form of database implementation or administration, there is need for a select group of administrators with ‘ALL’ privileges- these administrators with ALL privileges may GRANT or create additional users to access and utilize the database along with all other access to manipulate or add data into the database.

An administrative account can be created during the initial install and additional users can be added later on.

After the initial install, if there is need for additional administrative accounts, administrative personnel may create an additional administrative account by using the following SQL (Structured Query Language) statement:

CREATE USER 'Ash'@'localhost' IDENTIFIED BY 'pass';			
GRANT ALL PRIVILEGES ON *.* TO 'Ash'@'localhost'			
WITH GRANT OPTION;		

This statement will create an account named ‘Ash’ on the host ‘localhost’ and give access to all databases and all tables with the option to grant new users privileges or revoke existing privileges from existing users.

Administrative personnel also have the opportunity to add additional users with lesser privileges using SQL statements. For example: staff- such as secretaries and other employees that need to access data within certain tables within the database and perform certain tasks; each and every user account can have unique privileges.

Creating a user account is a very simple process, but the complex component requires a plan- which users can access what tables and what actions should these users beable to invoke on them?

In order to create a new user account, administrative personnel may use the following SQL statement:

CREATE USER 'Awesome'@'localhost' IDENTIFIED BY 'pass';		

The above statement would create a user named Awesome for the host- ‘localhost’. No privileges have been added for this account yet, but they can simply be implemented by the appending the following SQL statement to the end of the previous statement:

GRANT SELECT ON shop.clients TO 'Awesome'@'localhost';		

This statement would give the user Awesome the opportunity to query data from the shop table within the clients database only- by using the SELECT statement (i.e. USE clients; SELECT * FROM shop;).

By granting this level of privilege to the user Awesome, the Administrator can also give the user ‘ALL’ privileges to anything within the resources database by using this statement:

GRANT ALL ON resources.* TO 'Awesome'@'localhost';		

The user Awesome would then be able to successfully use the following SQL statement:

USE resources; SELECT * FROM tutorials;		

For personnel such as a secretary who may need to query, insert or update data on just the clients table in the shop database, we could create an account by using the following SQL syntax;

CREATE USER 'secretary'@'localhost' IDENTIFIED BY 'pass';			
GRANT SELECT, INSERT, UPDATE ON shop.clients TO 'secretary'@'localhost'			
WITH MAX_QUERIES_PER_HOUR 50			
MAX_UPDATES_PER_HOUR 50			
MAX_CONNECTIONS_PER_HOUR 5			
MAX_USER_CONNECTIONS 1;		

This will also restrict the secretary to a maximum of: 50 queries per hour, 50 updates per hour, 5 connections per hour and 1 connection at a time.

For other staff that may need to refer to all tables stored in the shop database, but not modify the data in anyway, an Administrator could create an account by using the following SQL syntax:

CREATE USER 'staff'@'localhost' IDENTIFIED BY 'pass';			
GRANT SELECT ON shop.* TO 'staff'@'localhost';		

If a user has already been created, but now is required to have administrative privileges, they can be updated by an administrative account with the GRANT privilege using the following SQL statement:

REVOKE ALL PRIVILEGES ON *.* FROM 'user'@'host';			
GRANT ALL ON *.* TO 'user'@'host';			
FLUSH PRIVILEGES;		

A user may also have some of their privileges revoked, by using the following SQL statement:

REVOKE ALL PRIVILEGES ON *.* FROM 'user'@'host';			
GRANT SELECT ON shop.clients TO 'user'@'host';			
FLUSH PRIVILEGES;		

This will leave the user ‘user’ with just the privilege to only SELECT data in the clients table within the clients database.

I hope you found this resource useful!