MySQL
  Home arrow MySQL arrow MySQL User Account Management
Dev Shed Forums  
Administration  
AJAX  
Apache  
BrainDump  
DHTML  
Flash  
Java  
JavaScript  
Multimedia  
MySQL  
Oracle  
Perl  
PHP  
Practices  
Python  
Reviews  
Security  
Smartphone Development  
Style-Sheets  
Web Services  
XML  
Zend  
Zope  
Mobile Linux  
App Generation ROI  
IBM® developerWorks  
Forums Sitemap  
E-Commerce Hosting  
Linux Web Hosting  
Managed Hosting  
Small Business Hosting  
VPS Hosting  
Weekly Newsletter

 
Developer Updates  
Free Website Content 
 RSS  Articles
 RSS  Forums
 RSS  All Feeds
Write For Us Get Paid  
Request Media Kit
Contact Us  
Site Map  
Privacy Policy  
Support  
 USERNAME
 
 PASSWORD
 
 
  >>> SIGN UP!  
  Lost Password? 
MYSQL

MySQL User Account Management
By: Sams Publishing
  • Search For More Articles!
  • Disclaimer
  • Author Terms
  • Rating: starstarstarstarstar / 19
    2006-07-20


    Table of Contents:
  • MySQL User Account Management
  • 12.2.2 The Grant Tables
  • 12.2.3 Granting and Revoking Privileges
  • 12.2.3.2 The REVOKE Statement
  • 12.2.4 Changing Account Passwords
  • 12.2.5 Specifying Resource Limits

  • Rate this Article: Poor Best 
      ADD THIS ARTICLE TO:
      error-file:tidyout.log Del.ici.ous error-file:tidyout.log Digg
      error-file:tidyout.log Blink error-file:tidyout.log Simpy
      error-file:tidyout.log Google error-file:tidyout.log Spurl
      error-file:tidyout.log Y! MyWeb error-file:tidyout.log Furl
    Email Me Similar Content When Posted
    Add Developer Shed Article Feed To Your Site
    Email Article To Friend
    Print Version Of Article
    PDF Version Of Article

     
     
    ADVERTISEMENT


    MySQL User Account Management
    ( Page 1 of 6 )

    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).

    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
     

       

    MYSQL ARTICLES

    - Null and Empty Strings
    - MySQL Server Tuning Tips and Tricks
    - MySQL Query Optimizations and Schema Design
    - MySQL Benchmarking Tools and Utilities
    - MySQL Benchmarking Concepts and Strategies
    - Take Some Load off MySQL with MemCached
    - MySQL Table Prefix Changer Tool in PHP
    - Using the SIGNAL Statement for Error Handling
    - Error Handling Examples
    - Error Handling
    - Completing a Search Engine with MySQL and PH...
    - Paginating Result Sets for a Search Engine B...
    - Building a Search Engine with MySQL and PHP 5
    - Using Boolean Operators for Full Text and Bo...
    - PHP, MySQL and the PEAR Database





    © 2003-2009 by Developer Shed. All rights reserved. DS Cluster 5 hosted by Hostway
    Stay green...Green IT