In today's blog I'm going to show you how you can easily manage users in mysql and also grant them the proper privileges so that you can securely perform the basic operations for every single schema without affecting other schema even if your script is vulnerable.
It's best practice to create a distinct user with proper privileges on every single schema so that if there is a vulnerable script that shares the same credentials, it wouldn't affect other schemas which reduces risks.
Before you begin this tutorial:
I'm assuming that you have the root access to your server
Prerequisites: sql root user
In many cases while you install mysql server it prompts for mysql root user but sometimes it doesn't. If it didn't prompt you for a root password, follow the below chapter to login to mysql as root (while you don't have it).
Login to your VPS as a root
sudo -i
cd to you ~ home dir (root home dir is located in /root/ )
cd ~
add a file called .my.cnf which will contain a username and password for your mysql root password
nano .my.cnf
you .my.cnf should look like this
[client] user = root password = xxx
where 'xxx' is your mysql root password
now you can login to mysql using this credentials
mysql -u root -p xxx
once you have access to the root account, make sure to update the password for mysql user root password
update user set authentication_string = PASSWORD('place_your_password_here') where user = 'root';then you need to flush privileges:
flush privileges
Now you are done with the root user.
Create a specific user for specific database:
I assume that you have a created schema that is called 'emad' and we want to create a user that has full privileges on that schema, full privileges means that this user can (alter the schema ddl, insert/select/update/delete operations are also available and many other privileges that I'l summarize later)
create user:
CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'user_password';
note: make sure to replace the 'newuser' that you want and 'user_password' with the ones you need.
the part 'localhost' in the above statement means that the created user will have NO ACCESS out site the site itself, i.e. you won't be able to access this user remotely and only scripts located on your VPS will have access to this user.
If, say, you want to access to this user by another host/machine so replace 'localhost' with the IP of your remote machine, The full statement should look like:
CREATE USER 'newuser'@'10.10.10.10' IDENTIFIED BY 'user_password';
where '10.10.10.10' is your remote IP that have access to this user. If in a specific scenario the don't have a fixed IP address then of course it's not a reliable solution to change the user credentials frequently and the solution is to allow access from ANY WHERE. This setting, of course, has its security effects so don't change it unless you know what you are about to do. Below is the full statement for an everywhere-accessible user creation:
CREATE USER 'newuser'@'%' IDENTIFIED BY 'user_password';
You can of course use wild cards to allow access from specific range of IPs but actually this is beyond our blog scope.
After creating the user you should grant the proper permissions for that specific user. Permissions/Privileges like:
- Create
- drop
- delete
- insert
- update
- select
or you can use 'ALL PRIVILEGES' to allow all of the above privileges and more
if, for a specific reason you want to only allow this user to select data and not manipulating nor editing the data so you should only grant him a select and insert:
grant select, insert on 'emad'.* to 'username'@'localhost'
- you can replace 'select, insert with any number of permissions you want'
- 'emad'.* means all the tables of that schema. If you want the permissions to be on a specific table say 'tbl_salary' then change 'emad'.* to => 'emad'.'tbl_salary'
- you can replace the schema name 'emad' to astrisk '*' to grant the user permissions on all existing schemas
- you can grant different permissions on different tables for one specific schema
with these variables you can always customize the permissions you like on the schemas you want to secure.
one more exmaple
grant all privileges on '*'.'*' to 'username'@'*'
which will grant the user 'username' all access and manipulation permissions on all existing schemas on the server, and the user is accessible from anywhere (remotely and locally).
Note: If the grant command above doesn't work properly, make sure to add identified by 'user_choosen_password'.
At this point we are done creating permissions. Now if you want to see permissions and privileges for a specific user
list all available users on the server
use mysql; select user from user;
mysql> select user from user; +------------------+ | user | +------------------+ | debian-sys-maint | | mysql.session | | mysql.sys | | root | | emads | +------------------+
then list the permissions of one of the previously shown users
SHOW GRANTS FOR 'USER_NAME'@'LOCALHOST'
mysql> show grants for 'root'@'localhost'; +---------------------------------------------------------------------+ | Grants for root@localhost | +---------------------------------------------------------------------+ | GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION | | GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION | +---------------------------------------------------------------------+ 2 rows in set (0.00 sec)
To show privileges for all user remove what is after the word 'for':
SHOW GRANTS
You can always add other privileges to an existing user with the same stated commands.
You can also remove a specific privilege from a user with 'revoke' command like:
revoke select on 'schema_name'.* to 'username'@'localhost'
or remove all privileges from single schema table for user 'username':
revoke all privileges on 'schema_name'.'single_table_name' to 'username'@'localhost'
Finally, to remove a user from the server:
drop user 'username'@'localhost'
Let me know if there is any blogs you want me to write about