How to grant privileges
The syntax of the GRANT statement
GRANT privilegeList ON [dbName.]table
TO userName1 [IDENTIFIED BY 'password1'][, userName2 [IDENTIFIED BY 'password2']...] [WITH GRANT OPTION
A statement that creates a user with no privileges
GRANT USAGE ON *.* TO joel@localhost IDENTIFIED BY 'sesame';
A statement that creates a user with database privileges
GRANT SELECT, INSERT, UPDATE, DELETE ON my_guitar_shop2.*
TO mgs_user@localhost IDENTIFIED BY 'pa55word';
A statement that creates a user with global privileges
GRANT ALL ON *.* TO dba IDENTIFIED BY 'supersecret' WITH GRANT OPTION;
A statement that grants table privileges to a user
GRANT SELECT, INSERT, UPDATE ON my_guitar_shop2.products TO joel@localhost;
A statement that grants database privileges to a user
GRANT SELECT, INSERT, UPDATE ON my_guitar_shop2.* TO joel@localhost;
A statement that grants global privileges to a user
GRANT SELECT, INSERT, UPDATE ON *.* TO joel@localhost;
A statement that grants column privileges to a user
GRANT SELECT (productCode, productName, listPrice), UPDATE (description) ON my_guitar_shop2.products TO joel@localhost;
A statement that uses the current database
GRANT SELECT, INSERT, UPDATE, DELETE ON customers TO joel@localhost;
Description
- The
WITH GRANT OPTION clause allows the user to grant the privileges for that user to other users.You can use the asterisk (*) to specify all databases or all tables.
- You can use the asterisk (*) to specify all databases or all tables.
- If you don’t specify a database, MySQL uses the current database.
Back