SELECT command denied to user 'user'@'localhost' for table 'table'

Question

I have deployed my database on MySQL Database server. I have a user 'bedgeaj_root@localhost' and it has all the permissions. But it still giving me the error :

SELECT command denied to user 'bedgeaj_root'@'localhost' for table 'transactions'

While searching on the net, I studied that it is some user permission issue. But I don't understand that why it is giving this error as I have given all the permission to this user.

Here is the screen shot of my cPanel user permissions screen:

bedgeaj_root@localhost permissions screen

Here goes my stack trace:

   Caused by: net.sf.jasperreports.engine.JRException: Error executing SQL statement for : itemizedStatementReport_patientBalance
at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createDatasource(JRJdbcQueryExecuter.java:246)
at net.sf.jasperreports.engine.fill.JRFillDataset.createQueryDatasource(JRFillDataset.java:1073)
at net.sf.jasperreports.engine.fill.JRFillDataset.initDatasource(JRFillDataset.java:667)
at net.sf.jasperreports.engine.fill.JRBaseFiller.setParameters(JRBaseFiller.java:1235)
at net.sf.jasperreports.engine.fill.JRBaseFiller.fill(JRBaseFiller.java:859)
at net.sf.jasperreports.engine.fill.JRBaseFiller.fill(JRBaseFiller.java:804)
at net.sf.jasperreports.engine.fill.JRFillSubreport.fillSubreport(JRFillSubreport.java:652)
at net.sf.jasperreports.engine.fill.JRSubreportRunnable.run(JRSubreportRunnable.java:59)
at net.sf.jasperreports.engine.fill.JRThreadSubreportRunner.run(JRThreadSubreportRunner.java:205)
... 1 more


   Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: SELECT command denied to user 'bedgeaj_root'@'localhost' for table 'transactions'
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
at java.lang.reflect.Constructor.newInstance(Constructor.java:513)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:407)
at com.mysql.jdbc.Util.getInstance(Util.java:382)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1052)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3603)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3535)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1989)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2150)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2626)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2119)
at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:2281)
at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createDatasource(JRJdbcQueryExecuter.java:239)
... 9 more

I am using this query:

    SELECT alias.patient_id , SUM(alias.balance) AS balance
    from (
    SELECT v.patient_id,trx.trx_id,trx.trx_status,trx.trx_seq,pb.billing_id,v.visit_id,v.visit_dt,forma t(SUM(tl.net_amount) - ifnull((SELECT SUM(pl.applied_amount+pl.discount_amount) FROM payment_lines pl where pl.trx_line_id = tl.trx_line_id),0.0),2)AS balance
    FROM ((transactions trx JOIN trx_lines tl ON (trx.trx_id = tl.trx_id))JOIN patient_billing pb ON(pb.billing_id = trx.billing_id))JOIN visits v ON (pb.visit_id = v.visit_id)
    where trx.trx_seq='4' AND trx.trx_status='PENDING' AND (date_format(v.visit_dt,'%Y/%m/%d')<date_format(DATE_SUB(NOW(), INTERVAL 1 day),'%Y/%m/%d'))
    group by tl.trx_id) alias
    where alias.patient_id = $P{patient_id}

group by alias.patient_id]

What I am doing wrong?

"as I have given all the permission to this user." - doesn't appear that way. — Mitch Wheat, Aug 06 '13 at 10:30
I have assigned the user bedgea_root@localhost all the permissions including SELECT permission. All other queries run fine except one specific query. — Muhammad Salman Farooq, Aug 06 '13 at 10:32
It gives me following results : GRANT USAGE ON *.* TO 'bedgeaj_root'@'localhost' IDENTIFIED BY PASSWORD '*423473EB2836A 93BE438D8C5185F0CA50D47F1B0' | | GRANT ALL PRIVILEGES ON `bedgeaj\_medmax`.* TO 'bedgeaj_root'@'localhost' — Muhammad Salman Farooq, Aug 06 '13 at 10:52
Show us your query,sometimes a query error might be mistaken as a permission issue. — Mihai, Aug 06 '13 at 11:17
Ofcorse the transaction table is in the DB. My application uses the same table on for other different modules, and it runs fine there. But here, i am using that table in a report with some joins to other tables. — Muhammad Salman Farooq, Aug 06 '13 at 11:18
I now double checked the tables that I am using in the query that whether they exists in DB or not. They do exist in the Database. — Muhammad Salman Farooq, Aug 06 '13 at 11:37
If its just this query its clear that the query has a mistake in it,I think. — Mihai, Aug 06 '13 at 11:41
But if I run the same query through mysql workbench , it runs fine. Also it runs fine on my local system, but it is giving this error on the Database server of our production server(A linux based server). — Muhammad Salman Farooq, Aug 06 '13 at 11:53

score 5 · Answer 1 · edited May 23 '17 at 11:46

5

grant all privileges on bedgeaj_medmax.transactions to 'bedgeaj_root'@'%'  identified by 'password';

Try this one.

EDIT

Error: select command denied to user '<userid>'@'<ip-address>' for table '<table-name>'

See comment by pisces in the above link.

edited May 23 '17 at 11:46

Community

1
1

answered Aug 06 '13 at 10:46

Mihai

26,325
7
66
81

I have now provided the stack trace also. – Muhammad Salman Farooq Aug 06 '13 at 11:16
I now double checked the tables that I am using in the query that whether they exists in DB or not. They do exist in the Database. – Muhammad Salman Farooq Aug 06 '13 at 11:39

score 1 · Answer 2 · answered Dec 09 '14 at 17:10

I had a similar issue. My local database was created from a dump from another server and the local server didn't have all the right permissions for a user that was on the dev server. I got this error trying to backup my local database using sequel pro. Then I tried mysqldump and got a different error: Got error: 1449: The user specified as a definer ('joey'@'127.0.0.1') does not exist when using LOCK TABLES . So I granted all on . to joey@127.0.0.1 and it fixed it.

score 1 · Answer 3 · answered May 18 '16 at 16:11

I had a similar problem.
In my situation, the error appeared because MySQL is case sensitive in Linux!

See this:

In MySQL, databases correspond to directories within the data directory. Each table within a database corresponds to at least one file within the database directory (and possibly more, depending on the storage engine). Triggers also correspond to files. Consequently, the case sensitivity of the underlying operating system plays a part in the case sensitivity of database, table, and trigger names. This means such names are not case sensitive in Windows, but are case sensitive in most varieties of Unix.

If GRANT command was run before, changing tablename to TABLENAME/Tablename can solve the problem.

SELECT command denied to user 'user'@'localhost' for table 'table'

3 Answers3