DCL数据控制语句 Data Control Language

可以利用window自带cmd命令行窗口输入mysql指令
mysql -u xxx -p
输入密码 xxx

DCL数据库控制语言 
grant 赋予  用来给用户赋予期限
revoke 权限
1.我们现在我身份是SYSDBA数据库管理员的身份
   管理员可以操作其他普通用的权限
   通过root账号查看mysql数据库中的user表格记录着所有用户信息
   查看 user列  host列 passeord列 authentication_string列
mysql> show databases ;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| cac                |
| ccc                |
| con                |
| mysql              | ##这个数据库
| performance_schema |
| pirates            |
| s                  |
| sys                |
| test               |
+--------------------+
10 rows in set (0.00 sec)

mysql> use mysql ;
Database changed
mysql> show tables ;
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| columns_priv              |
| db                        |
| engine_cost               |
| event                     |
| func                      |
| general_log               |
| gtid_executed             |
| help_category             |
| help_keyword              |
| help_relation             |
| help_topic                |
| innodb_index_stats        |
| innodb_table_stats        |
| ndb_binlog_index          |
| plugin                    |
| proc                      |
| procs_priv                |
| proxies_priv              |
| server_cost               |
| servers                   |
| slave_master_info         |
| slave_relay_log_info      |
| slave_worker_info         |
| slow_log                  |
| tables_priv               |
| time_zone                 |
| time_zone_leap_second     |
| time_zone_name            |
| time_zone_transition      |
| time_zone_transition_type |
| user                      |##这个是记录着所用用户信息的表
+---------------------------+
31 rows in set (0.01 sec)
查看 user列 host列 authentication_string列
mysql> select user,host,authentication_string from user ;
用户的账号       用户的ip      用户的密码 是经过加密的
+---------------+-----------+-------------------------------------------+
| user          | host      | authentication_string                     |
+---------------+-----------+-------------------------------------------+
| root          | localhost | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B |
| mysql.session | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| mysql.sys     | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| fhj           | localhost | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+---------------+-----------+-------------------------------------------+
4 rows in set (0.00 sec)

创建一个新的用户
语句 create user '名字'@'IP' identified by '密码' ;###locahost 表示本机的ip地址
mysql>  create user 'testroot'@'localhost' identified by '666' ;
Query OK, 0 rows affected (0.03 sec)
查看一下 多一个 testroot 用户 ；
mysql> select user,host,authentication_string from user ;
+---------------+-----------+-------------------------------------------+
| user          | host      | authentication_string                     |
+---------------+-----------+-------------------------------------------+
| root          | localhost | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B |
| mysql.session | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| mysql.sys     | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| fhj           | localhost | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| testroot      | localhost | *007D50CA06F69776D307B1BEC71CD73D0EA0999C |##是我们刚创建的用户
+---------------+-----------+-------------------------------------------+
5 rows in set (0.00 sec)

#查看用户的权限 show grants for '用户名'@'IP' ;
mysql> show grants for 'testroot'@'localhost' ;
+----------------------------------------------+
| Grants for testroot@localhost                |
+----------------------------------------------+
| GRANT USAGE ON *.* TO 'testroot'@'localhost' | ##useage 权限是只允许登录
+----------------------------------------------+
1 row in set (0.00 sec)
 

 给新用户赋予权限
 #  grant 权限 on数据库.表格 to '用户名'@'IP' ;
 #添加全部权限 全部数据.全部表格 的所有权限 ;
 mysql> grant all on *.* to 'testroot'@'localhost' ;
Query OK, 0 rows affected (0.00 sec)
#  看一下
mysql> show grants for 'testroot'@'localhost' ;
+-------------------------------------------------------+
| Grants for testroot@localhost                         |
+-------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'testroot'@'localhost' |
+-------------------------------------------------------+
1 row in set (0.00 sec)
#赋予权限后最好做一个刷新
flush privaileages ;  #刷新权限
 

回收用户权限 revoke
revoke 权限 on 数据库.表格 from ‘用户名’ @‘ip’ ;

mysql> revoke all on *.* from 'testroot'@'localhost' ;
Query OK, 0 rows affected (0.00 sec)

mysql> show grants for 'testroot'@'localhost' ;
+----------------------------------------------+
| Grants for testroot@localhost                |
+----------------------------------------------+
| GRANT USAGE ON *.* TO 'testroot'@'localhost' |
+----------------------------------------------+
1 row in set (0.00 sec)
删除用户drop user '用户名'@'ip' ;
mysql> drop user 'testroot'@'localhost' ;
Query OK, 0 rows affected (0.00 sec)