MySQL SELECT 命令拒绝用户 'user'@'localhost' 用于表 'table'

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

提问by Muhammad Salman Farooq

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 :

我已经在 MySQL 数据库服务器上部署了我的数据库。我有一个用户“bedgeaj_root@localhost”，它拥有所有权限。但它仍然给我错误：

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

SELECT 命令拒绝用户 'bedgeaj_root'@'localhost' 用于表 '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:

这是我的 cPanel 用户权限屏幕的屏幕截图：

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]

按 alias.patient_id 分组]

What I am doing wrong?

我做错了什么？

回答by Mihai

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>'

错误：选择命令拒绝用户 '<userid>'@'<ip-address>' 表 '<table-name>'

See comment by pisces in the above link.

请参阅上面链接中双鱼座的评论。

回答by superaesomedad

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 [email protected] and it fixed it.

我有一个类似的问题。我的本地数据库是从另一台服务器的转储创建的，本地服务器没有开发服务器上用户的所有正确权限。尝试使用 sequel pro 备份我的本地数据库时遇到此错误。然后我尝试了 mysqldump 并得到了一个不同的错误： Got error: 1449: The user specified as adefiner ('joey'@'127.0.0.1') does not exist when using LOCK TABLES 。所以我同意了。到 [email protected] 并修复它。

回答by ROMANIA_engineer

I had a similar problem.
In my situation, the error appeared because MySQLis case sensitivein Linux!

我有一个类似的问题。
在我的情况下，出现错误是因为MySQL在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 sensitivityof 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 sensitivein most varieties of Unix.

在 MySQL 中，数据库对应于数据目录中的目录。数据库中的每个表对应于数据库目录中的至少一个文件（可能更多，取决于存储引擎）。触发器也对应于文件。因此，区分大小写底层操作系统的起着数据库，表和触发器名称的情况下，灵敏度的一部分。这意味着这些名称在 Windows中不区分大小写，但在大多数Unix 版本中区分大小写。

If GRANTcommand was run before, changing tablenameto TABLENAME/Tablenamecan solve the problem.

如果GRANT之前运行过命令，tablename改为TABLENAME/Tablename即可解决问题。