I want to create a user with restricted grants compared to root. If I do
GRANT ALL PRIVILEGES ON * . * TO 'non-root'@'localhost';
am I assigning all the same privileges as for the root user? Or are there some privileges in the root user that are not included in the ALL PRIVILEGES privilege?
For example: Is the new user able to create other users?
Otherwise I'll explicitly list the grant I want but this question is more for my learning.
To answer your question:
Is the new user able to create other users?
The answer is NO, the new user is not able to create other users nor grant/revoke privileges to other users.
If you want to create a root similar user you must issue the following statement:
GRANT ALL PRIVILEGES ON *.* TO 'non-root'@'localhost' WITH GRANT OPTION;
There is nothing special about the user root.
root is normally granted SUPER and WITH GRANT OPTION, and doing it ON *.* thereby providing all the nasty things you should give to non-root users. But all this is a convention.
Often an application is given
GRANT ALL PRIVILEGES
ON db_app.* -- limited to the app's database
TO app@'...' -- '%' is scary, but may be necessary
-- but no `WITH GRANT OPTION`.
This would give you "admin" as much privilege as normally given just to root:
GRANT ALL PRIVILEGES ON *.* TO admin@'%' WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON *.* TO admin@'localhost' WITH GRANT OPTION;
Note that % does not include localhost. This is an important security point.
It is probably better to give only this to root (or your admin):
GRANT ALL PRIVILEGES ON *.* TO root@'localhost' WITH GRANT OPTION;
That is, not also exposed to the outside world. This gives you a layer of protection by forcing root to first log into the server, and only then can he get into mysql with full privileges.
