Advertisment

Access Privileges in MySQL

author-image
PANKAJ
New Update

The tables of 'mysql' database called grant tables are used to configure all aspects of user access privileges. There are two stages of access decisions. In the first stage, it is decided whether the incoming connection (client) is allowed to connect to mysqld-the database server. In the second stage it is decided, once connected, what kind of SQL queries the client can issue. An easy way to set up these access privileges is to use the 'Grant' and 'Revoke statements of MySQL. Grant is used to grant privileges and Revoke is used to take away privileges. We'll now see a few case studies and set up the access privileges accordingly.

Advertisment

Database level access

Take the scenario of a Web space provider, who creates a new database for each new customer. The customer may be using a leased (usually static IP) or dial-up (usually dynamic IP) connection. It is known in the first case that the customer will be accessing the database from a fixed location-IP address or FQDN (Fully Qualified Domain Name). This is not so in the latter case. Suppose the name of the database is 'database1' and the login name (any name decided by the web space provider) is 'user1'. First the database is created as:

mysqladmin create database1 -p
Advertisment

When prompted, enter the password you had set for the 'root' account (see Online Databases with MySQL on page 119, PCQuest October 2001). Let's assume a fixed IP address say 202.54.81.9 and a fixed FQDN as customer.foo.com. Now connect to the database server (running on the same machine) using the mysql client as:

mysql -u root -p

And then issue the following:

Advertisment
grant all on database1.* to user1@customer.foo.com identified by “secret”;



revoke all on database1.* from user1@customer.foo.com;

In the above line the words in bold are keywords. That is, they will be always present in a 'Grant' statement. 'All' means to grant all privileges for the database 'database1'. These privileges include creating, altering, removing tables and inserting, updating, deleting records in the tables. Apart from creating and dropping table, user1 can also create and drop the database specified in the 'Grant' command-database1. Instead of assigning all the privileges we can give some privileges (see below). Database1.* means, these privileges apply to all the tables in

the database. User1@customer.foo.com takes the form of:

@FQDN 

Advertisment

'Secret' is the password which must be supplied to connect to the database. Note that you type the password in plain, visible text. So, make sure no one is around. Though the password within the database is stored in encrypted form. In case of IP address, the grant command would be:

grant all on database1.* to user1@202.54.81.9 identified by “secret”;



revoke all on database1.* from user1@202.54.81.9;

As per the above 'Grant' commands, we can frame the syntax of the 'Grant' command will now take the form:

Advertisment
grant on

.

to @

identified by ;

and that of 'Revoke' takes the form :

revoke on .

from @;
Advertisment

For a dial-up connection or dynamic IP address, the grant command would be:

grant all on database1.* to user1@”%” identified by “secret”;



revoke all on database1.* from user1@”%”;

Here the % sign, enclosed within double quotes, matches to any IP or FQDN. Besides specifying full IP address and FQDN, we can use the % character for a range. For example:

%.foo.com (matches all hosts in the foo.com domain)



192.168.1.% (matches all machines in a private network 192.168.1.0)

Advertisment

You can even use the CIDR (Classless Inter-Domain Routing) notation like 192.168.1.0/14.



Now the customer - if using the mysql command line client - must specify the hostname or IP address of the machine (to connect to) as follows:

mysql -h provider.blah.com -u user1 -p

By default, the mysql client tries to connect to the MySQL server running on the same machine. Thus, using '-h' option you instruct it to connect to another machine, that is, the machine of you service provider.

Table level access

We have been granting access to a whole database. What if we want to give access to users on particular tables within the database. This scenario can be depicted by a company's database with a table each for a department like sales, transport, inventory, and accounts. Suppose the name of the database is 'company' and the name of the tables correspond to the name of the department. Now to allow only employees from inventory department with login name 'invent_depart' and password as 'secret1' to access the table 'inventory' in 'company' database, the grant statement would be:

grant all on company.inventory to

invent_depart@”192.168.1.%” identified by “secret1”;



revoke all on company.inventory from invent_depart@”192.168.1.%”;

We assume that the company is on the 192.168.1.0 network. As said before, instead of assigning all the privileges we can allocate selective privileges. For example, the sales department would like to only see the status of the inventory and update it as per the sales. So a sales employee would only require select and update privileges, which can be granted as follows:

grant select,update on company.inventory to

sales_depart@”192.168.1.%” identified by “secret2”;



revoke select,update on company.inventory from sales_depart@”192.168.1.%”;

Note the separation of the two privileges (select and update) by a comma (,).

Column level access 

The final level of access provided by MySQL is column level access. For a table called 'hr' managed by the HR department, a person from the finance department would like to only view (select) the employee id and salary. So using the following grant statement we give 'select' access only to the 'emp_id', and 'salary' columns in the table 'HR' of the 'company' database.

grant select (emp_id,salary) on company.hr to

finance_depart@”192.168.1.%” identified by “secret3”;



revoke select (emp_id,salary) on company.hr from finance_depart@”192.168.1.%”;

With column level access, we modify the syntax of the 'Grant' and 'Revoke' statements given above to:

grant on .

to @ identified by ;



revoke on .
from @;

But note that the

is optional and the 'Grant' and 'Revoke' statements can work without them.

Some glitches again

1. Though the 'Revoke' statement removes access privileges, it does not remove entries of login names from the 'user' table of the 'mysql' database. Thus one can still connect to the database server using those login names. So, in case of our examples, though you might have issued 'Revoke' statements for each 'Grant' statement, one can still connect to the server using the login names-user1, invent_depart, sales_depart, and finance_depart. These logins are considered as anonymous logins by MySQL and thus one can access the databases with names 'test' or one whose name start with 'test_' (as said in the previous article). If you don't want this to happen, connect as user root using the mysql client and then issue the following at the mysql prompt

use mysql;



delete from user where user='';

Issue the second command for all the login names, that is, in our case substitute with user1, invent_depart, sales_depart, finance_ depart

2. In case of the company's example we have specified that the users with login names invent_depart, sales_depart and finance_depart can connect to the database server from any machine in the 192.168.1.0 network. Suppose if one wants to connect to the database server from the same machine that is running it. Then issuing:

mysql —u invent_depart —p

will not work even if the machine is on the 192.168.1.0 network. This is because when you issue the 'mysql' command without the '—h' option, it is assumed that the connection is requested from local host (127.0.0.1) and we did not specify this network address in the 'Grant' statement. Suppose the IP address of this machine is 192.168.1.124, the following command would work:

mysql —h 192.168.1.15 —u invent_depart —p

In this case, the MySQL server would extract the IP address of the connecting machine instead of assuming it to be 'local host'.

There are a number of graphical clients also for MySQL. You will find them on the December 2001 PCQuest CD and we will see how to set them up next time.

Shekhar Govindarajan

Related Articles

Backing up Your MySQL Database

Linux Hands On : Query MySQL with C#

Advertisment