Oracle 5.0, Reference Manual

Page: 635 / 3232

SQL-Based MySQL Account Activity Auditing
615
wildcards, whereas account values (as returned by
CURRENT_USER()
[959] ) may contain user name
and host name wildcards.
For example, a blank user name matches any user, so an account of
''@'localhost'
enables
clients to connect as an anonymous user from the local host with any user name. If this case, if a client
connects as
user1
from the local host,
USER()
[964] and
CURRENT_USER()
[959] return different
values:
mysql> SELECT USER(), CURRENT_USER();
+-----------------+----------------+
| USER() | CURRENT_USER() |
+-----------------+----------------+
| user1@localhost | @localhost |
+-----------------+----------------+
The host name part of an account can contain wildcards, too. If the host name contains a
'%'
or
'_'
pattern character or uses netmask notation, the account can be used for clients connecting from
multiple hosts and the
CURRENT_USER()
[959] value will not indicate which one. For example,
the account
'user2'@'%.example.com'
can be used by
user2
to connect from any host in
the
example.com
domain. If
user2
connects from
remote.example.com
,
USER()
[964] and
CURRENT_USER()
[959] return different values:
mysql> SELECT USER(), CURRENT_USER();
+--------------------------+---------------------+
| USER() | CURRENT_USER() |
+--------------------------+---------------------+
| user2@remote.example.com | user2@%.example.com |
+--------------------------+---------------------+
If an application must invoke
USER()
[964] for user auditing (for example, if it does auditing from
within triggers) but must also be able to associate the
USER()
[964] value with an account in the
user
table, it is necessary to avoid accounts that contain wildcards in the
User
or
Host
column.
Specifically, do not permit
User
to be empty (which creates an anonymous-user account), and do not
permit pattern characters or netmask notation in
Host
values. All accounts must have a nonempty
User
value and literal
Host
value.
With respect to the previous examples, the
''@'localhost'
and
'user2'@'%.example.com'
accounts should be changed not to use wildcards:
RENAME USER ''@'localhost' TO 'user1'@'localhost';
RENAME USER 'user2'@'%.example.com' TO 'user2'@'remote.example.com';
If
user2
must be able to connect from several hosts in the
example.com
domain, there should be a
separate account for each host.
To extract the user name or host name part from a
CURRENT_USER()
[959] or
USER()
[964] value,
use the
SUBSTRING()
[894] function:
mysql> SELECT SUBSTRING_INDEX(CURRENT_USER(),'@',1);
+---------------------------------------+
| SUBSTRING_INDEX(CURRENT_USER(),'@',1) |
+---------------------------------------+
| user1 |
+---------------------------------------+
mysql> SELECT SUBSTRING_INDEX(CURRENT_USER(),'@',-1);
+----------------------------------------+
| SUBSTRING_INDEX(CURRENT_USER(),'@',-1) |
+----------------------------------------+
| localhost |
+----------------------------------------+