解决MySQL数据库报1142(42000)ALTER/REFERENCES command denied to user没权限问题

MySQL数据库报错的关键信息有“ALTER command denied to user”、“REFERENCES command denied to user”、“code=1142, state=42000”，具体如下：

Caused by: <openjpa-3.2.0-r6f721f6 nonfatal general error> org.apache.openjpa.persistence.PersistenceException: ALTER command denied to user 'xxx'@'192.168.1.102' for table 'james_mail' {stmnt 942649169 ALTER TABLE JAMES_MAIL ADD FOREIGN KEY (MAILBOX_ID) REFERENCES JAMES_MAILBOX (MAILBOX_ID) ON DELETE CASCADE} [code=1142, state=42000]
Caused by: org.apache.openjpa.lib.jdbc.ReportingSQLException: REFERENCES command denied to user 'xxx'@'192.168.1.102' for table 'james.james_mailbox' {stmnt 545707113 ALTER TABLE JAMES_MAIL ADD FOREIGN KEY (MAILBOX_ID) REFERENCES JAMES_MAILBOX (MAILBOX_ID) ON DELETE CASCADE} [code=1142, state=42000]

可以看出是账号没有ALTER/REFERENCES等权限造成的，解决办法就是缺什么权限授权什么就可以了。

#查看权限
show grants for '账号名'@'%';
#授权
GRANT ALTER,REFERENCES ON 数据库命.* TO '账号名'@'%';
flush privileges;

另外，具体还有哪些权限可以通过官网文档查看。

其中MySQL8.0版Privileges Provided by MySQL分静态（Table 6.2 Permissible Static Privileges for GRANT and REVOKE）和动态（Table 6.3 Permissible Dynamic Privileges for GRANT and REVOKE）两种权限，主要区别就是动态权限是定义在运行时，静态权限是内置在服务区中（Dynamic privileges are defined at runtime, in contrast to static privileges, which are built in to the server. ）。

而MySQL5.7版Privileges Provided by MySQL只用一种（Table 6.2 Permissible Privileges for GRANT and REVOKE）权限。