CODEWITHSUNDEEP
mysql
Trevor
Trevor

Reputation: 2206

Granting permission to a user on mysql

I am trying to create a user that is allowed to access mysql from any ip address.

I login with mysql -uroot -p

and then issue:

CREATE USER 'user1'@'%' IDENTIFIED BY 'password';
Query OK, 0 rows affected (0.00 sec)

grant usage on *.* to user1@'%' identified by 'password';
Query OK, 0 rows affected (0.00 sec)

flush privileges;
ERROR 1146 (42S02): Table 'mysql.servers' doesn't exist

service mysqld restart

and I can then login with the user, but when I issue the USE command I get an error

mysql> use db

ERROR 1044 (42000): Access denied for user 'user1'@'%' to database 'db'

I'm not sure what I am doing wrong

Upvotes: 0

Views: 101

Answers (2)

Lorenz Lo Sauer
Lorenz Lo Sauer

Reputation: 24740

Try upgrading / repairing the MySQL tables

mysql_upgrade -T -u root -p

Alternatively try creating (as root, the table servers) using the database mysql

CREATE TABLE `servers` (
`Server_name` char(64) NOT NULL,
`Host` char(64) NOT NULL,
`Db` char(64) NOT NULL,
`Username` char(64) NOT NULL,
`Password` char(64) NOT NULL,
`Port` int(4) DEFAULT NULL,
`Socket` char(64) DEFAULT NULL,
`Wrapper` char(64) NOT NULL,
`Owner` char(64) NOT NULL,
PRIMARY KEY (`Server_name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='MySQL Foreign Servers table';

Upvotes: 0

Peter van der Wal
Peter van der Wal

Reputation: 11856

USAGE is synonym for no privileges. Give the user SELECT-privileges or whatever is needed for that user. BTW: you don't need to restart mysqld after changing privileges.

Upvotes: 1

Related Questions