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

User Roles
Creating multipleuser accounts for one person is a bit tiresome. Imagine if you were the
administrator for an organization with many users similar to Lena Stankoska. You would
have to create a few user accounts for each of them. If a user needed certain privileges for a
short period of time, perhaps covering for someone on vacation, you would have to grant
them extra privileges and later revoke the privileges. It can be plenty of work to manage
user accounts like these, leading eventually to sloppy security policies (e.g., granting too
many privileges) and ineffective controls (e.g., poor monitoring of user accounts). There's
a better way to do this.
An alternative method, called user roles , was introduced in version 10.0.5 of MariaDB. It's
not available in MySQL. User roles allow you to a create a higher-level concept, a role , and
grant it to specific user accounts. The user accounts would have their normal privileges for
daily use, but when they need to perform an unusual task requiring special privileges, they
can temporarily assume the role you've created for them. When they're done, they can un-
assume the role. It's very convenient. Let's look at an example of how you would do this.
Earlier, we created for Lena a user account called admin_import withthe FILE priv-
ilege for her to be able to executethe LOAD DATA INFILE statement. She'll use this to
import data from text files into our databases. This SQL statement and the process involved
is covered in Chapter15 . Suppose there are two other users — Max Mether and Ulf Sand-
berg — who occasionally need to do this task. Rather than create extra user accounts for
Max and Ulf, in addition to Lena, we could give Max and Ulf the password for ad-
min_import . But that would be an unprofessional security method. Instead, we'll use the
CREATE ROLE statement tocreate a role that we'll name, admin_import_role and then
grant that role to Max and Ulf.
Enter the following if you have MariaDB installed on your server:
CREATE ROLE ' admin_import_role ';
GRANT FILE ON *.*
TO ' admin_import_role '@localhost;
The first SQL statement creates the role. The next usesthe GRANT statement to grant the
FILE privilege that this role will need to import files into the databases. Now let's grant
this role to Max and Ulf — assuming they already have user accounts. We would enter this
on the MariaDB server:
GRANT 'admin_import_role' TO 'max' @ localhost ;
GRANT 'admin_import_role' TO 'ulf' @ localhost ;