Database Reference
In-Depth Information
User Account Basics
In this topic,I have used the term
user account
several times instead of just
user
. This was
done to distinguish a person from the combination of a username and the location or host
from which the user may access the MySQL or MariaDB server.
For instance, the
root
user has full access to all databases and all privileges, but only when
connecting from the
localhost
. The
root
user is not allowed to access the server
through a remote host, such as through the Internet. That would be a major security vulner-
ability. At a minimum, access and privileges are based on the combination of the
user
and
its
host
, which is called the user account.
As the
root
user, you can create a user account withthe
CREATE USER
statement. Here's
an example using this SQL statement to create a user account for a woman named Lena
Stankoska:
CREATE
USER
'lena_stankoska'
;
In this example, we're just creating the user account without giving it any privileges. To see
the privileges a user account has, usethe
SHOW GRANTS
statement like this:
SHOW GRANTS FOR 'lena_stankoska';
+--------------------------------------------+
| Grants for lena_stankoska@% |
+--------------------------------------------+
| GRANT USAGE ON *.* TO 'lena_stankoska'@'%' |
+--------------------------------------------+
Notice that these results are in the form of an SQL statement. Instead of using the
CREATE
USER
statement, you can enter a
GRANT
statement exactly as shown in the results. Let's
pull apart the results here, but a bit in reverse order.
The user is
lena_stankoska
and the host is thewildcard,
%
. The wildcard was used because
we didn't specify a host when we created the user. Any privileges that will be granted to
this user account will be permitted from any host. This is not a good idea. You should al-
ways specify a host. For our examples, to start, we'll use localhost. We'll look at setting the
host in the next section.
The
*.*
part in the results says that usage is granted for all databases and tables — the
part before the period refers to databases, and the part after the period refers to tables. In
order to limit usage to a specific database or table, you would have to change that part to
database.table
. We'll look at that in a bit.
