Member Avatar for Cupidvogel

Hi, I installed MySQL from the MySQL site. The setup procedure required me to set an username and password, which I set as X and Y respectively (say). Then when I opened the mySQL command line client, it asked for password, and after providing Y, I was allowed in. Once in I created a new user with a password with this command:

CREATE USER 'CHRIS'@'localhost' IDENTIFIED BY 'CHRISSEC';

set up a database and populated it with tables ad values, and granted the user CHRIS selected privileges using the command:

GRANT SELECT
ON PLAYERS
TO CHRIS;

and then I logged out. However, next time when I tried to login, the password "CHRISSEC" didn't work, and I had to use the old password "Y" to login. Also two different users may have the same password, so just asking the password alone cannot let the database know which user is actually trying to log in. So how to make the database prompt with a required username and password, upon which only those privileges granted to him before will be available to him and nothing else?

  • 2 Contributors
  • 3 Replies
  • 2K Views
  • 3 Days Discussion Span
  • Latest Post Latest Post by cereal

Dani AI

Generated

What likely confused you is the Windows shortcut named "MySQL Command Line Client". That shortcut usually hardcodes a default user (often root) and only prompts for the password. To connect as a different account you must explicitly pass the username to the client, as suggested. Then the server authenticates that specific account and applies only its privileges.

A clean, repeatable setup looks like this (adjust the database name as needed):

-- create the account bound to the local machine
CREATE USER 'CHRIS'@'localhost' IDENTIFIED BY 'CHRISSEC';

-- grant only SELECT on the target object (better to qualify with db name)
GRANT SELECT ON `yourdb`.`PLAYERS` TO 'CHRIS'@'localhost';

-- verify what the account actually has
SHOW GRANTS FOR 'CHRIS'@'localhost';

Connect as that user:

mysql -u CHRIS -p
-- enter CHRISSEC at the prompt (safer than putting the password on the command line)

Quick checks once connected:

SELECT CURRENT_USER(), USER();   -- who MySQL thinks you are vs. how you connected

Tips that prevent head-scratchers:

  • The host part matters. 'CHRIS'@'localhost' can log in only from the same machine. If you need remote access, create an account like 'CHRIS'@'%' or a specific host/IP and grant to that one.
  • You do not need FLUSH PRIVILEGES after CREATE USER or GRANT; those commands take effect immediately.
  • Always reference objects with their database, e.g., yourdb.PLAYERS, to avoid granting on the wrong table.
  • If you often switch users, consider using a saved login path so you can do mysql --login-path=chris_local instead of typing credentials each time (available in modern MySQL clients).

Recommended Answers

All 3 Replies

Member Avatar for cereal

Did you tried this?

mysql -uCHRIS -pCHRISSEC
Member Avatar for Cupidvogel

Yeah yeah, I did and now it is working fine. There were some problems with the installation, that's why it wasn't working. Thanks.

Member Avatar for cereal

Ok, great!

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.