how to set privileges in mysql

Introducing the MySQL Privilege

The MySQL privilege system is always on. The first time you try to connect, and for
each subsequent action, MySQL checks the following three things:
Where you are accessing from (your host)
. Who you say you are (your username and password)
. What you’re allowed to do (your command privileges)
All this information is stored in the database called mysql, which is automatically
created when MySQL is installed. There are several privilege-related tables in the
mysql database, such as the following:
. columns_priv—Defines user privileges for specific fields within a table
. db—Defines the permissions for all databases on the server
. host—Defines the acceptable hosts that can connect to a specific database
. procs_priv—Defines user privileges for stored routines
. tables_priv—Defines user privileges for specific tables within a database
. user—Defines the command privileges for a specific user
These tables will become more important to you later in this chapter as you add a
few users to MySQL. For now, just remember that these tables exist and must have
relevant data in them for users to complete actions.
Understanding the Two-Step Authentication
As you’ve learned, MySQL checks three things during the authentication process.
The actions associated with these three things are performed in two steps:
1. MySQL looks at the host you are connecting from and the username and password
pair you are using. If your host is allowed to connect, your password is
correct for your username, and the username matches one assigned to the
host, MySQL moves to the second step.
2. For whichever SQL command you are attempting to use, MySQL verifies that
your user has permissions to perform that action for that database, table, and
If step 1 fails, you see an error about it and you cannot continue on to step 2. For
example, suppose that you are connecting to MySQL with a username of joe and a
password of abc123 and you want to access a database called myDB. You will receive
an error message if any of those connection variables is incorrect for any of the following
. Your password is incorrect.
. Username joe doesn’t exist.
. User joe can’t connect from localhost.
. User joe can connect from localhost but cannot use the myDB database.
You may see an error like the following:
# mysql -h localhost -u joe -pabc123 test
Error 1045: Access denied for user: ‘joe@localhost’ (Using password: YES)
If user joe with a password of abc123 is allowed to connect from localhost to the
myDB database, MySQL checks the actions that joe can perform in step 2 of the
process. For our purposes, suppose that joe is allowed to select data but is not allowed
to insert data. The sequence of events and errors would look like the following:
# mysql -h localhost -u joe -pabc123 test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 12 to server version: 5.5.21-log
Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the buffer.
mysql> SELECT * FROM your_table;
mysql> INSERT INTO test_table VALUES (‘’, ‘my text’);
Error 1044: Access denied for user: ‘joe@localhost’ (Using password: YES)
Action-based permissions are common in applications with several levels of administration.
For example, if you have created an application containing personal
financial data, you might grant only SELECT privileges to entry-level staff members,
but INSERT and DELETE privileges to executive-level staff with security clearances.
In most cases when you are accessing MySQL through an Internet service provider,
you have only one user and one database available to you. By default, that user has
access to all tables in that database and is allowed to perform all commands. In this
case, the responsibility is yours as the developer to create a secure application
through your programming.
However, if you are the administrator of your own server, or if your Internet service
provider allows you to add as many databases and users as you want and to modify
the access privileges of your users, you can do so as described in the following subsections
Adding Users to MySQL
Administering your server through a third-party application might afford you a simple
method for adding users by using a wizard-like process or a graphical interface.
However, adding users through the MySQL monitor is not difficult, especially if you
understand the security checkpoints used by MySQL, which you just learned.
The simplest method for adding new users is the GRANT command. By connecting to
MySQL as the root user, you can issue one command to set up a new user. The
other method is to issue INSERT statements into all the relevant tables in the mysql
database, which requires you to know all the fields in the tables used to store permissions.
This method works just as well but is more complicated than the simple
GRANT command. The simple syntax of the GRANT command is shown here:
GRANT privileges
ON databasename.tablename
TO username@host
IDENTIFIED BY “password”;
Following are some of the common privileges you can grant. (For a complete list, see
the GRANT entry in the MySQL Manual at
. ALL—Gives the user all common privileges.
. ALTER—User can alter (modify) tables, columns, and indexes.
. CREATE—User can create databases and tables.
. DELETE—User can delete records from tables.
. DROP—User can drop (delete) tables and databases.
. FILE—User can read and write files; this privilege is used to import or dump
. INDEX—User can add or delete indexes.
. INSERT—User can add records to tables.
PROCESS—User can view and stop system processes; only trusted users should
be able to do this.
. RELOAD—User can issue FLUSH statements; only trusted users should be able to
do this.
. SELECT—User can select records from tables.
. SHUTDOWN—User can shut down the MySQL server; only trusted users should be
able to do this.
. UPDATE—User can update (modify) records in tables.