User Accounts and Privileges - Learning MySQL and MariaDB - page 300

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 executethe 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 ac-
count later in this chapter.
Because we didn't specify a password for thisuser 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 re-
move it. We'll create this user account again later.
User accounts are removed throughthe 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 un-
derstand 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 state-
ment from your server:
SELECT User, Host
FROM mysql.user
WHERE User LIKE 'lena_stankoska';