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

Database Components and Privileges
Now we'll turn to theparts 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.
NOTE
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 assur-
ing 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'
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 ofthe 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 |