Open Source

Administer database permissions with GRANT and REVOKE

A database connected to the Internet without any kind of security mechanism is like a nice house with a sign in the front yard saying, “No one home. Door’s unlocked. Help yourself.” Luckily, most database programs are able to make use of the standard SQL GRANT statement to secure data access.

In this Daily Drill Down, I will use the open source databases MySQL and PostgreSQL to show how the GRANT and REVOKE statements work in these two applications.

GRANT giveth and REVOKE taketh away
GRANT is used to provide additional privileges to a user or group of users, while REVOKE is used to take privileges away from them.

The MySQL GRANT and REVOKE statements have specific syntaxes that must be followed. There are a number of options that can be specified in the statements. I will go through each one here and explain all the associated options, and I'll follow up with a few examples of how to use this syntax.

MySQL command-line basics
For an introduction to the MySQL command line, read Jack Wallen, Jr.'s article “An introduction to the MySQL command line.”

The GRANT statement
The basic syntax of the GRANT statement is shown in Listing A.

You can see from the syntax and the information in Table A that there are a number of options you can use to secure access to a MySQL database server.
Table A
Privilege name
ALL PRIVILEGES This does exactly what it sounds like: It grants all possible privileges to the named user.
ALTER Use this to grant the alter privilege, which allows the user to make changes to the table or the database.
CREATE This allows the named user to create new objects.
DELETE This allows the named user to delete objects.
DROP Use this privilege to allow an object, such as a table, to be dropped.
FILE This privilege is potentially dangerous, because it allows users to write files anywhere in the file system within the confines of the user associated with the MySQL process. If you are running MySQL as the root user (which is a bad idea), this would give someone free reign over the entire file system.
INDEX This allows the user to create or drop indexes.
INSERT This allows the user to insert data into a table.
PROCESS Use this privilege to allow the user to view and manage database system processes.
REFERENCES Although not yet implemented in MySQL, you can assign the privileges.
RELOAD This allows the user to force a reread of the grant tables, to flush tables, and to close log files.
SELECT This gives the user access to perform queries against a database.
SHUTDOWN This allows a user to shut down the MySQL server.
UPDATE This is used to update databases, which can modify data en masse.
USAGE This means the same thing as no privilege. It's useful when you want to create a user that has no privileges
The priv_types (privilege types) for MySQL database administration

An entry in the Column list field associates the priv_type with it. For example, to allow the INSERT priv_type for a specific field in the table, specify that column name with the options listed below.

Option: ON
This option specifies what table(s) will be operated on. The choices are shown in Table B.

Table B
Option Description
table name The operation is only performed on the table listed.
* The operation is performed on the currently selected database. If no database is selected, the operation is performed at a global level.
*.* The operation is performed on a global level, meaning that all databases are affected.
db_name.* The operation is performed on the database listed.

Option: TO
Specify the user name that should be granted the permissions being assigned with this option.

With this option, the default password (which is password) is replaced by a user-specified password.

The REQUIRE keyword can use any of the options from Table C.

Table C
Option Description
SSL Forces the server to allow only SSL encrypted connections
X509 Requires the client to have a valid certificate with no restrictions (If you want, you can use all of the options in this table in combination with this option.)
CIPHER Restricts connections to a specific cipher method to prevent old, weak, or short key lengths
ISSUER Requires that the client present a valid certificate from a specific issuer
SUBJECT Requires that the client present a valid certificate with a specific subject

Option: WITH
The option WITH can use one or both of the parameters found in Table D.

Table D
Option Description
GRANT OPTION Grants user1 the permissions of user2
MAX_QUERIES_PER_HOUR = # Limits the number of queries that the user named is allowed to perform in a given hour; when set to zero, an unlimited number of queries is allowed

The REVOKE statement
The REVOKE command in MySQL is very similar to GRANT, but it's less complicated to use because it has far fewer options.

All of the parameters listed in Listing B (the REVOKE command syntax) are used identically to their GRANT counterparts, but instead of giving access, you are taking it away.

For these examples, I created a table in a MySQL database named test2—this was done from within the MySQL command prompt—with the following statements:
create table t_salary(
    name varchar(20),
    salary int);

The above statements state that there are two columns in the t_salary table. The first is named name and has a variable character length of 20. The second is an integer value corresponding to an employee’s salary. Obviously, salary information is somewhat sensitive and must be properly secured.

Into this table, I have placed the data from Listing C.

To insert the data into the t_salary table within the test2 database, use the commands shown in Listing D from within the MySQL command line.

I also created a test user named scott for these examples. I'll give scott permissions to view all records in the table by typing grant select (name, salary) all on test2 to scott identified by ‘scott’; at the MySQL command line. In addition to giving scott the use of the select statement, this also sets up a password (which is also scott) for this user that he will need when he attempts to use the MySQL command line. When I try to connect and use the database (use is the term for switching to the currently active database at the MySQL command line) as the user scott, I am able to switch to it and select all of the records from the t_salary table.

However, I later decided that this user shouldn't be allowed to read the salary information under any circumstances. To prevent the user scott from being able to view this information, I typed revoke select(salary) on t_salary from scott; at the MySQL command line. Then, when I attempted to perform a select * from t_salary as scott, I got the message shown in Listing E. Now scott is limited to selecting individual columns that he actually has access to.

PostgreSQL, while maintaining the use of a much simpler form of the GRANT statement, uses a slightly different approach to user security. Unlike in MySQL, in PostgreSQL, you must first create a user that has certain permissions before you are able to grant other privileges to that user. PostgreSQL supports this by making use of the createuser command in addition to using GRANT.

The createuser syntax
To create a user in PostgreSQL, use the following syntax:
createuser username
  [ SYSID uid ]
  [ PASSWORD 'password' ] ]
  [ IN GROUP     groupname [, ...] ]
[ VALID UNTIL  'abstime' ]

Notice the embedded options? Look closely to see that the [ WITH has its closing bracket after groupname [, …]. This embedding ensures that everything from SYSID to groupname configures user information, whereas the last section, VALID UNTIL, forces an expiration date for the user password.

Table E lists all of the options available for PostgreSQL user creation.

Table E
Option Description
username This is the username that I want to assign to the new database user. This doesn't have to match a system user.
SYSID This option allows me to specify the user ID if I am creating a user that I want to match an existing system user.
PASSWORD To specify a user's password, use this option.
CREATEDB | NOCREATEDB This option specifies whether or not I want to allow the user to create new databases.
CREATEUSER | NOCREATEUSER This specifies whether or not I want to allow this user to create new users.
IN GROUP Use this option to assign a user to a group or groups. Groups can be created using the CREATE GROUP command.
VALID UNTIL To specify that the user account’s password should be deactivated after the date specified in this clause, use this option.

For the examples, I have created a single user named scott with the following command in PostgreSQL:
createuser scott

GRANT syntax
The basic PostgreSQL GRANT syntax, shown in Listing F, uses a much simpler approach than in MySQL.

Only six privileges are used in PostgreSQL, compared to the 15 used in MySQL. They are as follows:
  • ·        All gives the user all privileges in one step.
  • ·        Delete allows the user to delete rows from a specific table.
  • ·        Insert allows the user to insert data into any field in the table.
  • ·        Rule defines rules on a table or view (see below).
  • ·        Select runs queries against any column in the table.
  • ·        Update modifies the data in a table.

Notice that in the GRANT statement, the word object is used. To use the GRANT syntax properly, replace the object parameter with the name of a table, view, or sequence.

The last parameter in the GRANT statement specifies to whom the privileges are being granted. This can be one of the following options:
  • ·        Public gives the privilege to everyone.
  • ·        Group {groupname} gives the privilege to everyone who is a member of the named group.
  • ·        Username gives the privilege to a specific user name.

As in the MySQL example, I created a database named test2 (with the command createdb test2) and added the same table and data to it. The only difference is that the table is named tsalary rather than t_salary, because PostreSQL can sometimes choke on underscore characters in table names. To add the table, first open the PostgreSQL command prompt with the command psql test2. The commands will seem very similar to MySQL. Create the tsalary table with the command create table salary (name varchar (20), salary int); and enter the proper data with the same commands used for MySQL (only substituting t_salary with tsalary). An example of entering this information is shown in Listing G.

Initially, scott doesn't have access to the tables in the database. As proof, when I attempt to view all of the records in the table as user scott using select * from tsalary;, I receive a message indicating that permission has been denied. For this example, I will give this user the ability to view all of the contents of the table. This is easy to do. Simply issue the grant select on tsalary to scott; command at the PSQL command line. Then, when I log in as scott and attempt to select all of the records, PostgreSQL gives them to me.

Unfortunately, the security system in PostgreSQL is not quite as flexible as that of MySQL; allowing or disallowing access to specific columns within tables is not directly supported. To do this, you need to create a view, which can be thought of as a sub-table, and then GRANT the right to the view rather than directly to the table.

One thing that I like about PostgreSQL is its ability to very quickly show me exactly who has access to a table. Once I start PostgreSQL as a user with appropriate rights, I can issue a \z at the command line to see who has rights to what. For example, to see who has rights to the tables within the test2 database, I would issue the \z and receive the following output:
test2=# \z
 Access privileges for database "test2"
  Table  |      Access privileges
 tsalary | {=,postgres=arwdRxt,scott=r}
(1 row)

This output shows me that the user scott has both read and write access to the tsalary table.

Who's on first?
MySQL and PostgreSQL are two applications with the same purpose. But they offer two very different approaches to accomplishing the same goals, and the same possible permissions issues. Although PostgreSQL has a much more simplistic statement syntax, it is less flexible. MySQL, on the other hand, has a much more difficult syntax but is far more flexible. Both, however, have the ability to tighten up a database with the GRANT and REVOKE statements.

When deciding which database tool to use for a job, I ask myself: Does the job require more flexibility in its database than not? If so, the obvious choice is MySQL. If flexibility is not a key issue and simplicity is, I opt for PostgreSQL. But no matter which database I choose, I can depend on either for robust, stable, and reliable database application.

Editor's Picks