I cannot help look at that bug report and wonder the following: Since you have DEFINER= 192.168.12.197, it is possible that is behaving like 192.168.12.197 ? I say this because according to the MySQL Documentation on CREATE VIEW: At view definition time, the view creator must have the privileges needed to use the top-level objects accessed by the view. my.cnf you need to make sure that user and password are under the section If you do not see twice, then you are not authenticating correctly. Please run this command mysql -ANe"SELECT USER(),CURRENT_USER()" ~/my.cnf is really /root/.my.cnf, perhaps you are not logged in as Linux root. ~/my.cnf and still getting an error, you might be hitting this situation in Bug #70907 mysqldump: Couldn't execute 'show table status': SELECT command denied to user ' You could also specify the password too mysqldump -uroot -ppassword -all-databases -routines > all.sql Enter the password and you are off and running. That being the case, you were not logged in as You will have to be explicit in specifying the root user mysqldump -uroot -p -all-databases -routines > all.sql I see you did not specify the user and password. When you did this mysqldump -all-databases -routines > all.sql You should run this command: SHOW GRANTS FOR SHOW VIEW is not there, that's the reason why. LOCK TABLES (If you use an explicit -lock-tables).TRIGGER (If any table has one or more triggers).In that post I show these minimum privileges for a mysqldump I wrote about this Dec 2013 : Which are the minimum privileges required to get a backup of a MySQL database schema? You need to have the SHOW VIEW privilege. Update 3 mysql> SELECT COUNT(1) MySQLGrantsCount,VERSION() MySQLVersion FROM information_lumns WHERE table_schema='mysql' AND table_name='user' Update 2 $ mysql -ANe"SELECT USER(),CURRENT_USER()" | GRANT USAGE ON *.* TO IDENTIFIED BY PASSWORD '.striped.' | The user is the definer of view hdcn.hdkien (and the SQL SECURITY is DEFINER. | GRANT ALL PRIVILEGES ON *.* TO IDENTIFIED BY PASSWORD '.striped.' WITH GRANT OPTION | I run mysqldump with user mysql> show grants for Grants for | Why I got the error when running mysqldump and how can I resolve it? MySQL version: mysql-community-server-5.5.86_64.User 192.168.12.197 has already had the privilege to select on table hdcn_hd and I can select from view hdkien without problem. Hdkien is a view CREATE ALGORITHM=UNDEFINED SQL SECURITY DEFINER VIEW `hdcn`.`hdkien` AS (.striped.) Mysqldump: Couldn't execute 'show table status like 'hdkien'': SELECT command denied to user for column 'id' in table 'hdcn_hd' (1143) mysqldump -all-databases -routines > all.sql GRANT ALL PRIVILEGES ON `mysqldb`.* TO SELECT USER(), CURRENT_USER() select host,user,select_priv, Create_user_priv from mysql.When using mysqldump to backup MySQL, I got following error. GRANT ALL PRIVILEGES ON `mysqldb`.* TO SHOW GRANTS FOR CURRENT_USER() GRANT ALL PRIVILEGES ON *.* TO IDENTIFIED BY PASSWORD 'xxxxxxxxx' I have gone through similar posts in here and have done the following tests:Ä¡) SHOW GRANTS FOR GRANT ALL PRIVILEGES ON *.* TO IDENTIFIED BY PASSWORD 'xxxxxxxx' WITH GRANT OPTIONÄ¢) SHOW GRANTS FOR GRANT ALL PRIVILEGES ON *.* TO IDENTIFIED BY PASSWORD 'xxxxxxxxx' GRANT command denied to user for table 'testtable' I am new to MySQL admin duties, and am trying to unsuccessfully grant SELECT Permissions to a limited number of columns in a table, using MySQL Workbench 5.2.44: GRANT SELECT (col1, col2, col3) ON mysqldb.testtable TO get the following error: Error Code: 1142.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |