Permissions issues for Mysql database

Have not understood the permissions of MySQL database before

The root user has the highest privileges, that is, the superuser, the root user can see all the contents of the database, and other users can only be the root user authorized to operate the database, if you want to create a new database in other users and not visible to the root user, is not possible, Instead, set the permissions of other users to:

Grant all on * * to ' my_user ' @ ' localhost ';

At this point, the other user's permissions are equivalent to the root user, no practical significance.

Revoke all on * * from ' my_user ' @ ' localhost ';//Reclaim User's permissions

Grant all on db.* to ' my_user ' @ ' localhost ';//grant Operation permissions on DB database only

Grant Select (stu_no) on Db.stu to ' my_user ' @ ' localhost ';//grant only SELECT permission column-level authorization for STU_NO column of Stu table for DB database

SELECT * FROM Mysql.user

To view the user's permissions, note: This time you see the global permissions, that is, the operation of all databases permissions and:

Grant all on * * to ' my_user ' @ ' localhost ';//Grant database permissions, global permissions > Database permissions, and view database permissions when global permissions do not meet conditions

To modify global permissions:

Update mysql.user set create_priv= ' Y ' where user= ' my_user '; error occurs because non-primary keys in MySQL Safe mode cannot perform update and delete operations

Now look at the Mysql.user table:

Desc Mysql.user

In this table, host and user are combined primary keys, so there are two ways to modify them:

①set sql_safe_updates=0;

②update mysql.user set create_priv= ' Y where user= ' My_user ' and host= ' localhost ';

View permissions granted to a specified user

Show grants for ' my_user ' @ ' localhost '

Permissions issues for Mysql database