Mariadb and Mysql database user account management
As a root user, you can create and delete users in MYSQL or MARIADB RDBMS. Also root user can perform other administrative tasks like rename users' name, provide and revoke some administrative privileges to other users.
These all Can be accomplished by MYSQL and MARIADB statements in a very simple and understandable syntax.
Creating new user
As root user, if you want to add/create another user into MYSQL or MARIADB RDBMS you need to use the CREATE USER statement. The syntax for CREATE USER statement is like this: CREATE USER 'admin'@'localhost'
This is the basic of create user statement. The above statement will create new user with the username admin, which will allow to access the database while at localhost.
The above created user will access the database without prompted for password. To create a new user with the password that he/she will be using while login into RDBMS, we need to include IDENTIFIED BY 'user_password' at the end of CREATE USER statement. Example:
- CREATE USER 'admin'@'localhost' IDENTIFIED BY 'mynewpassword'
This above created user statement will create a new user with admin as username and mynewpassword as his/her password.
This statement will only create a user that can only login into the system, but can't access any data inside, because we haven't provide any privilege to him/her.
Grant privilege to MYSQL and MARIADB users
We need to provide privilege to new user in order for them to store and manipulate data that are stored in the database. We use the GRANT statement to achieve this.
The GRANT statement syntax:
- GRANT privilege list ON database.table TO 'username'@'host'
- GRANT ALL ON test_db.* TO 'admin'@'localhost'
The above code example will grant all privileges to admin as a system user, when access the system from a local machine (localhost).
We can view user privileges by using this statement, code examples:
- SHOW GRANTS FOR 'username'@'host'
- SHOW GRANTS FOR 'admin'@'localhost'
Delete user from the system
If we want to remove a user from the system, as root user we can. We can use DROP USER statement to accomplish this. Code example:
- DROP USER 'username'@'host'
- DROP USER 'admin'@'localhost'
The above code example will remove admin from access the system while in local machine (localhost).
Restricting the access of user accounts
As MYSQL or MARIADB administrator, we may give users full access to database from anywhere or we can limit them based on various aspects.
You can view all valid privileges that can be assigned to user by issuing this SQL command : SHOW PRIVILEGES
in the following example we give admin SELECT, UPDATE, DELETE and INSERT privileges, code example:
- GRANT SELECT, UPDATE, DELETE, INSERT ON test_db.table_example TO 'admin'@'localhost'
You can include as many privilege as you want in the comma seprated list, or you can use ALL keyword to provide all privileges to user.
To ddd more privilege to user we simply execute the GRANT statement with the privilege:
- GRANT CREATE ON test_db.table_example TO 'admin'@'localhost'
This statement will add CREATE privilege to admin@localhost, which has already have SELECT, UPDATE, DELETE, and INSERT privileges.
Restricting to specific columns
We can restrict access to only certain columns in the table. To give a user account access only to specific columns, we issue a GRANT statement listing all of the columns permitted for the table within parenthesis, in a comma separated list after the privilege for which they apply.
- GRANT SELECT(id, username, state) ON test_db.users TO 'admin'@'localhost'
This grant statement only grants select SQL command to be issued by admin@localhost to table users, which is located inside the test_db database. And admin@localhst will only be able to select id, username, and state column from users table.
Revoking user privileges
As root user you can revoke user privileges to the system. You can remove their ability to either SELECT, DELETE, UPDATE, or INSERT. This is achieved by utilizing the REVOKE statement. We can remove all privileges from user or we can remove some of them. Code example revoke all privileges from admin@localhost.
- REVOKE ALL PRIVILEGES FROM 'admin'@'localhost'
- REVOKE ALL PRIVILEGES ON test_db.table FROM 'admin'@'localhost'
We can also revoke some privilege from user. In order to achieve this we will use this syntax:
- REVOKE UPDATE, SELECT ON test_db.table FROM 'admin'@'localhost'
This above revoke statement will remove update and select privileges from admin@localhost. Also as root user we can remove privileges from specific columns, this is achieved by specifying column name that we want to remove privilege from after the privilege we want to remove.
- REVOKE SELECT(id, username, state) ON test_db.table FROM 'admin'@'localhost'
Change password for user
We can use the SET PASSWORD statement to change user's password.
- SET PASSWORD FOR 'admin'@'localhost' = PASSWORD ('newpassword')
This statement will change the password for admin@localhost
Rename user account
As root user we can rename users' account into another username. To achieve this we use the RENAME USER statement.
- RENAME USER 'admin'@'localhost' TO 'root'@'localhost'
This above statement will rename the user 'admin'@'localhost' into a new user 'root'@'localhost'