Database Reference
In-Depth Information
+--------------------+
| 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 evenexecute 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 herthe
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 namein 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
statementto 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%';"
