Detailed explanation of MySQL setting access permission examples-Mysql Tutorial-php.cn

Detailed explanation of MySQL setting access permission examples

巴扎黑

Release： 2017-05-20 14:11:27

Original

2426 people have browsed it

Set access permissions

After creating a user account, you must then assign access permissions. Newly created user accounts do not have access rights. They can log in to MySQL, but they cannot see the data or perform any database operations.

To see the permissions granted to a user account, use SHOW GRANTS FOR, as shown below:

Enter:

SHOW GRANTS FOR bforta;

Copy after login

Enter:

Detailed explanation of MySQL setting access permission examples

Analysis: The output shows that user bforta has a permission USAGE ON *.*. USAGE means no permissions at all (I know, not very intuitive), so this result means no permissions on anything on any database and on any table.

User is defined as user@host MySQL's permissions are defined by a combination of user name and host name. If you do not specify a hostname, the default hostname % is used (user is granted access regardless of hostname).

To set permissions, use the GRANT statement. GRANT requires you to give at least the following information:

1. The permissions to be granted;

2. The database or table to which access permissions are granted;

3. User name.

The following example shows the usage of GRANT:

Input:

GRANT SELECT ON crashcourse.*TO bforta;

Copy after login

Analysis: This GRANT allows the user to use SELECT on crashcourse.* (all tables of the crashcourse database). By granting only SELECT access, user bforta has read-only access to all data in the crashcourse database.

SHOW GRANTS Reflect this change:

Input:

SHOW GRANTS FOR bforta;

Copy after login

Output:

Detailed explanation of MySQL setting access permission examples

## Analysis: Each GRANT added (or update) a permission for the user. MySQL reads all authorizations and determines permissions based on them.

The reverse operation of GRANT is REVOKE, which is used to revoke specific permissions. Here is an example:

Input:

REVOKE SELECT ON crashcourse.* FROM bforta;

Copy after login

Analysis: This REVOKE statement cancels the SELECT access permission just given to user bforta. The revoked access must exist, otherwise an error will occur.

GRANT and REVOKE can control access permissions at several levels:

1. The entire server, use GRANT ALL and REVOKE ALL;

2. The entire database, use ON database.*;

3. Specific tables, use ON database.table;

4. Specific columns;

5. Specific stored procedures.

The following table lists each permission that can be granted or revoked.

Detailed explanation of MySQL setting access permission examples

Using GRANT and REVOKE, combined with the permissions listed in the table, you can control what users can do with your valuable data Have complete control over what you can and can't do.

Future Authorization When using GRANT and REVOKE, the user account must exist, but there is no such requirement for the objects involved. This allows administrators to design and implement security measures before creating databases and tables.

The side effect of this is that when a database or table is deleted (using the DROP statement), the relevant access rights still exist. Also, these permissions will still be in effect if the database or table is recreated in the future.

Simplify multiple authorizationsYou can string multiple GRANT statements together by listing each permission and separating it with commas, as shown below:

GRANT SELECT,INSERT ON crashcourse.* TO bforta;

Copy after login

[Related recommendations]

Mysql free video tutorial

MySQL creates user accounts and deletes user accounts

mysql Command line example operations for managing users and changing passwords

Several points to note about mysql access control

Character set used by MySQL and proofreading sequence usage tutorial

The above is the detailed content of Detailed explanation of MySQL setting access permission examples. For more information, please follow other related articles on the PHP Chinese website!