Home arrow MySQL arrow MySQL User Account Management

MySQL User Account Management

Last week, we began our discussion of MySQL database security. This week, we continue that discussion with user account management. The second of several parts, this article is excerpted from chapter 12 of the MySQL 5.0 Certification Guide, written by Paul Dubois et al. (Sams, 2005; ISBN: 0672328127).

TABLE OF CONTENTS:
  1. MySQL User Account Management
  2. 12.2.2 The Grant Tables
  3. 12.2.3 Granting and Revoking Privileges
  4. 12.2.3.2 The REVOKE Statement
  5. 12.2.4 Changing Account Passwords
  6. 12.2.5 Specifying Resource Limits
By: Sams Publishing
Rating: starstarstarstarstar / 20
July 20, 2006

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

12.2 User Account Management

The MySQL access control system enables you to create MySQL accounts and define what each account can do. Several types of privileges can be assigned to an account. They should be granted according to how the account is to be used. Some examples:

  • An account that needs only read access to a database can be given just the SELECT privilege.

  • An account used to modify data can be given the DELETE, INSERT, and UPDATE privileges.

  • Administrative accounts can be given the PROCESS or SUPER privileges for viewing client process activity or killing connections, or the SHUTDOWN privilege for stopping the server.

The MySQL server bases access control on the contents of the grant tables in the mysql database. These tables define MySQL accounts and the privileges they hold. To manage their contents, use the GRANT and REVOKE statements. These statements provide an interface to the grant tables that enables you to specify privileges without having to determine how to modify the tables directly. When you use GRANT and REVOKE to perform a privilege operation, the MySQL server determines what changes to the grant tables are needed and makes the modifications for you.

This section describes the structure and contents of the grant tables and how you set up user accounts using GRANT and REVOKE. Section 12.3, "Client Access Control," describes how the server uses the grant tables to check access privileges when clients connect.

12.2.1 Types of Privileges That MySQL Supports

You can grant several types of privileges to a MySQL account, and you can grant privileges at different levels (globally or just for particular databases, tables, or columns). For example, you can allow a user to select from any table in any database by granting the SELECT privilege at the global level. Or you might grant an account no global privileges, but give it complete control over a specific database. That allows the account to create the database and tables in it, select from the tables, and add new records, delete them, or update them.

The privileges that MySQL supports are shown in the following lists. The first names the administrative privileges and the second names the database-access privileges.

Administrative Privileges:

Privilege

Operations Allowed by Privilege

CREATE TEMPORARY TABLES

Use TEMPORARY with CREATE TABLE

FILE

Use statements that read and write files on the server host

GRANT OPTION

Grant privileges to other accounts

LOCK TABLES

Explicitly lock tables with LOCK TABLES

PROCESS

View process (thread) activity

RELOAD

Use FLUSH and RESET

REPLICATION CLIENT

Ask server for information about replication hosts

REPLICATION SLAVE

Act as a replication slave

SHOW DATABASES

See all databases with SHOW DATABASES

SHUTDOWN

Shut down the server

SUPER

Miscellaneous administrative operations


Database-Access Privileges:

Privilege

Operations Allowed by Privilege

ALTER

Modify tables with ALTER TABLE

CREATE

Create databases and tables

DELETE

Remove rows from tables

DROP

Drop databases and tables

INDEX

Create and drop indexes

INSERT

Add rows to tables

SELECT

Select records from tables

UPDATE

Modify records in tables


Some privileges not shown in these lists can be assigned to accounts but currently are unused. EXECUTE is reserved for future versions of MySQL, when stored procedures are implemented. REFERENCES may be implemented in relation to foreign key support at some point.

There are also some special privilege specifiers:

  • ALL and ALL PRIVILEGES are shorthand for all privileges except GRANT OPTION. They're shorthand for granting all privileges except the ability to give privileges to other accounts.

  • USAGE means no privileges other than being allowed to connect to the server. A record is created for the account in the user table. This causes the account to exist, and it can then be used to access the server for limited purposes such as issuing SHOW VARIABLES or SHOW STATUS statements. The account cannot be used to access databases or tables (although you could grant such privileges to the account at a later time).

Privileges can exist at different levels:

  • Any privilege can be granted globally. An account that possesses a global privilege can exercise it at any time. Global privileges are therefore quite powerful and are normally granted only to administrative accounts. For example, a global DELETE privilege allows the account to remove records from any table in any database.

  • Some privileges can be granted for specific databases: ALTER, CREATE, CREATE TEMPORARY TABLES, DELETE, DROP, GRANT OPTION, INDEX, INSERT, LOCK TABLES, SELECT, and UPDATE. A database-specific privilege applies to all tables in the database.

  • Some privileges can be granted for specific tables: ALTER, CREATE, DELETE, DROP, GRANT OPTION, INDEX, INSERT, SELECT, and UPDATE. A table-specific privilege applies to all columns in the table.

  • Some privileges can be granted for specific table columns: INSERT, SELECT, and UPDATE.



 
 
>>> More MySQL Articles          >>> More By Sams Publishing
 

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort
   

MYSQL ARTICLES

- Oracle Unveils MySQL 5.6
- MySQL Vulnerabilities Threaten Databases
- MySQL Cloud Options Expand with Google Cloud...
- MySQL 5.6 Prepped to Handle Demanding Web Use
- ScaleBase Service Virtualizes MySQL Databases
- Oracle Unveils MySQL Conversion Tools
- Akiban Opens Database Software for MySQL Use...
- Oracle Fixes MySQL Bug
- MySQL Databases Vulnerable to Password Hack
- MySQL: Overview of the ALTER TABLE Statement
- MySQL: How to Use the GRANT Statement
- MySQL: Creating, Listing, and Removing Datab...
- MySQL: Create, Show, and Describe Database T...
- MySQL Data and Table Types
- McAfee Releases Audit Plugin for MySQL Users

Developer Shed Affiliates

 


Dev Shed Tutorial Topics: