Learning Mysql

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 inset sec)
This confirms we’re logged in as 'dave'@'localhost'
, and not through the more general host specification 'dave'@'%'

Download 4.24 Mb.

Share with your friends: