0

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.

1
  • 1
    Or there are some privileges in the root user that are not included into the "ALL PRIVILEGES"? TRUE. There is a lot of privileges not relative to database/table. For example, FILE privilege... – Akina Mar 16 '20 at 11:33
1

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;
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.