MySQL 用户管理与授权管理

ditel

一、用户管理

[Shell] 纯文本查看 复制代码

1. Mysql的用户通常分为两类：       
     root   MySQL数据库的管理员用户
     普通用户
2. 用户帐号的命名方式：
      username@主机
      username@网络
               可以使用通配符，
                   %: 表示任意字符
                   _: 表示任意单个字符
eg:
   root@localhost                 --> root用户只能通过本机来登录
   [email]root@192.168.0.0[/email]/24            --> root用户只能通过192.168.0.0网段的主机登录
   cacti_user@'192.168.0.%'
3. 创建用户：
mysql> CREATE USER 'user_name'@'host' [IDENTIFIED BY 'password'] ;
4. 为用户更改密码：
   (1).在shell命令行中更改：

# mysqladmin -u root passwrord  'new_password'  -p
# mysqladmin -u root -h this_host_name  password 'new_password'
  (2).在Mysql的命令行中更改
mysql> SET PASSWORD  FOR user_name@'host_name'=PASSWORD('password');
5. 删除用户：
mysql> DROP  USER 'user_name'@'host_name';
mysql >DROP  USER ''@'localhost';                删除匿名用户
mysql> DELETE FROM user  WHERE HOST='::1';
6. 不用输入密码登录 mysql：
# cd ~
# vi .my.cnf
[mysql]
user=root
host=localhost
password=redhat

二、权限管理
1. 为用户授权：

[Shell] 纯文本查看 复制代码

mysql> GRANT  ALL ON *.*  TO user_name  IDENTIFIED BY 'redhat';
mysql> GRANT ALL PRIVILEGES ON player.* TO user_name@'host_name' IDENTIFIED BY 'redhat';
mysql> GRANT SELECT,UPDATE,DELETE ON test.player TO  astd@'192.168.0.%' IDENTIFIED BY '123456';
mysql>flush privileges;                            刷新授权表(进行授权后要刷新内存中的授权表)

2. 查看某个用户的授权情况：

[Shell] 纯文本查看 复制代码

mysql> SHOW GRANTS FOR astd@'192.168.0.%';
+---------------------------------------------------------------------------------------------------------------+
| Grants [email]forastd@192.168.0[/email].%                                                                                   |
+---------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'astd'@'192.168.0.%' IDENTIFIED BY PASSWORD'*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' |
| GRANT SELECT, UPDATE, DELETE ON `test`.`player` TO'astd'@'192.168.0.%'                                       |
+---------------------------------------------------------------------------------------------------------------+

3.收回用户的某些权限：

[Shell] 纯文本查看 复制代码

1
mysql> REVOKE  privilege_list  ON  db.tables  FROM  'user'@'host';

4. 与mysql用户授权相关的表:

[Shell] 纯文本查看 复制代码

mysql> use mysql
Database changed
mysql> show tables;
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| columns_priv              |       --> 字段授权相关的表
| db                        |       --> 库级别授权相关的表
| func                      |
| host                      |
| proc                      |
| procs_priv                |       --> 存储过程和存储函数执行权限相关的表
| tables_priv               |       --> 表级别授权相关的表
| time_zone                 |
| user                      |       --> 用户授权相关的表
+---------------------------+

三、GRANT 的语法

[Shell] 纯文本查看 复制代码

GRANT
    priv_type [(column_list)] [, priv_type [(column_list)]]  ON [object_type] priv_level TOuser_specification [, user_specification] ...
    [REQUIRE {NONE | ssl_option [[AND] ssl_option] ...}]  [WITH with_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
  | X509
  | 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            一个用户最多允许同时发起的连接请求次数

Global level
Global privileges apply to all databases on a given server. These privileges are stored in the mysql.user table. GRANT ALL ON *.* and REVOKE ALL ON *.* grant and revoke only global privileges.

[Shell] 纯文本查看 复制代码

1
2
GRANT ALL ON *.* TO 'someuser'@'somehost';
GRANT SELECT, INSERT ON *.* TO 'someuser'@'somehost';

Database level
Database privileges apply to all objects in a given database. These privileges are stored in the mysql.db and mysql.host tables. GRANT ALL ON db_name.* and REVOKE ALL ON db_name.* grant and revoke only database privileges.

[Shell] 纯文本查看 复制代码

1
2
GRANT ALL ON mydb.* TO 'someuser'@'somehost';
GRANT SELECT, INSERT ON mydb.* TO 'someuser'@'somehost';

Table level
Table privileges apply to all columns in a given table. These privileges are stored in the mysql.tables_priv table. GRANT ALL ON db_name.tbl_name and REVOKE ALL ON db_name.tbl_name grant and revoke only table privileges.

[Shell] 纯文本查看 复制代码

1
2
GRANT ALL ON mydb.mytbl TO 'someuser'@'somehost';
GRANT SELECT, INSERT ON mydb.mytbl TO 'someuser'@'somehost';

If you specify tbl_name rather than db_name.tbl_name, the statement applies to tbl_name in the default database.
Column level
Column privileges apply to single columns in a given table. These privileges are stored in the mysql.columns_priv table. When using REVOKE, you must specifythe same columns that were granted. The column or columns for which the

privileges are to be granted must be enclosed within parentheses.

[Shell] 纯文本查看 复制代码

1
GRANT SELECT (col1), INSERT (col1,col2) ON mydb.mytbl TO 'someuser'@'somehost';

Routine level
The CREATE ROUTINE, ALTER ROUTINE, EXECUTE, and GRANT OPTION privileges apply to stored routines (functions and procedures). They can be granted at the global and database levels. Also, except for CREATE ROUTINE, these privileges

can be granted at the routine level for individual routines and are stored in the mysql.procs_priv table.

[Shell] 纯文本查看 复制代码

1
2
GRANT CREATE ROUTINE ON mydb.* TO 'someuser'@'somehost';
GRANT EXECUTE ON PROCEDURE mydb.myproc TO 'someuser'@'somehost';

大湖之子

人生自古谁无死，啊个拉屎不用纸!

生活如麻

月经不仅仅是女人的痛苦，也是男人的痛苦。

lihu129c

如果跟导师讲不清楚，那么就把他搞胡涂吧!

sxyzy

自从我变成了狗屎,就再也没有人踩在我头上了。

cxin

要是我灌水，就骂我“三个代表”没学好吧。

史蒂夫和斯凯

我抢、我抢、我抢沙发~