Книга: Fedora™ Unleashed, 2008 edition

Granting and Revoking Privileges in MySQL

Granting and Revoking Privileges in MySQL

You probably want to grant yourself some privileges, and eventually you will probably want to grant privileges to other users. Privileges, also known as rights, are granted and revoked on four levels:

? Global level — These rights allow access to any database on a server.

? Database level — These rights allow access to all tables in a database.

? Table level — These rights allow access to all columns within a table in a database.

? Column level — These rights allow access to a single column within a database's table.

To add a user account, you connect to the database by typing mysql -u root -p and pressing Enter. You are then connected as the root user and prompted for a password. (You did set a password for the root user as instructed in the last section, right?) After you enter the root password, you are placed at the MySQL command prompt.

To grant privileges to a user, you use the GRANT statement, which has the following syntax:

grant what_to_grant ON where_to_grant TO user_name IDENTIFIED BY 'password';

The first option, what_to_grant, is the privileges you are granting to the user. These privileges are specified with keywords. For example, the ALL keyword is used to grant global-, database-, table-, and column-level rights for a specified user.

The second option, where_to_grant, specifies the resources on which the privileges should be granted. The third option, user_name, is the username to which you want to grant the privileges. Finally, the fourth option, password, is a password that should be assigned to this user. If this is an existing user who already has a password and you are modifying permissions, you can omit the IDENTIFIED BY portion of the statement.

For example, to grant all privileges on a database named sampledata to a user named foobar, you could use the following command:

GRANT ALL ON animals.* TO foobar IDENTIFIED BY 'secretword';

The user foobar can now connect to the database sampledata by using the password secretword, and foobar has all privileges on the database, including the ability to create and destroy tables. For example, the user foobar can now log in to the server (by using the current hostname — shuttle2, in this example), and access the database like so:

$ mysql -h shuttle2 -u foobar -p animals
Enter password:
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 43 to server version: 3.23.58
Type 'help;' or 'h' for help. Type 'c' to clear the buffer.
mysql>

Later, if you need to revoke privileges from foobar, you can use the REVOKE statement. For example, the following statement revokes all privileges from the user foobar:

REVOKE ALL ON animals FROM foobar;

Advanced database administration, privileges, and security are very complex topics that are beyond the scope of this book. See the "Reference" section at the end of this chapter for links to online documentation. You can also check out Luke Welling's and Laura Thompson's book PHP and MySQL Development from Sams Publishing.

Оглавление книги