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

oychw

§11, 管理用户权限
本章包含以下内容:
l      
用GRANT and REVOKE 创建用户账号
l      
权限级别
l        
privilege tables

§11.1使用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可以要求用户必须使用安全的连接


§11.2权限级别

  
   
   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要查询用户的全局权限，数据库权限，表权限，列权限来决定。 REVOKErevoke all on employee.* from fred; REVOKE priv_type [(column_list)] [, priv_type [(column_list)] ...]ON {tbl_name | * | *.* | db_name.*}FROM user_name [, user_name ...]
§11.3特权表
可以直接修改这些表,但是需要使用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.

               
               
               

本文来自ChinaUnix博客，如果查看原文请点：http://blog.chinaunix.net/u/21908/showart_483110.html