3

I have this:

mysql> SELECT CURRENT_USER();
+----------------+
| CURRENT_USER() |
+----------------+
| root@%         |
+----------------+
1 row in set (0.00 sec)

mysql> SELECT USER();
+------------------+
| USER()           |
+------------------+
| root@CQ2404LA-PC |
+------------------+
1 row in set (0.00 sec)

mysql>
mysql> GRANT ALL PRIVILEGES ON `Company`.* TO 'TheUser'@'%' IDENTIFIED BY PASS
WORD '*3814FFAFF303C7DBB5511684314B57577D754FF9';
ERROR 1044 (42000): Access denied for user 'root'@'%' to database 'Company'

Access denied for user 'root'@'%' to database 'Company'

Now reviewing the root privileges I have:

mysql> show grants for 'root'@'localhost';
+-------------------------------------------------------------------------------
---------------------------------------------------------+
| Grants for root@localhost
                                                         |
+-------------------------------------------------------------------------------
---------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*158
FB31F24156B52B2408974EF221C5100001544' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION
                                                         |
+-------------------------------------------------------------------------------
---------------------------------------------------------+
2 rows in set (0.00 sec)

Before, I tested (Locally) And Works fine!.

Now Remotely Privileges:

mysql> show grants for 'root'@'%';
+-------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
---------------------------------------------------------------------------+
| Grants for root@%



                                                                           |
+-------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
---------------------------------------------------------------------------+
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS,
 FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES,
 LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW
VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER ON *.* TO 'root'@'%' IDENTIFIED
 BY PASSWORD '*158FB31F24156B52B2408974EF221C5100001544' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'%' WITH GRANT OPTION

Doesn't work!!!, I think that it must work because: "ON *.* TO 'root'@'%'"

Looking for the difference: 'root'@'%' haven't CREATE TABLESPACE, EVENT and TRIGGER

mysql> SELECT Host,     Event_priv,     Trigger_priv,   Create_tablespace_priv,
authentication_string   FROM mysql.user WHERE USER = "root";
+-----------+------------+--------------+------------------------+--------------
---------+
| Host      | Event_priv | Trigger_priv | Create_tablespace_priv | authenticatio
n_string |
+-----------+------------+--------------+------------------------+--------------
---------+
| localhost | Y          | Y            | Y                      |
         |
| %         | N          | N            | N                      | NULL
         |
+-----------+------------+--------------+------------------------+--------------
---------+
2 rows in set (0.01 sec)

mysql>

But, I think that is not root of problem...

Maybe The solution will be, to use: GRANT ALL PRIVILEGES ON *.* TO 'root'@'%', but I think "all privileges" have other thing than "an amount of privileges".

Martijn Pieters
  • 1,048,767
  • 296
  • 4,058
  • 3,343
  • As you can see the user root@% has privileges to grant to other users, but is not Working. ----------------------------------------------------------------------------------------------------------------------------------------------------------- ON \*.* TO 'root'@'%' ... WITH GRANT OPTION ----------------------------------------------------------------------------------------------------------------------------------------------------------- –  Dec 07 '13 at 17:51

1 Answers1

1

To use GRANT, you must have the GRANT OPTION privilege, and you must have the privileges that you are granting.

— http://dev.mysql.com/doc/refman/5.6/en/grant.html

If you don't hold "all privileges," you can't grant "all privileges."

Fix the root@% user's missing privileges and the problem will be resolved, although you really should understand what each privilege does and only grant the appropriate ones to each user.

Community
  • 1
  • 1
Michael - sqlbot
  • 169,571
  • 25
  • 353
  • 427
  • Thank you, but you can see that ---show grants for 'root'@'%';--- has WITH GRANT OPTION. Only the difference with ---show grants for 'root'@'localhost';--- are four privileges (Event_priv, Trigger_priv, Create_tablespace_priv, authentication_string) according to my question... –  Dec 10 '13 at 02:41
  • According the reference: > The GRANT statement grants privileges to MySQL user accounts. GRANT also serves to specify other account characteristics such as use of secure connections and limits on access to server resources. To use GRANT, you must have the GRANT OPTION privilege, and you must have the privileges that you are granting. >'root'@'%' has privileges over all (\*.*) Database on MySQL; –  Dec 10 '13 at 02:47
  • No, it doesn't. You said yourself, 'root'@'%' does not have `CREATE TABLESPACE`, `EVENT` or `TRIGGER`, which are part of `ALL PRIVILEGES`. An account that does not *have* all privileges ... cannot `GRANT ALL PRIVILEGES`. `GRANT OPTION` only allows you to give what you, yourself, possess: "you must have the privileges that you are granting" – Michael - sqlbot Dec 10 '13 at 02:50