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;/shows the current user rights show GRANTS for ' pinnsvin ' @ ' localhost ';/display specified user rights show GRANTS for Current_User ();/Show Current user rights

1.GRANT Authorization Statement

Grammar:

650) this.width=650; "src="/img/fz.gif "alt=" Copy Code "style=" Margin:0px;padding:0px;border:none; "/>

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]&NBSP, ...}]     [WITH {GRANT OPTION | RESOURCE_OPTION}&NBSP, ...] /Agent grant proxy on user_specification    to user_specification [,  user_specification] ...    [with grant option]/at the level of the authorization, selected this item, The user has the right to grant his or her own rights to the child user/Authorization target object type created by Object_type: {    table  | function   | procedure}/Authorized 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 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 per hour by the user   | MAX_UPDATES_PER_HOUR  count /allows the user to execute the number of UPDATE statements per hour   | max_connections_per_hour count /the number of times the user is allowed to connect per hour    | max_user_connections count /allow users to simultaneously connect the number of servers}

650) this.width=650; "src="/img/fz.gif "alt=" Copy Code "style=" Margin:0px;padding:0px;border:none; "/>

Instance:

650) this.width=650; "src="/img/fz.gif "alt=" Copy Code "style=" Margin:0px;padding:0px;border:none; "/>

/To create a user, create a username named Jeffrey, login host as localhost, password for mypass users create user ' jeffrey ' @ ' localhost ' identified by ' mypass ';/granted the user [Email protected] All data tables under Database DB1 all Operation Rights Grant all on db1.* to ' Jeffrey ' @ ' localhost ';/granted user ' Jeffrey ' @ ' localhost ' Query permissions on Invoice Data table under database DB2 grant SELECT on Db2.invoice to ' Jeffrey ' @ ' localhost ';/usage means no permissions, user [email protected] Only 90 query permissions within one hours Grant USAGE on *. * to ' Jeffrey ' @ ' localhost ' with max_queries_per_hour 90;

650) this.width=650; "src="/img/fz.gif "alt=" Copy Code "style=" Margin:0px;padding:0px;border:none; "/>

2.REVOKE REVOKE Authorization statement

Syntax:

650) this.width=650; "src="/img/fz.gif "alt=" Copy Code "style=" Margin:0px;padding:0px;border: none; "/>

 revoke    priv_type [(column_list)]       [, priv_type [(column_list)]] ...    on [object_type] priv_ LEVEL    FROM USER [, USER]&NBSP, ..... revoke all privileges, grant option    from user [,  User] .../Authorized Agent Revoke proxy on user    from user [, user
 ]&NBSP, ..... 

650) this.width=650; "src="/img/fz.gif "alt=" Copy Code "style=" Margin:0px;padding:0px;border:none; "/>

Instance:

/recalled user [email protected] All operation permissions on database DB1 All data tables revoke all on db1.* from ' Jeffrey ' @ ' localhost ';/recalled user ' Jeffrey ' @ ' localhost ' Query permission for Invoice Data table under database DB2 revoke SELECT on Db2.invoice from ' Jeffrey ' @ ' localhost ';

MySQL Data Control Language DCL