Mysql – trying to log into correct database without password

MySQLmysql-5.6permissions

My SQL 5.6.31 is setup on RHEL 7.2 as follows:

I can log in using the root user account as follows:

mysql -u root -p
Enter Password:

I created two normal users (one via my command line and one via my PHP based application)

Normal User Created via Command Line:

CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'mypass';

Normal User Created via my PHP based Web App:

My PHP Web app is just inserting the username into the database (database name: practiceDB) using INSERT INTO command in the code. Say for
example normal user that got created and stored into the database has name peter

Scenario #1:

When I log in into the mysql from the command line using username jeffery and password mypass like the following:

mysql -u jeffery -pmypass

And run the command, show databases, I can see my practiceDB and other default MySQL databases like information_schema.

Scenario #2:

Since one of the normal user (peter) got created by the webapp and is residing in the database (practiceDB), I tried to access from the
commandline the user without using any password as follows:

mysql -u peter

I can get into the mysql command prompt. Infact, forget about specifying the existing username peter in the above commandline.
Even if I specify any thing , I can get into the command prompt. Also, when I checked show databases, I only saw only information_schema database
which is not where my user got stored.

Could anyone tell me why this is happening?

Best Answer

When you use CREATE USER, all it does is add a row to mysql.user with host set to localhost, user set to jeffrey or peter, and the password column is filled in. All the DB privileges are defaulted to 'N'.

When you connect, run this:

SHOW GRANTS;

It will simply say GRANT USAGE. That means you can connect, AND NOTHING ELSE !!!

When you login as root, you must now explicitly tell mysqld to give grant privileges at various levels.

EXAMPLES

This will let jeffrey do everything except give away his privileges

GRANT ALL PRIVILEGES ON *.* to 'jeffrey'@'localhost';

This will let jeffrey do everything, including give away his privileges

GRANT ALL PRIVILEGES ON *.* to 'jeffrey'@'localhost' WITH GRANT OPTION;

This will let jeffrey do everything inside a database called jeff

GRANT ALL PRIVILEGES ON jeff.* to 'jeffrey'@'localhost';

Next time you login as root, run the following

DESC mysql.user;
DESC mysql.db;

This will show you what global (mysql.user) and database (mysql.db) privileges look like.

Please read MySQL Documentation on all the grants you can give out to users.

Related Question