MYSQL之用户管理

   

1.  权限表

   库mysql下存储权限表，系统根据表的内容为用户赋予权限

   1.1  user表

     字段分4类：

     1.1.1  用户列

         Host、User、Password：主机名、用户、密码

     1.1.2  权限列   权限针对所有数据库

         Select_priv、Insert_priv、*_priv
         值只有Y、N（默认）
       给权限的方式：GRANT赋予、UPDATE更新user表赋予
         Grant_priv：GRANT权限
         Shutdown_priv：停止MYSQL服务权限
         Super_priv：超级权限


         Execute_priv：EXECUTE权限（执行存储过程和函数）

     1.1.3  安全列

         ssl_type、ssl_cipher：用于加密
         x509_issuer、x509_subject：标识用户
        SHOW VARIABLES LIKE 'have_openssl';查看是否具有ssl功能

     1.1.4  资源控制列

        默认为0，无限制
         max_questions、max_updates：规定每小时可以允许执行多少次咨询和更新
         max_connections：每小时可以建立多少连接
         max_user_connections：单个用户可以同时具有的连接数

   1.2  db表和host表   

     两表结构类似。db表存储某个用户对一个数据库的权限。host表是db表的扩展

     1.2.1  用户列

         db表：Host、Db、User字段，表示主机名、数据库名、用户名 
         host表的用户列有Host、Db 

     1.2.2  权限列     权限针对某个用户

         db表比host表多Create_routine_priv、Alter_routine_priv（决定用户是否具有创建和修改存储过程的权限）
 


          

   1.3  tables_priv、columns_priv表    

     tables_priv表对单个表进行权限设置，columns_priv对单个数据列进行权限设置

     1.3.1  tables_priv表（8个字段）

         Host、Db、User、Table_name
         Table_priv、Column_priv、Timestamp、Grantor
         1.3.1.1  Table_priv字段
               包括Select、Insert、Update、Delete、Create、Drop、Grant、References、Index、Alter
         1.3.1.2  Column_priv字段
               对表中数据列操作的权限，包括Select、Insert、Update、References
         1.3.1.3  Timestamp字段
               修改权限的时间
         1.3.1.4  Grantor字段
               谁设置的权限

     1.3.2  columns_priv表（7个字段）

         Host、Db、User、Table_name、Column_name、Column_priv、Timestamp




 权限分配的顺序：user表→db表→tables_priv表→columns_priv表
                 对所有数据库→单个数据库→单个表→单个字段

   1.4  procs_priv表

     对存储过程和存储函数进行权限设置
     Host、Db、User
     Routine_name、Routine_type、Proc_priv、Timestamp、Grantor


     1.4.1  Routine_name
         存储过程、存储函数的名称
     
     1.4.2  Routine_type字段 
         FUNCTION：表示这是一个存储函数
         PROCEDURE：表示这是一个存储过程


     1.4.3  Proc_priv字段
         拥有的权限
         Execute、Alter Routine、Grant


     1.4.4  Timestamp字段、Grantor字段
         存储更新的时间
         存储权限是谁设置的

2.  账户管理

   2.1  登录和退出MYSQL服务

     mysql -h hostname|hostIP -P port -u username -p DatabaseName -e "SQL语句"  


       -h：后接主机名或主机IP（登录本机服务，主机名为 localhost）
       -P：MYSQL服务的端口，默认3306
       -u：用户名
       -p：提示输入密码，也可直接将密码接在-p后，无空格
       DatabaseName：指明登录到某数据库，如果没有该库则登录到MYSQL数据库中
       -e：登录MYSQL服务器后立即执行此SQL语句，然后退出MYSQL服务器
    
     退出：exit;或quit;或\q;

   2.2  新建普通用户

     CREATE USER或在mysql.user表添加用户'、GRANT新建用户
     将Host字段赋值"%"时，则可从mysql服务器以外的机器登录mysql服务器


     2.2.1  
         有CREATE USER的权限时
         CREATE USER user IDENTIFIED BY [PASSWORD]'password'
      ,user IDENTIFIED BY [PASSWORD]'password'...........
         user：由用户名User和主机名Host构成，如 'test1'@'localhost'
         密码是一个普通字符串时不需关键字PASSWORD


     2.2.2
         有对mysql.user的INSERT权限时   
         ①INSERT INTO mysql.user(Host,User,Password) values('hostname','username',PASSWORD('password'));   PASSWORD用来给密码加密
         ②INSERT INTO mysql.user(Host,User,Password,ssl_cipher,x509_issuer,x509_subject) values('hostname','username',PASSWORD('password'),'','','');


         使用户生效：在INSERT语句完成后，  FLUSH  PRIVILEGES;


     2.2.3  GRANT----重要
         GRANT priv_type ON database.table
                 TO user IDENTIFIED BY [PASSWORD]'password',....
         可创建用户、修改密码、设置权限
     

   2.3  删除普通用户

     2.3.1  DROP USER user1,user2...
     2.3.2  DELETE FROM mysql.user WHERE Host='主机名' AND User='用户名';
            FLUSH PRIVILEGES;

   2.4  root用户修改密码

     2.4.1  mysqladmin
         mysqladmin -u username -p password "new_password";  //password为关键字
                                            //此处必须为双引号


     2.4.2  修改user表
         UPDATE mysql.user SET Password=PASSWORD("new_password")
                WHERE User="root" AND Host="localhost";
         FLUSH PRIVILEGES;


     2.4.3  
         SET PASSWORD=PASSWORD("new_password");
         FLUSH PRIVILEGES;

   2.5  root用户修改普通用户密码

     2.5.1
         SET PASSWORD FOR 'username'@'hostname'=PASSWORD("new_password");
   
     2.5.2
         UPDATE mysql.user SET Password=PASSWORD("new_password")
                WHERE User="username" AND Host="hostname"; 


     2.5.3  GRANT
         GRANT priv_type ON database.table
                 TO user IDENTIFIED BY [PASSWORD]'password',....

   2.6  普通用户修改自己的密码

     SET PASSWORD=PASSWORD('new_password');

   2.7  root密码丢失

     （1）停止权限判断启动MYSQL服务
         ①windows
           mysqld --skip-grant-tables
         或mysqld-nt --skip-grant-tables
         或net start mysql --skip-grant-tables
         ②linux 
           mysqld_safe --skip-grant-tables user=mysql
         或/etc/init.d-mysql start --mysqld --skip-grant-tables
     （2）登录root用户，设置新密码
          mysql -u root
          UPDATE mysql.user SET Password=PASSWORD("new_password")
                WHERE User="root" AND Host="localhost"; //只能用这个
        加载权限表：FLUSH PRIVILEGES;
           

3.  权限管理

   3.1  各种权限

  _____________________________________________________
    user表中的列        权限范围
     Create_priv        数据库、表或索引
     Drop_priv          数据库或表
  _____________________________________________________
   

   3.2  授权

     对SUPER、GRANT权限要小心


     GRANT priv_type  列1，列2.. ON database.table
           TO user  IDENTIFIED BY PASSWORD 'password' 
              WITH with_option...   //没有列参数则作用于整个表；所有库，*.*
                                 
     with_option：①GRANT OPTION：被授权的用户可以将这些权限赋予别的用户
                  ②MAX_QUERIES_PER_HOUR count：设置每个小时可以允许执行count次查询                
                  ③MAX_UPDATES_PER_HOUR count：设置每个小时可以允许执行count次更新
                  ④MAX_CONNECTIONS_PER_HOUR count：设置每个小时可以建立count连接
                  ⑤MAX_USER_CONNECTIONS count：设置单个用户可以同时具有count个连接数   

   3.3  收回权限  

     3.3.1 
         REVOKE priv_type 列1，列2...
              ON database.table
              FROM user1,user2...
     
     3.3.2  回收全部权限
         REVOKE ALL PRIVILEGES,GRANT OPTION FROM user1,user2...
      

   3.4  查看权限

     SELECT * FROM mysql.user;
   或SHOW GRANT FOR 'username'@'hostname';