Member Avatar for tanha

Hi.
I want to GRANT privileges on two tables, but I dont know why error, but on one table it is ok. what is wrong?

GRANT SELECT ON mysql.db, mysql.user TO 'user'@'localhost' IDENTIFIED BY 'password';

I dont know why it is not working on two tables???

  • 3 Contributors
  • 4 Replies
  • 101 Views
  • 1 Week Discussion Span
  • Latest Post Latest Post by tanha

Dani AI

Generated

: the failure is not a bug in the client — it results from the GRANT syntax. As noted, MySQL expects a single privilege level in the ON clause (for example global, database, or a single table). That means a comma-separated list of objects is not accepted.

Fixes and practical notes:

  • Grant per object (one GRANT per table) or grant at the database level (db_name.*) if access to all tables in the schema is acceptable.
  • Prefer creating the account explicitly first (CREATE USER) and then granting privileges; modern MySQL releases favor that workflow over creating users inside GRANT statements. See the MySQL documentation for details on GRANT and CREATE USER: GRANT syntax and CREATE USER.

Security and troubleshooting reminders:

  • Granting SELECT on mysql.* tables (in particular mysql.user) exposes sensitive data (account rows and hashed credentials). Avoid giving read access to MySQL grant tables unless strictly necessary; prefer INFORMATION_SCHEMA views or dedicated procedures for needed data: INFORMATION_SCHEMA.
  • If an error persists, check the exact error text and server version. Common causes are insufficient privileges to issue GRANT, using deprecated syntax for the server version, or attempting to grant to a nonexisting user. GRANT changes take effect immediately; direct edits to grant tables require FLUSH PRIVILEGES, but GRANT/REVOKE do not.

Recommended Answers

All 4 Replies

Member Avatar for tanha

Hi.
any idea ???

Member Avatar for mwasif

what is the error message?

Member Avatar for tesuji

hi tanha,

standard SQL only allows one object per GRANT command. Possibly MySql also does not allow enumerative objects. If so, you should try

GRANT SELECT ON mysql.db TO 'user'@'localhost' IDENTIFIED BY 'password';
GRANT SELECT ON mysql.user TO 'user'@'localhost' IDENTIFIED BY 'password';

krs,
tesu

Member Avatar for tanha

Thanks for the infor ..

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.