shell mysql 登录用户_shell mysql 创建用户 授权

先设置该用户只有show database权限 grant select,insert,update,delete on redmine1.* to jira@"%" identified by "jira";   新增超级权限并允许远程访问：  GRANT ALL PRIVILEGES ON *.* TO 'myuser'@'192.168.1.3' IDENTIFIED BY 'mypassword' WITH GRANT

脚本代码MySql用户管理使用CREATE USER，DROP USER，RENAME USER，SET PASSWORD等语法来完成，而MySql中用户权限配置管理中大部分都可以使用GRANT(赋予权限)和REVOKE(撤销权限)这两个语法来操作。其中，需要注意的是GRANT语句来赋予权限时，当被赋予

#!/bin/bash

#功能描述：创建用户

#参数1 用户名称

#参数2 密码

#参数3 数据库名称1

#参数4 权限1(readwrite、readonly)

# 数据库名称n

# 权限n(readwrite、readonly)

username=root

password=root

host=10.70.2.157

port=3306

connectstr="mysql -u${username} -p${password} -h${host} -P${port}"

#创建用户

result=`${connectstr} -e "CREATE USER \"$1\"@\"localhost\" IDENTIFIED BY \"$2\";CREATE USER \"$1\"@\"%\" IDENTIFIED BY \"$2\"" 2>&1`

# $? 值为 0，表示执行成功

if [ $? -eq 0 ]

then

#分配公共权限

result=`${connectstr} -e "GRANT PROCESS, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO \"$1\"@\"%\" ;

GRANT SELECT ON performance_schema.* TO \"$1\"@\"%\";

GRANT SELECT ON mysql.proc TO \"$1\"@\"%\";

GRANT SELECT ON mysql.general_log TO \"$1\"@\"%\";

GRANT SELECT ON mysql.func TO \"$1\"@\"%\";

GRANT SELECT ON mysql.time_zone_transition_type TO \"$1\"@\"%\";

GRANT SELECT ON mysql.time_zone_leap_second TO \"$1\"@\"%\";

GRANT SELECT ON mysql.slow_log TO \"$1\"@\"%\";

GRANT SELECT ON mysql.time_zone_name TO \"$1\"@\"%\";

GRANT SELECT ON mysql.help_category TO \"$1\"@\"%\";

GRANT SELECT ON mysql.help_relation TO \"$1\"@\"%\";

GRANT SELECT ON mysql.time_zone_transition TO \"$1\"@\"%\";

GRANT SELECT ON mysql.help_topic TO \"$1\"@\"%\";

GRANT SELECT ON mysql.time_zone TO \"$1\"@\"%\";

GRANT SELECT ON mysql.event TO \"$1\"@\"%\";

GRANT SELECT ON mysql.help_keyword TO \"$1\"@\"%\";

" 2>&1`

if [ $? -eq 0 ]

then

#分配特定数据库权限

#第一个参数是用户名，第二个参数是密码，然后：数据库名 权限 数据库名 权限....

if [ $# -gt 3 ]

then

count=0

for var in $@

do

count=`expr ${count} + 1`

if [ ${count} -eq 1 ] || [ ${count} -eq 2 ]

then

continue

fi

#余数是1，说明 var 是数据库，余数是0，说明 var 是权限

quyu=`expr ${count} % 2`

if [ $quyu -eq 0 ]

then

account_privilege=$var

if [ ${account_privilege} = "readwrite" ]

then

grant="GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON"

else

grant="GRANT SELECT, LOCK TABLES, SHOW VIEW ON"

fi

result=`${connectstr} -e "${grant} ${database}.* TO \"$1\"@\"%\"" 2>&1`

if [ $? -ne 0 ]

then

#出现异常，退出

echo "{\"result\":false,\"desc\":\"${result}\"}"

exit 0

fi

else

database=${var}

fi

done

echo "{\"result\":true}"

else

#没有指定数据库权限时，创建用户成功后，直接返回true

echo "{\"result\":true}"

fi

else

echo "{\"result\":false,\"desc\":\"${result}\"}"

fi

else

echo "{\"result\":false,\"desc\":\"${result}\"}"

fi

调用

sh 脚本名称  user01 123456  db01  readwrite   db02  readonly