• Choose a category
• All
• Classic-Fiction

By Root 448 0

to add users by inserting rows into user directly, but to be safe this is generally not recommended. The tables used by MariaDB to store user account information (as well as table schemas and more) are extremely important, and any damage to them could seriously harm the MariaDB server. As such, it is always better to use tags and functions to manipulate these tables as opposed to manipulating them directly.

* * *

To rename a user account, use the RENAME USER statement like this:

Input

RENAME USER ben TO bforta;

Deleting User Accounts

To delete a user account (along with any associated rights and privileges), use the DROP USER statement as seen here:

Input

DROP USER bforta;

Setting Access Rights

With user accounts created, you must next assign access rights and privileges. Newly created user accounts have no access at all. They can log in to MariaDB, but they see no data and cannot perform any database operations.

To see the rights granted to a user account, use SHOW GRANTS FOR as seen in this example:

Input

SHOW GRANTS FOR bforta;

Output

+-------------------------------------------------+

| Grants for bforta@% |

+-------------------------------------------------+

| GRANT USAGE ON *.* TO 'bforta'@'%' |

+-------------------------------------------------+

Analysis

The output shows that user bforta has a single right granted, USAGE ON *.*. USAGE means no rights at all (not overly intuitive, I know), so the results mean no rights to anything on any database and any table.

* * *

Note: Users Are Defined As user@host

MariaDB privileges are defined using a combination of user name and hostname. If no hostname is specified then a default hostname of % will be used (effectively granting access to the user regardless of the hostname).

* * *

To set rights the GRANT statement is used. At a minimum, GRANT requires that you specify

• The privilege being granted

• The database or table being granted access to

• The user name

The following example demonstrates the use of GRANT:

Input

GRANT SELECT ON crashcourse.* TO bforta;

Analysis

This GRANT allows the use of SELECT on crashcourse.* (crashcourse database, all tables). By granting SELECT access only, user bforta has read-only access to all data in the crashcourse database.

SHOW GRANTS reflects this change:

Input

SHOW GRANTS FOR bforta;

Output

+-------------------------------------------------+

| Grants for bforta@% |

+-------------------------------------------------+

| GRANT USAGE ON *.* TO 'bforta'@'%' |

| GRANT SELECT ON 'crashcourse'.* TO 'bforta'@'%' |

+-------------------------------------------------+

Analysis

Each GRANT adds (or updates) a permission statement for the user. MariaDB reads all the grants and determines the rights and permissions based on them.

The opposite of GRANT is REVOKE, which is used to revoke specific rights and permissions. Here is an example:

Input

REVOKE SELECT ON crashcourse.* FROM bforta;

Analysis

This REVOKE statement takes away the SELECT access just granted to user bforta. The access being revoked must exist or an error will be thrown.

GRANT and REVOKE can be used to control access at several levels:

• Entire server, using GRANT ALL and REVOKE ALL

• Entire database, using ON database.*

• Specific tables, using ON database.table

• Specific columns

• Specific stored procedures

Table 28.1 lists each of the rights and privileges that may be granted or revoked.

Table 28.1 Rights and Privileges

Using GRANT and REVOKE in conjunction with the privileges listed in Table 28.1, you have complete control over what users can and cannot do with your precious data.

* * *

Note: Granting for the Future

When using GRANT and REVOKE, the user account must exist, but the objects being referred to need not. This allows administrators to design and implement security before databases and tables are even created.

A side effect of this is that if a database or table is removed (with a DROP statement) any associated access rights will still exist. And if the database or table is re-created