I install mysql on Ubuntu. I put root user as admin and create new user for non-admin.
CREATE USER 'user'@'localhost' IDENTIFIED BY '';
GRANT ALTER,CREATE,CREATE VIEW,DELETE,DROP,INDEX,INSERT,REFERENCES,RELOAD,SELECT,SHOW DATABASES,SHOW VIEW,TRIGGER,UPDATE ON *.* TO 'user'@'localhost';
But, it turns out, this usual user have access to (literally) mysql database. Which contains user table. So, I can modify other users even using non-admin.
mysql> DELETE FROM mysql.user WHERE User = "otheruser";
Query OK, 1 row affected (0,03 sec)
How to forbid non-admin user to modify mysql.* tables? Is my permission wrong?
Add-on
I'm an admin of computer labs for database subject where every student can create their own. So, theoretically, deny privileges to mysql database is the only way, right? How can I do that? Is it possible?
答案1
The part *.* in your GRANT statement - "turns out, this usual user have access to (literally) mysql database.". Specify only that database that user allowed to access as
CREATE USER 'user'@'localhost' IDENTIFIED BY '';
GRANT ALTER,CREATE,CREATE VIEW,DELETE,DROP,INDEX,INSERT,SELECT,SHOW VIEW,TRIGGER,UPDATE ON AllowedDatabaseName.* TO 'user'@'localhost';
P.S.
And I completely agree with @Walmart, don't give too much permissions to the user if he really don't need it (such as REFERENCES,RELOAD,SHOW DATABASES ....)
答案2
Is my permission wrong?
Yes, you are granting the account more than it needs so your GRANT statement is wrong.
How to forbid non-admin user to modify mysql.* tables?
When you GRANT permissions to the local SQL account you create, only grant it permissions that it needs to access DB records or DB objects it needs to access and nothing more.
The most secure method would be to only grant it the explicit permissions it needs to the explicit DB objects such as SELECT to a particular table or EXECUTE to a particular stored procedure for example.
Look over the Privileges Provided by MySQL to get a better understanding of what each privilege allows an account to access.
Once you understand how each privilege works permission wise to DB objects, etc. you can then define the security for the SQL account you'll create.
When you say you do not want to allow modification of mysql tables, just remember you can modify data within a table where DELETE and UPDATE would allow this. The DROP permission on the other hand would allow the deletion of an entire table or ALTER permission would allow modification of the table schema, column definitions, or other objects that exist on a table.
Example
You could use you the below to grant an account SELECT access to all tables and EXECUTE access to all Stored Procedures in a particular DB:
GRANT CREATE, SELECT, EXECUTE ON 'DatabaseName'
TO 'user'@'localhost'
Every student can create their own database. So, theoretically, deny privileges to mysql database is the only way, right? How can I do that?
No, I tested and confirmed that the below solution works. You essentially allow the student MYSQL accounts GRANT ALL PRIVILEGES but to only DBs matching a specific naming pattern.
This mean when you have the students complete the exercises, you need to ensure you instruct then to have the DBs they create have a name matching that pattern. This also means the naming convention pattern you choose should NOT match a pattern as other DBs you do not want them to access.
Again, I tested and confirmed that this solution works and is a rather simple solution than getting explicit with DENY permissions which can be dangerous since you can easily overlook or not test every possible scenario and this is a reason why you DENY all and only GRANT what you want to allow security wise.
Example
GRANT ALL PRIVILEGES ON `student\_%` . * TO 'student'@'%';
Now a user named student can create any database on the instance of MYSQL as long as it's name is or starts with student_. The backslash is the escape character for the underbar character.