CODEWITHSUNDEEP
mysqlcommand-line
anatak
anatak

Reputation: 440

create mysql user with command line

I created a user with this command in mysql command line

GRANT ALL PRIVILEGES ON `db_name.*` to 'db_name_admin'@'localhost' IDENTIFIED BY 'password' WITH GRANT OPTION;

I connect to the db with 

mysql -u db_name_admin -p -h localhost

check to see if I am connectd

show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| db_name            |
+--------------------+
2 rows in set (0.00 sec)

then I change to use the right database with

use db_name

but when I do

show tables

I get 

empty set (0.00 sec)

when I login with the root account I can see all the tables and records in db_name. so I guess something is wrong with my GRANT query but I can not figure out what.

when I check the TABLE_PRIVILEGES I see

+-----------------------------+---------------+--------------+--------------+----------------+--------------+
| GRANTEE                     | TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME   | PRIVILEGE_TYPE | IS_GRANTABLE |
+-----------------------------+---------------+--------------+--------------+----------------+--------------+
| 'db_name_admin'@'localhost' | def           | db_name      | db_name.*    | SELECT         | YES          |
| 'db_name_admin'@'localhost' | def           | db_name      | db_name.*    | INSERT         | YES          |
| 'db_name_admin'@'localhost' | def           | db_name      | db_name.*    | UPDATE         | YES          |
| 'db_name_admin'@'localhost' | def           | db_name      | db_name.*    | DELETE         | YES          |
| 'db_name_admin'@'localhost' | def           | db_name      | db_name.*    | CREATE         | YES          |
| 'db_name_admin'@'localhost' | def           | db_name      | db_name.*    | DROP           | YES          |
| 'db_name_admin'@'localhost' | def           | db_name      | db_name.*    | REFERENCES     | YES          |
| 'db_name_admin'@'localhost' | def           | db_name      | db_name.*    | INDEX          | YES          |
| 'db_name_admin'@'localhost' | def           | db_name      | db_name.*    | ALTER          | YES          |
| 'db_name_admin'@'localhost' | def           | db_name      | db_name.*    | CREATE VIEW    | YES          |
| 'db_name_admin'@'localhost' | def           | db_name      | db_name.*    | SHOW VIEW      | YES          |
| 'db_name_admin'@'localhost' | def           | db_name      | db_name.*    | TRIGGER        | YES          |
+-----------------------------+---------------+--------------+--------------+----------------+--------------+

my database name uses a - sign the command should be 

GRANT ALL PRIVILEGES ON `db-name`.* to 'db_name_admin'@'localhost' IDENTIFIED BY 'password' WITH GRANT OPTION;

Upvotes: 1

Views: 294

Answers (1)

Nir Alfasi
Nir Alfasi

Reputation: 53545

Do: GRANT ALL PRIVILEGES ON db_name.* ... without the backticks

The backticks "freeze" what you type in there so you get permissions only for a table with the name "db_name.*"

Upvotes: 2

Related Questions