realLifeinfo

Mariadb and Mysql database user account management

Nerd, Solutions 
  

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.

Sponsored

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: 

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:

Code example:

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:

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:

 

 The above code example will remove admin from access the system while in local machine (localhost).

Sponsored

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:

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: 

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.

Code example:

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.

OR

We can also revoke some privilege from user. In order to achieve this we will use this syntax:

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. 

Code example:

Change password for user

We can use the SET PASSWORD statement to change user's password.

Code example: 

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.

Code example:

This above statement will rename the user 'admin'@'localhost' into a new user 'root'@'localhost'

Sponsored