mysql高级 权限管理[1]

mysql 的用户与权限管理  user-> db -> tables_priv

主要的mysql中mysql库的表来操作

主要是 user  db  tables_priv 三个表  分别控制     链接权限， 数据库权限，  表权限

用户连接到mysql,并且做各种查询


[用户] <<->> [服务器]
有两个阶段
1. 有没有权限连接上来
2. 有没有权限执行操作

对于1：

  服务器如何判断用户有没有权限链接上去
依据三个参数：
从哪来？  host
是谁？   user
密码是什么？  passwd


用户的三个信息，存储在mysql库中的user表中

		use mysql
		desc user
		mysql> select host,user,password from user;
	+-----------+------+-------------------------------------------+
	| host      | user | password                                  |
	+-----------+------+-------------------------------------------+
	| localhost | root | *7FE40129ED82D8B5978B02E9E2135C16412D8F4D |
	+-----------+------+-------------------------------------------+

	-- 修改 host域 使ip可以链接
	update user set host = '192.168.1.113' where user = 'root';

	flush privileges; -- 重新刷权限
	> mysql -h192.168.1.113 -uroot -p  -- 就可以登录了

	C:\wamp\mysql\bin>mysql -hlocalhost -uroot -p  -- 则 他不可登录了......
	Enter password: **
	ERROR 1130 (HY000): Host 'localhost' is not allowed to connect to this MySQL server

--  修改用户密码：
	mysql> update user set password = password('1111') where xxx;
	mysql> flush privileges;
--  但是仅限于单个用户， 如何让局域网内的用户都连接？
	

对于二：

-- 全局授权与收回
	
	
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| columns_priv              |
| db                        |*****
| func                      |
| help_category             |
| help_keyword              |
| help_relation             |
| help_topic                |
| host                      |
| proc                      |
| procs_priv                |
| tables_priv               |*****
| time_zone                 |
| time_zone_leap_second     |
| time_zone_name            |
| time_zone_transition      |
| time_zone_transition_type |
| user                      |*****
+---------------------------+

-- 新增一个用户

	-- 授权
grant[权限1,权限2,权限3....] on *.* to 用户@'主机' identified by '密码';

-- 常用权限有 all , create , drop , insert , delete , update , select ;

grant all on *.* to lisi@'192.168.1.%' identified by '111111';  -- 就添加了一个

-- | 192.168.1.% | lisi | *FD571203974BA9AFE270FE62151AE967ECA5E0AA |
-- C:\wamp\mysql\bin>mysql -h192.168.1.113  -ulisi -p
-- Enter password: ******

mysql> select * from user where user='lisi' \G
*************************** 1. row ***************************
                 Host: 192.168.1.%
                 User: lisi
             Password: *FD571203974BA9AFE270FE62151AE967ECA5E0AA
          Select_priv: Y
          Insert_priv: Y
          Update_priv: Y
          Delete_priv: Y
          Create_priv: Y
            Drop_priv: Y
          Reload_priv: Y
        Shutdown_priv: Y
         Process_priv: Y
            File_priv: Y
           Grant_priv: N
      References_priv: Y
           Index_priv: Y
           Alter_priv: Y
         Show_db_priv: Y
           Super_priv: Y
Create_tmp_table_priv: Y
     Lock_tables_priv: Y
         Execute_priv: Y
      Repl_slave_priv: Y
     Repl_client_priv: Y
     Create_view_priv: Y
       Show_view_priv: Y
  Create_routine_priv: Y
   Alter_routine_priv: Y
     Create_user_priv: Y
             ssl_type:
           ssl_cipher:
          x509_issuer:
         x509_subject:
        max_questions: 0
          max_updates: 0
      max_connections: 0
 max_user_connections: 0
1 row in set (0.00 sec)
*************************************************************






-- 收回权限
revoke all on *.* from lisi@'192.168.1.%';

mysql> select * from user where user='lisi' \G
*************************** 1. row ***************************
                 Host: 192.168.1.%
                 User: lisi
             Password: *FD571203974BA9AFE270FE62151AE967ECA5E0AA
          Select_priv: N
          Insert_priv: N
          Update_priv: N
          Delete_priv: N
          Create_priv: N
            Drop_priv: N
          Reload_priv: N
        Shutdown_priv: N
         Process_priv: N
            File_priv: N
           Grant_priv: N
      References_priv: N
           Index_priv: N
           Alter_priv: N
         Show_db_priv: N
           Super_priv: N
Create_tmp_table_priv: N
     Lock_tables_priv: N
         Execute_priv: N
      Repl_slave_priv: N
     Repl_client_priv: N
     Create_view_priv: N
       Show_view_priv: N
  Create_routine_priv: N
   Alter_routine_priv: N
     Create_user_priv: N
             ssl_type:
           ssl_cipher:
          x509_issuer:
         x509_subject:
        max_questions: 0
          max_updates: 0
      max_connections: 0
 max_user_connections: 0
1 row in set (0.00 sec)
*************************************************************




-- 针对某个库做授权：
grant all on zp.* to lisi@'192.168.1.%';

mysql> show databases;
lisi:
		+--------------------+
		| Database           |
		+--------------------+
		| information_schema |
		| zp                 |
		+--------------------+
root:
		mysql> show databases;
		+--------------------+
		| Database           |
		+--------------------+
		| information_schema |
		| eyou               |
		| ffllcms            |
		| flbb               |
		| mysql              |
		| owncms             |
		| phpmyadmin         |
		| test               |
		| test1              |
		| zp                 |
		+--------------------+

	
	mysql的db 表中：

mysql> select * from db \G;
*************************** 1. row ***************************
                 Host: 192.168.1.%
                   Db: zp
                 User: lisi
          Select_priv: Y
          Insert_priv: Y
          Update_priv: Y
          Delete_priv: Y
          Create_priv: Y
            Drop_priv: Y
           Grant_priv: N
      References_priv: Y
           Index_priv: Y
           Alter_priv: Y
Create_tmp_table_priv: Y
     Lock_tables_priv: Y
     Create_view_priv: Y
       Show_view_priv: Y
  Create_routine_priv: Y
   Alter_routine_priv: Y
         Execute_priv: Y
1 row in set (0.00 sec)
*************************************************************

revoke all on zp.* from lisi@'192.168.1.%';  -- 收回db的权限





针对表级别：
grant insert,update,select on zp.goods to lisi@'192.168.1.%';
-- 同上，只是只有表的操作权限了

-- 还可以精确到列
-- 常用授权权限全在手册上

常用授权权限全在手册上！！！