Luckily, MySQL provides you with a handful of ways to control who has access to your systems and to what resources exactly.

User Administration

In order to help control the user accounts in your database, MySQL provides a couple of ways to maintain user accounts. Whether it's an account for a co-developer, a tech-savvy client, or a web-based front end to the system, each user needs to have access specifically granted for the resources they require.

 One of the first things you need to do when configuring a database for the real world is set up any  accounts that might be needed by any users, or the system itself. In order to set up specialized access  for a user in your database, you need to know about the different levels for which MySQL can control  access. MySQL 4.1 can differentiate four different access levels in regards to permissions: the global  level, database level, table level, and column level.

Global-level privileges are the most powerful and apply to all the databases running on an instance of  MySQL. Normally such privileges are reserved for administrator accounts, as it is a rare occasion when  an application or end user will require access to all aspects of the database server's operation.
  Database-level privileges apply just like they sound -- at the database level. Database-level access is  often used by the administrator of a specific database or application, and has the ability to give the user  complete control over one specific database as specified.

  Table-level privileges apply on a per-table basis. You can create or remove access for a user on a specific  table or set of tables. This level of access control is often used when configuring a database to be accessed directly by an application -- the application only needs certain kinds of access on each table, and no more.
  Column-level privileges are the finest level of control that MySQL provides. As you might expect, it controls the access to a specific column or columns within a given table.

Using GRANT

  Now that you understand the varying scope of the MySQL access system, it's time to look at how to  actually control the privileges.

  To create and add privileges to user accounts, MySQL gives you the GRANT command. The GRANT command is relatively simple to use, as long as you know what access you're granting, and to whom. The  GRANT command has the following generalized syntax: 

      GRANT [privileges] ON [resource] TO [identity]

  Let's take a second to dissect GRANT. The first part of the command, GRANT [privileges] is where you  specify what privileges to allow on the resource (specified later). Some of the more common privileges  you can use are shown in the following table.

    Privilege                                   Provides

    ALL [PRIVILEGES]                    Sets all simple privileges except GRANT OPTION

    ALTER                                 Allows use of ALTER TABLE

    CREATE                               Allows use of CREATE TABLE

    DELETE                                Allows use of DELETE

    DROP                                  Allows use of DROP TABLE

    INDEX                                 Allows use of CREATE INDEX and DROP INDEX

    INSERT                               Allows use of INSERT

    SELECT                              Allows use of SELECT

 

   Privilege                                       Provides
 

  UPDATE                          Allows use of UPDATE

   USAGE                           Synonym for "no privileges"; user can log in but no more
 

The second part of the command ON [resource] is where you specify what database level and item you are making available. To grant access at the global level, you specify *.* as the resource, like so:

    GRANT [privileges] ON *.* TO [identity]...

When you grant access at the database level, you specify the name of the database, followed by .*. For
example, if you wanted to grant access to the testdb database, you would use something similar to the
following:

    GRANT [privileges] ON testdb.* TO [identity]...

To create permissions for a specific table within a database, you provide the name of the database, followed by the table name (separated by a period). If you were trying to specify access for the testtable
table in the testdb database, you might use something like this:

    GRANT [privileges] ON testdb.testtable TO [identity]...

Granting column-level permissions is slightly different than the logical progression down the previous
three levels. When giving column-level permissions, you must specify a column list in parentheses after each privilege in the list, such as the following:

    GRANT SELECT (column1), INSERT (column1) ON testdb.testtable TO [identity]...  

When you are specifying column-level privileges, you must use the full table-level syntax after ON, or    MySQL will return an error.

The last key part of the GRANT command, TO [identity]..., is where you specify who the access permissions will be granted to, and any optional requirements the user must meet to be granted this access.

The minimum you must provide to satisfy the TO ... section of GRANT is a username/host combination.
In MySQL, each distinct user must have at least basic usage permissions set for their username and thelocalhost. For example:

    GRANT USAGE ON *.* TO testuser@localhost

Here the user `testuser' has been granted basic usage access globally, which is equivalent to no accessat all, but the user is now known to the system. Once a localhost account is created, you can create additional access statements for the same username at any remote host you specify:

    GRANT SELECT ON testdb.* TO This e-mail address is being protected from spam bots, you need JavaScript enabled to view it

It should be noted MySQL will not complain if you try and create a remote access account for a user without a localhost entry first. Your applications, however, probably will -- they might not be able to connect remotely, and it's a common source of hair-pulling when first getting used to the MySQL access
system.