MYSQL 教程:§11, 管理用户权限

管理用户权限

本章包含以下内容:

l        用GRANT and REVOKE 创建用户账号

l        权限级别

l         privilege tables

 

使用GRANT 和 REVOKE创建用户账号

实例:

mysql> grant all on *.* to meil@172.19.148.164  identified by 'meil';

Query OK, 0 rows affected (0.00 sec)

 

这样就可以在172.19.148.164 通过mysql客户端访问所有数据库, 用户名:meil 密码: meil

用户名可长达16个字符。

 

 

GRANT 的详细语法请参见教材

 

GRANT表明要授予的权限,分全局(所有数据库)的和局部的(数据库,表,列)

ON表明被授予权限的对象的内容,可以是指定的表,数据的所有表(dbname.*),所有数据库的所有表(*.*),如果仅使用*,表示当前选中数据库的所有表,如果没有选中数据库,则表示(*.*).

TO 表示被授予权限的对象,如果对象不存在,则会创建

IDENTIFIED BY用于设置用户密码.

set password = password('newpassword');可以改变自己的密码

改变别人的密码       ,要对mysql数据库有权限才行

set password for fred@localhost = password('newpassword');

    WITH GRANT OPTION 指授予权限的权限,WITH  还可以用来限制查询,连接,更新的数目.

    REQUIRE可以要求用户必须使用安全的连接

 

 

权限级别

Table 11.1. User-Level Privileges
Privilege	Meaning
CREATE	User can create tables.
CREATE TEMPORARY TABLES	User can create temporary tables.
DELETE	User can delete rows.
EXECUTE	User can execute procedures.
INDEX	User can create indexes.
INSERT	User can insert rows.
LOCK TABLES	User can lock tables.
SELECT	User can select rows.
SHOW DATABASES	User can execute a SHOW DATABASES command to retrieve the list of available databases.
UPDATE	User can update rows.
USAGE	User can log in, but cannot do anything else.

 

Table 11.2. Administrator-Level Privileges
Privilege	Meaning
ALL	User has all the privileges except WITH GRANT OPTION.
ALTER	User can alter tables. You may give this to some power users, but proceed with caution because it may be used to change the privilege tables.
DROP	User can drop tables. You may give this to trusted users.
FILE	User can load data from a file. Again, you may give this to trusted users. Beware of users trying to load arbitrary files, such as /etc/passwd or similar files!
PROCESS	User can show full process list—that is, see all the processes that MySQL is executing.
RELOAD	User can use the FLUSH statement. This has various purposes. We will look at FLUSH PRIVILEGES later in this chapter and will revisit FLUSH in Chapter 13.
REPLICATION CLIENT	User can check where the masters and slaves are.
REPLICATION SLAVE	Special privilege designed for the special replication user on the slave. See Chapter 16 for more details.
SHUTDOWN	User can run mysqladmin shutdown. For more information see Chapter 13.
SUPER	User can connect even if MySQL has its maximum number of connections and can execute the commands CHANGE MASTER, KILL (thread), mysqladmin debug, PURGE MASTER LOGS, and SET GLOBAL.
WITH GRANT OPTION	User can pass on any privileges he has.

There is one other privilege called REFERENCES. This is reserved for future use, and although you can grant it, at present, it doesn't do anything.

 

GRANT 有4种特权

·         grant all on *.* to fred;

·         grant all on employee.* to fred;

·         grant select on department to fred;

·         grant select (employeeID) on employee to fred;

mysql要查询用户的全局权限，数据库权限，表权限，列权限来决定。

REVOKE

revoke all on employee.* from fred;

REVOKE priv_type [(column_list)] [, priv_type [(column_list)] ...]

ON {tbl_name | * | *.* | db_name.*}

FROM user_name [, user_name ...]

特权表

可以直接修改这些表,但是需要使用flush privileges;使之生效

 

·                     user

·                     db

·                     host

·                     tables_priv

·                     columns_priv

·                     func

user, db, and host确定你是否可以连接到数据库,最后一个暂不相关,存储个人函数的.

 

这方面和实际版本有一定的变化，以实际版本为主。

具体介绍略。

 

§11.4           小结

GRANT and REVOKE

The GRANT statement is used to grant privileges to a user or to create a user account. It has the following format:

 

 

 

GRANT priv_type [(column_list)] [, priv_type [(column_list)] ...]

ON {tbl_name | * | *.* | db_name.*}

TO user_name [IDENTIFIED BY [PASSWORD] 'password']

    [, user_name [IDENTIFIED BY 'password'] ...]

[REQUIRE

    NONE |

    [{SSL| X509}]

    [CIPHER cipher [AND]]

    [ISSUER issuer [AND]]

    [SUBJECT subject]]

[WITH [GRANT OPTION | MAX_QUERIES_PER_HOUR # |

                      MAX_UPDATES_PER_HOUR # |

                      MAX_CONNECTIONS_PER_HOUR #]]

 

The REVOKE statement is used to take privileges away from a user. It has the following format:

 

 

 

REVOKE priv_type [(column_list)] [, priv_type [(column_list)] ...]

ON {tbl_name | * | *.* | db_name.*}

FROM user_name [, user_name ...]

 

Privileges

Individual privileges can be granted to users.

 

These are the user privileges:

 

 

CREATE

 

 

 

CREATE TEMPORARY TABLES

 

 

 

DELETE

 

 

 

EXECUTE

 

 

 

INDEX

 

 

 

INSERT

 

 

 

LOCK TABLES

 

 

 

SELECT

 

 

 

SHOW DATABASES

 

 

 

UPDATE

 

 

 

USAGE

 

 

These are the administrator privileges:

 

 

ALL

 

 

 

ALTER

 

 

 

DROP

 

 

 

FILE

 

 

 

PROCESS

 

 

 

RELOAD

 

 

 

REPLICATION CLIENT

 

 

 

REPLICATION SLAVE

 

 

 

SHUTDOWN

 

 

 

SUPER

 

 

 

WITH GRANT OPTION

 

 

Privilege Tables

MySQL's account and privilege information is stored in the database called mysql.

 

There are five privilege tables.

 

The user table stores usernames, passwords, and global privilege information.

 

The db table stores information about privileges for specific databases.

 

The host table stores information about which databases can be accessed from which hosts.

 

The tables_priv table stores information about table-level privileges.

 

The columns_priv table stores information about column-level privileges.