mysql 用户权限管理

作/译者：陶会祥 来源：http://blog.chinaunix.net/u3/107145/ 欢迎转载,请注明作者及出处~

相关语法：

CREATE和DROP USER语法

CREATE USER user [IDENTIFIED BY [PASSWORD] 'password']

 [, user [IDENTIFIED BY [PASSWORD] 'password']] ...

DROP USER user [, user] ...

 GRANT和REVOKE语法
GRANT priv_type [(column_list)] [, priv_type [(column_list)]] ...
    ON [object_type] {tbl_name | * | *.* | db_name.*}
    TO user [IDENTIFIED BY [PASSWORD] 'password']
        [, user [IDENTIFIED BY [PASSWORD] 'password']] ...
    [REQUIRE
        NONE |
        [{SSL| X509}]
        [CIPHER 'cipher' [AND]]
        [ISSUER 'issuer' [AND]]
        [SUBJECT 'subject']]
    [WITH with_option [with_option] ...]

object_type =
    TABLE
  | FUNCTION
  | PROCEDURE

with_option =
    GRANT OPTION
  | MAX_QUERIES_PER_HOUR count
  | MAX_UPDATES_PER_HOUR count
  | MAX_CONNECTIONS_PER_HOUR count
  | MAX_USER_CONNECTIONS count
REVOKE priv_type [(column_list)] [, priv_type [(column_list)]] ...
    ON [object_type] {tbl_name | * | *.* | db_name.*}
    FROM user [, user] ...

REVOKE ALL PRIVILEGES, GRANT OPTION FROM user [, user] ...
grant：


reovke 用法：


一个权限管理脚本
(给指定ip加权限及删除10.xx.xx.%的用户):
#!/bin/sh
#echo $#
#[ $# -ne 2 ]  && { echo " error! usage: $0 host port" ; exit 0; }
function grant_it()
{
#!/bin/sh
#echo $#
#[ $# -ne 2 ]  && { echo " error! usage: $0 host port" ; exit 0; }
function grant_it()
{
     host=$1
     port=$2
     echo -e "$host $port \t: grant  privileges  "
     ssh $host "mysql -h 127.0.0.1 -P $port -u root -pxxxxxx -e \"grant all on *.* to  identified by 'psw123'\"" <
/dev/null
}    
     
function revoke_it()
{    
     host=$1
     port=$2
     echo -e "$host $port \t: revoke privileges  "
     ssh $host "mysql -h 127.0.0.1 -P $port -u root -pxxxxxx -e \" delete from mysql.user where user='guest' and host like '%.\%'\"" 
     ssh $host "mysql -h 127.0.0.1 -P $port -u root -pxxxxxx -e \" flush privileges\"" }    
     
cat slave_host | while read line
do
#     echo $line
     host=`echo $line | cut -d ' ' -f 1`
     port=`echo $line | cut -d ' ' -f 3`
#     echo "$host :  $port"
     grant_it "$host" "$port"
     sleep 1
done
     

 
参考：http://dev.mysql.com/doc/refman/5.1/zh/database-administration.html#user-account-management