Database Reference
In-Depth Information
We customize the
<privileges>
,
<database>
, and
<user>
parts as needed. The
<user>
section should match the
'username'@'host'
part of the
CREATE
statement,
otherwise, we'll be creating what is essentially a new user. We can also add an
IDENTIFIED BY 'password'
section to the end of the
GRANT
statement if we want to
change the password (or add a password to an account that doesn't have one).
Here are some examples. This first one grants all privileges including the grant
option on all databases and the user can log in from anywhere. We should not often
set up users with such broad authority, and when we do we need to make sure, we
use an appropriate
CREATE USER
statement first and assign the user a password
(or assign the password here). If the user doesn't exist, the
GRANT
statement will
create one, but if the user doesn't exist and our
GRANT
statement doesn't include
an
IDENTIFIED BY 'password'
section then the user will be created without a
password, so it's a good habit to first create the user with a password, and then grant
the user the rights they need.
GRANT ALL ON *.* TO 'robert'@'%' WITH GRANT OPTION;
The following example is a standard set of permissions for a regular user who needs
read and write access to a database called
serv
. If a user just needs read access, we
can just assign the user the
SELECT
privilege. By specifying
serv.*
as the database,
the user only has these rights on tables in the
serv
database. Multiple privileges are
separated by commas.
GRANT SELECT,INSERT,UPDATE,DELETE ON serv.* TO 'jeffrey'@'localhost';
This following user has read access (
SELECT
) to just the staff table in the
edu
database, and the user has the
GRANT OPTION
privilege so they can grant that same
right to other users.
GRANT SELECT ON edu.staff TO 'david'@'localhost' WITH GRANT OPTION;
The following example gives a user all rights on the
logan
database. We'll also limit
this user to 100 queries per hour, just because we can.
GRANT ALL ON logan.* TO 'quentin'@'localhost' WITH MAX_QUERIES_PER_HOUR
100;
Complete documentation of the
GRANT
statement is available at
https://mariadb.com/kb/en/grant/
.
