009-LAMP_MySQL_privilege
用户及账号管理
创建用户
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.
创建一个用户shirley，其通过主机172.16.249.207登陆到mysql-server，密码为2012
MariaDB [(none)]> CREATE USER 'shirley'@'172.16.249.207' IDENTIFIED BY '2012';
Query OK, 0 rows affected (0.00 sec)
在host为172.16.249.207的主机以shirley用户登录mariadb-server，server的地址为172.16.249.206
# 在另一台主机172.16.249.207上安装mysql服务器，包括客户端
[root@localhost ~]# yum install mysql-server
# 使用mysql客户端连接
[root@localhost ~]# mysql -ushirley -h172.16.249.206 -p2012
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.5.41-MariaDB MariaDB Server
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> show databases; # 可以发现shirley用户的权限有限
+--------------------+
| Database |
+--------------------+
| information_schema |
| test |
+--------------------+
2 rows in set (0.00 sec)ffot
以下是mysql.user表中的用户信息，表示shirley用户可以通过172.16.249.207远程登录
MariaDB [mysql]> select Host,User,Password from mysql.user where User='shirley';
+----------------+---------+-------------------------------------------+
| Host | User | Password |
+----------------+---------+-------------------------------------------+
| 172.16.249.207 | shirley | *9F6F2DC1B40B6DF5D2A5F762E1CF33782CA1AB29 |
+----------------+---------+-------------------------------------------+
1 row in set (0.00 sec)重要的事情说三遍
注意：在上面有两个IP地址，我在这里的时候真的真的萌B了；需要指出的是，创建用户的时候@的IP表示允许远程登陆的主机；而mysql -ushirley -hx.x.x.x表示的是远程的mariadb的服务器地址！
注意：在上面有两个IP地址，我在这里的时候真的真的萌B了；需要指出的是，创建用户的时候@的IP表示允许远程登陆的主机；而mysql -ushirley -hx.x.x.x表示的是远程的mariadb的服务器地址！
注意：在上面有两个IP地址，我在这里的时候真的真的萌B了；需要指出的是，创建用户的时候@的IP表示允许远程登陆的主机；而mysql -ushirley -hx.x.x.x表示的是远程的mariadb的服务器地址！
删除用户
<font color=099ff size=5 face="΢ÈíÑźÚ">这里的user使用'username'@'host'完整表示</font>
Syntax:
DROP USER user [, user] ...
The DROP USER statement removes one or more MySQL accounts and their
privileges. It removes privilege rows for the account from all grant
tables. To use this statement, you must have the global CREATE USER
privilege or the DELETE privilege for the mysql database. Each account
name uses the format described in
http://dev.mysql.com/doc/refman/5.5/en/account-names.html. For example:
DROP USER 'jeffrey'@'localhost';
If you specify only the user name part of the account name, a host name
part of '%' is used.
删除shirley用户
MariaDB [mysql]> DROP USER 'shirley'@'172.16.249.207';
[root@localhost ~]# mysql -ushirley -h172.16.249.206 -p2012
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 5.5.41-MariaDB MariaDB Server
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> show databases; # 授权之后shirley用户可以有人以权限了。
+--------------------+
| Database |
+--------------------+
| information_schema |
| db01 |
| mysql |
| performance_schema |
| sdb |
| test |
| wordpress |
+--------------------+
7 rows in set (0.00 sec)用户授权
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 ...]
priv_type:#指定的权限
ALL
SELECT
DELETE
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
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
授权shirley用户可以访问所有数据库,并刷新授权表
MariaDB [mysql]> GRANT ALL PRIVILEGES ON *.* TO 'shirley'@'172.16.249.207';
Query OK, 0 rows affected (0.00 sec)
MariaDB [mysql]> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)授权husa用户可以访问mysql.user表，且在这个表上的权限有限
MariaDB [mysql]> GRANT SELECT,INSERT,DELETE PRIVILEGES ON mysql.user TO 'husa'@'172.16.249.207';
Query OK, 0 rows affected (0.00 sec)查看授权
Syntax:
SHOW GRANTS [FOR user]
This statement lists the GRANT statement or statements that must be
issued to duplicate the privileges that are granted to a MySQL user
account. The account is named using the same format as for the GRANT
statement; for example, 'jeffrey'@'localhost'. If you specify only the
user name part of the account name, a host name part of '%' is used.
For additional information about specifying account names, see [HELP
GRANT].
mysql> SHOW GRANTS FOR 'root'@'localhost';
+---------------------------------------------------------------------+
| Grants for root@localhost |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
+---------------------------------------------------------------------+
To list the privileges granted to the account that you are using to
connect to the server, you can use any of the following statements:
SHOW GRANTS;
SHOW GRANTS FOR CURRENT_USER;
SHOW GRANTS FOR CURRENT_USER();
If SHOW GRANTS FOR CURRENT_USER (or any of the equivalent syntaxes) is
used in DEFINER context, such as within a stored procedure that is
defined with SQL SECURITY DEFINER), the grants displayed are those of
the definer and not the invoker.
URL: http://dev.mysql.com/doc/refman/5.5/en/show-grants.html
查看root用户权限
MariaDB [db01]> SHOW GRANTS for CURRENT_USER;
+---------------------------------------------------------------------+
| Grants for root@localhost |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION |
+---------------------------------------------------------------------+
2 rows in set (0.00 sec)查看shirley用户权限
mysql> SHOW GRANTS for CURRENT_USER\g\c
+------------------------------------------------------------------------------------------------------------------------------+
| Grants for shirley@172.16.249.207 |
+------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'shirley'@'172.16.249.207' IDENTIFIED BY PASSWORD '*9F6F2DC1B40B6DF5D2A5F762E1CF33782CA1AB29' |
+------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)回收权限
Syntax:
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] ...
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
回收shirley用户对mysql.user表的查看权限
MariaDB [mysql]> REVOKE SELECT ON *.* FROM 'shirley'@'172.16.249.207';
Query OK, 0 rows affected (0.00 sec)
[root@localhost ~]# mysql -ushirley -h172.16.249.206 -p2012
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 5.5.41-MariaDB MariaDB Server
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> SELECT Host,User,Password FROM mysql.user;
ERROR 1142 (42000): SELECT command denied to user 'shirley'@'172.16.249.207' for table 'user'回收权限后并不能对已经链接的shirley用户立即生效，只有在shirley用户退出登录并重新连接后，权限才会重新生效
注意：mariadb服务进程启动时，会读取mysql库的所有授权表至内存中
1、GRANT或REVOKE命令等执行的操作权限会保存至授权表中，mariadb此时一般会自动重读授权表，权限修改会立即生效
2、其他方式实现的权限修改，要想生效，必须手动运行FLUSH PRIVILEGES命令才行
mysql_secure_installation
安装mysql之后，会有一个设置mysql用户安全机制的脚本，运行之确保server的安全。
[root@husa ~]# mysql_secure_installation #帮助实现安全设定，然后就可以设置密码
/usr/bin/mysql_secure_installation:行379: find_mysql_client: 未找到命令
NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB
SERVERS IN PRODUCTION USE! PLEASE READ EACH STEP CAREFULLY!
In order to log into MariaDB to secure it, we'll need the current
password for the root user. If you've just installed MariaDB, and
you haven't set the root password yet, the password will be blank,
so you should just press enter here.
Enter current password for root (enter for none):
扫一扫
3459
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
