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.
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
Stankoska:
CREATEUSER'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
section.
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
bit.
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
this:
DROPUSER'lena_stankoska'@'localhost';DROPUSER'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.
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.
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 accounts.
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 lena_stankoska@lena_stankoska_home.
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:
CREATEUSER'lena_stankoska'@'localhost'IDENTIFIEDBY'her_password_123';GRANTUSAGEON*.*TO'lena_stankoska'@'lena_stankoska_home'IDENTIFIEDBY'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
USER 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'
IDENTIFIED BY PASSWORD ' *B1A8D5415ACE5AB4BBAC120EC1D17766B8EFF1A1'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
database.
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
the SELECT, INSERT, and
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:
GRANT SELECT, INSERT, UPDATE ON rookery.*
TO 'lena_stankoska'@'localhost';
GRANT SELECT, INSERT, UPDATE ON birdwatchers.*
TO 'lena_stankoska'@'localhost';
SHOW GRANTS FOR 'lena_stankoska'@localhost \G
*************************** 1. row ***************************
Grants for lena_stankoska@localhost:
GRANT USAGE ON *.*
TO 'lena_stankoska'@'localhost'
*************************** 2. row ***************************
Grants for lena_stankoska@localhost:
GRANT SELECT, INSERT, UPDATE ON `birdwatchers`.*
TO 'lena_stankoska'@'localhost'
*************************** 3. row ***************************
Grants for lena_stankoska@localhost:
GRANT SELECT, INSERT, UPDATE ON `rookery`.*
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:
GRANT DELETE ON rookery.*
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:
GRANT USAGE ON *.*
TO 'lena_stankoska'@'localhost'
*************************** 2. row ***************************
Grants for lena_stankoska@localhost:
GRANT SELECT, INSERT, UPDATE, DELETE ON `birdwatchers`.*
TO 'lena_stankoska'@'localhost'
*************************** 3. row ***************************
Grants for lena_stankoska@localhost:
GRANT SELECT, INSERT, UPDATE, DELETE ON `rookery`.*
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.
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 columns.
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.
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'
IDENTIFIED BY PASSWORD '*B1A8D5415ACE5AB4BBAC120EC1D17766B8EFF1A1'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 rookery.eu --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
following:
GRANT SELECT ON rookery.*
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
executes SHOW DATABASES and a SELECT
statement to get a list of Avocet birds from the command
line:
mysql --user lena_stankoska --password='her_password_123' --host rookery.eu \
--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 |
+---------------------+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
tables.
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 rookery.eu \
--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:
#!/bin/shmysql_connect="mysql --user root -pmy_pwd"results=`$mysql_connect --skip-column-names \--execute 'SHOW TABLES FROM birdwatchers;'`items=$(echo$results|tr" ""\n")foritemin$itemsdoif[$item='humans']||[$item='birder_families']||[$item='birding_events_children']thencontinuefi`$mysql_connect --execute "GRANT SELECT ON birdwatchers.$item \TO 'lena_stankoska'@'lena_stankoska_home'"`doneexit
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.
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
statement.
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:
GRANTSELECT(human_id,formal_title,name_first,name_last,membership_type)ONbirdwatchers.humansTO'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.
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.
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
required.
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:
CREATEUSER'admin_backup'@'localhost'IDENTIFIEDBY'its_password_123';GRANTSELECT,LOCKTABLESONrookery.*TO'admin_backup'@'localhost';GRANTSELECT,LOCKTABLESONbirdwatchers.*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.
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
indexes.
Because a dump file can include SQL statements to alter tables
to set the collation, the ALTER privilege may be
needed.
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
TABLES 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
server:
CREATEUSER'admin_restore'@'localhost'IDENTIFIEDBY'different_pwd_456';GRANTINSERT,LOCKTABLES,CREATE,CREATETEMPORARYTABLES,INDEX,ALTERONrookery.*TO'admin_restore'@'localhost';GRANTINSERT,LOCKTABLES,CREATE,CREATETEMPORARYTABLES,INDEX,ALTERONbirdwatchers.*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.
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
secure_file_priv variable.
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:
CREATEUSER'admin_import'@'localhost'IDENTIFIEDBY'another_pwd_789';GRANTFILEON*.*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.
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
databases:
GRANTALLPRIVILEGESONrookery.*TO'admin_granter'@'localhost'IDENTIFIEDBY'avocet_123'WITHGRANTOPTION;GRANTALLPRIVILEGESONbirdwatchers.*TO'admin_granter'@'localhost'IDENTIFIEDBY'avocet_123'WITHGRANTOPTION;
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:
GRANTCREATEUSERON*.*TO'admin_granter'@'localhost';GRANTSELECTONmysql.*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 client:
mysql --user admin_granter --password=avocet_123 SELECT CURRENT_USER() AS 'User Account'; +-------------------------+ | 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.
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:
REVOKEALLPRIVILEGESONrookery.*FROM'michael_stone'@'localhost';REVOKEALLPRIVILEGESONbirdwatchers.*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:
REVOKEALTERONrookery.*FROM'admin_restore'@'localhost';REVOKEALTERONbirdwatchers.*FROM'admin_restore'@'localhost';
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:
DROPUSER'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
'
and then user'@'host'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:
DROPUSER'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 following:
SHOW PROCESSLIST;
...
*************************** 4. row ***************************
Id: 11482
User: mstone
Host: mstone_home
db: NULL
Command: Query
Time: 78
State: init
Info: SELECT * FROM `birds`
Progress: 0.000These 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:
KILL11482;
Notice that we used the process identification number from the
results of the SHOW PROCESSLIST statement. The SHOW
PROCESSLIST 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.
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.
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
this:
ALTERUSER'admin_granter'@'localhost'PASSWORDEXPIRE;
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
executed.
Let’s change the password for the admin_granter@localhost user account:
SETPASSWORDFOR'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')"
*D47F09D44BA0456F55A2F14DBD22C04821BCC07BThe 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:
SETPASSWORDFOR'admin_granter'@'localhost'='*D47F09D44BA0456F55A2F14DBD22C04821BCC07B';
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:
UPDATEmysql.userSETPassword=PASSWORD('new_pwd')WHEREUser='root';FLUSHPRIVILEGES;
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
option.
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
database.
In order to see how the RENAME USER statement works,
let’s rename the lena_stankoska@lena_stankoska_home
user account to lena@stankoskahouse.com, assuming
she is the owner of that domain and will access our databases from it.
Do that by entering the following:
RENAMEUSER'lena_stankoska'@'lena_stankoska_home'TO'lena'@'stankoskahouse.com';
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 following:
SHOW GRANTS FOR 'lena'@'stankoskahouse.com';
+------------------------------------------------------------------------------+
| Grants for lena@stankoskahouse.com |
+------------------------------------------------------------------------------+
| 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.
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 this.
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
ROLE statement to create a role that we’ll name,
admin_import_role and then grant that role to Max and
Ulf.
Enter the following if you have MariaDB installed on your server:
CREATE ROLE 'admin_import_role'; GRANT FILE ON *.* 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
server:
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 this:
SET ROLE 'admin_import_role';
LOAD DATA INFILE
...
SET ROLE NONE;As you can see here, Max set his role to
admin_import_role and then executed the LOAD
DATA INFILE 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
role.
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
afterward.
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 Nutshell, or on the MySQL Resources site.
Although you can easily refer back to this chapter for the syntax
for using CREATE USER, GRANT,
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.
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.
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.
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
sakari.
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 MySQL.
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 checking.
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.
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.