User Accounts and Privileges - Learning MySQL and MariaDB - page 302

Restricting the Access of User Accounts
As a databaseadministrator, you may give users full access to databases from anywhere, or
you can limit them based on various aspects of the connection and the database. Put simply,
you can restrict user access and privileges based on the username and host, the database
components (e.g., tables) the user account may access, and the SQL statements and func-
tions that may be used on those database components. We'll address these restrictions in
this section.
Username and Host
When you create user accounts, consider both who needs access and from where. First,
let's define who . This can represent a person or a group of people. You can give an indi-
vidual a username — which might be related to their actual name, such as lena_stankoska
for Lena Stankoska — or define a username to a group of people, such as sales_dept for the
Sales Department. You could also create a user account based on a function or use. In that
case, one person might have several user accounts.
If Lena Stankoska is a database administrator of the rookery and birdwatchers data-
bases, she might have multiple usernames, perhaps all from the localhost, for example,
lena_stankoska , for personal use; admin_backup , for when she makes backups; ad-
min_restore , for when she restores backups; and admin_import , if she regularly imports
large amounts of data.
Let's first create the personal accounts for Lena Stankoska. We'll create the administrative
accounts later. For her personal username, lena_stankoska , let's give her two user accounts:
one from localhost and another from a remote location. We'll give her more privileges
when she's logged into the localhost, but less when she accesses the server remotely —
from her home if she has a static IP address. Let's create for her lena_stankoska@localhost
and lena_stankoska@lena_stankoska_home .
The hostname for a user account can be a name that a DNS can translate to an IP address or
it can be an actual IP address. The DNS could be the server's external DNS, which trans-
lates Internet domain names to an IP address. Or you can use the bind system and put the
name in the server's hosts file (e.g., /etc/hosts on a Linux system). If you do that, you'll
have to restart MySQL for it to take effect.
Let's create these two personal user accounts for Lena. Enter the following SQL statements
on your server:
CREATE USER 'lena_stankoska' @ 'localhost'
IDENTIFIED BY 'her_password_123' ;