MySQL Database Administration 101_MySQL

PHP中文网
發布: 2017-03-23 09:27:42
原創
1059 人瀏覽過

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!

來源:php.cn
本網站聲明
本文內容由網友自願投稿,版權歸原作者所有。本站不承擔相應的法律責任。如發現涉嫌抄襲或侵權的內容,請聯絡admin@php.cn
熱門教學
更多>
最新下載
更多>
網站特效
網站源碼
網站素材
前端模板