mysql beginners tutorial

MySQL Database Administration 101

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:

For more information on utilizing a MySQL database, visit http://www.mysql.com/ and http://www.phpmyadmin.net/.

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:

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:

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:

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:

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

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;

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:

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:

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

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!

Published by

Ashley Menhennett

Ashley is a web designer + developer, programmer and technical author from Australia.Ashley holds certification in IT + Web Based Technologies and specializes in modern mobile and responsive web design, working with bleeding-edge web based technologies—including HTML5 and CSS3.

Share this article