| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES ON `myequity_production`.* TO WITH GRANT OPTION |
#ERROR 1142 MYSQL CREATE VIEW PASSWORD#
| GRANT USAGE ON *.* TO IDENTIFIED BY PASSWORD '0f3d714d2e2a5422' |
doesn't this mean this user should have all privileges for this database). > create view test_view as select * from transactions ERROR 1142 (42000): CREATE VIEW command denied to user for table 'test_view'mysql>Īny ideas why? Note below in select * from db, all the fields are "Y" (i.e. > grant all on myequity_production.* to identified by 'password' with grant option In that instance, you will have to manage the grants of every single user the same way.I can't figure out why I get the following error. For the sake of continuity, you should avoid using wildcards at the database level if your want to manage the grants of a single user. The problem here is MySQL will not merge them. But yes, it looks like it's the order I suspected that already but is this the expected behaviour? I would expect MySQL to "merge" the permissions so I didn't have to worry about order, since the grants are made by a SP automatically in here. To prove that, just rerun SELECT * FROM mysql.db WHERE db='schema_user' AND user='user'\GĪfter doing the REVOKE and GRANT. This reverses the order in the mysql.db table. When you deleted the grants and reinserted them using REVOKE SELECT ON `schema_%`.* FROM user
This will display those grants in whatever order it was entered. To see this order, please run this query: SELECT * FROM mysql.db WHERE db='schema_user' AND user='user'\G
#ERROR 1142 MYSQL CREATE VIEW UPDATE#
GRANT INSERT, SELECT, UPDATE ON demo_priv.* TO test Īs "test": UPDATE demo_priv.tbl SET some_text='update' ĮRROR 1142 (42000): UPDATE command denied to user for table 'tbl'Īs root: REVOKE SELECT ON `demo_%`.* FROM test Ĭan anybody help me? I don't know if it's a bug or I'm understanding wrong the privileges in MySQL. INSERT INTO demo_priv.tbl(some_text) VALUES ('insert') ĬREATE USER test IDENTIFIED BY 'Password' Here's some script that you can use in order to "replay" my error:ĬREATE TABLE demo_priv.tbl (id INT(5) NOT NULL AUTO_INCREMENT PRIMARY KEY, some_text VARCHAR(50)) I tried with my local machine in 5.6.34 and got the same error, but tried again in VM Centos and same MySQL version and worked fine. This is happening with MySQL Community Server 5.6.36 in Centos. The thing now is, everytime I do FLUSH PRIVILEGES the order is back to the first and "user" can't UPDATE. BUT if I do: REVOKE SELECT ON `schema_%`.* FROM user Īnd check again the GRANTS, the order changed and now it's first the "full" GRANT: +-+Īnd I can UPDATE now without error: mysql> UPDATE schema_user.table SET field='some_text' | GRANT SELECT, INSERT, UPDATE, DELETE ON `schema_user`.* TO |Īnd I try (with user) mysql> UPDATE schema_user.table SET field='some_text' ĮRROR 1142 (42000): UPDATE command denied to user for table 'table' | GRANT USAGE ON *.* TO IDENTIFIED BY PASSWORD '*XXXXXXXXXXXXXXXXXXXXXX' | The thing is, when I give him the permissions, MySQL is giving "UPDATE command denied".Īfter long research, trying everything that came to my mind, I found that when the order is: +-+ There's an user that needs SELECT on all schemas starting with some text and INSERT, SELECT, UPDATE, DELETE on one schema. I'm having troubles with GRANTs when trying to use particular permissions on specific database.
0 kommentar(er)
