Shulou(Shulou.com)06/01 Report--

The following is followed by the author to understand the MySQL data control language DCL, I believe you will benefit a lot after reading, the text in the essence is not much, hope that the MySQL data control language DCL this short content is what you want.

We use DDL's "CREATE USER" statement to create users. New SQL users are not allowed to access tables belonging to other SQL users, nor can they immediately create their own tables, which must be authorized. The permissions that can be granted include the following groups:

1. Column permissions: related to a specific column in the table

two。 Table permissions: related to all data in a specific data table

3. Database permissions: related to all data tables in a specific database

4. User permissions: related to all databases in MySQL

You can use the SHOW GRANTS command to view the permissions of the current user.

SHOW GRANTS;/ displays current user permissions SHOW GRANTS FOR 'pinnsvin'@'localhost';/ displays specified user rights SHOW GRANTS FOR CURRENT_USER (); / displays current user rights 1.GRANT authorization statements

Syntax:

GRANT priv_type [(column_list)] [, priv_type [(column_list)]]... ON [object_type] priv_level TO user_specification [, user_specification]... [REQUIRE {NONE | ssl_option [[AND] ssl_option]...}] [WITH {GRANT OPTION | resource_option}...] / proxy GRANT PROXY ON user_specification TO user_specification [, user_specification]. [WITH GRANT OPTION] / cascading authorization, selected The user has the right to grant his or her own permissions to the sub-user / authorized target object type object_type: {TABLE | FUNCTION | PROCEDURE} / authorized target priv_level: {* | *. * | db_name.* | db_name.tbl_name | db_name.routine_name} / authorized user user_specification: user [auth_option] auth_option: {IDENTIFIED BY' Auth_string' | IDENTIFIED BY PASSWORD 'hash_string' | IDENTIFIED WITH auth_plugin | IDENTIFIED WITH auth_plugin AS' hash_string'} / SSL Settings ssl_option: {SSL | X509 | CIPHER 'cipher' | ISSUER' issuer' | SUBJECT 'subject'} resource_option: {| MAX_QUERIES_PER_HOUR count / number of query statements allowed to be executed by users per hour | MAX_UPDATES_PER_HOUR count / allow users to execute per hour Number of update statements | MAX_CONNECTIONS_PER_HOUR count / number of connections allowed by users per hour | MAX_USER_CONNECTIONS count / number of simultaneous connections to CVM}

Example:

First, to create a user, create a user named jeffrey, login host is localhost, password is mypass user CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY' mypass';/ granted user jeffrey@localhost all operation permissions on all data tables under database db1 GRANT ALL ON db1.* TO 'jeffrey'@'localhost';/ granted user' jeffrey'@'localhost' query rights to invoice data tables under database db2 GRANT SELECT ON db2.invoice TO 'jeffrey'@'localhost' / USAGE means no permission. User jeffrey@localhost only has 90 queries in an hour. GRANT USAGE ON *. * TO 'jeffrey'@'localhost' WITH MAX_QUERIES_PER_HOUR 90

2.REVOKE revoke authorization statement

Syntax:

REVOKE priv_type [(column_list)] [, priv_type [(column_list)]]... ON [object_type] priv_level FROM user [, user]... REVOKE ALL PRIVILEGES, GRANT OPTION FROM user [, user]... / authorized agent REVOKE PROXY ON user FROM user [, user]...

Example: / revoke all operation permissions of user jeffrey@localhost on all data tables under database db1 REVOKE ALL ON db1.* FROM 'jeffrey'@'localhost';/ revoke user' jeffrey'@'localhost' 's query authority on invoice data tables under database db2 REVOKE SELECT ON db2.invoice FROM 'jeffrey'@'localhost'

After reading this article of MySQL's data control language DCL, many readers will want to know more about it. If you need more industry information, you can follow our industry information section.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.