0

I need to revoke permission for root user in mySql. root user should not be able to create and drop tables in the database.

I checked revoke command but somehow it is not working for root user. If I create a new user and revoke permission, it works, what am I missing for root user or we can't revoke permission for 'root'?

SHOW GRANTS FOR root@localhost;

--Displays

GRANT RELOAD, SHUTDOWN, PROCESS, FILE, SHOW DATABASES, SUPER, REPLICATION SLAVE, REPLICATION CLIENT, CREATE USER, CREATE TABLESPACE ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*7BB96B4D3E986612D96E53E62DBE9A38AAA40A5A'

GRANT INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON `%`.* TO 'root'@'localhost' WITH GRANT OPTION

GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION

Then

REVOKE all on myDb.* from root@'%';
FLUSH PRIVILEGES;

Access denied for user 'root'@'localhost' to database 'myDb'

Johny Bravo
  • 414
  • 9
  • 34

1 Answers1

0

I think, you don't want to do that. Root user is made that way to have all the privileges of all CRUD operations and creating new users and giving them privileges.

If your motive is to limit the access, make different users depending upon your need. For eg: you may only want to a user to read the records. That's a safe option because you may use that user only for reading purpose. OR just for read, write and edit privileges.

Edit:

If you still want to do this, check this answers How can I restore the MySQL root user’s full privileges? . It might help you.

Nikhil Pareek
  • 734
  • 9
  • 24
  • The reason I want to do this, when I install mysql at end user, they accidentally runs some scripts, that is causing issue(drops table) in my application. So I want to restrict create and drop table for root as well – Johny Bravo Jan 03 '18 at 10:08
  • I would suggest to make changes in your application and make a new user in mysql that will have the rights that you want and restrict them. Make your application use the new user. It may happen in future that you may need root rights. At that time, just login as root and make those changes. – Nikhil Pareek Jan 03 '18 at 10:13
  • Yes that is one way but the problem with this approach is that, end user is still able to login with root and execute scripts, right? – Johny Bravo Jan 03 '18 at 10:14
  • You can change the root password so that you can only access it https://dev.mysql.com/doc/refman/5.7/en/resetting-permissions.html – Nikhil Pareek Jan 03 '18 at 10:17