create mysql user with command line

Question

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;

thank you for editing my post. how do you edit to have a code block show up as code ? I can only manage 1 line at a time. — anatak
– anatak, Commented Jul 26, 2014 at 0:59

Nir Alfasi · Accepted Answer · 2014-07-26 00:52:48Z

2

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.*"

answered Jul 26, 2014 at 0:52

Nir Alfasi

53.6k11 gold badges94 silver badges138 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

create mysql user with command line

1 Answer 1

Comments

Your Answer

Collectives™ on Stack Overflow

create mysql user with command line

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related