Hi. I have tried to test some privilege on strict mode.
At strict mode permissions(SingleStoreDB Cloud · SingleStore Documentation), no.3 showed a different result with this link.
The scenario follows.
[1. Login as ‘root’ and GRANT CREATE USER ON *.* TO a user]
memsql> select current_user();
±---------------+
| current_user() |
±---------------+
| root@% |
±---------------+
1 row in set (0.02 sec)
memsql> show users;
±-------------------±-------±------------±-----------±----------------------±---------+
| User | Type | Connections | Is deleted | Default resource pool | Is local |
±-------------------±-------±------------±-----------±----------------------±---------+
| ‘SWNAM’@‘%’ | Native | 0 | | | LOCAL |
| ‘nam’@‘localhost’ | Native | 0 | | | LOCAL |
| ‘nam2’@‘127.0.0.1’ | PAM | 0 | | | LOCAL |
| ‘nam2’@‘localhost’ | PAM | 0 | | | LOCAL |
| ‘new_nam’@‘%’ | Native | 1 | | | LOCAL |
| ‘root’@‘%’ | Native | 11 | | | LOCAL |
| ‘user01’@‘%’ | Native | 0 | | | LOCAL |
| ‘user02’@‘%’ | Native | 0 | | system_auto | LOCAL |
| ‘user03’@‘%’ | Native | 0 | | system_auto | LOCAL |
±-------------------±-------±------------±-----------±----------------------±---------+
9 rows in set (0.00 sec)
memsql> grant create user on *.* to ‘SWNAM’@‘%’;
Query OK, 0 rows affected (0.00 sec)
[2. Login as ‘SWNAM’@‘%’ and try to change password on some user.]
Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.
memsql> show grants
→ ;
±------------------------------------------------+
| Grants for SWNAM@% |
±------------------------------------------------+
| GRANT CREATE USER ON *.* TO ‘SWNAM’@‘%’ |
| GRANT SELECT ON TEST_NAM.TB1 TO ‘SWNAM’@‘%’ |
±------------------------------------------------+
2 rows in set (0.01 sec)
memsql> grant select on TEST_NAM.TB1 to ‘new_nam’@‘%’ identified by ‘memsql’;
ERROR 1142 (42000): GRANT command denied to user ‘SWNAM’@‘%’ for table ‘TB1’
- As I know, the user ‘SWNAM’@‘%’ has the select on TEST_NAM.TB1 privilege. the user can change password on some user because the user have CREATE USER privilege.
Why “ERROR 1142” occurs?