Databases Reference
In-Depth Information
Which User Is Connected?
So far, we have created several users and deliberately chosen a different name for each.
We did this to avoid discussing an important issue: what happens if more than one
user and host combination matches when a connection is attempted?
To understand how MySQL allows connections, connect as the MySQL user
root
and
create two users with the same name and different host specifications:
mysql>
GRANT SELECT ON music.* TO 'dave'@'%' IDENTIFIED BY '
the_password
';
Query OK, 0 rows affected (0.06 sec)
mysql>
GRANT ALL ON music.* TO 'dave'@'localhost' IDENTIFIED BY '
the_password
';
Query OK, 0 rows affected (0.01 sec)
The first user
dave
can now connect from any host and run only
SELECT
statements on
the
music
database. The second user
dave
is specific to the
localhost
and is allowed all
privileges on
music
. The host specifications of the two users overlap:
'%'
means all hosts,
and so includes
localhost
as one of the allowed hosts. Now, let's experiment with
dave
.
Let's connect to the server using the monitor installed on
localhost
:
$
mysql --user=dave --password=
the_password
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 57 to server version: 5.0.22-standard-log
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql>
Now, let's try to do more than just
SELECT
data:
mysql>
USE music;
Database changed
mysql>
INSERT INTO artist VALUES (8, "The Psychedelic Furs");
Query OK, 1 row affected (0.06 sec)
That worked, so we must be logged in as the user
'dave'@'localhost'
and not
'dave'@'%'
.
Here's what the
CURRENT_USER( )
function reports for the connection we've just used:
mysql>
SELECT CURRENT_USER()
;
+----------------+
| CURRENT_USER() |
+----------------+
| dave@localhost |
+----------------+
1 row in set (0.00 sec)
This confirms we're logged in as
'dave'@'localhost'
, and not through the more general
host specification
'dave'@'%'
.
