Managing user permissions in MariaDB and MySQL is critical for controlling access to your database. Administrators can assign specific permissions to users, allowing them to interact with databases while ensuring data security. Permissions can be granted at different levels, such as databases, tables, and columns, depending on the tasks the user needs to perform.
The main command used to assign permissions is the GRANT statement. Permissions can vary from basic tasks like reading data with SELECT to more advanced actions like inserting, updating, or deleting records. Administrative privileges like creating and dropping databases can also be granted when necessary.
Granting permissions requires careful consideration. Providing too many privileges can expose the database to security risks. Best practices recommend assigning only the minimum necessary permissions for each user to perform their required tasks, maintaining a balance between functionality and security.
Steps to grant permissions to users in MariaDB or MySQL:
- Log in to the database as an administrator.
$ mysql -u root -p Enter password: ******** mysql>
Replace 'root' with your admin username if necessary.
- Create a new user if one does not already exist.
mysql> CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password'; Query OK, 0 rows affected (0.00 sec)
Replace 'newuser' with the desired username and 'localhost' with the appropriate hostname or '%' for any host.
- Grant the necessary permissions to the user.
mysql> GRANT SELECT, INSERT, UPDATE ON mydatabase.* TO 'newuser'@'localhost'; Query OK, 0 rows affected (0.00 sec)
Replace 'mydatabase' with the target database name, and 'newuser' with the username you created.
Parameter Description [privilege1, privilege2, …] A list of privileges to be granted to the user. Some common privileges include SELECT, INSERT, UPDATE, DELETE, CREATE, and DROP. [database_name.]table_name The name of the database and table for which the privileges are being granted. If the database name is omitted, the privileges will apply to all tables in the default database. 'username'@'hostname' The username and hostname of the user to whom the privileges are being granted. The hostname can be a wildcard (e.g., %) to grant privileges to all users connecting from any host. IDENTIFIED BY 'password' An optional clause that allows you to set a password for the user. - Specify the database or table for the user’s access.
mysql> GRANT ALL PRIVILEGES ON mydatabase.mytable TO 'newuser'@'localhost'; Query OK, 0 rows affected (0.00 sec)
Grant all privileges for a specific table in the database.
- Define the user's host.
mysql> GRANT ALL PRIVILEGES ON *.* TO 'newuser'@'%'; Query OK, 0 rows affected (0.00 sec)
This allows the user to connect from any host. Replace '%' with a specific hostname if required.
- Optionally, set or update the user's password.
mysql> SET PASSWORD FOR 'newuser'@'localhost' = PASSWORD('newpassword'); Query OK, 0 rows affected (0.00 sec)
Change 'newpassword' to the desired password.
- Review the permissions granted to the user.
mysql> SHOW GRANTS FOR 'newuser'@'localhost'; +----------------------------------------------------------------+ | Grants for newuser@localhost | +----------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'newuser'@'localhost' | | GRANT SELECT, INSERT, UPDATE ON `mydatabase`.* TO 'newuser'@'localhost' | +----------------------------------------------------------------+ 2 rows in set (0.00 sec)
Verify the permissions assigned to the user.
- Apply the changes by flushing privileges.
mysql> FLUSH PRIVILEGES; Query OK, 0 rows affected (0.00 sec)
Run this command to immediately apply the changes.
- Verify the user’s access by logging in as the user and performing the allowed tasks.
$ mysql -u newuser -p Enter password: ******** mysql>
Login to verify that the user can perform the tasks with their assigned permissions.
- Grant SELECT privilege on all tables in a database to a user.
mysql> GRANT SELECT ON mydatabase.* TO 'myuser'@'localhost' IDENTIFIED BY 'mypassword'; Query OK, 0 rows affected (0.00 sec)
- Grant multiple privileges on a specific table in a database to a user.
mysql> GRANT SELECT, INSERT, UPDATE, DELETE ON mydatabase.mytable TO 'myuser'@'localhost' IDENTIFIED BY 'mypassword'; Query OK, 0 rows affected (0.00 sec)
- Grant all privileges on a specific database to a user with wildcard host.
mysql> GRANT ALL PRIVILEGES ON mydatabase.* TO 'myuser'@'%' IDENTIFIED BY 'mypassword'; Query OK, 0 rows affected (0.00 sec)
- Grant all privileges on all databases to a user.
mysql> GRANT ALL PRIVILEGES ON *.* TO 'myuser'@'localhost' IDENTIFIED BY 'mypassword'; Query OK, 0 rows affected (0.00 sec)
Mohd Shakir Zakaria is an experienced cloud architect with a strong development and open-source advocacy background. He boasts multiple certifications in AWS, Red Hat, VMware, ITIL, and Linux, underscoring his expertise in cloud architecture and system administration.
Comment anonymously. Login not required.