Error 1410 Mysql
Contents |
June 22, 2011 Less known facts about MySQL user grants Reading MySQL you are not allowed to create a user with grant security: inconsistencies I remembered a few related experiments that I did several mysql error code 1064 years ago when I was studying for the MySQL certification. The first fact that came to mind
Mysql Error Codes
is about the clause "WITH GRANT OPTION", which can only be given on the full set of options, not on a single grant. For example GRANT INSERT,DELETE,UPDATE on world.* to
Mysql Create Table
myuser identified by 'mypass'; GRANT SELECT on world.* to myuser identified by 'mypass' WITH GRANT OPTION; show grants for myuser\G *************************** 1. row *************************** Grants for myuser@%: GRANT USAGE ON *.* TO 'myuser'@'%' IDENTIFIED BY PASSWORD '*6C8989366EAF75BB670AD8EA7A7FC1176A95CEF4' *************************** 2. row *************************** Grants for myuser@%: GRANT SELECT, INSERT, UPDATE, DELETE ON `world`.* TO 'myuser'@'%' WITH GRANT OPTION If you are surprised about the "WITH GRANT OPTION" clause applying to all grants instead of only applying to the SELECT, you forgot to consider how the grants are stored. All the grants for a given user (and a user is the combination of a name and a provenience) are stored in a single record in the mysql.user table. The GRANT OPTION is a column in that record. It is either set or not. You can't assign this option for only one attribute in the same record. It's either all the flagged grants or nothing. If you want to assign the "with grant option" on a single column, you must change either the provenience or the name of the user (thus opening another record). But also this addition may not be enough to reach your goal, as you can see in the next section. The other fact that came to mind about the "WITH GRANT OPTION" clause is that, in the examples given, it is ineffective. I dare say illusory. Let's start. As root user, we create
Connectors More MySQL.com Downloads Developer Zone Section Menu: Documentation Home MySQL 5.6 Reference Manual Preface and Legal Notices General Information Installing and Upgrading MySQL Tutorial MySQL Programs MySQL Server Administration Security Backup and Recovery Optimization Language Structure Globalization Data Types Functions and Operators SQL Statement Syntax The InnoDB Storage Engine Alternative Storage Engines High Availability and Scalability Replication MySQL Cluster NDB 7.3 and MySQL Cluster NDB 7.4 Partitioning Stored Programs and Views INFORMATION_SCHEMA Tables MySQL Performance http://datacharmer.blogspot.com/2011/06/less-known-facts-about-mysql-user.html Schema Connectors and APIs Extending MySQL MySQL Enterprise Edition MySQL Workbench MySQL 5.6 Frequently Asked Questions Errors, Error Codes, and Common Problems Sources of Error Information Types of Error Values Server Error Codes and Messages Client Error Codes and Messages Problems and Common Errors How to Determine What Is Causing a Problem Common Errors https://dev.mysql.com/doc/refman/5.6/en/error-messages-server.html When Using MySQL Programs Access denied Can't connect to [local] MySQL server Lost connection to MySQL server Client does not support authentication protocol Password Fails When Entered Interactively Host 'host_name' is blocked Too many connections Out of memory MySQL server has gone away Packet Too Large Communication Errors and Aborted Connections The table is full Can't create/write to file Commands out of sync Ignoring user Table 'tbl_name' doesn't exist Can't initialize character set File Not Found and Similar Errors Table-Corruption Issues Administration-Related Issues Problems with File Permissions How to Reset the Root Password What to Do If MySQL Keeps Crashing How MySQL Handles a Full Disk Where MySQL Stores Temporary Files How to Protect or Change the MySQL Unix Socket File Time Zone Problems Query-Related Issues Case Sensitivity in String Searches Problems Using DATE Columns Problems with NULL Values Problems with Column Aliases Rollback Failure for Nontransactional Tables Deleting Rows from Related Tables Solving Problems with No Matching Ro
Community Podcasts MySQL.com Downloads Documentation Section Menu: MySQL Forums :: Newbie :: ERROR 1410 (42000): You are not allowed to create a user with GRANT New Topic Advanced Search ERROR 1410 http://forums.mysql.com/read.php?10,109168 (42000): You are not allowed to create a user with GRANT Posted by: Girish Agarwal () Date: August 11, 2006 03:48PM Hi All, I have created a user with grant option for https://support.mayfirst.org/ticket/798 e.g. Logged in as root grant select on patient.d_user_info to test1 identified by 'test1' with grant option; // User is created Successfully Logged in as test1 grant select on patient.d_user_info to test2. mysql error // I get the you are not allowed error. Also on a related note the command grant select on patient.d_user_info to test1 identified by 'test1' with grant option // when changed to grant select on patient.d_user_info to test1 identified by password 'test1' with grant option produces the following error ERROR 1372 (HY000): Password hash should be a 41-digit hexadecimal number Please Help. Girish Navigate:Previous mysql error code Message•Next Message Options:Reply•Quote Subject Written By Posted ERROR 1410 (42000): You are not allowed to create a user with GRANT Girish Agarwal 08/11/2006 03:48PM Sorry, you can't reply to this topic. It has been closed. powered by phorum Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party. Contact Sales USA: +1-866-221-0634 Canada: +1-866-221-0634 Germany: +49 89 143 01280 France: +33 1 57 60 83 57 Italy: +39 02 249 59 120 UK: +44 207 553 8447 Japan: 0120-065556 China: 10800-811-0823 India: 0008001005870 More Countries» Contact Us Online» Products MySQL Enterprise Edition MySQL Standard Edition MySQL Classic Edition MySQL Cluster CGE MySQL Embedded (OEM/ISV) Services Training Certification Consulting Support Downloads MySQL Community Server MySQL Cluster MySQL Fabric MySQL Utilities MySQL Workbench About MySQL Contact Us How to Buy Partners Job Opportunities Site Map Documentation MySQL Reference Manuals MySQL Workbench Expert Guides Topic Guides MySQL Cluster Legal Legal Policies Your Privacy Rights Terms of Use Trademark Policy Contributor Agreement © 2015, Oracle Corporation and/or its affiliates
closed Feature/Enhancement Request (wontfix) mf-mysql-create should allow granting permissions to an exisiting user Reported by: http://mchapman2000.myopenid.com/ Owned by: https://id.mayfirst.org/jamie Priority: High Component: Tech Keywords: mysql mf-mysql-create Cc: Sensitive: no Description This script is a good start. It should also allow one to grant privileges to an existing mysql user. It would also be good to have a way to DROP a database. (I frequently need to DROP & re-CREATE a CiviCRM database due to breaks in foreign key constraints during upgrades) Oldest first Newest first Threaded Comments only Change History (6) comment:1 Changed 9 years ago by https://id.mayfirst.org/dkg Keywords mysql added If you have full privileges ("ALL PRIVILEGES") on a database, you should be able to drop it and recreate it already, right? Please let me know if you aren't able to do this. I agree with you that we should allow granting access to other mysql users as well. comment:2 Changed 9 years ago by https://id.mayfirst.org/jamie Keywords mf-mysql-create added Ah - I didn't realize that was possible. Thanks for pointing that out dkg - also relevant to #539. I'm leaving this ticket open to address the concern in the summary, which is still un-resolved. comment:3 Changed 9 years ago by https://id.mayfirst.org/dkg You could allow the owner of the database to allow other users access to it by including the WITH GRANT OPTION argument to the script. comment:4 Changed 8 years ago by https://id.mayfirst.org/jamie I'm having trouble making the WITH GRANT OPTIONS work. I'm testing on my local machine. As the mysql root user I ran: mysql> grant all privileges on foo.* to 'foo'@'localhost' identified by 'foo' WITH GRANT OPTION; Query OK, 0 rows affected (0.02 sec) mysql> Then, I logged in as foo and ran: mysql> GRANT SELECT ON foo.* TO 'bar'@'localhost' IDENTIFIED BY 'bar'; ERROR 1410 (42000): You are not allowed to create a user with GRANT mysql> Hm. Maybe you can't create the user, but you can grant privileges to an existing user. As root, I ran: mysql> USE mysql; Database changed mysql> INSERT INTO user SET User='bar'; Query OK, 1 row affected (0.02 sec) mysql> flush privileges; Query OK,