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 までご連絡ください。
人気のチュートリアル
詳細>
最新のダウンロード
詳細>
ウェブエフェクト
公式サイト
サイト素材
フロントエンドテンプレート