users in Mysql


I have Mysql under Ubuntu.

I'm using the root user to connect to MySql and also to create my database, tables, etc.

# mysql -u root -p<password>

I guess this is not recommended for security reasons.

What is the normal way and what users do you recommend to administrate Mysql, to create database, access, creating, view, backups and so on?

Thanks in advance.
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

are you in sudo mode before you try  

You can use whatever user in your system you want to. But I'd recommend using a designated SQL admin user. Below some tips from MySQL Administrator Best Practices.

Administering Users and Granting Privileges

In larger organizations, DBAs would typically be responsible for implementing a complete security strategy and privilege system to prevent possible security breaches. Regardless of whose responsibility it is, adopting a proactive approach is crucial to preventing violations. The graphical interface of MySQL Administrator leverages the full power of MySQL's user management and privilege capabilities and significantly simplifies and reduces the time it takes to set up Access Control Lists and grant privileges. The graphical interface provides a simple method for adding users, assigning passwords and includes user profile information. Assigning and revoking global, database, and table/column privileges is as easy as adding and removing privilege items from an available list.

Our most important piece of advice on this topic is the learn and use the powerful authentication and privilege system. Some specific guidelines include:

    *      Do not have an empty root password. Otherwise anyone can connect as root without a password and be granted all privileges.
    *      Always use passwords and make sure that passwords are not plain text passwords. If your computer becomes compromised, the intruder can take full advantage of passwords and use them.
    *      Give permissions only as needed and use different logins for different applications.

      Note: From a performance point of view it better to have table level permissions. Column level permissions tend to slow things down a bit.
    *      Users are granted privileges on a "per host" basis. peter@myhost has nothing to do with peter@yourhost. You should avoid granting users privileges to all hosts unless absolutely necessary.

      Note: Users, usernames, and passwords have nothing to do with OS users. You can specify any user name you want.
    *      Avoid having MySQL Server open to the Internet. Remote exploits are uncommon, but why risk it when it can be easily avoided.
lulonAuthor Commented:
I can access with the root password when I am with my personal OS user or with root OS user.
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

lulonAuthor Commented:
I just want to create two MySql users.

-One user to be able to create my database (tables, indexes, etc). And to be able to drop it and create it again if needed.

-One user to be able to access with it through my application web, which will do selects, inserts, deletes, updates, etc in those tables in the database.

I guess that is enough for security, right?
Yes, it should be enough secure, just select long complex passwords.

Adding users in MySQL:

Are you using phpMyAdmin? It's an easy way to administer MySQL.
lulonAuthor Commented:
Yes, I'm using it, but I didn't find that...
lulonAuthor Commented:
Should I use the root user to create/drop my database or is there any security reason why I should use another user for that?

you should create a dedicated user with all privileges and grant option for your administration tasks for the simple reason that this user should be know by other people than those who access the machine.

That imposes a difference between root user who by default has administration privileges and this new user who will replace it. You can name it whatever you want and his password will be known by few people.

The other user will be the one with select, insert, delete, update, create temporary tables privileges (and probably some others) to be used in your web application context.

hope this helps.
lulonAuthor Commented:
Should the user which will be in charge of creating/droping the dabase and tables, has the same privileges than the root user?

In the table mysql.user I have 3 rows for the root user, the Host changes. And the other columns are the privileges, aren't they?
use SHOW GRANTS FOR <user>@<host> to get details on an account.

about admin account you gonna create, it's simply expected to replace root user. Root user should the, be disabled/removed.

the other account is website one with the privileges I mentionned.
lulonAuthor Commented:
user root removed??? are you sure about that??
lulonAuthor Commented:
I'm not an expert of MySql but I don't think I should remove the root user.

I need the root user to be able to manage the tables in mysql database.

What I want is to create a user to create/drop only one database, the one for my application web.

and another user to select/insert/delete/update the tables in that database.

How can I do/set/configure that?
the minimum restriction is to remove root account with no password in order to prevent access of a user simply root logged on the machine.

It's really frequent to remove/rename/disable root/sa user since it should no be used. It's not mandatory, the goal is simply to prevent natural login from the machine.

Hope I'm clear enough.
be careful, once again I don't tell you to drop it without any preparation. the goal is to replace root user with a new user+password and same privileges.

you can build this user with GRANT statement as follows :


more info on :

this user will own all privileges on every objects and will be able to GRANT himself rights to other users.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
lulonAuthor Commented:
ok, I got you now, but I already set a password for the root user.

Do you think then that I should create my database and tables with the root password and then create another user to be able to select/delete/update the tables in the database?
lulonAuthor Commented:
I just created this user with the root user:

mysql> CREATE USER '<myuser>'@'' IDENTIFIED BY '<mypassword>';
mysql> CREATE USER '<myuser>'@'<mylaptop>' IDENTIFIED BY '<mypassword>';
mysql> CREATE USER '<myuser>'@'localhost' IDENTIFIED BY '<mypassword>';

and I created my database with the root user:

mysql> CREATE DATABASE <mydatabase>;

Which GRANT options should I set to the user <myuser> to be able to select/delete/update the tables in <mydatabase>?

Note that I also use SSL connections
lulon: everything is pretty well explained in the link I provided, you should read it carefully. Grants needed for your user are naturaly named.

they are : SELECT, DELETE, UPDATE which gives something like :

GRANT SELECT, DELETE, UPDATE ON <mydatabase>.* TO '<myuser>'@'<mylaptop>';
GRANT SELECT, DELETE, UPDATE ON <mydatabase>.* TO '<myuser>'@'';
GRANT SELECT, DELETE, UPDATE ON <mydatabase>.* TO '<myuser>'@'localhost'; will probably never used since you are connecting through unix socket (I suppose).
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.