You are here
马哥 34_03 _MySQL系列之十一——MySQL用户和权限管理 有大用
传输安全,存储安全,访问安全
mysql用户帐号及密码,密码加密方式与操作系统没有关系
Mysql
用户
密码: password()
用户名@主机
账户:认证
权限
授权
mysqld 在启动的时候会读这6张表(名叫mysql数据库的 user,db,host,tables_priv,columns_priv,procs_priv )(这6张表直接读入内存的),并在内存中生成授权表,,,, 认证和授权通过访问内存中的这6张表的结构信息来完成
为什么要放在内存中?因为任何一个sql语句的执行都可能要查询授权表,为了加速这一过程,mysql才读进6张表进内存
user表:用户帐号,全局权限
db表:数据库级别权限,
host表:废弃了,基本上没什么用,已经整合进user表了
table_priv表:表级别权限
columns_priv表:列级别权限
proc_priv表:存储过程和存储函数相关的权限
proxies_priv表:第7张表,(mysql代理服务的时候,授权将mysql的访问给某个代理服务器来验证的时候会用到)代理用户权限
用户帐号:
用户名@主机 (这里的主机大约是指客户端主机吧)
用户名:16字符以内
主机:
IP:172.16.10.177
网络地址:
172.16.0.0/255.255.0.0
通配符: %,_
172.16.%.%
%.magedu.com
如果用主机名,涉及到正解,反解,会消耗许多时间
--skip-name-resolve:跳过名称解析,会大大提高连接的时间
--skip-grant-tables
--skip-name-resolve
权限级别:
全局级别: super, replication client,replication slave,..... 等
库级别:
表级别: delete,alter,trigger......等
grant all priviledge on *.* ....... (即使用第一个星表示所有库,第二个星表示所有表)第二个星表示所有表了,换句话号说,仍然是表级别
列(字段)级别: select ,insert ,update
字段级别的权限:
查询,插入,修改,删除
存储过程和存储函数级别
临时表:指的是内存表,因为速度快,数据不需要永久保存,
heap:堆内存,类型为heap的内存,默认好像是 16M,
一般不允许用户随意创建临时表,mysql当中很多内在的语句在执行的时候,很有可能用到临时表
触发器: 主动数据库
insert,delete,update操作的时候,会自动触发另外一个操作
比如往user表插数据的时候, log(它就是主动数据库)表中自动插入记录
mysql> use mysql
Database changed
mysql>
mysql> select * from db\G
*************************** 1. row ***************************
Host: %
Db: test
User:
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: N
Execute_priv: N
Event_priv: Y
Trigger_priv: Y
*************************** 2. row ***************************
Host: %
Db: test\_% # 下划线本来表示任意单个字符, \_ 表示转义,即本来下划线的意思, % 表示任意字符
User:
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: N
Execute_priv: N
Event_priv: Y
Trigger_priv: Y
2 rows in set (0.00 sec)
mysql>
mysql 权限列表
create 权限,可用于创建表,索引,数据库的权限
drop 权限, 类似于 同 create
grant option 权限 就是自己拥有的权限能不能再授予其它用户
lock tables: 数据库级别权限
references: 数据库级别权限
event: 事件调度器,数据库级别的权限
insert权限 用在表,字段上的
select权限 同 insert
update权限 同 insert
delete权限 只能用在表级别(因为删只能删一行,与某个单独的字段无关)
看看 https://www.mysqlzh.com/doc/44/175.html 吧
file:访问服务器上的文件的,(导出或导入文件)
alter routine: 修改一个存储例程(存储过程,存储函数) 与存储过程,存储函数相关的
create routine: 创建一个存储例程(存储过程,存储函数) 与存储过程,存储函数相关的
execute:执行一个存储例程(存储过程,存储函数) 与存储过程,存储函数相关的
create view:与视图相关的
show view:与视图相关的
alter:修改表,与表相关的
delete:删除表中的数据,与表相关的
index:给表创建索引,与表相关的
create temporary table:创建临时表权限,与表相关的
trigger:触发器,与表相关的
create tablespace:创建表空间,服务器管理级别的权限
create user:创建用户,服务器管理级别的权限
process:查看进程列表,服务器管理级别的权限
proxy:代理服务(代理用户的创建),服务器管理级别的权限
reload:重载授权表(相当于 # flush priviledge ?),服务器管理级别的权限
replication client:用户复制的权限(能够创建的时候,授予具有复制权限的用户的),服务器管理级别的权限
replication slave:用户复制的权限(能够创建的时候,授予具有复制权限的用户的),服务器管理级别的权限
show database:显示数据库,服务器管理级别的权限?
shutdown:关闭服务的,服务器管理级别的权限
super:管理权限(服务器级别的管理权限,用来设定从服务器的主服务器是谁,杀死线程,改变全局变量的值,删除二进制日志,),服务器管理级别的权限
all [privileges]:所有权限,服务器管理级别的权限
usage:没有任何权限,仅允许能连接到mysql服务器上,(事实上创建的默认的用户都有一些简单的权限),服务器管理级别的权限
创建用户:
第1)种方法 create user username@host [identified by 'password'] #一般只有usage权限和简单的show databases 的权限\
第2)种方法 grant #它是授权的,如果帐号不存在,会自动调用create user去创建用户帐号的
第3)种方法 insert into mysql.user #必须要手动 执行 flush privilege,因为它不会主动通知执行清空授权表(然后把硬盘6个有关用户表的数据加载进内存)的(即它不会自动执行 flush privilege )
show grants for 'username@host' #查看某个用户相关的授权信息的
grant all privileges on [object_type] db.* to username@'%'
# 这里 * 不仅仅指所有表,还指存储过程,存储函数(存储过程,存储函数,它们就是一段代码,相当于shell脚本,它能够自动执行一堆sql语句)
object_type
TABLE
| FUNCTION
| PROCEDURE
给数据库db里面的一个叫做abc存储函数给某一个用户以执行权限
(如果既有abc表,又有abc存储函数,,,,下行的 FUNCTION 表示在abc这个存储函数上具有执行权限)
grant execute on FUNCTION db.abc to username@'%'
with_option: #资源使用限定 #下面的不再限定的话,把 count改为 0 就可以了
GRANT OPTION #将自己获得的授权可以授给别人
| MAX_QUERIES_PER_HOUR count # 每小时最多允许发起多少次查询请求
| MAX_UPDATES_PER_HOUR count # 每小时最多允许发起多少次更新请求
| MAX_CONNECTIONS_PER_HOUR count # 每小时最多允许发起多少次连接请求
| MAX_USER_CONNECTIONS count # 同一个用户帐号最大连接数
删除用户
DROP USER 'usename'@'host'
重命名用户
RENAME USER old_name to new_name #名称需要包含用户名加主机
取消授权
REVOKE
开一个putty ( mysql 用户为 root )
mysql> 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']
]
The CREATE USER statement creates new MySQL accounts. To use it, you
must have the global CREATE USER privilege or the INSERT privilege for
the mysql database. For each account, CREATE USER creates a new row in
the mysql.user table and assigns the account no privileges. An error
occurs if the account already exists.
Each account name uses the format described in
http://dev.mysql.com/doc/refman/5.5/en/account-names.html. For example:
CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'mypass';
If you specify only the user name part of the account name, a host name
part of '%' is used.
The user specification may indicate how the user should authenticate
when connecting to the server:
o To enable the user to connect with no password (which is insecure),
include no IDENTIFIED BY clause:
CREATE USER 'jeffrey'@'localhost';
In this case, the account uses built-in authentication and clients
must provide no password.
o To assign a password, use IDENTIFIED BY with the literal plaintext
password value:
CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'mypass';
The account uses built-in authentication and clients must match the
given password.
o To avoid specifying the plaintext password if you know its hash value
(the value that PASSWORD() would return for the password), specify
the hash value preceded by the keyword PASSWORD:
CREATE USER 'jeffrey'@'localhost'
IDENTIFIED BY PASSWORD '*90E462C37378CED12064BB3388827D2BA3A9B689';
The account uses built-in authentication and clients must match the
given password.
o To authenticate the account using a specific authentication plugin,
use IDENTIFIED WITH, where auth_plugin is the plugin name. It can be
an unquoted name or a quoted string literal. 'auth_string' is an
optional quoted string literal to pass to the plugin. The plugin
interprets the meaning of the string, so its format is plugin
specific. Consult the documentation for a given plugin for
information about the authentication string values it accepts.
CREATE USER 'jeffrey'@'localhost'
IDENTIFIED WITH my_auth_plugin;
For connections that use this account, the server invokes the named
plugin and clients must provide credentials as required for the
authentication method that the plugin implements. If the server
cannot find the plugin, either at account-creation time or connect
time, an error occurs. IDENTIFIED WITH can be used as of MySQL 5.5.7.
The IDENTIFIED BY and IDENTIFIED WITH clauses are mutually exclusive,
so at most one of them can be specified for a given user.
For additional information about setting passwords, see
http://dev.mysql.com/doc/refman/5.5/en/assigning-passwords.html.
URL: http://dev.mysql.com/doc/refman/5.5/en/create-user.html
mysql>
mysql> create user cactiuser@'%' identified by 'cactiuser'; # 这个用户信息保存在user表中,而user表信息已经在mysqld启动时
# (要建立的用户或新的授权)已经加载进内存了,所以要用 flush # privilege 方法来重读授权,并重新生效权限的
#事实上,create user 会自动触发 flush privileges (自动清空内存
#授权表,从硬盘加载进内存进行读取)
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> show grants for cactiuser@'%';
+----------------------------------------------------------------------------------------------------------+
| Grants for cactiuser@% |
+----------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'cactiuser'@'%' IDENTIFIED BY PASSWORD '*43DD7940383044FBDE5B177730FAD3405BC6DAD7' |
+----------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql>
重开另一个putty窗口 (mysql 用户为 cactiuser )
[root@mail ~]# mysql -u cactiuser -p #使用 cactiuser 用户登录
Enter password:cactiuser
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.5.28-log Source distribution
Copyright (c) 2000, 2012, 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> show databases; #虽然是 usage ,但是简单的mysql访问命令的权限,它是有的(比如 show databases; )
#但是这里看不到全部的数据库
+--------------------+
| Database |
+--------------------+
| information_schema |
| test |
+--------------------+
2 rows in set (0.01 sec)
mysql>
mysql> create database cactidb; # 没有权限建数据库
ERROR 1044 (42000): Access denied for user 'cactiuser'@'%' to database 'cactidb'
mysql>
mysql> help grant;
Name: 'GRANT'
Description:
Syntax:
GRANT
priv_type [(column_list)] # 权限
[, priv_type [(column_list)]] ... # 用逗号隔开权限
ON [object_type] priv_level # ON 某个对象类型上的对应的权限级别
TO user_specification [, user_specification] ... # TO 某个用户,逗号隔开
[REQUIRE {NONE | ssl_option [[AND] ssl_option] ...}] # 要求用户远程连接的时候要使用NONE 或 ssl(加密的会话)
[WITH with_option ...] #一些额外的授权属性, 如 WITH GRANT OPTION 就是一个用户得到授权后还可以把权利授给其它人
GRANT PROXY ON user_specification
TO user_specification [, user_specification] ...
[WITH GRANT 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 # 必须要使用 X509 格式的证书,来实现建立 ssl 会话的
| CIPHER 'cipher'
| ISSUER 'issuer'
| SUBJECT 'subject'
with_option:
GRANT OPTION
| MAX_QUERIES_PER_HOUR count
| MAX_UPDATES_PER_HOUR count
| MAX_CONNECTIONS_PER_HOUR count
| MAX_USER_CONNECTIONS count
The GRANT statement grants privileges to MySQL user accounts. GRANT
also serves to specify other account characteristics such as use of
secure connections and limits on access to server resources. To use
GRANT, you must have the GRANT OPTION privilege, and you must have the
privileges that you are granting.
Normally, a database administrator first uses CREATE USER to create an
account, then GRANT to define its privileges and characteristics. For
example:
CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'mypass';
GRANT ALL ON db1.* TO 'jeffrey'@'localhost';
GRANT SELECT ON db2.invoice TO 'jeffrey'@'localhost';
GRANT USAGE ON *.* TO 'jeffrey'@'localhost' WITH MAX_QUERIES_PER_HOUR 90;
However, if an account named in a GRANT statement does not already
exist, GRANT may create it under the conditions described later in the
discussion of the NO_AUTO_CREATE_USER SQL mode.
The REVOKE statement is related to GRANT and enables administrators to
remove account privileges. See [HELP REVOKE].
When successfully executed from the mysql program, GRANT responds with
Query OK, 0 rows affected. To determine what privileges result from the
operation, use SHOW GRANTS. See [HELP SHOW GRANTS].
URL: http://dev.mysql.com/doc/refman/5.5/en/grant.html
mysql>
#另一个putty窗口(mysql root 用户)
mysql> grant create on cactidb.* to cactiuser@'%'; # 如果用 IDENTIFIED by,那么密码就会改了
Query OK, 0 rows affected (0.01 sec)
mysql>
#再另一个putty窗口(mysql用户为cactiuser)
mysql> create database cactidb; #现在可以创建数据库 cactidb
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> show databases; #看到了数据库 cactidb
+--------------------+
| Database |
+--------------------+
| information_schema |
| cactidb |
| test |
+--------------------+
3 rows in set (0.00 sec)
mysql>
mysql> use cactidb;
Database changed
mysql>
mysql> create table testdb (id int unsigned not null auto_increment,name char(20),primary key(id)); #创建一个表
Query OK, 0 rows affected (0.07 sec)
mysql>
mysql> insert into testdb (name) values ('Tom'); #没有权限插入,因为只有create权限,没有其它如insert的权限
ERROR 1142 (42000): INSERT command denied to user 'cactiuser'@'localhost' for table 'testdb'
mysql>
#另一个putty窗口(mysql root 用户)
mysql> grant insert on cactidb.* to cactiuser@'%'; #授予insert权限(insert既可以用在表级别,又可用在字段级别) 这里是默认情况下,表示可以用在表级别的
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> flush privileges; #清空一个内存的用户,权限
Query OK, 0 rows affected (0.00 sec)
mysql> grant select on cactidb.* to 'cactiuser'@'%';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql>
#再另一个putty窗口(mysql用户为cactiuser)
mysql> insert into testdb (name) values ('Tom'); #还是不能insert插入
ERROR 1142 (42000): INSERT command denied to user 'cactiuser'@'localhost' for table 'testdb'
mysql>
mysql> show grants for 'cactiuser'@'%'; #看下授权
+----------------------------------------------------------------------------------------------------------+
| Grants for cactiuser@% |
+----------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'cactiuser'@'%' IDENTIFIED BY PASSWORD '*43DD7940383044FBDE5B177730FAD3405BC6DAD7' |
| GRANT CREATE ON `cactiuser`.* TO 'cactiuser'@'%' |
| GRANT SELECT, INSERT, CREATE ON `cactidb`.* TO 'cactiuser'@'%' |
+----------------------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)
mysql>exit # 退出一下
[root@mail ~]# tail /mydata/data/mail.magedu.com.err # 看日志文件,没看出什么问题
201006 10:01:47 InnoDB: Completed initialization of buffer pool
201006 10:01:47 InnoDB: highest supported file format is Barracuda.
201006 10:01:47 InnoDB: Waiting for the background threads to start
201006 10:01:48 InnoDB: 1.1.8 started; log sequence number 1761576
201006 10:01:48 [Note] Server hostname (bind-address): '0.0.0.0'; port: 3306
201006 10:01:48 [Note] - '0.0.0.0' resolves to '0.0.0.0';
201006 10:01:48 [Note] Server socket created on IP: '0.0.0.0'.
201006 10:01:48 [Note] Event Scheduler: Loaded 0 events
201006 10:01:48 [Note] /usr/local/mysql/bin/mysqld: ready for connections.
Version: '5.5.28-log' socket: '/usr/local/mysql/mysql.sock' port: 3306 Source distribution
[root@mail ~]#
#重新 再另一个putty窗口(mysql用户为cactiuser)
[root@mail ~]# mysql -u cactiuser -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 5.5.28-log Source distribution
Copyright (c) 2000, 2012, 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;
Database changed
mysql> insert into testdb (name) values('Tom'); # 可以插入了,说明用户,权限与会话相关
Query OK, 1 row affected (0.01 sec)
mysql> flush privileges # 使用 flush privileges 应该是可以的,,,,但问题是它没有 flush privileges 的权限,所以只能断开重新连接一下
ERROR 1227 (42000): Access denied; you need (at least one of) the RELOAD privilege(s) for this operation
mysql> select * from testdb;
+----+------+
| id | name |
+----+------+
| 1 | Tom |
+----+------+
1 row in set (0.00 sec)
mysql>
mysql> alter table testdb add age tinyint unsigned; #增加字段权限拒绝
ERROR 1142 (42000): ALTER command denied to user 'cactiuser'@'localhost' for table 'testdb'
mysql>
#另一个putty窗口(mysql用户为root)
[root@mail ~]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 5.5.28-log Source distribution
Copyright (c) 2000, 2012, 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> grant alter on cactidb.* to cactiuser@'%'; # 授权 alter (alter是表级别权限)
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> flush privileges; #清空一个内存用户权限表
Query OK, 0 rows affected (0.00 sec)
mysql>
#在另一个putty窗口(mysql用户为cactiuser)
mysql> alter table testdb add age tinyint unsigned; #还是权限不够,我们断开再重新连接吧
ERROR 1142 (42000): ALTER command denied to user 'cactiuser'@'localhost' for table 'testdb'
mysql> exit;
Bye
[root@mail ~]#
#重新连接在另一个putty窗口(mysql用户为cactiuser)
[root@mail ~]# mysql -u cactiuser -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 13
Server version: 5.5.28-log Source distribution
Copyright (c) 2000, 2012, 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> use cactidb;
Database changed
mysql> alter table testdb add age tinyint unsigned; # 可以alter了
Query OK, 1 row affected (0.09 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql>
mysql> desc testdb;
+-------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| name | char(20) | YES | | NULL | |
| age | tinyint(3) unsigned | YES | | NULL | |
+-------+---------------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
mysql>
mysql> select * from testdb;
+----+------+------+
| id | name | age |
+----+------+------+
| 1 | Tom | NULL |
+----+------+------+
1 row in set (0.00 sec)
mysql>
#另一个putty窗口(mysql用户为root)
mysql> grant update(age) on cactidb.testdb to cactiuser@'%'; #给这个数据库这张表的age字段有update权限
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges; #清空一下用户授权表
Query OK, 0 rows affected (0.00 sec)
mysql>
再断开,重新连一次putty窗口(mysql用户为cactiuser)
[root@mail ~]# mysql -u cactiuser -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 14
Server version: 5.5.28-log Source distribution
Copyright (c) 2000, 2012, 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> update testdb set age=30 where id=1; # 可以更新age字段了
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql>
mysql> select * from testdb; #看到age字段值变化了
+----+------+------+
| id | name | age |
+----+------+------+
| 1 | Tom | 30 |
+----+------+------+
1 row in set (0.00 sec)
mysql>
mysql> update testdb set name='Jerry' where id=1; # 没有权限改 name 字段了,因为授予的是 update (age) 权限
ERROR 1143 (42000): UPDATE command denied to user 'cactiuser'@'localhost' for column 'name' in table 'testdb'
mysql>
mysql> show grants for cactiuser@'%'; #查看某用户的授权 权限
+----------------------------------------------------------------------------------------------------------+
| Grants for cactiuser@% |
+----------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'cactiuser'@'%' IDENTIFIED BY PASSWORD '*43DD7940383044FBDE5B177730FAD3405BC6DAD7' |
| GRANT CREATE ON `cactiuser`.* TO 'cactiuser'@'%' |
| GRANT SELECT, INSERT, CREATE, ALTER ON `cactidb`.* TO 'cactiuser'@'%' |
| GRANT UPDATE (age) ON `cactidb`.`testdb` TO 'cactiuser'@'%' |
+----------------------------------------------------------------------------------------------------------+
4 rows in set (0.00 sec)
mysql>
mysql> set @@global.tx_isolation='READ-UNCOMMITTED'; # 没有权限改全局变量的值
ERROR 1227 (42000): Access denied; you need (at least one of) the SUPER privilege(s) for this operation
mysql> set global tx_isolation='READ-UNCOMMITTED'; #这个语句的功能应该同上吧
ERROR 1227 (42000): Access denied; you need (at least one of) the SUPER privilege(s) for this operation
mysql>
在另一putty窗口(root用户)
mysql> grant super to cactiuser@'%'; #语法错误
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'to cactiuser@'%'' at line 1
mysql> grant super on * to cactiuser@'%'; #语法错误
ERROR 1046 (3D000): No database selected
mysql> grant super on *.* to 'cactiuser'@'%'; #这个才正确
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges; #清空
Query OK, 0 rows affected (0.00 sec)
mysql>
再断开,重新连一次putty窗口(mysql用户为cactiuser)
[root@mail ~]# mysql -u cactiuser -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 15
Server version: 5.5.28-log Source distribution
Copyright (c) 2000, 2012, 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> set @@global.tx_isolation='READ-UNCOMMITTED'; #可以设置全局变量了
Query OK, 0 rows affected (0.00 sec)
mysql> set global tx_isolation='READ-UNCOMMITTED'; #这句和上句效果一样的
Query OK, 0 rows affected (0.00 sec)
mysql>
直接得到授权的时候,是无法 grant 给别人的,,只有别人授权给我的时候 带上 with grant option ,才能授权给别人(当然也可以 with grant option给别人了) (所以 with grant option 非常危险,一般不建议使用)
必须要使用 ssl 来连接的话, require ssl , 马哥这里不演示过程了 , require ssl 加上去之后,要求用户必须要通过 ssl 才能够连接;
没写 require ssl ,别人也可以用 ssl 来连接
mysql> help rename user
Name: 'RENAME USER'
Description:
Syntax:
RENAME USER old_user TO new_user # rename user 旧名称 to 新名称
[, old_user TO new_user] ...
The RENAME USER statement renames existing MySQL accounts. To use it,
you must have the global CREATE USER privilege or the UPDATE privilege
for the mysql database. An error occurs if any old account does not
exist or any new account exists. Each account name uses the format
described in http://dev.mysql.com/doc/refman/5.5/en/account-names.html.
For example:
RENAME USER 'jeffrey'@'localhost' TO 'jeff'@'127.0.0.1'; #这是例子
If you specify only the user name part of the account name, a host name
part of '%' is used.
RENAME USER causes the privileges held by the old user to be those held
by the new user. However, RENAME USER does not automatically drop or
invalidate databases or objects within them that the old user created.
This includes stored programs or views for which the DEFINER attribute
names the old user. Attempts to access such objects may produce an
error if they execute in definer security context. (For information
about security context, see
http://dev.mysql.com/doc/refman/5.5/en/stored-programs-security.html.)
The privilege changes take effect as indicated in
http://dev.mysql.com/doc/refman/5.5/en/privilege-changes.html.
URL: http://dev.mysql.com/doc/refman/5.5/en/rename-user.html
mysql>
mysql> help revoke
Name: 'REVOKE'
Description:
Syntax:
REVOKE
priv_type [(column_list)] # priv_type 是权限级别, (column_list) 表示的是哪个字段上的权限
[, priv_type [(column_list)]] ...
ON [object_type] priv_level # on 表
FROM user [, user] ... # from 用户
REVOKE ALL PRIVILEGES, GRANT OPTION
FROM user [, user] ...
REVOKE PROXY ON user
FROM user [, user] ...
The REVOKE statement enables system administrators to revoke privileges
from MySQL accounts. Each account name uses the format described in
http://dev.mysql.com/doc/refman/5.5/en/account-names.html. For example:
REVOKE INSERT ON *.* FROM 'jeffrey'@'localhost';
If you specify only the user name part of the account name, a host name
part of '%' is used.
For details on the levels at which privileges exist, the permissible
priv_type and priv_level values, and the syntax for specifying users
and passwords, see [HELP GRANT]
To use the first REVOKE syntax, you must have the GRANT OPTION
privilege, and you must have the privileges that you are revoking.
To revoke all privileges, use the second syntax, which drops all
global, database, table, column, and routine privileges for the named
user or users:
REVOKE ALL PRIVILEGES, GRANT OPTION FROM user [, user] ...
To use this REVOKE syntax, you must have the global CREATE USER
privilege or the UPDATE privilege for the mysql database.
URL: http://dev.mysql.com/doc/refman/5.5/en/revoke.html
mysql>
在另一putty窗口(root用户)
mysql> show grants for 'cactiuser'@'%'; #查看权限
+----------------------------------------------------------------------------------------------------------+
| Grants for cactiuser@% |
+----------------------------------------------------------------------------------------------------------+
| GRANT SUPER ON *.* TO 'cactiuser'@'%' IDENTIFIED BY PASSWORD '*43DD7940383044FBDE5B177730FAD3405BC6DAD7' |
| GRANT CREATE ON `cactiuser`.* TO 'cactiuser'@'%' |
| GRANT SELECT, INSERT, CREATE, ALTER ON `cactidb`.* TO 'cactiuser'@'%' |
| GRANT UPDATE (age) ON `cactidb`.`testdb` TO 'cactiuser'@'%' |
+----------------------------------------------------------------------------------------------------------+
4 rows in set (0.00 sec)
mysql>
mysql> revoke select on cactidb.* from cactiuser@'%'; # 收回 cactidb数据库的表上的 select 权限
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> show grants for 'cactiuser'@'%'; # select 权限没有了
+----------------------------------------------------------------------------------------------------------+
| Grants for cactiuser@% |
+----------------------------------------------------------------------------------------------------------+
| GRANT SUPER ON *.* TO 'cactiuser'@'%' IDENTIFIED BY PASSWORD '*43DD7940383044FBDE5B177730FAD3405BC6DAD7' |
| GRANT CREATE ON `cactiuser`.* TO 'cactiuser'@'%' |
| GRANT INSERT, CREATE, ALTER ON `cactidb`.* TO 'cactiuser'@'%' |
| GRANT UPDATE (age) ON `cactidb`.`testdb` TO 'cactiuser'@'%' |
+----------------------------------------------------------------------------------------------------------+
4 rows in set (0.00 sec)
mysql>
select 权限是查,一般给予,像其它权限写,要注意,,像 super ,with grant option 要特别慎重
如是忘记了mysql的root帐号,
没有其它数据,没有其它用户的话,直接初始化一下mysql那个数据库(先删掉mysql库,再初始化)就可以了
若有其它用户,有许多授权,只能重新找回,
1)先关掉数据库服务器 # service mysqld stop
2)手动启动mysql(使用命令行启动,或者编辑一下启动脚本,启动完后再改回来)
这里编辑一下启动脚本
~]# vim /etc/init.d/mysqld
$bindir/mysqld_safe --skip-grant-tables --skip-networking --datadir="$datadir" --pid-file="$mysqld_pid_file_path" $other_args >/ dev/null 2>&1 & # 在原来的这一行(约283行)里加上 --skip-grant-tables(跳过授权表) 和 --skip-networking (跳过网络,也就是此时不允许别人从网络连接)
3) 启动服务 # service mysqld start
4)把默认的密码文件关掉 [root@mail ~]# mv .my.cnf .my-bak.cnf (把默认的密码文件关掉)(一般情况下不用密码文件,所以一般情况下跳过此步骤)
5)此时mysql可以连上去了
[root@mail ~]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.28-log Source distribution
Copyright (c) 2000, 2012, 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> use mysql;
Database changed
mysql> select User,Host,Password from user; # 看看几个用户及其密码
+-----------+-----------------+-------------------------------------------+
| User | Host | Password |
+-----------+-----------------+-------------------------------------------+
| root | localhost | *ED0DE3278EF02C0701FD328CA1DA43F22173FF3F |
| root | mail.magedu.com | *ED0DE3278EF02C0701FD328CA1DA43F22173FF3F |
| root | 127.0.0.1 | *ED0DE3278EF02C0701FD328CA1DA43F22173FF3F |
| cactiuser | % | *43DD7940383044FBDE5B177730FAD3405BC6DAD7 |
+-----------+-----------------+-------------------------------------------+
4 rows in set (0.00 sec)
mysql>
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
mysql>
6)通过sql语句改密码
mysql> update user set password=password('123456') where user='root'; #这种方法可改密码
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3 Changed: 3 Warnings: 0
mysql>
mysql> select User,Host,Password from user; #可以看到三个密码都改了
+-----------+-----------------+-------------------------------------------+
| User | Host | Password |
+-----------+-----------------+-------------------------------------------+
| root | localhost | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| root | mail.magedu.com | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| root | 127.0.0.1 | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| cactiuser | % | *43DD7940383044FBDE5B177730FAD3405BC6DAD7 |
+-----------+-----------------+-------------------------------------------+
4 rows in set (0.00 sec)
mysql>
7) 停掉mysql # service mysqld stop
8) 把 $bindir/mysqld_safe --skip-grant-tables --skip-networking --datadir="$datadir" --pid-file="$mysqld_pid_file_path" $other_args >/ dev/null 2>&1 & # 在原来的这一行(约283行)里面加上 --skip-grant-tables(跳过授权表) 和 --skip-networking (跳过网络,也就是此时不允许别人从网络连接) 的这两样东西去掉
[root@mail ~]# vim /etc/init.d/mysqld
$bindir/mysqld_safe --datadir="$datadir" --pid-file="$mysqld_pid_file_path" $other_args >/ dev/null 2>&1 &
9) 启动mysql服务,# service mysqld start 就可以使用新密码了
10) 连上mysql服务器
[root@mail ~]# mysql # 连不上了,因为现在用新密码了
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
[root@mail ~]# mysql -u root -p # 使用密码可以连上
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.5.28-log Source distribution
Copyright (c) 2000, 2012, 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>
启动 mysqld_safe (或者 mysqld) 时传递两个参数:
--skip-grant-tables 跳运授权表
--skip-networking (跳过网络,不让网络上主机连接,是为了安全)
通过更新授权表方式直接修改其密码,而后移除这两个选项重启服务器,就能重新设置管理员密码了