Most modern versions of MyQL replace the database_name with * in the grant privileges command after you select the database that you want to use. GRANT ALL PRIVILEGES ON *.* TO The GRANT ALL PRIVILEGES ON database_name.* TO command may not work for modern versions of MySQL. For me, I am using the root user that runs on the localhost host: use my_app_db We will need to first select the database before granting permission to it. When I try to backup the mysql table I get this error: mysqldump: Got error: 1142: SELECT,LOCK TABL command denied to user 'root''localhost' for table 'condinstances' when using LOCK TABLES Anyone seen that before I've seen some references to my mysql and my mysqldump being different versions but when I run which they are in the same. Next, based on the information gotten from the list, grant privileges to the user that you want. This should give you an output of this sort: +-+-+ Note: if you don't run the use mysql, you get the no database selected error. So we need to select the mysql database first: use mysql Unlike PostgreSQL this is often stored in the mysql database. Next, list out all the users and their host on the MySQL server. You can change root to the user you want to login with: mysql -u root -p GRANT ALL PRIVILEGES ON my_app_db.* TO get the error:ĮRROR 1064 (42000): You have an error in your SQL syntax check the manual that corresponds to your MySQL server version for the right syntax to use near 'my_app_db.* TO at line 1> I wanted to grant permissions of a database named my_app_db to the root user running on localhost host.īut when I run the command: use my_app_db I had this challenge when working on MySQL Ver 8.0.21 If you are not comfortable with the command line then you can use a client like MySQL workbench, Navicat or SQLyog Once you have finalized the permissions that you want to set up for your new users, always be sure to reload all the privileges. * TO asterisks in this command refer to the database and table (respectively) that they can access-this specific command allows to the user to read, edit, execute and perform all tasks across all the databases and tables. Therefore, the first thing to do is to provide the user with access to the information they will need. In fact, if newuser even tries to login (with the password, password), they will not be able to reach the MySQL shell. Sadly, at this point newuser has no permissions to do anything with the databases. Eternal gratitude.From MySQL command line: CREATE USER IDENTIFIED BY 'password' Please tell me I'm overlooking something obvious. I have tried reconnecting and restarting the server, to no avail. ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8 ) ENGINE=InnoDB AUTO_INCREMENT=657 DEFAULT CHARSET=utf8 Here are my tables: SHOW CREATE TABLE `isotemp` The foreign key command in question: alter table isotemp add foreign key(statusid) references `ISO-3166-2-status`.`id` GRANT ALL PRIVILEGES ON `geo`.* TO ALL PRIVILEGES ON `symfony`.* TO REFERENCES ON `geo`.`ISO-3166-2-status` TO REFERENCES ON `geo`.`isotemp` TO still I cannot create the foreign key. I then tried executing GRANT REFERENCES ON geo.ISO-3166-2-status to and GRANT REFERENCES ON geo.isotemp to which both executed without error, and the following is the output from the SHOW GRANTS command, as user symfony: GRANT FILE, REFERENCES ON *.* TO IDENTIFIED BY PASSWORD '************' When I connected as root the command GRANT REFERENCES ON geo.* TO executes without error, although the error message persists. I went to consult the docs at ( ) and noticed the REFERENCES privilege is documented as "Unused" in both table as column contexts. REFERENCES command denied to user for table 'ISO-3166-2-status.id' happened. I had added references to all previous tables in the same database with the same user before, but this time Error Code: 1142. I'm on MariaDB trying to set a foreign key on a table called isotemp referencing another table, called ISO-3166-2-status.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |