Tailoring User Permissions


GRANT privileges ON db.table TO user@host IDENTIFIED BY 'password'; 



Use the GRANT command to give a database user a set of privileges on a database or on a list of tables. The GRANT command creates the specified user account if it does not already exist. In fact, in MySQL version 4.1 and earlier where there is no CREATE USER command, you have to use GRANT to create a new user.

The privileges that can be set are shown in Table 6.1. Specifying ALL PRIVILEGES grants each of these options.

Table 6.1. Privileges Allocated Using GRANT

Privilege

Meaning

ALTER

Allows use of ALTER TABLE.

ALTER ROUTINE

Alter or drop stored routines.

CREATE

Allows use of CREATE TABLE.

CREATE ROUTINE

Create stored routines.

CREATE TEMPORARY TABLES

Allows use of CREATE TEMPORARY TABLE.

CREATE USER

Allows use of CREATE USER, DROP USER, RENAME USER, and REVOKE ALL PRIVILEGES.

CREATE VIEW

Allows use of CREATE VIEW.

DELETE

Allows use of DELETE.

DROP

Allows use of DROP TABLE.

EXECUTE

Allows the user to run stored routines.

FILE

Allows use of SELECT ... INTO OUTFILE and LOAD DATA INFILE.

INDEX

Allows use of CREATE INDEX and DROP INDEX.

INSERT

Allows use of INSERT.

LOCK TABLES

Allows use of LOCK TABLES on tables for which you also have SELECT privileges.

PROCESS

Allows use of SHOW FULL PROCESSLIST.

RELOAD

Allows use of FLUSH.

REPLICATION CLIENT

Allows the user to ask where slave or master servers are.

REPLICATION SLAVE

Needed for replication slaves.

SELECT

Allows use of SELECT.

SHOW DATABASES

Allows use of SHOW DATABASES.

SHOW VIEW

Allows use of SHOW CREATE VIEW.

SHUTDOWN

Allows use of mysqladmin shutdown.

SUPER

Allows use of CHANGE MASTER, KILL, PURGE MASTER LOGS, and SET GLOBAL SQL statements. Allows mysqladmin debug command. Allows one extra connection to be made if maximum connections are reached.

UPDATE

Allows use of UPDATE.

USAGE

Allows connection without any specific privileges.


A comma-separated list of privileges can be used with GRANT to give a user several permissions in one statement. The following example gives the user zak permission to query the table mytable and insert new records, but not to update or delete existing records:

GRANT SELECT, INSERT ON sampdb.mytable TO 'zak'@'localhost' IDENTIFIED BY 'phrasebook'; 


To give a user all the privileges listed in Table 6.1, you can use ALL PRIVILEGES, or simply the keyword ALL, in the GRANT statement. The following example gives zak full access to the table:

GRANT ALL PRIVILEGES ON sampdb.mytable TO 'zak'@'localhost' IDENTIFIED BY 'phrasebook'; 


If you are granting new privileges to a user that already exists, you do not need to specify the IDENTIFIED BY clause. If IDENTIFIED BY is used, the user's password is overwritten. If it is omitted, the password remains unchanged.



MySQL Phrasebook. Essential Code and Commands
MySQL Phrasebook
ISBN: 0672328399
EAN: 2147483647
Year: 2003
Pages: 130

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net