MySQL
用户：
密码：password()
用户名@主机
账号：认证
权限：授权
启动mysqld进程时，会将所有的授权表读入内存，每次SQL语句的执行都会去查询是否有权限执行
mysql的用户信息存储在mysql数据库的user,db,host,tables_priv,columns_priv,procs_priv表中
user表：包含有用户的账号，全局权限，以及非权限字段
db表：数据库级别的表
host表：废弃
table_priv：表级别权限
columns_priv：列级别权限
procs_priv：存储过程和存储函数相关的权限
proxies_priv：代理用户权限
用户账号：
- 用户名@主机
- 用户名：16个字符以内
- 主机：主机名，IP(192.168.88.123)，网络地址：192.168.0.0/255.255.255.0，通配符：%—192.168.88.%
- –skip-name-resolve：跳过名称解析
权限级别
- 全局级别：SUPER,CREATE_DATABASE,CREATE_USER,PROCESS,PROXY,RELOAD,SUPR,USAGE,SHUTDOWN
- 库
mysql> select * from db\G;
*************************** 1. row ***************************
Host: localhost
Db: performance_schema
User: mysql.session
Select_priv: Y
Insert_priv: N
Update_priv: N
Delete_priv: N
Create_priv: N
Drop_priv: N
Grant_priv: N
References_priv: N
Index_priv: N
Alter_priv: N
Create_tmp_table_priv: N
Lock_tables_priv: N
Create_view_priv: N
Show_view_priv: N
Create_routine_priv: N
Alter_routine_priv: N
Execute_priv: N
Event_priv: N
Trigger_priv: N
*************************** 2. row ***************************
Host: localhost
Db: sys
User: mysql.sys
Select_priv: N
Insert_priv: N
Update_priv: N
Delete_priv: N
Create_priv: N
Drop_priv: N
Grant_priv: N
References_priv: N
Index_priv: N
Alter_priv: N
Create_tmp_table_priv: N
Lock_tables_priv: N
Create_view_priv: N
Show_view_priv: N
Create_routine_priv: N
Alter_routine_priv: N
Execute_priv: N
Event_priv: N
Trigger_priv: Y
2 rows in set (0.00 sec)
- 表:DELETE,ALTER,TRIGGER,DROP,INDEX,
- 列:SELECT,INSERT,UPDATE
- 存储过程和存储函数
字段级别
临时表存储在内存中
heap(16M)
触发器：主动数据库
INSERT,DELETE,UPDATE
创建用户
create user username@host [identified by ‘password’]
mysql> create user edison@'%' identified by 'edison';
Query OK, 0 rows affected (0.24 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
grant创建用户，如果用户不存在，则会自动创建create user进行用户创建
mysql> grant all privileges on *.* to dizzy@'%' identified by 'dizzy';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> select host,user,authentication_string from mysql.user;
+-----------+---------------+-------------------------------------------+
| host | user | authentication_string |
+-----------+---------------+-------------------------------------------+
| localhost | root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| localhost | mysql.session | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| localhost | mysql.sys | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| % | root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| % | edison | *56CD4346C98403D546CAC1CFEE73840892C2D114 |
| % | dizzy | *9C1DFBDE443A0C7E96767F5A39CC60E429ADABC4 |
+-----------+---------------+-------------------------------------------+
6 rows in set (0.00 sec)
授权用户
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库的所有表
| db_name.tbl_name db_name库的tbl_name表[需加入object_type]
| tbl_name 某张特定表
| db_name.routine_name db_name中的存储过程或函数[需加入object_type]
REQUIRE ssl_option:
ssl 需要通过ssl建立会话连接
with_option:
GRANT OPTION
| MAX_QUERIES_PER_HOUR count 每小时最多允许发起请求数,count为0表示不限定
| MAX_UPDATES_PER_HOUR count 每小时最多允许发起更新请求数
| MAX_CONNECTIONS_PER_HOUR count 每小时最多允许发起连接数
| MAX_USER_CONNECTIONS count 同一用户最大并发连接数
重命名用户
RENAME USER old_user TO new_user
[, old_user TO new_user] …
RENAME USER ‘jeffrey’@‘localhost’ TO ‘jeff’@‘127.0.0.1’;
#创建用户1
mysql> create user cactiuser@'%' identified by '123456';
Query OK, 0 rows affected (0.01 sec)
#创建用户2
mysql> grant all privileges on *.* to 'test1'@'%' identified by '123456';
Query OK, 0 rows affected (0.00 sec)
#创建用户3,使用INSERT INTO user
#查看授权表
mysql> show grants for 'test1'@'%';
+---------------------------------------------------------------------------------------------------------------+
| Grants for test1@% |
+---------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'test1'@'%' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' |
+---------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> show grants for 'cactiuser'@'%';
+----------------------------------------------------------------------------------------------------------+
| Grants for cactiuser@% |
+----------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'cactiuser'@'%' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' |
+----------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
#移除权限
mysql> revoke CREATE on *.* from 'cactiuser'@'%';
Query OK, 0 rows affected (0.03 sec)
#使用cactiuser用户登录
[root@mail ~]# mysql -ucactiuser -p
Enter password:
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| test |
+--------------------+
2 rows in set (0.00 sec)
#cactiuser用户没有创建数据库权限，不能创建数据库
mysql> create database cactiuser;
ERROR 1044 (42000): Access denied for user 'cactiuser'@'%' to database 'cactiuser'
#对cactiuser用户添加创建数据库权限
mysql> grant create on cactidb.* to 'cactiuser'@'%';
Query OK, 0 rows affected (0.00 sec)
#在另外一个窗口使用cactiuser用户登录，创建数据库成功
mysql> create database cactidb;
Query OK, 1 row affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| cactidb |
| test |
+--------------------+
4 rows in set (0.00 sec)
#创建表成功
mysql> create table test(id tinyint not null auto_increment primary key,name varchar(30)));
Query OK, 0 rows affected (0.03 sec)
#只能创建表，无法插入数据
mysql> insert into test(name)values('zhangsan');
ERROR 1142 (42000): INSERT command denied to user 'cactiuser'@'localhost' for table 'test'
#通过管理端窗口对cactiuser用户进行insert授权
mysql> grant insert on cactidb.* to 'cactiuser'@'%';
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for 'cactiuser'@'%';
+----------------------------------------------------------------------------------------------------------+
| Grants for cactiuser@% |
+----------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'cactiuser'@'%' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' | |
| GRANT INSERT, CREATE ON `cactidb`.* TO 'cactiuser'@'%' |
+----------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
#重新连接一下会话，插入成功
[root@mail ~]# mysql -ucactiuser -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.6.10 MySQL Community Server (GPL)
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> use cactidb;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> insert into test(name)values('zhangsan');
Query OK, 1 row affected (0.00 sec)
#授权cactiuser用户可以对cactidb中的所有表执行alter和select操作
mysql> grant alter,select on cactidb.* to 'cactiuser'@'%';
Query OK, 0 rows affected (0.00 sec)
#授权cactiuser用户能对cactidb的test表执行update(Age)字段
mysql> grant update(Age) on cactidb.test to 'cactiuser'@'%';
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for 'cactiuser'@'%';
+----------------------------------------------------------------------------------------------------------+
| Grants for cactiuser@% |
+----------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'cactiuser'@'%' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' | |
| GRANT SELECT, INSERT, CREATE, ALTER ON `cactidb`.* TO 'cactiuser'@'%' |
| GRANT UPDATE (Age) ON `cactidb`.`test` TO 'cactiuser'@'%' |
+----------------------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)
#更新数据
mysql> update test set age=13 where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
#授权只能修改cactidb.test表的age字段
mysql> update test set name='lisi' where id=1;
ERROR 1143 (42000): UPDATE command denied to user 'cactiuser'@'localhost' for column 'name' in table 'test'
#cactiuser用户没有权限修改全局变量
mysql> SET GLOBAL TX_ISOLATION='READ-COMMITTED';
ERROR 1227 (42000): Access denied; you need (at least one of) the SUPER privilege(s) for this operation
#授权cactiuser用户具有super权限
mysql> grant super on *.* to 'cactiuser'@'%';
Query OK, 0 rows affected (0.00 sec)
MySQL管理员密码忘记操作
#停止mysql服务
[root@mail ~]# service mysqld stop
#在mysqld的启动脚本中添加--skip-grants-tables --skip-networking两个参数
[root@mail ~]# vim /etc/init.d/mysqld
case "$mode" in
'start')
# Start daemon
# Safeguard (relative paths, core dumps..)
cd $basedir
echo $echo_n "Starting MySQL"
if test -x $bindir/mysqld_safe
then
# Give extra arguments to mysqld with the my.cnf file. This script
# may be overwritten at next upgrade.
$bindir/mysqld_safe **--skip-grants-tables --skip-networking** --datadir="$datadir" --pid-file="$mysqld_pid_file_path" $other_args >/dev/null 2>&1 &
wait_for_pid created "$!" "$mysqld_pid_file_path"; return_value=$?
#使用--skip-grant-tables时不能使用set password来修改密码
mysql> set password for 'root'@'localhost'=password('123456');
ERROR 1290 (HY000): The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement
#使用update直接更新user表内容
mysql> update user set password=password('password') where user='root' and host='localhost';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
#修改/etc/init.d/mysqld文件,去除--skip-grants-tables --skip-networking选项，重新连接mysql使用新密码登录成功
[root@mail data]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.6.10 MySQL Community Server (GPL)
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
#mysql.db表结构
mysql> desc db;
+-----------------------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------------+---------------+------+-----+---------+-------+
| Host | char(60) | NO | PRI | | |
| Db | char(64) | NO | PRI | | |
| User | char(16) | NO | PRI | | |
| Select_priv | enum('N','Y') | NO | | N | |
| Insert_priv | enum('N','Y') | NO | | N | |
| Update_priv | enum('N','Y') | NO | | N | |
| Delete_priv | enum('N','Y') | NO | | N | |
| Create_priv | enum('N','Y') | NO | | N | |
| Drop_priv | enum('N','Y') | NO | | N | |
| Grant_priv | enum('N','Y') | NO | | N | |
| References_priv | enum('N','Y') | NO | | N | |
| Index_priv | enum('N','Y') | NO | | N | |
| Alter_priv | enum('N','Y') | NO | | N | |
| Create_tmp_table_priv | enum('N','Y') | NO | | N | |
| Lock_tables_priv | enum('N','Y') | NO | | N | |
| Create_view_priv | enum('N','Y') | NO | | N | |
| Show_view_priv | enum('N','Y') | NO | | N | |
| Create_routine_priv | enum('N','Y') | NO | | N | |
| Alter_routine_priv | enum('N','Y') | NO | | N | |
| Execute_priv | enum('N','Y') | NO | | N | |
| Event_priv | enum('N','Y') | NO | | N | |
| Trigger_priv | enum('N','Y') | NO | | N | |
+-----------------------+---------------+------+-----+---------+-------+
#user表结构
mysql> desc user;
+------------------------+-----------------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------------+-----------------------------------+------+-----+---------+-------+
| Host | char(60) | NO | PRI | | |
| User | char(16) | NO | PRI | | |
| Password | char(41) | NO | | | |
| Select_priv | enum('N','Y') | NO | | N | |
| Insert_priv | enum('N','Y') | NO | | N | |
| Update_priv | enum('N','Y') | NO | | N | |
| Delete_priv | enum('N','Y') | NO | | N | |
| Create_priv | enum('N','Y') | NO | | N | |
| Drop_priv | enum('N','Y') | NO | | N | |
| Reload_priv | enum('N','Y') | NO | | N | |
| Shutdown_priv | enum('N','Y') | NO | | N | |
| Process_priv | enum('N','Y') | NO | | N | |
| File_priv | enum('N','Y') | NO | | N | |
| Grant_priv | enum('N','Y') | NO | | N | |
| References_priv | enum('N','Y') | NO | | N | |
| Index_priv | enum('N','Y') | NO | | N | |
| Alter_priv | enum('N','Y') | NO | | N | |
| Show_db_priv | enum('N','Y') | NO | | N | |
| Super_priv | enum('N','Y') | NO | | N | |
| Create_tmp_table_priv | enum('N','Y') | NO | | N | |
| Lock_tables_priv | enum('N','Y') | NO | | N | |
| Execute_priv | enum('N','Y') | NO | | N | |
| Repl_slave_priv | enum('N','Y') | NO | | N | |
| Repl_client_priv | enum('N','Y') | NO | | N | |
| Create_view_priv | enum('N','Y') | NO | | N | |
| Show_view_priv | enum('N','Y') | NO | | N | |
| Create_routine_priv | enum('N','Y') | NO | | N | |
| Alter_routine_priv | enum('N','Y') | NO | | N | |
| Create_user_priv | enum('N','Y') | NO | | N | |
| Event_priv | enum('N','Y') | NO | | N | |
| Trigger_priv | enum('N','Y') | NO | | N | |
| Create_tablespace_priv | enum('N','Y') | NO | | N | |
| ssl_type | enum('','ANY','X509','SPECIFIED') | NO | | | |
| ssl_cipher | blob | NO | | NULL | |
| x509_issuer | blob | NO | | NULL | |
| x509_subject | blob | NO | | NULL | |
| max_questions | int(11) unsigned | NO | | 0 | |
| max_updates | int(11) unsigned | NO | | 0 | |
| max_connections | int(11) unsigned | NO | | 0 | |
| max_user_connections | int(11) unsigned | NO | | 0 | |
| plugin | char(64) | YES | | | |
| authentication_string | text | YES | | NULL | |
| password_expired | enum('N','Y') | NO | | N | |
+------------------------+-----------------------------------+------+-----+---------+-------+
43 rows in set (0.00 sec)
错误
#通过grant给不存在用户授权时，会报错
mysql> grant drop on test.* to water@'%';
ERROR 1133 (42000): Can't find any matching row in the user table
解决方法
#通过添加identified by来添加用户，并设定密码，或者先通过create user创建用户，然后再授权
mysql> grant drop on test.* to water@'%' identified by 'water';
Query OK, 0 rows affected, 1 warning (0.00 sec)
1466
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
