MySQL DB access for user with roles base accesses



  Hi Folks,

🙏 Namaste and Welcome to my blog 🙏

Hope this finds you safe and healthy!

 

Today I am going to explain about if you are wanting to secure your Database server with proper user access-based policy. We can use MySQL role based policy,  A MySQL role is a named collection of privileges.

I will show you how to do that by creating roles and users, then associating those users with newly created roles.

Now each user will have different roles and access by that way a user like in read-only roles will not be able to delete anything as he is read only. In each role you can give access based on your project requirement. I will create all role and users form MySQL root user.

Before that we need to understand privileges, type provided by MySQL

Admin Privileges – It allow user to managed MySQL server. They are Global level access can be hold by lower level user or not specific to a database.

Database Privilege – This privilege applies to a database level and all object within that. It can be granted to specific database or globally, so they apply to all databases.

 

For detail you can refer to original page of MySQL site.
    https://dev.mysql.com/doc/refman/8.0/en/privileges-provided.html

 

Below are points which you need to follow one by one

·         I am assuming you have ssh user access to that DB server if not then you need to create a ssh user first with proper access (combination of username and password or username and ssh key is best I prefer, soon I will create a document for this also keep connected)

 

·         Once you are logged in to DB server with ssh user go to MySQL server by command

mysql -u root -p

(use your password once it asks for password) you can also use another user but that should have admin access.

·         Once you are in db you can check existing users in db by running following command

SELECT User FROM mysql.user;

·         It depends on you if you want to create user first or role first no issue you can choose anything, I am creating roles here 
create role 'db_own';

·         Now choose what type of access you want to provide to your role (For detail about privileges you can refer MySQL document

(https://dev.mysql.com/doc/refman/8.0/en/privileges-provided.html)

·         Now you must check your db name (my db name is api and role name db_own) on which you will provide access to this role by below commands I will show two three access how I am doing then you can repeat same for your one.

GRANT ALTER ON api.* TO 'db_own';

GRANT ALTER ROUTINE ON api.* TO 'db_own';

GRANT CREATE ON api.* TO 'db_own';

GRANT CREATE ROUTINE ON api* TO 'db_own';

 

·         Keep in mind we cannot provide Admin privileges to our role as I have already discussed above. But for better clarity I am giving some privileges example which can not be given on databases level

PROXY

CREATE TABLESPACE

FILE

PROCESS

RELOAD

REPLICATION CLIENT

REPLICATION SLAVE

SHOW DATABASES

USAGE

 

·         Next, I will create new user (you can use your existing user if you have already created)

CREATE USER 'jan'@'%' IDENTIFIED BY 'jan@123';

CREATE USER 'feb'@'%' IDENTIFIED BY 'feb@123';

 

·         Now we must grant these users access to role

grant 'db_own' to 'jan'@'%';

grant 'db_own' to 'feb'@'%';

 

·         Next, you must set role for user. One you can do only for session its kind of temporary each time you login to server you have to set role, second you can set fix role for user by set default role for user. I will show both command its upon you which one you want to choose and prefer 😊.

set role db_own;

(Note :- Keep in mind for using this command you first need to login that db server with this new user “jan” and then use database and now you can run this command it will set role db_own for this “jan” user)

 

·         As I already told, if you want to set default role for user every time and this user login to db server he will be associated with this role automatically I prefer this command.

set default role 'db_owner' to 'jan';

set default role 'db_owner' to 'feb';

 

·         I will only show example for two roles, but you can create as many you want. I will create next read_only role. I will not explain each line below because I have already explained above this is same its just a another example.

create role 'read_only';

 

GRANT SELECT ON api.* TO 'read_only';

GRANT SHOW VIEW ON api.* TO 'read_only';

 

CREATE USER 'mar'@'%' IDENTIFIED BY 'mar@123';

CREATE USER 'april'@'%' IDENTIFIED BY 'april@123';

 

grant 'read_only' to 'mar'@'%';

grant 'read_only' to 'april'@'%';

 

set default role 'read_only' to 'mar';

set default role 'read_only' to 'april';

 

·         By above command you have created a new role “read_only” with two new user “mar” and “april” which have been associated with this role now. Now “mar” “april” user have SELECT, SHOW VIEW access only on “api” db.

 

·         For final confirmation like everything is working as expected you just need to login to DB server and run following command which will login further user in DB then use required database and run permitted command and see their result you should be only allowed to run those command which is only allowed to access.

mysql -u jan -p

use api;

show databases;

show grants;

SHOW FULL PROCESSLIST;

 

·         I have shown below my excel sheet screenshot which is best way to categories accesses of each users with role-based policy. You first must create a excel sheet for each environment basis because each environment has different level of access requirement. Mention all details with role name username and the access which you are going to provide them so it will be easy to understand privileges of each users. I will show mine one for two env. DEV and QA)

·         As shown in above diagram I have created a proper sheet with each user access level and then defined roles for that each user and even that role have specific privileges.

 Cheers👍

Vik 
          Sr. Cloud-DevOps Engineer