MySQL – MariaDB – User Accounts and Privileges

Docker Compose Tutorial for Beginners

We’ve addressed user accounts and privileges a few
times up until this point, but in this chapter we’re going to thoroughly
discuss this crucial topic. Given the importance of security in any
data-related activity, some readers might feel that this topic should have
been covered thoroughly at the beginning of the book, and there’s some logic
to support that approach. But it’s much more interesting to work with
databases first before spending a lot of time on the less exciting
administrative tasks such as user privileges and security. Plus, it’s easier
to understand the importance of user privileges, and to think about the
various ways to set privileges, after you have a firm understanding of
tables and other elements of a database. You’re now ready to consider user
accounts and related topics, and will have a better appreciation of what’s
covered here than you would have if we had explored this subject earlier in
the book.

We’ll start by looking at the basics of creating a user account and
granting privileges. Then we’ll go through the details of restricting access
and granting privileges for various database components. Once you understand
these ways to restrict access, we’ll look at what privileges to give some
common administrative user accounts. We’ll then look at how to revoke
privileges and delete user accounts, as well as how to change passwords and
rename user accounts.

User Account Basics

In this book, I have used the term user account
several times instead of just user. This was done to
distinguish a person from the combination of a username and the location
or host from which the user may access the MySQL or MariaDB server.

For instance, the root user has full access to
all databases and all privileges, but only when connecting from the
localhost. The root user is not allowed
to access the server through a remote host, such as through the Internet.
That would be a major security vulnerability. At a minimum, access and
privileges are based on the combination of the user and its
host, which is called the user account.

As the root user, you can create a user account
with the CREATE USER statement. Here’s an example
using this SQL statement to create a user account for a woman named Lena

CREATE USER 'lena_stankoska';

In this example, we’re just creating the user account without giving
it any privileges. To see the privileges a user account has, use
the SHOW GRANTS statement like this:

SHOW GRANTS FOR 'lena_stankoska';

| Grants for lena_stankoska@%                |
| GRANT USAGE ON *.* TO 'lena_stankoska'@'%' |

Notice that these results are in the form of an SQL statement.
Instead of using the CREATE USER statement, you can enter a
GRANT statement exactly as shown in the results. Let’s pull
apart the results here, but a bit in reverse order.

The user is lena_stankoska and the host is the
wildcard, %. The wildcard was used because we
didn’t specify a host when we created the user. Any privileges that will
be granted to this user account will be permitted from any host. This is
not a good idea. You should always specify a host. For our examples, to
start, we’ll use localhost. We’ll look at setting the host in the next

The *.* part in the results says that usage is granted
for all databases and tables—the part before the period refers to
databases, and the part after the period refers to tables. In order to
limit usage to a specific database or table, you would have to change that
part to database.table. We’ll look at that in a

Once you create a user account, you would generally then give it
privileges. If you want to give an existing user account all privileges to
be able to use all SQL statements from the localhost, you would execute
the GRANT statement like this:

GRANT ALL ON rookery.*
TO 'lena_stankoska'@'localhost';

SHOW GRANTS FOR 'lena_stankoska'@'localhost';

| Grants for lena_stankoska@localhost                                 |
| GRANT USAGE ON *.* TO 'lena_stankoska'@'localhost'                  |
| GRANT ALL PRIVILEGES ON `rookery`.* TO 'lena_stankoska'@'localhost' |

Notice that the results of the SHOW GRANTS statement
for the lena_stankoska@localhost user account now
shows two rows: one similar to the result shown previously, but with the
host as localhost, and the new SQL statement we executed. This user
account now has all of the privileges allowed on the rookery
database, except the ability to give privileges to others. We’ll cover
that one and the many privileges that may be given to a user account later
in this chapter.

Because we didn’t specify a password for this user account, it can be accessed without a password. That
makes this user account a high security risk: it can allow anyone who gets
on to the server to do almost anything to the database, and it doesn’t
require a password. Because we created it only to see how granting and
showing privileges works, let’s remove it. We’ll create this user account
again later.

User accounts are removed through the DROP USER statement. However, removing the
user accounts for Lena isn’t as straightforward as you might think. When
we executed the CREATE USER statement and didn’t specify a
host, we created one user account—one with the wildcard for the host. When
we executed the GRANT statement to give privileges to the
same user, but with the host of localhost, a second user account was
created. To understand this better, let’s look at what is stored in the
user table in the mysql database. That’s where
this user account information is stored. Execute the following SQL
statement from your server:

SELECT User, Host
FROM mysql.user
WHERE User LIKE 'lena_stankoska';

| User           | Host      |
| lena_stankoska | %         |
| lena_stankoska | localhost |

As you can see here, there are two user accounts, although we sought
to create only one. If you had not understood before the distinction
between a user and a user account, I hope you do now.


Although you may be able to access the user account privileges
directly in the mysql database, you should never use that
method to make changes to user account data. Although the examples so far have been
simple, there are situations in which user permissions will affect
several tables in the mysql database. If you attempt to
insert, update, or delete a user account in the user
table using the INSERT, UPDATE, or
DELETE statements instead of the appropriate user account
statements described in this chapter, you may not make the changes the
way you want and may orphan entries in other tables.

To eliminate both of the user accounts that we created for Lena, we
will have to execute the DROP USER statement twice, like

DROP USER 'lena_stankoska'@'localhost';
DROP USER 'lena_stankoska'@'%';

This eliminates both user accounts for Lena. We’ll create more user
accounts for her in the next sections. In doing so, though, we will look
more closely at how to restrict access of user accounts, rather than give
her all privileges and access from anywhere and without a password.

Restricting the Access of User Accounts

As a database administrator, you may give users full access to databases
from anywhere, or you can limit them based on various aspects of the
connection and the database. Put simply, you can restrict user access and
privileges based on the username and host, the database components (e.g.,
tables) the user account may access, and the SQL statements and functions
that may be used on those database components. We’ll address these
restrictions in this section.

Username and Host

When you create user accounts, consider both who needs access and
from where. First, let’s define who. This can
represent a person or a group of people. You can give an individual a
username—which might be related to their actual name, such as
lena_stankoska for Lena Stankoska—or define a
username to a group of people, such as sales_dept
for the Sales Department. You could also create a user account based on
a function or use. In that case, one person might have several user

If Lena Stankoska is a database administrator of the
rookery and birdwatchers databases, she might
have multiple usernames, perhaps all from the localhost, for example,
lena_stankoska, for personal use;
admin_backup, for when she makes backups;
admin_restore, for when she restores backups; and
admin_import, if she regularly imports large
amounts of data.

Let’s first create the personal accounts for Lena Stankoska. We’ll
create the administrative accounts later. For her personal username,
lena_stankoska, let’s give her two user accounts:
one from localhost and another from a remote location. We’ll give her
more privileges when she’s logged into the localhost, but less when she
accesses the server remotely—from her home if she has a static IP
address. Let’s create for her
lena_stankoska@localhost and

The hostname for a user account can be a name that a DNS can
translate to an IP address or it can be an actual IP address. The DNS
could be the server’s external DNS, which translates Internet domain
names to an IP address. Or you can use the bind system and put the name
in the server’s hosts file (e.g., /etc/hosts on a Linux system). If you do
that, you’ll have to restart MySQL for it to take effect.

Let’s create these two personal user accounts for Lena. Enter the
following SQL statements on your server:

CREATE USER 'lena_stankoska'@'localhost'
IDENTIFIED BY 'her_password_123';

GRANT USAGE ON *.* TO 'lena_stankoska'@'lena_stankoska_home'
IDENTIFIED BY 'her_password_123';

These examples used the CREATE USER and the GRANT
statements to create the user accounts. If you enter GRANT
and specify a username that doesn’t exist, it automatically creates the
user—and remember that each combination of user and hostname is a unique
user account. However, it’s recommended that you start with CREATE
to create the user account and then grant privileges. We
added the IDENTIFIED BY clauses in each of these
SQL statements to set the passwords for each user account.

Let’s see how one of Lena’s user accounts looks at this point. Enter the following on
your server:

SHOW GRANTS FOR 'lena_stankoska'@'localhost' \G

*************************** 1. row ***************************
Grants for admin_backup@localhost:
     GRANT USAGE ON *.* TO 'lena_stankoska'@'localhost'

Notice that the password is encrypted in the results. There isn’t
a way within MySQL to retrieve the password in plain text, to decrypt
it. Also notice that the encrypted password is preceded by the PASSWORD keyword. If you
don’t want to enter someone’s password with clear text as we did in the
earlier commands, you could encrypt the password on a different computer
with the PASSWORD() function and then copy the
results to the server using the GRANT statement. You would
do that like this:

SELECT PASSWORD('her_password_123');

| PASSWORD('its_password_123')              |
| *B1A8D5415ACE5AB4BBAC120EC1D17766B8EFF1A1 |

The encrypted text is identical to the one in the results of the
earlier SHOW GRANTS statement. If your server is logging
all transactions, you may want to encrypt passwords on your personal
computer by this method and use the results for entering the passwords
on your server so no one else will know the password for a user account.
Starting with MySQL version 5.6, any SQL statement that contains the
reserved word PASSWORD will not be logged.

At this point, Lena can log into the server with any one of these
user accounts—one allows her to do so only from home, and the other four
only when logging in from the server. But she can’t access any database,
other than the default ones (i.e., test and
information_schema) and not always those. This
allows her to do anything she wants in the test database,
including creating tables and selecting, updating, and deleting data.
She can’t access or even see the other databases, and she can’t create
another database. She is greatly limited with these user accounts. Let’s
proceed to the next section to learn more about what a user account may
access and then give Lena access to more than the test

SQL Privileges

Lena needs more than access to the databases to be able to perform her
duties. We have to grant her the privileges to execute various tasks,
such as reading and writing data on the rookery and
birdwatchers databases. At this point, we need to give
the lena_stankoska@localhost user account
UPDATE privileges for both of our databases. To give a user
account multiple privileges, list the privileges in a comma-separated
list. Enter this on the server:

TO 'lena_stankoska'@'localhost';

TO 'lena_stankoska'@'localhost';

SHOW GRANTS FOR 'lena_stankoska'@localhost \G

*************************** 1. row ***************************
Grants for lena_stankoska@localhost: 
TO 'lena_stankoska'@'localhost'

*************************** 2. row ***************************
Grants for lena_stankoska@localhost: 
TO 'lena_stankoska'@'localhost'

*************************** 3. row ***************************
Grants for lena_stankoska@localhost: 
TO 'lena_stankoska'@'localhost'

Some privileges cover more than one SQL statement. For a list of
privileges, see Table 13-1.

Although we gave lena_stankoska@localhost
enough privileges to manipulate data on our two databases, we didn’t
give it the ability to delete data. To add privileges to a user account,
you don’t have to list again all of the privileges it already has. Just
execute the GRANT statement with the new privileges
and the system will add them to the user account’s privileges list. Do
that like so:

TO 'lena_stankoska'@'localhost';

GRANT DELETE ON birdwatchers.*
TO 'lena_stankoska'@'localhost';

SHOW GRANTS FOR 'lena_stankoska'@localhost \G

*************************** 1. row ***************************
Grants for lena_stankoska@localhost: 
TO 'lena_stankoska'@'localhost'

*************************** 2. row ***************************
Grants for lena_stankoska@localhost: 
TO 'lena_stankoska'@'localhost'

*************************** 3. row ***************************
Grants for lena_stankoska@localhost: 
TO 'lena_stankoska'@'localhost'

Now Lena can manipulate data in all of the basic ways on our two
databases, but only from the localhost. She still can’t do anything from
home. We’ll give her privileges from home later.

Table 13-1. Privileges for GRANT and REVOKE statements




Grants all of the basic privileges. Does not include the


Allows use of the ALTER TABLE statement, but
requires also the CREATE and INSERT
privileges. DROP is also needed to rename a table.
This is a security risk: someone could rename a table to get
access to it.


Allows user account to alter or drop stored routines. This
includes the ALTER FUNCTION and ALTER
statements, as well as the DROP


Allows use of the CREATE TABLE statement.
Needs INDEX privilege to define


Allows user account to create stored routines. This
statements. Gives the user has ALTER
privileges to any routine he


Allows the CREATE TEMPORARY TABLES statement
to be used.


Allows the user account the ability to execute several
user account management statements: CREATE
, and the DROP USER


Permits the CREATE VIEW


Allows the DELETE statement to be


Permits the user to execute DROP TABLE and
TRUNCATE statements.


Allows the user account to create events for the event
scheduler. It allows the use of the CREATE EVENT,


Allows the execution of stored procedures, the
EXECUTE statement.


Allows the use of SELECT...INTO OUTFILE and
LOAD DATA INFILE statements to export and import to
and from a filesystem. This is a security risk. It can be
limited to specific directories with the
secure_file_priv variable.


Grants the use of the CREATE INDEX and
DROP INDEX statements.


Permits the use of INSERT statements. It’s
required to execute ANALYZE TABLE, OPTIMIZE


Allows the use of LOCK TABLES statements for
tables for which the user has SELECT


Allows the use of the SHOW PROCESSLIST and
SHOW ENGINE statements.


Allows the FLUSH statement to be


Allows the user to query master and slave servers for
status information, the SHOW MASTER STATUS and
SHOW SLAVE STATUS statements, as well as the


Required for replication slave servers, this allows binary
log events to be read from the master server.


Allows the use of the SELECT


Permits the use of the SHOW DATABASES
statement for all databases, not just the ones for which the
user has privileges.


Allows the use of the SHOW CREATE VIEW


Allows the use of the shutdown option
with the mysqladmin utility.


Grants use of CHANGE MASTER TO,
statements, and the debug option with
the command-line utility mysqladmin.


This privilege allows the user account the ability to
create and drop triggers, using the CREATE TRIGGER
and the DROP TRIGGER statements.


Allows the UPDATE statement to be


Included to create a user without privileges, or to modify
an existing one without affecting the existing privileges.

Database Components and Privileges

Now we’ll turn to the parts of the database a user account can access. A user
account can be given access to all of the databases on a server, or
limited to specific databases, specific tables, and even specific
columns. Let’s first see how to limit user accounts to specific
databases, and then how to limit user accounts to tables and


We’ve given Lena more restrictions when she’s at home than when
she’s at work. Of course, if she really wants access to more
information at home, she can first log into the server at the
operating system level using ssh and then
log into MySQL from there using her
lena_stankoska@localhost user account. This may
be fine, because we can more easily control security at the operating
system level, and we’re assuring that sensitive data isn’t being
passed unencrypted through the Internet by adding extra restrictions
to the home account. But on the operating system level, if you want,
you can restrict use of ssh to
prevent Lena from getting around security.

Restricting to specific databases

In order to limit the
lena_stankoska@lena_stankoska_home user account
to the rookery database, we would have to do something
like this:

GRANT USAGE ON rookery.*
TO 'lena_stankoska'@'lena_stankoska_home'
IDENTIFIED BY 'her_password_123';

SHOW GRANTS FOR 'lena_stankoska'@'lena_stankoska_home' \G

*************************** 1. row ***************************
Grants for lena_stankoska@lena_stankoska_home:
    GRANT USAGE ON *.* TO 'lena_stankoska'@'lena_stankoska_home'

Here we’re limiting this user account’s access on the server to
the rookery database. However, we can see from the
results of the SHOW GRANTS statement that she still
has global usage. If she were to access the server from her home to
get a list of databases, this is what she’d see:

mysql --user lena_stankoska --password='her_password_123' \
      --host --execute='SHOW DATABASES'

| Database           |
| information_schema |
| test               |

She still can’t see the rookery database. This is
because she can’t do anything on that database. She can’t even
execute a SHOW TABLES statement or a
SELECT statement for that database. To do that, we need
to give her privileges other than hollow access to the
rookery database. Let’s start by giving her the SELECT privilege for the
rookery database. We’ll do that by executing the

TO 'lena_stankoska'@'lena_stankoska_home';

SHOW GRANTS FOR 'lena_stankoska'@'lena_stankoska_home';

| Grants for lena_stankoska@lena_stankoska_home                             |
| GRANT USAGE ON *.* TO 'lena_stankoska'@'lena_stankoska_home'              |
| IDENTIFIED BY PASSWORD '...'                                              |
| GRANT SELECT ON `rookery`.* TO 'lena_stankoska'@'lena_stankoska_home'     |

You can’t specify just the database name in the GRANT statement; you have to specify
a table too. That’s why we added .* to refer to all
tables in the rookery database.

In the results, notice that there is still the row granting
global usage for this user account. Following that is an entry related
to the rookery database. To make the results fit on the
page here, I replaced the password with an ellipsis. Lena can now
access the rookery database from her home, although she
can only select data. Here’s what she sees from her home when she
statement to get a list of Avocet birds from the command

mysql --user lena_stankoska --password='her_password_123' --host \
      --execute="SHOW DATABASES; \
                 SELECT common_name AS 'Avocets'
                 FROM rookery.birds \
                 WHERE common_name LIKE '%Avocet%';"

| Database           |
| information_schema |
| rookery            |
| test               |
| Avocets             |
| Pied Avocet         |
| Red-necked Avocet   |
| Andean Avocet       |
| American Avocet     |
| Mountain Avocetbill |

Restricting to specific tables

At this point, Lena has sufficient access to the two databases when at her
office. However, although she can select data on the
rookery database from home, she can’t access the
birdwatchers databases from home. Let’s give her the
SELECT privilege for that database, but only for certain

If we want to give Lena access only to the
bird_sightings table in the birdwatchers
database from home, we would enter the following:

GRANT SELECT ON birdwatchers.bird_sightings
TO 'lena_stankoska'@'lena_stankoska_home';

SHOW GRANTS FOR 'lena_stankoska'@'lena_stankoska_home';

| Grants for lena_stankoska@lena_stankoska_home                             |
| GRANT USAGE ON *.* TO 'lena_stankoska'@'lena_stankoska_home'              |
| IDENTIFIED BY PASSWORD '...'                                              |
| GRANT SELECT ON `rookery`.* TO 'lena_stankoska'@'lena_stankoska_home'     |
| GRANT SELECT ON `birdwatchers`.`bird_sightings`                           |
| TO 'lena_stankoska'@'lena_stankoska_home'                                 |

Now Lena can see only that one table in the
birdwatchers database. Here is what happens if she
executes the following from her home computer:

mysql --user lena_stankoska --password='her_password_123' --host \
      --execute="SHOW TABLES FROM birdwatchers;"

| Tables_in_birdwatchers |
| bird_sightings         |

To give her access to more tables in the
birdwatchers database, we could execute a GRANT statement for each table.
That can be tedious with a database that has many tables, to give her
access to many of them but not all. But there’s no simple way around
it. I have requested while writing this chapter that a feature be
added to MariaDB to specify multiple tables in a single
GRANT statement. So maybe one day there will be an easy
way to do it with MariaDB. For now, you can either manually enter the
GRANT statement many times, or you can create a short
script to do it.

For example, suppose that we want to give Lena access to all of
the tables in the birdwatchers database, except ones with
personal and sensitive information. The tables to exclude would be the
humans table and the two tables containing information
about children, the birder_families and
birding_events_children tables. Here’s how such a shell
script might look:


mysql_connect="mysql --user root -pmy_pwd"

results=`$mysql_connect --skip-column-names \
         --execute 'SHOW TABLES FROM birdwatchers;'`

items=$(echo $results | tr " " "\n")

for item in $items

  if [ $item = 'humans' ] ||
     [ $item = 'birder_families' ] ||
     [ $item = 'birding_events_children' ]

  `$mysql_connect --execute "GRANT SELECT ON birdwatchers.$item \
                             TO 'lena_stankoska'@'lena_stankoska_home'"`


This simple shell script gets a list of tables using the
SHOW TABLES statement. The script then goes through the
list to execute a GRANT statement for each table name in
the results, but skipping the three sensitive tables.

At this point, Lena can do plenty from her office and check on
things from her home. If she needs to do more than this, it will
probably be because she is performing an administrative task like
making a backup or importing large amounts of data. When she does
those tasks, she’ll use one of the three administrative user accounts
we created for her. Let’s give those three accounts the necessary
privileges so that Lena can perform the tasks required of her.

Restricting to specific columns

To give a user account access only to specific columns, issue a GRANT
statement listing all of the columns permitted for the table within
parentheses, in a comma-separated list after the privilege for which
they apply. This will make more sense when you see an example. If
you’re granting many privileges, this can be an excessively long SQL

In the previous section, as a security precaution, we didn’t
give Lena access to the humans table in the
birdwatchers database from home. Suppose we changed our
mind about that. Suppose we want her to have access to most of the
humans table when she works at home, but not to the
contact information of our clients (e.g., email addresses). Looking at
the columns in the humans table, we decide she needs
access to the human_id column to be able to join to other
tables, and the formal_title, name_first,
and name_last columns, as well as
membership_type. The other columns either contain
sensitive information or are unnecessary for her duties.

Based on the list of columns we want to permit Lena to access
from home, let’s enter the following:

GRANT SELECT (human_id, formal_title, name_first,
name_last, membership_type)
ON birdwatchers.humans
TO 'lena_stankoska'@'lena_stankoska_home';

Now Lena can access the humans table from home to
get the names of members, as well as the type of membership each has.

Administrative User Accounts

Earlier, I mentioned that we need to create three administrative accounts for Lena to use
in performing her duties as a database administrator from the localhost:
admin_backup, admin_restore, and
admin_import. These are common administrative user
accounts that you may need to create and use. You’ll use them in examples
and exercises in Chapter 14 (which covers
backing up and restoring), and Chapter 15
(importing data). In this section, we’ll create these administrative user
accounts and look at the privileges needed for them, as well as another
one for granting privileges to other user accounts.

User Account for Making Backups

The admin_backup user account will be
used with the mysqldump
utility to make back-ups of the rookery and
birdwatchers databases. This is covered in Chapter 14. Just a few privileges are needed to
accomplish these tasks:

  • At a minimum, it will need the SELECT privilege to read our two
    databases. You should limit an administrative account to the
    databases it needs to backup. In particular, you should not let it
    have SELECT privileges for the mysql
    database, because that contains user passwords.

  • To lock the tables when making a backup, the LOCK TABLES privilege is

  • If a database contains views and triggers, which we didn’t
    cover in this book, the user account will need the SHOW VIEW and TRIGGER
    privileges, respectively.

Based on those considerations, let’s create the
admin_backup@localhost user account and give it the
SELECT and LOCK TABLES privileges, but only
for the rookery and birdwatchers databases. Do
that by executing the following SQL statement:

CREATE USER 'admin_backup'@'localhost'
IDENTIFIED BY 'its_password_123';

ON rookery.*
TO 'admin_backup'@'localhost';

ON birdwatchers.*
TO 'admin_backup'@'localhost';

This allows Lena to use this admin_restore account to
make backups of our databases. We created another account for restoring
data, so let’s give that account the privileges it needs.

User Account for Restoring Backups

Although you could create one administrative user account for both making backups and
restoring them, you might want to use separate user accounts for those
tasks. The main reason is that the task of making backups is usually one
handled by scripts that run automatically. But the task of restoring
data is generally run manually and can overwrite or destroy data on a
live server. You might not want the user account with those privileges
to be the same one for which you use in a script containing its
password. For our examples in this chapter, let’s give the
admin_restore@localhost user account the privileges
needed for restoring data to our databases:

  • At a minimum, a user account for restoring a dump file
    needs the INSERT privilege to insert data
    into tables.

  • It should also have the LOCK TABLES privilege to lock the
    tables while inserting data.

  • It will need the CREATE privilege to create tables and INDEX to create

  • Because a dump file can include SQL statements to alter tables
    to set the collation, the ALTER privilege may be

  • Depending on the method Lena uses to restore tables, she might also want to restore them to
    temporary tables. For that, she will need the CREATE TEMPORARY
    privilege. Temporary tables are dropped when the
    client connection is closed.

  • If a database has views and triggers, the CREATE VIEW and TRIGGER
    privileges are required.

For our database usage, we won’t need CREATE VIEW or
TRIGGER, but we will need the other privileges. Create the
admin_restore@localhost user account and give it
the necessary privileges by entering the following on your

CREATE USER 'admin_restore'@'localhost'
IDENTIFIED BY 'different_pwd_456';

ON rookery.*
TO 'admin_restore'@'localhost';

ON birdwatchers.*
TO 'admin_restore'@'localhost';

With those privileges, Lena should have what she needs to restore
any of the data in the rookery and
birdwatchers databases.

User Account for Bulk Importing

The last administrative user we need to create for Lena is
admin_import. She’ll use this user account to
import large data text files into our databases. This is covered in
Chapter 15. For this method of importing
data, she’ll use the LOAD DATA INFILE statement. That
requires just the FILE privilege.


The FILE privilege is a security risk because it has the ability to read data
from any file on the server to which MySQL has rights. This is why it
is especially important that this privilege be given only to a user
account designated for importing files. The password for that user
account should be given only to someone who is trusted. You can
restrict the directory from which files may be loaded with the
That will minimize the security risk to the filesystem. You can also
revoke this privilege when it’s not in use and grant it again when
needed to minimize risk to the databases.

The FILE privilege cannot be given for specific
databases or components. It’s a global privilege. If we give it to the
admin_import@localhost user account, it can import
data into any database—and it can export data from any database,
including the mysql database. So be careful who gets this
privilege and never allow it with a remote host. Still, create
admin_import@localhost and give it this privilege
by entering the following on the server:

CREATE USER 'admin_import'@'localhost'
IDENTIFIED BY 'another_pwd_789';

TO 'admin_import'@'localhost';

We have created all of Lena’s administrative user accounts and set
each one with the necessary privileges (no more and no less) for her to
perform her duties related to our databases. Let’s create one more
administrative user account, though, that may be of use to you.

User Account to Grant Privileges

Another user account that you might need is one for creating other users. You could use
root for that, but to continue the policy of using
limited administrative user accounts for separate functions, we should
create a separate user account for user and privilege maintenance.
Besides, this task might be given to someone who we don’t want to have
complete control over our database system.

To create a user account with the ability to create other user
accounts and grant those other user accounts privileges, the
GRANT statement has to include the GRANT OPTION clause. This clause allows
the user to grant the same privileges it has to other users—but only the
precise privileges granted in this GRANT statement. If we
limit the privileges in the GRANT statement to our two
databases, the user account cannot grant privileges to other databases.
For instance, execute the following on your server to create this user
account and give it the GRANT OPTION for our two

TO 'admin_granter'@'localhost'
IDENTIFIED BY 'avocet_123'

TO 'admin_granter'@'localhost'
IDENTIFIED BY 'avocet_123'

This creates the admin_granter@localhost user
account, which has the privilege of granting privileges on the
rookery and birdwatchers databases to other
user accounts.

This user account’s privileges are still fairly limited if we want
it to be used to manage other user accounts. Suppose we want this user
account to create and drop user accounts for our databases. To do that,
we need to grant the CREATE USER privilege globally to
admin_granter@localhost. So that this user account
can execute the SHOW GRANTS statement, it will also need
the SELECT privilege on the
mysql database. This is another security risk, so be
careful who gets this privilege. Enter these two SQL statements to give
this user account these two additional privileges:

TO 'admin_granter'@'localhost';

TO 'admin_granter'@'localhost';

Now the admin_granter@localhost user account
has the privileges to perform its tasks of managing user accounts on our
databases. Let’s test it by entering the first line in the following
example from the command line to log into MySQL, then the following SQL
statements from within the mysql

mysql --user admin_granter --password=avocet_123


| User Account            |
| admin_granter@localhost |

CREATE USER 'bird_tester'@'localhost';

GRANT SELECT ON birdwatchers.*
TO 'bird_tester'@'localhost';

SHOW GRANTS FOR 'bird_tester'@'localhost';

| Grants for bird_tester@localhost                              |
| GRANT USAGE ON *.* TO 'bird_tester'@'localhost'               |
| GRANT SELECT ON `birdwatchers`.* TO 'bird_tester'@'localhost' |

DROP USER 'bird_tester'@'localhost';

That worked well. We logged in with the
admin_granter@localhost user account and used the
CURRENT_USER() to confirm the user account. Then we created a user with the
SELECT privilege on the birdwatchers database.
We were able to execute SHOW GRANTS to verify this and then
successfully issued DROP USER to delete the user account.
We can give this user account to someone on our staff whose
responsibility will be to manage user accounts for our databases.

Revoking Privileges

So far in this chapter we have been giving privileges to user accounts. But there
may also be times when you want to revoke a privilege that you gave to a
user account. Maybe you gave a privilege by mistake, or you’ve changed
your mind about which tables you want the user account to have access, or
changed your policy about which tables you want to protect.

The REVOKE statement revokes all or certain privileges that were granted to a
user account. There are two forms of syntax to do this: one to revoke all
privileges and another for specific privileges. Let’s look at examples for
both syntaxes.

Suppose we have a user, Michael Stone, who is taking a leave of
absence for a few months, and there is no chance he will access the
database while he’s gone. We could delete his user account, but instead we
decide to revoke his user account privileges. We’ll add them back when he
returns. To do this, we would enter something like this:

ON rookery.*
FROM 'michael_stone'@'localhost';

ON birdwatchers.*
FROM 'michael_stone'@'localhost';

The syntax is similar to the GRANT statement that
grants all privileges. The main difference is that instead
of an ON clause, there’s a FROM to revoke
privileges from a user account. Although Michael may have had privileges
for only certain tables in the two databases, this removes them all. We
don’t have to remove the specific privileges with multiple SQL statements
for each table. To give privileges again to the user account, though, we
may have to use the GRANT statement many times as we would
for a new user account.

The second syntax can be used to revoke only some privileges. The
specific privileges have to be given in a comma-separated list after the
keyword REVOKE. The privileges for REVOKE are
the same as for GRANT (see Table 13-1). You can specify one table per
REVOKE statement, or revoke privileges
on all tables of a database by putting an asterisk in as the table name.
To revoke privileges for specific columns, list them within parentheses in
a comma-separated list—the same as with the GRANT statement.
Let’s look at an example of this second syntax.

To keep security tight, suppose we have a policy of removing any
privileges not needed by user accounts. When we granted privileges to the
admin_restore@localhost user account, we included the
ALTER privilege. Suppose we have found that ALTER is never
needed. We can revoke it like so:

ON rookery.*
FROM 'admin_restore'@'localhost';

ON birdwatchers.*
FROM 'admin_restore'@'localhost';

Deleting a User Account

The DROP USER statement deletes a user account. Let’s look at an example of how this
is done. Suppose Michael Stone tells us that he won’t return from his
leave of absence because he has found a new job. We would execute the
following to delete his user account:

DROP USER 'michael_stone'@'localhost';


If you use an older version of MySQL (i.e., before 5.0.2), you
must first revoke all privileges before you drop the user account. This
requires executing REVOKE ALL ON *.* FROM
' user'@' host'

and then DROP USER
' user'@' host'

Some users, like Lena, may have more than one personal user account.
So we should check to see whether there are any other accounts associated
with Michael Stone. Unfortunately, there isn’t a SHOW USERS
statement. Instead, we’ll have to check the user table in the
mysql database like this:

SELECT User, Host
FROM mysql.user
WHERE User LIKE '%michael%'
OR User LIKE '%stone%';

| User                | Host        |
| mstone              | mstone_home |
| michael_zabbalaoui  | localhost   |

It seems that Michael Stone has another user account related to his
home IP address. After confirming that it’s his user account, we’ll drop
it like so:

DROP USER 'mstone'@'mstone_home';

When you drop a user account, if the user account is logged in and
has active sessions running, it won’t stop the sessions. The active
sessions will continue for the user account until the user exits or
they’ve been idle so long that they end. However, you can shut down a
user’s activities sooner. First, you will need to get the process
identifier for the session. You can do this be executing the


*************************** 4. row ***************************
      Id: 11482
    User: mstone
    Host: mstone_home
      db: NULL
 Command: Query
    Time: 78
   State: init
    Info: SELECT * FROM `birds`
Progress: 0.000

These are trimmed results, but we can see that
mstone@mstone_home has an active connection even
though we’ve dropped this user account. We’re concerned that he’s
selecting data from our databases from his home, even though he no longer
works for us and isn’t intending on returning. We can kill this process by
executing the following:

KILL 11482;

Notice that we used the process identification number from the
results of the SHOW PROCESSLIST statement. The SHOW
statement requires the PROCESS privilege,
and the KILL statement requires the user account to have the SUPER privilege to execute it. Now that
that session has been killed and his user accounts have been dropped, he
can no longer access our databases. For good measure, we should remove his
account from our server at the operating system level, a topic beyond the
scope of this book.

Changing Passwords and Names

For better security, it’s a good idea to change the passwords for
user accounts regularly, especially for accounts with administrative
privileges. How to change passwords is covered in the next subsection. A
user may ask, or you may want to rename a user account. This isn’t done as
often, although it could be another security precaution. However, when you
change a name or a password, you should be mindful of whether the user
account name and password are incorporated into any scripts, in particular
ones that run automatically to make backups of the databases. You’ll have
to change them in those scripts, as well.

Setting a User Account Password

In the examples throughout this chapter, we have created user accounts
without passwords or given them passwords when creating the user
accounts. You will occasionally need to change the password for a user
account, and actually should do so regularly for good security. To do
this, use the SET PASSWORD statement with the PASSWORD() function to encrypt the
password given.


As of version 5.6, you can force a user to change their password
by expiring it. For this, you would use the ALTER USER statement with the PASSWORD EXPIRE clause like

ALTER USER 'admin_granter'@'localhost' PASSWORD EXPIRE;

The next time the user tries to log in or execute an SQL
statement, he will receive an error message instructing him to change
his password. He’ll have to use the SET PASSWORD
statement to do that, before any other SQL statements can be

Let’s change the password for the
admin_granter@localhost user account:

SET PASSWORD FOR 'admin_granter'@'localhost' = PASSWORD('some_pwd_123');

That’s not a very good password. Let’s change the password to
something more complicated, such as
P1ed_Avoce7-79873. For an extra security measure,
we’ll use our personal computer to encrypt that password before logging
onto the server to set it in MySQL. From a local computer, we’ll execute
the following from the command line, assuming MySQL is running
on it:

mysql -p --skip-column-names --silent \
      --execute="SELECT PASSWORD('P1ed_Avoce7-79873')"


The result returned by the statement is the encrypted password.
We’ll copy that, log into the server, and use it to change the password
for admin_granter@localhost, like so:

SET PASSWORD FOR 'admin_granter'@'localhost' =

This will immediately update the privileges cache for the new
password. Try that on your server and then see whether you can log in
with the P1ed_Avoce7-79873 password.


If you forget the root password, there’s an
easy way to reset it. First, create a simple text file with this text,
each SQL statement on one line:

UPDATE mysql.user SET Password=PASSWORD('new_pwd') WHERE User='root';

Name this file something like rt-reset.sql and put it in a protected
directory. Then start MySQL from the command line using the
--init-file option like so:

mysqld_safe --init-file=/root/rt-reset.sql &

Once it’s started, log into MySQL to confirm the password has
changed. You can change it again, if you want. Then delete the
rt-reset.sql file, and if you
want, restart MySQL without the --init-file

Renaming a User Account

A username can be changed with the RENAME USER statement.
This SQL statement can change the username and the host
for the user account. The user account that you use to rename another
user account needs to have the CREATE USER privilege, as well as the
UPDATE privilege for the mysql

In order to see how the RENAME USER statement works,
let’s rename the lena_stankoska@lena_stankoska_home
user account to, assuming
she is the owner of that domain and will access our databases from it.
Do that by entering the following:

RENAME USER 'lena_stankoska'@'lena_stankoska_home'
TO 'lena'@'';

When you do this, all of the privileges related to
lena_stankoska@lena_stankoska_home will be changed
for the new username and host. Let’s check that by executing the

SHOW GRANTS FOR 'lena'@'';

| Grants for                                           |
| GRANT USAGE ON *.* TO 'lena'@'...' IDENTIFIED BY PASSWORD '...'              |
| GRANT SELECT ON `rookery`.* TO 'lena'@'...'                                  |
| GRANT SELECT ON `birdwatchers`.`eastern_birders_spottings` TO 'lena'@'...'   |
| GRANT SELECT ON `birdwatchers`.`membership_prospects` TO 'lena'@'...'        |
| GRANT SELECT ON `birdwatchers`.`survey_answers` TO 'lena'@'...'              |
| GRANT SELECT ON `birdwatchers`.`surveys` TO 'lena'@'...'                     |
| GRANT SELECT ON `birdwatchers`.`survey_questions` TO 'lena'@'...'            |
| GRANT SELECT ON `birdwatchers`.`eastern_birders` TO 'lena'@'...'             |
| GRANT SELECT ON `birdwatchers`.`prospects` TO 'lena'@'...'                   |
| GRANT SELECT ON `birdwatchers`.`prize_winners` TO 'lena'@'...'               |
| GRANT SELECT ON `birdwatchers`.`possible_duplicate_email` TO 'lena'@'...'    |
| GRANT SELECT ON `birdwatchers`.`birdwatcher_prospects_import` TO 'lena'@'...'|
| GRANT SELECT (membership_type, human_id, name_last, formal_title, name_first)|
|       ON `birdwatchers`.`humans` TO 'lena'@'...'                             |
| GRANT SELECT ON `birdwatchers`.`bird_identification_tests` TO 'lena'@'...'   |
| GRANT SELECT ON `birdwatchers`.`birdwatcher_prospects` TO 'lena'@'...'       |
| GRANT SELECT ON `birdwatchers`.`bird_sightings` TO 'lena'@'...'              |
| GRANT SELECT ON `birdwatchers`.`birding_events` TO 'lena'@'...'              |
| GRANT SELECT ON `birdwatchers`.`random_numbers` TO 'lena'@'...'              |

This user account has many entries in the grants tables. This is
because we gave it some privileges based on the tables and one based on
columns, in addition to privileges at the database level. What’s
important here is that all of these privileges have been changed for the
user account when we renamed it and changed the host for it.

User Roles

Creating multiple user accounts for one person is a bit tiresome. Imagine if
you were the administrator for an organization with many users similar to
Lena Stankoska. You would have to create a few user accounts for each of
them. If a user needed certain privileges for a short period of time,
perhaps covering for someone on vacation, you would have to grant them
extra privileges and later revoke the privileges. It can be plenty of work
to manage user accounts like these, leading eventually to sloppy security
policies (e.g., granting too many privileges) and ineffective controls
(e.g., poor monitoring of user accounts). There’s a better way to do

An alternative method, called user roles, was
introduced in version 10.0.5 of MariaDB. It’s not available in MySQL. User
roles allow you to a create a higher-level concept, a
role, and grant it to specific user accounts. The
user accounts would have their normal privileges for daily use, but when
they need to perform an unusual task requiring special privileges, they
can temporarily assume the role you’ve created for them. When they’re
done, they can unassume the role. It’s very convenient. Let’s look at an
example of how you would do this.

Earlier, we created for Lena a user account called
admin_import with the FILE privilege for her to be able to
execute the LOAD DATA INFILE statement. She’ll use this
to import data from text files into our databases. This SQL statement and
the process involved is covered in Chapter 15.
Suppose there are two other users—Max Mether and Ulf Sandberg—who
occasionally need to do this task. Rather than create extra user accounts
for Max and Ulf, in addition to Lena, we could give Max and Ulf the
password for admin_import. But that would be an
unprofessional security method. Instead, we’ll use the CREATE
statement to create a role that we’ll name,
admin_import_role and then grant that role to Max and

Enter the following if you have MariaDB installed on your

CREATE ROLE 'admin_import_role';

TO 'admin_import_role'@localhost;

The first SQL statement creates the role. The next uses the GRANT statement to grant the
FILE privilege that this role will need to import files into
the databases. Now let’s grant this role to Max and Ulf—assuming they
already have user accounts. We would enter this on the MariaDB

GRANT 'admin_import_role' TO 'max'@localhost;
GRANT 'admin_import_role' TO 'ulf'@localhost;

Now Max and Ulf can assume the role of
admin_import_role when they need it. Max, for
instance, would enter the following while he’s logged into MariaDB to do

SET ROLE 'admin_import_role';



As you can see here, Max set his role to
admin_import_role and then executed the LOAD
statement—I removed the details of that SQL statement
and any others he might execute so that we can focus just on the user
role. Then Max set his role to NONE to unassume the


One drawback with roles is that they may be used only for the
current session. This makes it difficult to use with an external utility
such as mysqldump. If
you run the mysql client from the
command line to set the role for your user account and then exit
mysql or open a different terminal to
execute the mysqldump, the dump would
be in a new client session and wouldn’t have the assumed role. So you
wouldn’t have the privileges you need.

User roles work well and are much easier than creating many user
accounts and setting passwords and privileges for each. They’re ideal for
granting someone a role temporarily. They make the management of user
accounts and privileges easier for you as an administrator. For users,
they will need to enter only one username and password for all of their
activities. They will need only to assume a role when necessary. Of
course, you will have to rely on each user to assume the role only when
necessary, and to reset the role to NONE


When you first start as a database administrator, you may have a
tendency to create a minimal number of user accounts—you may even try to
use only the root user account. However, you should
learn not to use root and to instead use various user
accounts. You should also learn to give each person at least one personal
user account—try not to allow sharing of user accounts, if practical.
Additionally, learn to give access only to databases and tables that are
needed by each user account and only the privileges needed. This may be
tedious, but it’s a good security practice—not just to protect sensitive
data, but to protect data from being lost and schema being changed or
deleted inadvertently.

There are several options related to user accounts and security that
we did not discuss. Some options limit the number of connections at a time
or per hour for a user account. There are several functions for encrypting
and decrypting strings that may be used for passwords. You probably won’t
need these often, especially not as a newcomer to MySQL and MariaDB.
However, you can find more information on them in my book,
MySQL in a
, or on the MySQL Resources site.


Although you can easily refer back to this chapter for the syntax
REVOKE, and DROP USER, you should try to learn
them well without having to do so every time. The SHOW GRANTS
statement can help you to remember the syntax. Still, if you know these
SQL statements well, you will be more likely to tweak user account
privileges. Otherwise, you might resort to using the same user accounts
for everyone in your database department and giving each user account all
privileges. The exercises here are therefore intended to make you more
familiar and comfortable with these SQL statements. However, you will need
to discipline yourself to always maintain good policies about managing
user accounts and privileges.

  1. Log onto your server and use the CREATE USER
    statement to create an administrative user account with the username
    admin_boss and the host localhost.

    Then use the GRANT statement to give this account
    ALL privileges on the rookery and
    birdwatchers databases, and the SUPER
    privilege to be able to change server settings. Also give the account
    the GRANT OPTION rights, covered in User Account to Grant Privileges. You may have to use the
    GRANT statement more than once. Be sure to use the
    IDENTIFIED BY clause at least once to set the password
    for the user account.

    When you’ve finished creating this user account, exit MySQL and
    try to log in again with the admin_boss user account to
    be sure the password was entered correctly. Try using this user
    account instead of root for now on.

  2. While logged into the server as admin_boss,
    use the GRANT statement to create a user named
    sakari for the localhost. Assign the user account
    only the SELECT, INSERT, and
    UPDATE privileges on the rookery and
    birdwatchers databases. Be sure to give the user account
    a password. Do all of this in one GRANT statement. When
    you’re finished, exit MySQL.

    Log into MySQL with the sakari@localhost
    user account you created. Execute the SHOW DATABASES
    statement to make sure you see only the two default databases and our
    two databases. Execute a SELECT to get a list of rows
    from the humans table in the birdwatchers
    database. Use the INSERT statement to insert one row with
    minimal data. Then use the UPDATE statement to change the
    data in at least one column for the row you added. You should be able
    to do all of this. If you can’t, log in as
    admin_boss and use SHOW GRANTS to
    see how the permissions look for
    sakari@localhost. Fix whatever is wrong or
    missing and test the user account again.

    Now try to delete the row you added with DELETE,
    while logged in with the sakari@localhost user
    account—not admin_boss. You shouldn’t be able to
    do that with this user account.

  3. While logged into the server as admin_boss, use the
    REVOKE statement to revoke the INSERT and
    UPDATE privileges from the
    sakari@localhost user account you created in the
    second exercise. When finished, exit MySQL.

    Log into MySQL with the sakari@localhost
    user account. Try to use the INSERT statement to insert
    another row in the humans table. You shouldn’t be able to
    do this. If sakari still has the user privilege,
    log back into MySQL with admin_boss and determine
    what you did wrong when you executed the REVOKE statement
    and fix it. Then try again to insert a row using

  4. Log into the server with admin_boss and
    change the password for the sakari@localhost user
    account (this was covered in Changing Passwords and Names). When finished, log out of

    Log in with sakari, using the new password.
    Then press the up arrow key on your keyboard a few times. Check
    whether you can you see the sakari@localhost
    password in one of the entries. If so, this means that other users may
    also be able to see the password. Exit MySQL when finished

    From the command line using the mysql client on
    your personal computer—preferably not on the server—execute the
    SET statement, using the PASSWORD()
    function to get an encrypted password for
    sakari@localhost. Set a different password. For
    an example of how to do this, refer to Changing Passwords and Names.

    Log into the server with admin_boss and
    change the password for sakari@localhost using
    the encrypted password without the PASSWORD() function
    and plain text this time. Then log out and back in as
    sakari with the new password. Press the up arrow a few
    times to see that it shows the new password encrypted and not in plain
    text this time.

  5. Log into the server with admin_boss and use
    the DROP USER statement to drop the
    sakari@localhost user account. Then log out and
    try logging in as sakari. You shouldn’t be able
    to do that.

Comments are closed.