--THE grant Tables
As already mentioned,the server uses the information in the grant tables to determine whether to allow clients
to connect,and to detimine for every statement that a connected client issues whether it has sufficient privileges to
execute it.However,the server does not actually access the on-disk grant tables each time it needs
to verify client access because that would result in a great deal of overhead.Instead,the server reads
the grant tables into memory during its startup sequence and uses the in-memory copies to check client access.

    mysql server 使用grant table 来校验客户端连接和每一个sql的执行权限，grant table 在mysql server 启动的时候被载入到内存中。

The server refreshes its in-memory copied of the grant tables under the following conditions:(以下条件会刷新内存中的grant tables)
1.you modify a user account in the on-disk tables by issuing a account-management statement such as
    CREATE USER,GRANT,REVOKE, or SET PASSWORD
2.you tell the server to reload the tables explicitly by issuing a FLUSH PRIVILEGES statement or by executing
    a   MYsqladmin flush-privileges or mysqladmin reload command.

--Creating and DROp user Accounts
  CREATE USER creates a new account and optionally assigns it a passowrd.This statement create a record
       in the user table.It does not grant any privileges;to do so,use the GRANT statement later.(ALternatively,you
       can use GRANT to create the account and grant it privileges at the same time)

   mysql> create user 'ylc'@'localhost' identified by 'mysql'; --% 代表任意一台主机
Query OK, 0 rows affected (0.00 sec)
  select * from mysql.user where user='ylc';
  'localhost', 'ylc', '*E74858DB86EBA20BC33D0AECAE8A8108C56B17FA', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', '', ?, ?, ?, '0', '0', '0', '0', 'mysql_native_password', '', 'N'

      DROP user revokes all privileges for an existing account and then removes the account.It deletes all records
      for the account from any grant table in which they exist.To revoke privileges without removing the account itself,use the revoke statement.

mysql> drop user 'ylc'@'localhost';
Query OK, 0 rows affected (0.00 sec)

mysql> show grants;
+-------------------------------------------------------------------------------
---------------------------------------------------------+
| Grants for root@localhost
|
+-------------------------------------------------------------------------------
---------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*E74
858DB86EBA20BC33D0AECAE8A8108C56B17FA' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION
|
+-------------------------------------------------------------------------------
---------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> select user();
+----------------+
| user() |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)

mysql> select current_user();
+----------------+
| current_user() |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)

mysql>

C:\mysql\MySQL Server 5.6\bin>mysql -u ylc -p -h 127.0.0.1
mysql> \s
--------------
mysql Ver 14.14 Distrib 5.6.14, for Win64 (x86_64)

Connection id:          55
Current database:
Current user:           ylc@localhost
SSL:                    Not in use
Using delimiter:        ;
Server version:         5.6.14-log MySQL Community Server (GPL)
Protocol version:       10
Connection:             127.0.0.1 via TCP/IP
Server characterset:    utf8
Db     characterset:    utf8
Client characterset:    utf8
Conn. characterset:    utf8
TCP port:               3306
Uptime:                 30 min 16 sec

Threads: 11 Questions: 1334 Slow queries: 0 Opens: 93 Flush tables: 1 Open
tables: 86 Queries per second avg: 0.734
--------------

---重命名用户和创建匿名用户
mysql> rename user 'ylc'@'%' to 'dsg'@'%';
Query OK, 0 rows affected (0.02 sec)

mysql> insert into user(host,user,password) values('%','',''); --貌似新版本不能直接插入
ERROR 1364 (HY000): Field 'ssl_cipher' doesn't have a default value

--刷新内存
   flush privileges;

--GRANTING PRIVILEGES
the syntax for the GRANT statement includes serveral sections.In simles form,you specify;
   the privileges to be granted;
   what the privileges apply to
   the account that should be given the privileges
   a passowrd

mysql> create user 'jack'@'localhost';
Query OK, 0 rows affected (0.00 sec)

mysql> grant all privileges on *.* to 'jack'@'localhost' identified by 'jack'; --前面的* 代表任何数据库，后面的* 代表任何数据库的下的任何对象
Query OK, 0 rows affected (0.00 sec)

           ON *.*
           ON db_name.*
           ON db_name.table_name.column_name
           ON db_name.routine_name

GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP ON db_name.* TO 'username'@'localhost' IDENTIFIED BY 'password';

本语句中的权限根据实际需要确定：
"db_name"替换为授权访问的数据表table名
"username"是步骤2授权用户名
"password"是步骤2授权用户的设置密码
这样就为该用户授予了对某数据表的SELECT, INSERT, UPDATE, DELETE, CAREATE, DROP权限。

grant select(idtest1) on test2.test1to 'jack'@'localhost';
set password for 'jack'@'localhost'=password('jack');
flush privileges;

   mysql -ujack -e"select count(1) from test1.t"
--对账户进行资源限制
with_option:
    GRANT OPTION
| MAX_QUERIES_PER_HOUR count
| MAX_UPDATES_PER_HOUR count
| MAX_CONNECTIONS_PER_HOUR count
| MAX_USER_CONNECTIONS count   --连接数
grant all privileges on *.* to 'jack'@'locahost' with MAX_QUERIES_PER_HOUR 2;

--回收权限
mysql> show grants for 'jack'@localhost;
+-------------------------------------------------------------------------------
---------------------------------------+
| Grants for jack@localhost
                                       |
+-------------------------------------------------------------------------------
---------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'jack'@'localhost' IDENTIFIED BY PASSWORD '*9BC
DC990E611B8D852EFAF1E3919AB6AC8C8A9F0' |
| GRANT SELECT (idtest1) ON `test2`.`test1` TO 'jack'@'localhost'
                                       |
+-------------------------------------------------------------------------------
---------------------------------------+
2 rows in set (0.00 sec)

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

mysql> show grants for 'jack'@localhost;
+-------------------------------------------------------------------------------
------------------------------+
| Grants for jack@localhost
                              |
+-------------------------------------------------------------------------------
------------------------------+
| GRANT USAGE ON *.* TO 'jack'@'localhost' IDENTIFIED BY PASSWORD '*9BCDC990E611
B8D852EFAF1E3919AB6AC8C8A9F0' |
| GRANT SELECT (idtest1) ON `test2`.`test1` TO 'jack'@'localhost'
                              |
+-------------------------------------------------------------------------------
------------------------------+
2 rows in set (0.00 sec)
--mysql 重置密码
mysqld_safe --skip-grant-tables
update user set password='' where user='root' and host='localhost';
flush privileges;

ITPub博客

mysql用户管理

最新文章