MySQL Data Control Language DCL

We create a user using the DDL "create user" statement, and the new SQL user does not allow access to tables belonging to other SQL users, nor does it immediately create its own table, which must be authorized. The permissions that you can grant include the following groups:

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

2. 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 rights: Related to all MySQL databases

You can use the show grants command to view the permissions of the current user.

SHOW GRANTS; /   for ' pinnsvin ' @ ' localhost '; /  forcurrent_user(); / Show Current user rights

1.GRANT Authorization Statement

Grammar:

GRANTPriv_type[(column_list)]      [, Priv_type [(column_list)]] ...     on [object_type]Priv_level toUser_specification[, User_specification] ...    [REQUIRE {NONE | ssl_option [[ and]ssl_option] "...}" [With {GRANT OPTION | resource_option} ...]/AgentGRANTPROXY onuser_specification toUser_specification[, User_specification] ...    [With GRANT OPTION]/, this option is selected, and the user has the right to grant his or her own rights to the child user who created it ./Authorization target object type object_type: {TABLE  | FUNCTION  | PROCEDURE}/Authorization target priv_level: {*  | *.*  | db_name.*  | db_name. Tbl_name|Tbl_name| db_name. routine_name}/authorized user user_specification:User [auth_option]auth_option: {identified by 'auth_string'  |Identified byPASSWORD'hash_string'  |Identified withAuth_plugin|Identified withAuth_plugin as 'hash_string'}/ssl Setting Ssl_option: {SSL|X509|CIPHER'cipher'  |ISSUER'Issuer'  |SUBJECT'subject'}resource_option: {|Max_queries_per_hourCount /Number of query statements allowed to be executed per hour by the user   |Max_updates_per_hourcount /Allow user to execute UPDATE statement number per hour   |Max_connections_per_hourCount /number of times a user is allowed to connect per hour   |Max_user_connectionscount /allow users to connect to the server at the same time }

Instance:

/Create a user first, create a user named Jeffrey, login host localhost, password mypassCREATE USER 'Jeffrey'@'localhost'Identified by 'Mypass';/User Jeffrey was granted@localhost All operation permissions for all data tables under Database DB1GRANT  All  onDB1.*  to 'Jeffrey'@'localhost';/Granted to the user'Jeffrey'@'localhost'Query permissions for invoice data tables under database DB2GRANT SELECT  onDb2.invoice to 'Jeffrey'@'localhost';/usage means no permissions, user [email protected] only 90 query permissions within one hoursGRANTUSAGE on *.*  to 'Jeffrey'@'localhost'  withMax_queries_per_hour -;

2.REVOKE REVOKE Authorization statement

Grammar:

REVOKEPriv_type[(column_list)]      [, Priv_type [(column_list)]] ...     on [object_type]Priv_level from User [, the user] ...REVOKE  All Privileges,GRANT OPTION     from User [, the user].../authorized AgentREVOKEPROXY on User     from User [, the user]...

Instance:

/User Jeffrey Withdrawn@localhost All operation permissions for all data tables under Database DB1REVOKE  All  onDB1.*  from 'Jeffrey'@'localhost';/User withdrawn'Jeffrey'@'localhost'Query permissions for invoice data tables under database DB2REVOKE SELECT  onDb2.invoice from 'Jeffrey'@'localhost';

MySQL Data Control Language DCL