User Priveleges

ALL Grants all privileges
ALTER Alter the structure of existing tables; e.g. adding or renaming columns or tables & changing data types
CREATE Create new databases or tables
DELETE Delete existing rows
DROP Delete databases or tables
INDEX Create and drop indexes on tables
INSERT Insert new records into table
SELECT Select records from table
UPDATE Modify values in existing table
USAGE Grants no privileges. Can only logon. Additional privileges can be added later


Added or taken away using the GRANT or REVOKE keywords.


To setup and administrator:

mysql> GRANT ALL on * to admin identified by ‘password’ with grant option ;
Query OK, 0 rows affected (0.00 sec)



To revoke all privileges:

mysql> revoke all on * from admin ;
Query OK, 0 rows affected (0.00 sec)



Setup a user (called simon in this example) with no privileges, for the Academy database and all tables:

mysql> grant usage on Academy.* to simon identified by ‘password’ ;
Query OK, 0 rows affected (0.00 sec)



Grant privileges:

mysql> grant alter, create, delete, drop, index, insert, select, update on Academy.* to simon ;
Query OK, 0 rows affected (0.00 sec)



Revoke privileges:

mysql> revoke alter, drop, index on Academy.* from simon ;
Query OK, 0 rows affected (0.00 sec)

Leave a Reply