Description:
Below are a just small sample of these errors. I believe the errors are incorrect in just about every case where there is a mismatch between the GRANT types, or GRANT levels the invoking user posesses, and those he is attempting to GRANT to another user.
How to repeat:
# log in as root:
CREATE DATABASE privDB;
GRANT CREATE ON privDB.* TO
'create'@'localhost' IDENTIFIED BY 'create';
GRANT USAGE ON *.* TO
'none'@'localhost' IDENTIFIED BY 'none';
# CASE 1
# log in as user 'create':
GRANT CREATE ON *.* TO 'none'@'localhost';
# Yields error:
# ERROR 1045 (28000): Access denied for user
# 'create'@'localhost' (using password: YES)
# This one seems more appropriate:
# Error: 1141 SQLSTATE: 42000 (ER_NONEXISTING_GRANT)
# Message: There is no such grant defined for user
# '%s' on host '%s'
# Case 2:
GRANT CREATE ROUTINE ON privDB.* TO 'none'@'localhost';
# Yields untrue error:
# ERROR 1044 (42000): Access denied for user
# 'create'@'localhost' to database 'privDB'
# This one seems more appropriate:
# Error: 1141 SQLSTATE: 42000 (ER_NONEXISTING_GRANT)
# Message: There is no such grant defined for user
# '%s' on host '%s'
# GRANT SELECT ON privDB.* TO 'none'@'localhost';
# Yields error:
# ERROR 1044 (42000): Access denied for user
# 'create'@'localhost' to database 'privDB'
# These messages seems more appropriate:
# Error: 1147 SQLSTATE: 42000 (ER_NONEXISTING_TABLE_GRANT)
# Message: There is no such grant defined
# for user '%s' on host '%s' on table '%s'
# OR
# Error: 1227 SQLSTATE: 42000 (ER_SPECIFIC_ACCESS_DENIED_ERROR)
# Message: Access denied; you need the
# %s privilege for this operation
Suggested fix:
In just about every possible case of a mismatch, a single default error could be thrown, which unfortunately doesn't currently exist in the error list, something to this effect.
(INSUFFICIENT_GRANT_LEVEL)
Message: user must have '%s' GRANT on '%s' level to carry out this operation.
Error 1147 is close to this but is specifically for a table level operation.
Error 1141 is close to this but lacks information related to a level mismatch
However either of these is more informative and preferable to those that are being thrown now.
© 2024, Oracle Corporation and/or its affiliates
Description: Below are a just small sample of these errors. I believe the errors are incorrect in just about every case where there is a mismatch between the GRANT types, or GRANT levels the invoking user posesses, and those he is attempting to GRANT to another user. How to repeat: # log in as root: CREATE DATABASE privDB; GRANT CREATE ON privDB.* TO 'create'@'localhost' IDENTIFIED BY 'create'; GRANT USAGE ON *.* TO 'none'@'localhost' IDENTIFIED BY 'none'; # CASE 1 # log in as user 'create': GRANT CREATE ON *.* TO 'none'@'localhost'; # Yields error: # ERROR 1045 (28000): Access denied for user # 'create'@'localhost' (using password: YES) # This one seems more appropriate: # Error: 1141 SQLSTATE: 42000 (ER_NONEXISTING_GRANT) # Message: There is no such grant defined for user # '%s' on host '%s' # Case 2: GRANT CREATE ROUTINE ON privDB.* TO 'none'@'localhost'; # Yields untrue error: # ERROR 1044 (42000): Access denied for user # 'create'@'localhost' to database 'privDB' # This one seems more appropriate: # Error: 1141 SQLSTATE: 42000 (ER_NONEXISTING_GRANT) # Message: There is no such grant defined for user # '%s' on host '%s' # GRANT SELECT ON privDB.* TO 'none'@'localhost'; # Yields error: # ERROR 1044 (42000): Access denied for user # 'create'@'localhost' to database 'privDB' # These messages seems more appropriate: # Error: 1147 SQLSTATE: 42000 (ER_NONEXISTING_TABLE_GRANT) # Message: There is no such grant defined # for user '%s' on host '%s' on table '%s' # OR # Error: 1227 SQLSTATE: 42000 (ER_SPECIFIC_ACCESS_DENIED_ERROR) # Message: Access denied; you need the # %s privilege for this operation Suggested fix: In just about every possible case of a mismatch, a single default error could be thrown, which unfortunately doesn't currently exist in the error list, something to this effect. (INSUFFICIENT_GRANT_LEVEL) Message: user must have '%s' GRANT on '%s' level to carry out this operation. Error 1147 is close to this but is specifically for a table level operation. Error 1141 is close to this but lacks information related to a level mismatch However either of these is more informative and preferable to those that are being thrown now.