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

Exercises
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 state-
ments. 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 adminis-
trative 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 data-
bases. 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 data-
bases and our two databases. Execute a SELECT to get a list of rows from the hu-
mans 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 permis-
sions look for sakari@localhost . Fix whatever is wrong or missing and test the
user account again.