In this guide, we would look at various ways you can manage user's accounts and privileges in mariadb, If you are new to mariadb, you can read the following guide to follow the progression right from the start.
- Introduction To MariaDB
- [MariaDB] Guide To Creating Databases and Tables
- Altering MariaDB Tables
- Indexes In MariaDB
- Guide To Understanding INSERT Statement (MariaDB)
- Guide To Selecting Data In MariaDB
- Guide To Updating and Deleting Data In MariaDB
- Joining and Subquerying Data (MariaDB)
- List of Functions and Practical Uses In MariaDB
Let's start with the basics of creating a user account...
Basics of Creating a User Account
By default, mariadb already has the root account created as an administrative user, so, we would create a new account to make it separate from the root account, this way, others won’t be able to make use of the root account.
I guess you've run the mysql_secure_installation command when you installed mariadb, it handles the security of your MariaDB installation, for example:
- You can set a password for root accounts.
- You can remove root accounts that are accessible from outside the localhost.
- You can remove anonymous-user accounts.
- You can remove the test database, which by default can be accessed by anonymous users.
If you haven't done that already, then you can learn more about that here: Installing MariaDB (Ubuntu)
Back to creating accounts:
As the root user, you can create a user account with the CREATE USER statement. Here is a basic example, showing you how to create a user account:
CREATE USER 'devsrealm_guy';We are creating a user account without giving it any privileges, to check the privileges a user account has, you can use the SHOW GRANTS statement like so:
MariaDB [(none)]> SHOW GRANTS FOR 'devsrealm_guy';
+-------------------------------------------+
| Grants for devsrealm_guy@% |
+-------------------------------------------+
| GRANT USAGE ON *.* TO 'devsrealm_guy'@'%' |
+-------------------------------------------+
The GRANT USAGE means the devsrealm_guy user has no privileges - allow connect only. *.* - The first asterisk refers to the database, and the asterisk after the period refers to tables. In order words, you are granting the devsrealm_guy user no privileges on either the databases or tables from a remote host.
The percent symbol(%) represents a wildcard, you can log in from any network or location.
To limit it to a specific network, you can do:
CREATE USER 'devsrealm_guy'@'192.168.1.%';The % is a wildcard, which means it would match any number that follows the IP address.
Once you create a user account, you would generally then give it privileges. If you want to give an existing user account all privileges to be able to use all SQL statements from the localhost, you would execute the GRANT statement like so:
GRANT ALL on marn_melodic.*
To 'devsrealm_guy'@'localhost';
FLUSH PRIVILEGES;If you recall I said, the first asterisk represents the database, and the second one after the period represents the tables. In the above example, we are granting devsrealm_guy all privileges allowed on the marn_melodic database with no restrictions on the tables created in the marn_melodic database. However, we didn't grant the user ability to give a user account.
Always FLUSH PRIVILEGES when you use the GRANT and CREATE USER statement, this would reload the user privileges.
Note: The user would be able to use all SQL statements from the localhost.
If you get:
Can't find any matching row in the user tableThis is because we didn't create the user for our localhost, and thus we get the error, to confirm that the user for the localhost is in fact not created, you can take a look at what is stored in the user tabled in the mysql database:
SELECT User, Host FROM mysql.user WHERE USER LIKE 'devsrealm_guy';
+---------------+------+
| User | Host |
+---------------+------+
| devsrealm_guy | % |
+---------------+------+
You can either create a user for the localhost or change the GRANT statement like so:
GRANT ALL on marn_melodic.*
To 'devsrealm_guy'@'%';
FLUSH PRIVILEGES;The crazy thing here is that we didn't specify a password for devsrealm_guy user account, which means it can be accessed without a password. This can cause a major security risk: it can allow anyone who gets on to the server to do almost anything to the database, and it doesn’t require a password.
We would remove the account, and I'll show you how to create a user account coupled with the password right off the bat.
To drop a user account you do:
DROP USER 'devsrealm_guy'@'%';
FLUSH PRIVILEGES;To confirm the user account is actually dropped, we can take a look at what is stored in the user tabled in the mysql database:
SELECT User, Host FROM mysql.user WHERE USER LIKE 'devsrealm_guy';
It won't return anything, and that means, it has been DROPPED.
Restricting the Access of User Accounts Plus Managing User Creation
In this section, you'll learn how to create user accounts with restriction, be it restricting 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 functions that may be used on those database components.
Creating Username and Host
The things to put into consideration when creating a user account is who needs access and from where. The person that needs access might be a person, a group of users, and even a test account.
If you are creating a single user username, then you'll most create it by their name, e.g paul, and if it is for a group of people, you can do something like acct_dept for Account Department, eng_dept for Engineering Department, and the likes.
Suppose your organization just hired a new guy (we would use the name devsrealm_guy) to work on some of your databases, you can create two accounts for the user, one for localhost, and another from a remote location,
We would give the guy more privileges when logged into the localhost, and less privileges when accessing from a remote location.
Let's create the localhost account:
CREATE USER 'devsrealm_guy'@'localhost'
IDENTIFIED BY 'a_strong_password';To create the remote account, we can do:
CREATE USER 'devsrealm_guy'@'192.168.1.%'
IDENTIFIED BY 'a_strong_password';The IDENTIFIED BY clauses is used to set password for the user account.
Using the GRANT statement, we can see how it look:
SHOW GRANTS FOR 'devsrealm_guy'@'localhost';
+----------------------------------------------------------------------------------------------------------------------+
| Grants for devsrealm_guy@localhost |
+----------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'devsrealm_guy'@'localhost' IDENTIFIED BY PASSWORD '*8BB874FBC1BE0D620B5BE546BE235A5308E6737C' |
+----------------------------------------------------------------------------------------------------------------------+
As you can see, the password is encrypted. There is no way you can decrypt a password in mariadb, but you can change your password if you forget your password.
We've created two user account, one can log in from the server;localhost, and the other from a certain remote location.
We haven't given the user any access to the database, or the ability to create tables, selecting, updating, and deleting data. Let's see how we can do that:
Granting Privileges
We can create a set of permission with the Grant command, such as reading and writing data in the database and tables.
To give a user account multiple privileges, we do:
GRANT SELECT, INSERT, UPDATE ON marn_melodic.*
TO 'devsrealm_guy'@'localhost';Using the GRANT statement, we can see the privileges the user has:
MariaDB [(none)]> SHOW GRANTS FOR 'devsrealm_guy'@'localhost';
+----------------------------------------------------------------------------------------------------------------------+
| Grants for devsrealm_guy@localhost |
+----------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'devsrealm_guy'@'localhost' IDENTIFIED BY PASSWORD '*8BB874FBC1BE0D620B5BE546BE235A5308E6737C' |
| GRANT SELECT, INSERT, UPDATE ON `marn_melodic`.* TO 'devsrealm_guy'@'localhost' |
+----------------------------------------------------------------------------------------------------------------------+
We restricted the user the privilege to delete data, if for some reason, you want to allow that, you can do:
GRANT DELETE ON marn_melodic.* TO 'devsrealm_guy'@'localhost';
Let's see what we have:
MariaDB [(none)]> SHOW GRANTS FOR 'devsrealm_guy'@'localhost';
+----------------------------------------------------------------------------------------------------------------------+
| Grants for devsrealm_guy@localhost |
+----------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'devsrealm_guy'@'localhost' IDENTIFIED BY PASSWORD '*8BB874FBC1BE0D620B5BE546BE235A5308E6737C' |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `marn_melodic`.* TO 'devsrealm_guy'@'localhost' |
+----------------------------------------------------------------------------------------------------------------------+
Now, devsrealm_guy can manipulate data in all of the basic way on the marn_melodic database, but only from the localhost.
If you are interested in the Privileges you can give a user, you can use the SHOW PRIVILEGES statement to shows the list of system privileges that the MariaDB server supports. The exact list of privileges depends on the version of your server.
SHOW PRIVILEGES;
+-------------------------+---------------------------------------+-------------------------------------------------------+
| Privilege | Context | Comment |
+-------------------------+---------------------------------------+-------------------------------------------------------+
| Alter | Tables | To alter the table |
| Alter routine | Functions,Procedures | To alter or drop stored functions/procedures |
| Create | Databases,Tables,Indexes | To create new databases and tables |
| Create routine | Databases | To use CREATE FUNCTION/PROCEDURE |
| Create temporary tables | Databases | To use CREATE TEMPORARY TABLE |
| Create view | Tables | To create new views |
| Create user | Server Admin | To create new users |
| Delete | Tables | To delete existing rows |
| Drop | Databases,Tables | To drop databases, tables, and views |
| Event | Server Admin | To create, alter, drop and execute events |
| Execute | Functions,Procedures | To execute stored routines |
| File | File access on server | To read and write files on the server |
| Grant option | Databases,Tables,Functions,Procedures | To give to other users those privileges you possess |
| Index | Tables | To create or drop indexes |
| Insert | Tables | To insert data into tables |
| Lock tables | Databases | To use LOCK TABLES (together with SELECT privilege) |
| Process | Server Admin | To view the plain text of currently executing queries |
| Proxy | Server Admin | To make proxy user possible |
| References | Databases,Tables | To have references on tables |
| Reload | Server Admin | To reload or refresh tables, logs and privileges |
| Replication client | Server Admin | To ask where the slave or master servers are |
| Replication slave | Server Admin | To read binary log events from the master |
| Select | Tables | To retrieve rows from table |
| Show databases | Server Admin | To see all databases with SHOW DATABASES |
| Show view | Tables | To see views with SHOW CREATE VIEW |
| Shutdown | Server Admin | To shut down the server |
| Super | Server Admin | To use KILL thread, SET GLOBAL, CHANGE MASTER, etc. |
| Trigger | Tables | To use triggers |
| Create tablespace | Server Admin | To create/alter/drop tablespaces |
| Update | Tables | To update existing rows |
| Usage | Server Admin | No privileges - allow connect only |
+-------------------------+---------------------------------------+-------------------------------------------------------+
Restricting Access to Specific database
To give the user full access to the database, you can use the following:
GRANT ALL ON marn_melodic.*
TO 'devsrealm_guy'@'localhost';
In short, whatever you want grant, make sure you use the SHOW PRIVILEGES statement to see a list of privileges you can grant.
Restricting Access to Specific Table
To restrict privilege to a specific table in a database, you can do the following:
GRANT ALL ON marn_melodic.cmusic
TO 'devsrealm_guy'@'localhost';If you've already granted a user full privileges for a database, and you subsequently grant it for a specific table on the same database, make sure you revoke the first grant, or else, it doesn't make much sense.
Here is an example, using the SHOW GRANT statement:
MariaDB [(none)]> SHOW GRANTS FOR 'devsrealm_guy'@'localhost';
+----------------------------------------------------------------------------------------------------------------------+
| Grants for devsrealm_guy@localhost |
+----------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'devsrealm_guy'@'localhost' IDENTIFIED BY PASSWORD '*8BB874FBC1BE0D620B5BE546BE235A5308E6737C' |
| GRANT ALL PRIVILEGES ON `marn_melodic`.* TO 'devsrealm_guy'@'localhost' |
| GRANT ALL PRIVILEGES ON `marn_melodic`.`cmusic` TO 'devsrealm_guy'@'localhost' |
+----------------------------------------------------------------------------------------------------------------------+
Since we've granted all privileges on all the marn_melodic database, and tables, we can revoke that, and leave the other as is, I'll show you how to revoke a grant privilege in a moment.
To give her access to more tables in the marn_melodic database, we could execute a GRANT statement for each table.
That can be tedious with a database that has many tables, but that's how it is, if you know bash script, then you can create a script that loops through the list of tables, and grant them permission on each run.
Restricting Access to Specific Columns
To give a user account access only to specific columns, issue a GRANT statement listing all of the columns permitted for the table within parentheses, in a comma-separated list after the privilege for which they apply.
This is good in the sense that you can restrict access to sensitive information in your table, an example is email addresses, so, based on the list of columns you want to permit, you can do the following:
GRANT SELECT(user_id, name_first, name_last, membership_expiration)
ON marn_melodic.cmusic
TO 'devsrealm_guy'@'localhost';Now the user can access the cmusic table from the localhost to get the names of users, as well as the membership expiration of each user.
Again, when you are doing stuff like this, you should check what privileges the user already has.
Revoking Privileges
There may be times when you want to revoke a privilege that you gave to a user account. Maybe you gave a privilege by mistake, or you gave unnecessary privileges or you’ve changed your mind about which tables you want the user account to have access to.
The REVOKE statement revokes all or certain privileges that were granted to a user account.
Suppose you want to revoke all privileges for a specific user, you can do:
REVOKE ALL PRIVILEGES
ON marn_melodic.*
FROM 'devsrealm'@'localhost';This is similar to the GRANT statements that grants all privileges. The main difference is that instead of an ON clause, there’s a FROM to revoke privileges from a user account.
To revoke specific privileges, you can use the REVOKE statement with a comma-separated list after the keyword REVOKE. You can use the SHOW PRIVILEGES to see the privileges you can revoke, it is the same as for GRANT.
Here is an example:
REVOKE CREATE, ALTER
ON marn_melodic.*
FROM 'devsrealm'@'localhost';This revoke CREATE, and ALTER privileges for user devsrealm_guy at localhost on the marn_melodic database.
Deleting a User Account
The DROP USER statement deletes a user account, here is an example to remove to a user account:
DROP USER 'james'@'localhost';If you don't know what user you need to remove, you can always look into the mysql.user table like so:
SELECT user, host FROM mysql.user;
+---------------+-----------+
| user | host |
+---------------+-----------+
| dbuser | localhost |
| devsrealm_guy | localhost |
| root | localhost |
+---------------+-----------+
Then from there, you can know what to remove.
When you drop a user account, if the user account is logged in and has active sessions running, it won’t stop the sessions.
The active sessions will continue for the user account until the user exits or they’ve been idle so long that they end. To shut down a user's active process, you will need to get the process identifier for the session.
To get the active session running, you can do the following:
SHOW PROCESSLIST;
+----+------+-----------+------+---------+------+-------+------------------+----------+
| Id | User | Host | db | Command | Time | State | Info | Progress |
+----+------+-----------+------+---------+------+-------+------------------+----------+
| 30 | root | localhost | NULL | Query | 0 | init | SHOW PROCESSLIST | 0.000 |
+----+------+-----------+------+---------+------+-------+------------------+----------+
The info field would show what the user is currently doing, in this case, I am the user, and the last thing I ran is the SHOW PROCESSLIST.
Nevertheless, you can kill the process by executing the following:
KILL 30;We used the process identification number from the results of the SHOW PROCESSLIST statement to kill the process.
Changing Passwords and Names
You will always need to change a user password somehow, be it for better security or your defined policy.
The good thing about mariadb is that you can force a user to change a password by expiring it. You can do so like this:
ALTER USER 'admin_user'@'localhost' PASSWORD EXPIRE;The next time the admin_user tries to log in or execute an SQL statement, he will receive an error message instructing him to change his password.
He’ll have to use the SET PASSWORD statement to do that before any other SQL statements can be executed.
To change a user password, you can do the following:
SET PASSWORD FOR 'admin_user'@'localhost' = PASSWORD('a_strong_password');
FLUSH PRIVILEGES;A better approach for changing a password is logging into your personal computer, and encrypt the password which you would then copy over to your main server when changing the password. This ensures no one would be able to see the password in plain text.
From a local server, you can run the following to encrypt a password:
mysql -p --skip-column-names --silent \
--execute="SELECT PASSWORD ('a_strong_password')"
You would be prompted to enter the password for your mysql, something like the following:
Enter password:
*56145FE44E9515E46BF98ACB9118FBC1FF597A9B
The result returned by the statement is the encrypted password. We’ll copy that, log into the server, and use it to change the password for admin_user@localhost, like so:
SET PASSWORD FOR 'admin_user'@'localhost' = '*56145FE44E9515E46BF98ACB9118FBC1FF597A9B';
FLUSH PRIVILEGES;I didn't add the PASSWORD(' ') anymore because it is already encrypted.
Change password for root
If you forget the root password, there’s an easy way to reset it. First, create a simple text file with this text, each SQL statement on one line:
UPDATE mysql.user SET Password=PASSWORD('a_new_strong_pass') WHERE User='root';
FLUSH PRIVILEGES;Name the file something like root-rest.sql, and put it in a private protected directory.
Start MariaDB from the command line using the --init-file option like so:
mysqld_safe --init-file=/protect_directory/root-reset.sql &Then, log into mariadb to confirm the change.
Renaming a User Account
A username can be changed with the RENAME USER statement. This SQL statement can change the username and the host for the user account.
To rename a user, you can:
RENAME USER 'devsrealm_guy'@'localhost'
TO 'the_devsrealm_guy'@'localhost';All the privileges related to devsrealm@localhost will be changed for the new username and host, lets check:
SHOW GRANTS FOR 'the_devsrealm_guy'@'localhost';
+--------------------------------------------------------------------------------------------------------------------------+
| Grants for the_devsrealm_guy@localhost |
+--------------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'the_devsrealm_guy'@'localhost' IDENTIFIED BY PASSWORD '*8BB874FBC1BE0D620B5BE546BE235A5308E6737C' |
| GRANT ALL PRIVILEGES ON `marn_melodic`.* TO 'the_devsrealm_guy'@'localhost' |
| GRANT ALL PRIVILEGES ON `marn_melodic`.`cmusic` TO 'the_devsrealm_guy'@'localhost' |
+--------------------------------------------------------------------------------------------------------------------------+
This would conclude the guide on managing users and privileges in mariadb. You can also go through this guide: Managing MariaDB Databases (Ubuntu Server) for related reading.