trying to log into correct database without password

Question

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?

Answer 1

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.

• Tan: Thanks for answer. When I ran SHOW GRANTS after logging in as root user, I saw Grants for root@localhost | +----------------------------------------------------------------------------------------------------------------------------------------+ | GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*829353BIGPASSWORDHEREF977' WITH GRANT OPTION | | GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION
• Tan: Based on your answer, was it supposed to show GRANT USAGE only?
• RolandoMySQLDBA: Yes. Doing CREATE USER does not give out any grants. So, USAGE is the only grant. You would have to execute GRANT commands on that new user thereafter.
• Tan: Thanks. One more question. Suppose I am inserting a user into the database table from PHP , how can I make sure that the user shows up after running the command select * from mysql.user; . Right now only those users shows up which are created using CREATE USER command. Because of PHP related user insertion using INSERT INTO DATABASE command , I am unable to run grant command.