RDBMS必须要保证数据安全，包括传输安全、存储安全，访问安全。RDBMS不能被随意的非授权访问，为了确保每一个访问数据库的请求，都是合法的授权的。对于mysql而言，也是如此。
用户：是用来获取资源的凭证。mysql用户是一个针对mysql服务的虚拟用户，和操作系统没有关系，所以mysql的用户和系统用户没有任何关系；一般情况下，用户名是公开的，密码只有授权用户才可以得到，密码使用mysql自己的加密函数进行加密，默认是PASSWORD函数。用户名@主机，只有通过对应的主机和用户名和密码才能登陆mysql。mysql的用户密码仅仅是控制是能登陆
登陆成功后，用户是否可以访问具体的某个库、表、或者其他数据，还需要有权限。权限有一些表来维护，如：user、db、host表等，mysql启动时回读取这些表，并在内存中生成授权表。
加载进内存的原因：任何对mysql的操作几乎都需要授权表的查询，这些表使用率特别高，放入内存可以加速访问
关于权限的六张授权表：
| 表名 | 功能 | |
|---|---|---|
| user | 用户帐号、全局权限、非权限字段 | |
| db | 库级别的权限定义 | |
| host | 已经废弃 | |
| tables_priv | 表级别的权限定义 | |
| columns | 列级别权限定义 | |
| procs_priv | 存储过程和存储函数相关权限定义 | |
| proxies_priv | 代理用户权限定义 |
用户帐号由用户名+主机，用户名一般在16个字符以内，主机可以使用主机名、IP、网络地址等。
--skip-name-resolve：略过主机名反解等操作，大大提高用户登陆时的认证效率。
权限级别：
全局级别
库级别：Databases
表级别：tables
列级别：columns
存储过程和存储函数级别
服务器管理级别：Server administration
Table 6.2 Permissible Static Privileges for GRANT and REVOKE：
| Privilege | Column | Context(应用范围) |
|---|---|---|
| ALL [PRIVILEGES] | Synonym for “all privileges” | Server administration |
ALTER | Alter_priv | Tables |
ALTER ROUTINE | Alter_routine_priv | Stored routines |
CREATE | Create_priv | Databases, tables, or indexes |
CREATE ROLE | Create_role_priv | Server administration |
CREATE ROUTINE | Create_routine_priv | Stored routines |
CREATE TABLESPACE | Create_tablespace_priv | Server administration |
CREATE TEMPORARY TABLES | Create_tmp_table_priv | Tables |
CREATE USER | Create_user_priv | Server administration |
CREATE VIEW | Create_view_priv | Views(视图权限) |
DELETE | Delete_priv | Tables |
DROP | Drop_priv | Databases, tables, or views |
DROP ROLE | Drop_role_priv | Server administration |
EVENT | Event_priv | Databases |
EXECUTE | Execute_priv | Stored routines(存储过程相关权限) |
FILE | File_priv | File access on server host |
GRANT OPTION | Grant_priv | Databases, tables, or stored routines |
INDEX | Index_priv | Tables |
INSERT | Insert_priv | Tables or columns |
LOCK TABLES | Lock_tables_priv | Databases |
PROCESS | Process_priv | Server administration |
PROXY | See proxies_priv table | Server administration |
REFERENCES | References_priv | Databases or tables |
RELOAD | Reload_priv | Server administration |
REPLICATION CLIENT | Repl_client_priv | Server administration |
REPLICATION SLAVE | Repl_slave_priv | Server administration |
SELECT | Select_priv | Tables or columns |
SHOW DATABASES | Show_db_priv | Server administration |
SHOW VIEW | Show_view_priv | Views |
SHUTDOWN | Shutdown_priv | Server administration |
SUPER | Super_priv | Server administration |
TRIGGER | Trigger_priv | Tables |
UPDATE | Update_priv | Tables or columns |
USAGE | Synonym for “no privileges” | Server administration |
创建用户
方法一
创建用户可以是用CREATE USER命令：
MariaDB [fsx]> HELP CREATE USER Name: 'CREATE USER' Description: Syntax: CREATE USER user_specification(用户定义) [, user_specification] ... user_specification: user [ IDENTIFIED BY [PASSWORD] 'password' | IDENTIFIED WITH auth_plugin [AS 'auth_string'] ]
具体示例：
创建一个名为fsx的用户：
MariaDB [mysql]> CREATE USER qpy@'%' IDENTIFIED BY "qq123.456"; Query OK, 0 rows affected (0.00 sec) //创建用户默认只有USAGE权限，但是要flush一次，让mysql重读授权表(mysql会自动触发)
方法二
可以使用GRANT命令，这是授权命令，授权时如果用户帐号不存在，会自动调用CREATE USER命令创建用户帐号。为用户授权方法下一节学习
方法三
在mysql库的user表中插入数据，使用INSERT INTO命令：
MariaDB [mysql]> INSERT INTO user (User) VALUES('coco'); Query OK, 1 row affected, 4 warnings (0.00 sec) MariaDB [mysql]> SELECT User,Password FROM user; +-----------+-------------------------------------------+ | User | Password | +-----------+-------------------------------------------+ | root | *A85CD7E5412791985DC4E62D4356D1DEE177D6B8 | | root | *A85CD7E5412791985DC4E62D4356D1DEE177D6B8 | | root | *A85CD7E5412791985DC4E62D4356D1DEE177D6B8 | | root | *A85CD7E5412791985DC4E62D4356D1DEE177D6B8 | | coco | | +-----------+-------------------------------------------+ MariaDB [mysql]> UPDATE user SET Password=PASSWORD("coco123.456") WHERE User="coco"; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 MariaDB [mysql]> SELECT User,Password FROM user; +-----------+-------------------------------------------+ | User | Password | +-----------+-------------------------------------------+ | root | *A85CD7E5412791985DC4E62D4356D1DEE177D6B8 | | root | *A85CD7E5412791985DC4E62D4356D1DEE177D6B8 | | root | *A85CD7E5412791985DC4E62D4356D1DEE177D6B8 | | root | *A85CD7E5412791985DC4E62D4356D1DEE177D6B8 | | coco | *99AD894F35AD42B5F00AAC24D681BC41264D863D | +-----------+-------------------------------------------+
查看某个用户授权信息，可以使用SHOW GRANT FOR "username@localhost"；如：
MariaDB [mysql]> SHOW GRANTS FOR qpy@'%'\G *************************** 1. row *************************** Grants for qpy@%: GRANT USAGE ON *.* TO 'qpy'@'%' IDENTIFIED BY PASSWORD '*43358C9D73D651A44061750BEC2981B021FEA8CB' //创建用户默认usage权限，没有任何权限
创建好用户尝试登陆：
[root@mysql ~]# mysql -ufsx -p Enter password: Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 36 Server version: 5.5.35-MariaDB MariaDB Server Copyright (c) 2000, 2013, Oracle, Monty Program Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> MariaDB [(none)]> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | test | +--------------------+ 2 rows in set (0.00 sec) MariaDB [(none)]> CREATE DATABASE fsx_user; ERROR 1044 (42000): Access denied for user ''@'localhost' to database 'fsx_user' //此时的fsx用户只有很少很少的权限，甚至没有创建库的权限，所以要在root用户下为之授权，请看下节
为用户授权
授权使用GRANT命令，先查看GRANT的帮助：
MariaDB [(none)]> HELP GRANT; Name: 'GRANT' Description: Syntax: 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] ...}]//连接时的属性 [WITH with_option ...] //额外的授权属性 object_type: //对象类型，可以省略，默认是表，但是如果表和函数或者过程重名，就需要指明 TABLE 表 | FUNCTION 存储函数 | PROCEDURE 存储过程 priv_level: * 所有库 | *.* 所有库的所有表 | db_name.* 某个库的所有表 | db_name.tbl_name 某个库的某个表 | tbl_name 特定表 | db_name.routine_name 某个库的存储函数或者存储过程 user_specification: user [ IDENTIFIED BY [PASSWORD] 'password' | IDENTIFIED WITH auth_plugin [AS 'auth_string'] ] ssl_option: //使用ssl连接认证 SSL | X509 | CIPHER 'cipher' | ISSUER 'issuer' | SUBJECT 'subject' with_option: GRANT OPTION | MAX_QUERIES_PER_HOUR count //每小时最多允许发起几次请求 | MAX_UPDATES_PER_HOUR count //每小时最多允许几次UPDATE | MAX_CONNECTIONS_PER_HOUR count //每小时最多允许几次连接 | MAX_USER_CONNECTIONS count //最大并发连接数 // priv_type 权限类型 ON priv_level 权限级别 TO user_specification 用户选项
使用root用户对fsx用户进行授权：
MariaDB [(none)]> GRANT CREATE ON fsx_test.* TO fsx@'%'; Query OK, 0 rows affected (0.00 sec) //给fsx用户一个可以创建fsx_test库的权限
再次使用fsx用户登陆，执行CREATE命令：
MariaDB [(none)]> CREATE DATABASE fsx_test; Query OK, 1 row affected (0.00 sec) MariaDB [(none)]> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | fsx_test | | test | +--------------------+ 3 rows in set (0.00 sec) MariaDB [fsx_test]> CREATE TABLE test (id INT UNSIGNED AUTO_INCREMENT NOT NULL,name VARCHAR(30),PRIMARY KEY(id)); Query OK, 0 rows affected (0.39 sec) MariaDB [fsx_test]> INSERT INTO test (name) VALUES('fsx'); ERROR 1142 (42000): INSERT command denied to user 'fsx'@'localhost' for table 'test' //但是此时只有创建库和表的权限，并没有其他权限，所以也不能插入
使用root用户为fsx用户提供INSERT、ALTER和SELECT权限：
MariaDB [(none)]> GRANT INSERT ON fsx_test.* TO fsx@'%'; Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> GRANT SELECT ON fsx_test.* TO fsx@'%'; Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> GRANT ALTER ON fsx_test.* TO fsx@'%'; Query OK, 0 rows affected (0.00 sec)
再次在fsx用户登陆的mysql中进行插入(重新登陆fsx用户，因为和会话连接相关)：
MariaDB [fsx_test]> INSERT INTO test (name) VALUES('fsx');Query OK, 1 row affected (0.06 sec) MariaDB [fsx_test]> ALTER TABLE test ADD age TINYINT UNSIGNED; Query OK, 1 row affected (0.15 sec) Records: 1 Duplicates: 0 Warnings: 0 MariaDB [fsx_test]> DESC test; +-------+---------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+---------------------+------+-----+---------+----------------+ | id | int(10) unsigned | NO | PRI | NULL | auto_increment | | name | varchar(30) | YES | | NULL | | | age | tinyint(3) unsigned | YES | | NULL | | +-------+---------------------+------+-----+---------+----------------+
使用root用户为fsx用户授权UPDATE权限，但只能UPDATE，fsx表中age字段：
MariaDB [(none)]> GRANT UPDATE (age) ON fsx_test.test TO fsx@'%'; Query OK, 0 rows affected (0.00 sec)
使用fsx用户进行UPDATE：
MariaDB [fsx_test]> UPDATE test SET age=11 WHERE id=1; Query OK, 1 row affected (0.07 sec) Rows matched: 1 Changed: 1 Warnings: 0 MariaDB [fsx_test]> UPDATE test SET name="coco" WHERE id=1; ERROR 1143 (42000): UPDATE command denied to user 'fsx'@'localhost' for column 'name' in table 'test' //这里只能对age字段进行修改，不能对其他字段进行修改
使用root用户为fsx用户提供SUPER权限，SUPER是表级别权限
MariaDB [(none)]> GRANT SUPER ON * TO fsx@'%'; ERROR 1046 (3D000): No database selected MariaDB [(none)]> GRANT SUPER ON *.* TO fsx@'%'; Query OK, 0 rows affected (0.00 sec)
为fsx提供SUPER权限，fsx用户对于表，有很大的权限，包括全局变量的修改。
要想让fsx也可以对其他用户进行授权，使用with-grant option
要求qpy用户必须使用ssl连接：
MariaDB [(none)]> GRANT SUPER ON *.* TO qpy@'%' REQUIRE ssl; Query OK, 0 rows affected (0.00 sec) //指定后，就必须使用ssl连接
查看一个用户的权限：
MariaDB [(none)]> SHOW GRANTS FOR fsx@'%'\G *************************** 1. row *************************** Grants for fsx@%: GRANT SUPER ON *.* TO 'fsx'@'%' IDENTIFIED BY PASSWORD '*43358C9D73D651A44061750BEC2981B021FEA8CB' *************************** 2. row *************************** Grants for fsx@%: GRANT CREATE ON `qpy`.* TO 'fsx'@'%' *************************** 3. row *************************** Grants for fsx@%: GRANT SELECT, INSERT, CREATE, ALTER ON `fsx_test`.* TO 'fsx'@'%' *************************** 4. row *************************** Grants for fsx@%: GRANT UPDATE (age) ON `fsx_test`.`test` TO 'fsx'@'%'
删除用户及取消授权
使用DROP USER username删除用户
使用RENAME USER oldname@host TO newname为用户重命名
使用REVOKE FROM 取消用户授权，使用方法和GRANT一样，TO改成FROM，如：
MariaDB [(none)]> REVOKE SELECT ON fsx_test.* FROM fsx@'%'; Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> SHOW GRANTS FOR fsx@'%'\G *************************** 1. row *************************** Grants for fsx@%: GRANT SUPER ON *.* TO 'fsx'@'%' IDENTIFIED BY PASSWORD '*43358C9D73D651A44061750BEC2981B021FEA8CB' *************************** 2. row *************************** Grants for fsx@%: GRANT CREATE ON `qpy`.* TO 'fsx'@'%' *************************** 3. row *************************** Grants for fsx@%: GRANT INSERT, CREATE, ALTER ON `fsx_test`.* TO 'fsx'@'%' *************************** 4. row *************************** Grants for fsx@%: GRANT UPDATE (age) ON `fsx_test`.`test` TO 'fsx'@'%' //可以看到fsx用户已经没有了SELECT权限
找回管理员密码
当我们忘记了管理员(root)密码，需要找回，思路是跳过授权表(为了防止别人在这时也恶意登陆，关掉网络)具体方法步骤：
方法一：
关闭数据库
[root@mysql ~]# systemctl stop mariadb进入mysql安全模式
[root@mysql ~]# mysqld_safe --skip-grant-tables & [1] 3039 [root@mysql ~]# 180531 04:10:03 mysqld_safe Logging to '/var/log/mariadb/mariadb.log'. 180531 04:10:03 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql [root@mysql ~]# mysql Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 1 Server version: 5.5.35-MariaDB MariaDB Server Copyright (c) 2000, 2013, Oracle, Monty Program Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> //进行修改密码
方法二：
关闭mysql服务
[root@mysql ~]# systemctl stop mariadb修改mysql配置文件：
vim /etc/my.cnf 在[mysqld]模块下添加一行信息： skip-grant-tables
重启mysql服务
[root@mysql ~]# systemctl start mariadb [root@mysql ~]# mysql Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 3 Server version: 5.5.35-MariaDB MariaDB Server Copyright (c) 2000, 2013, Oracle, Monty Program Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> //修改密码，这里使用UPDATE命令修改
修改完成之后，记得把步骤2的信息删除，重启服务，使用密码登陆
扫一扫
257
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
