MySQL用户管理：
用户帐号：username@hostname，password
用户帐号管理：
CREATE USER
DROP USER
RENAME USER
SET PASSWORD
权限管理：
GRANT
REVOKE
CREATE USER
CREATE USER username@hostname
[ IDENTIFIED BY [PASSWORD] 'password'
| IDENTIFIED WITH auth_plugin [AS 'auth_string' ]
]
查看用户能够使用权限：SHOW GRANTS FOR username@主机名或IP地址
MySQL的 权限类别：
库级别
表级别
字段级别
管理类
程序类
管理类权限：
CREATE TEMPORARY TABLES
CREATE USER
FILE
SUPER
SHOW DATABASES
RELOAD
SHUTDOWN
REPLICATION SLAVE
REPLICATION CLIENT
LOCK TABLES
PROCESS
库级别和表级别：
ALTER
ALTER ROUTINE
CREATE
CREATE ROUTINE
CREATE VIEW
DROP
EXECUTE
GRANT OPTION
INDEX
SHOW VIEW
数据操作（表级别）
SELECT
INSERT
UPDATE
DELETE
字段级别：
SELECT（col1,...）
UPDATE( col1,... )
INSERT（col1,...）
GRANT ALL ON [FUNCTION] *.*
GRANT
mysql> 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']
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
收回授权：
REVOKE
REVOKE
priv_type [(column_list)]
[, priv_type [(column_list)]] ...
ON [object_type] priv_level
FROM user [, user] ...
REVOKE ALL PRIVILEGES, GRANT OPTION
FROM user [, user] ...
几个跟用户授权相关的表：（mysql库中）
db：库级别权限：
host：主机级别的权限，已废弃
table.priov :表级别的权限
colomns.priov：列级别的权限
procs_priov：存储过程和存储函数相关的权限
proxies_priov：代理用户权限
1、获取目录帮助
mysql> HELP CONTENTS
You asked for help about help category: "Contents"
For more information, type 'help - ', where
- is one of the following
categories:
Account Management
Administration
Compound Statements
Data Definition
Data Manipulation
Data Types
Functions
Functions and Modifiers for Use with GROUP BY
Geographic Features
Help Metadata
Language Structure
Plugins
Procedures
Storage Engines
Table Maintenance
Transactions
User-Defined Functions
Utility
2、查看跟用户管理相关的帐号
mysql> HELP Account Management
You asked for help about help category: "Account Management"
For more information, type 'help - ', where
- is one of the following
topics:
CREATE USER
DROP USER
GRANT
RENAME USER
REVOKE
SET PASSWORD
3、创建用户
1)、创建一个testuser的帐号，密码为testpassword
mysql> CREATE USER testuser@localhost IDENTIFIED BY 'testpassword';
Query OK, 0 rows affected (0.00 sec)
2)、通知授权表
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
3)、使用mysql客户端连入访问
[root@localhost ~]# mysql -utestuser -hlocalhost -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.1.73 Source distribution
4、使用新创建的用户创建数据库，提示没有权限
mysql> CREATE DATABASE testdb;
ERROR 1044 (42000): Access denied for user 'testuser'@'localhost' to database 'testdb'
5、获取刚新建的用户的权限，发现只有USAGE，也就是使用的权限
mysql> SHOW GRANTS FOR testuser@localhost;
+-----------------------------------------------------------------------------------------------------------------+
| Grants for testuser@localhost |
+-----------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'testuser'@'localhost' IDENTIFIED BY PASSWORD '*9F69E47E519D9CA02116BF5796684F7D0D45F8FA' |
+-----------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)
6、把用户名为testuser用户改名为tuser，需要使用有管理员权限的用户
mysql> RENAME USER testuser@localhost TO tuser@localhost;
Query OK, 0 rows affected (0.08 sec)
7、查看服务器上执行的所有线程
mysql> SHOW PROCESSLIST
-> ;
+----+----------+-----------+---------+---------+------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+----------+-----------+---------+---------+------+-------+------------------+
| 3 | root | localhost | success | Query | 0 | NULL | SHOW PROCESSLIST |
| 4 | testuser | localhost | NULL | Sleep | 610 | | NULL |
+----+----------+-----------+---------+---------+------+-------+------------------+
2 rows in set (0.00 sec)
8、使用管理员的用户为tuser用户授权以使之可以创建happy的数据库
1）、创建授权
mysql> GRANT CREATE ON happy.* TO tuser@localhost;
Query OK, 0 rows affected (0.00 sec)
2）、tuser可以创建happy的库
mysql> CREATE DATABASE happy;
Query OK, 1 row affected (0.00 sec)
3）、tuser创建别的库时失败，因为没有授权
mysql> CREATE DATABASE hello;
ERROR 1044 (42000): Access denied for user 'tuser'@'localhost' to database 'hello'
4）、删除happy库也提示没有权限
mysql> DROP DATABASE happy;
ERROR 1044 (42000): Access denied for user 'tuser'@'localhost' to database 'happy'
5）、查看tuser用户所获取的权限
mysql> SHOW GRANTS FOR tuser@localhost;
+--------------------------------------------------------------------------------------------------------------+
| Grants for tuser@localhost |
+--------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'tuser'@'localhost' IDENTIFIED BY PASSWORD '*9F69E47E519D9CA02116BF5796684F7D0D45F8FA' |
| GRANT CREATE ON `happy`.* TO 'tuser'@'localhost' |
+--------------------------------------------------------------------------------------------------------------+
9、给tuser用户赋予删除happy数据库的权限
1)、删除授权
mysql> GRANT DROP ON happy.* TO tuser@localhost;
Query OK, 0 rows affected (0.01 sec)
2）、可以看到数据库已可删除
mysql> DROP DATABASE happy;
Query OK, 0 rows affected (0.09 sec)
3）、查看tuser用户所获取的权限，可以看到tuser用户已有CREATE、DROP等权限
mysql> SHOW GRANTS FOR tuser@localhost;
+--------------------------------------------------------------------------------------------------------------+
| Grants for tuser@localhost |
+--------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'tuser'@'localhost' IDENTIFIED BY PASSWORD '*9F69E47E519D9CA02116BF5796684F7D0D45F8FA' |
| GRANT CREATE, DROP ON `happy`.* TO 'tuser'@'localhost' |
+--------------------------------------------------------------------------------------------------------------+
2 rows in set (0.03 sec)
10、给tuser用户赋予增、删、查、改的权限
1）、授权
mysql> GRANT SELECT,INSERT,UPDATE,DELETE ON happy.* TO 'tuser'@'localhost';
Query OK, 0 rows affected (0.00 sec)
2）、查看权限，可以看到增、删查、改的权限已经加入到tuser的权限中
mysql> SHOW GRANTS FOR tuser@localhost;
+--------------------------------------------------------------------------------------------------------------+
| Grants for tuser@localhost |
+--------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'tuser'@'localhost' IDENTIFIED BY PASSWORD '*9F69E47E519D9CA02116BF5796684F7D0D45F8FA' |
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP ON `happy`.* TO 'tuser'@'localhost' |
+--------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
11、收回tuser用户创建happy数据库的权限，使用revoke命令
1）、收回创建权限
mysql> REVOKE CREATE ON happy.* FROM tuser@localhost;
Query OK, 0 rows affected (0.00 sec)
2）、可以看到tuser用户创建数据库失败
mysql> CREATE DATABASE happy;
ERROR 1044 (42000): Access denied for user 'tuser'@'localhost' to database 'happy'
3）、再查看tuser对happy数据库的权限，可以看到已没有CREATE权限
mysql> SHOW GRANTS FOR 'tuser'@'localhost';
+--------------------------------------------------------------------------------------------------------------+
| Grants for tuser@localhost |
+--------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'tuser'@'localhost' IDENTIFIED BY PASSWORD '*9F69E47E519D9CA02116BF5796684F7D0D45F8FA' |
| GRANT SELECT, INSERT, UPDATE, DELETE, DROP ON `happy`.* TO 'tuser'@'localhost' |
+--------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
阅读(1293) | 评论(0) | 转发(0) |
