如何修改mysql 存储过程权限

方法如下：

修改mysql 存储过程的definer

修改mysql.proc表 的definer字段

update mysql.proc set definer='root@%' where db='servant_591up'UPDATE `mysql`.`proc` SET `definer`='root00@%' WHERE `db`='test' AND `name`='jjjj' AND `type`='PROCEDURE'UPDATE `mysql`.`proc` SET `definer`='wtc_678869@%' WHERE `db`='servant_591up' AND `type`='PROCEDURE'

2.修改sql security

ALTER PROCEDURE www SQL SECURITY INVOKER ； ALTER PROCEDURE www SQL SECURITY DEFINER ；

（1）MySQL存储过程是通过指定SQL SECURITY子句指定执行存储过程的实际用户；

（2）如果SQL SECURITY子句指定为DEFINER，存储过程将使用存储过程的DEFINER执行存储过程，验证调用存储过程的用户是否具有存储过程的execute权限和DEFINER用户是否具有存储过程引用的相关对象的权限；

（3）如果SQL SECURITY子句指定为INVOKER，那么MySQL将使用当前调用存储过程的用户执行此过程，并验证用户是否具有存储过程的execute权限和存储过程引用的相关对象的权限；

（4）如果不显示的指定SQL SECURITY子句，MySQL默认将以DEFINER执行存储过程。

3.执行存储过程授权

GRANT EXECUTE ON test.* TO 'wtc'@'%'； GRANT CREATE ROUTINE,ALTER ROUTINE, SELECT,CREATE, INSERT, UPDATE, DELETE, EXECUTE ON test.* TO 'wtc'@'%' IDENTIFIED BY '111111'

CREATE ROUTINE ： 创建存储过程的权限

ALTER ROUTINE ： 修改存储过程的权限

4.删除用户

REVOKE  all ON test.* FROM wtc@'%'

DELETE FROM user WHERE User='user_name' and Host='host_name' 

FLUSH PRIVILEGES

好文要顶 关注我

MySql安装

yum -y install libaio

wget https://cdn.mysql.com//Downloads/MySQL-5.7/mysql-5.7.24-linux-glibc2.12-x86_64.tar.gz

cd /usr/local/soft/mysql/src

mkdir ../5.7.24_3306 &&tar -xzvf mysql-5.7.24-linux-glibc2.12-x86_64.tar.gz -C ../5.7.24_3306 --strip-components 1

mkdir -p /usr/local/soft/mysql/5.7.24_3306/{run,data,logs,bin_log,sock,cert}

groupadd mysql &&useradd -r -g mysql -s /bin/false mysql

cd /usr/local/soft/mysql/5.7.24_3306/bin &&./mysqld --initialize

--explicit_defaults_for_timestamp

--user=mysql

--basedir=/usr/local/soft/mysql/5.7.24_3306

--datadir=/usr/local/soft/mysql/5.7.24_3306/data

--lc_messages_dir=/usr/local/soft/mysql/5.7.24_3306/share

--lc_messages=en_US

2018-12-01T09:56:45.934627Z 0 [Warning] InnoDB: New log files created, LSN=45790

2018-12-01T09:56:46.056837Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.

2018-12-01T09:56:46.127141Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 69d028f9-f54f-11e8-9c4a-00163e08e793.

2018-12-01T09:56:46.130249Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.

2018-12-01T09:56:46.131377Z 1 [Note] A temporary password is generated for root@localhost: sDdPx3>.,oBi

cd /usr/local/soft/mysql/5.7.24_3306/bin &&./mysql_ssl_rsa_setup --datadir=/usr/local/soft/mysql/5.7.24_3306/cert

修改证书权限为644

cd /usr/local/soft/mysql/5.7.24_3306/bin &&./mysql -uroot -S /usr/local/soft/mysql/5.7.24_3306/sock/mysql.sock

use mysql

update user set host = '%', authentication_string = password('123456') where user = 'root'

select host,user,authentication_string from user

flush privileges

grant all privileges on . to 'root'@'%'

flush privileges

cat >/etc/systemd/system/3306.service <<EOF

[Unit]

Description=MySQL 3306

Documentation= http://dev.mysql.com/doc/refman/en/using-systemd.html

After=network.target remote-fs.target nss-lookup.target

[Service]

Type=forking

PIDFile=/usr/local/soft/mysql/5.7.24_3306/run/mysql.pid

ExecStart=/usr/local/soft/mysql/5.7.24_3306/support-files/mysql.server start

ExecReload=/usr/local/soft/mysql/5.7.24_3306/support-files/mysql.server restart

ExecStop=/usr/local/soft/mysql/5.7.24_3306/support-files/mysql.server stop

PrivateTmp=true

[Install]

WantedBy=multi-user.target

EOF

systemctl enable 3306.service

1、

cd /usr/local/soft/mysql/5.7.24.3306/bin &&./mysqld_safe --skip-grant-tables &

cd /usr/local/soft/mysql/5.7.24.3306/bin &&./mysqld_safe --skip-grant-tables --skip-networking &

2、

./mysql -uroot -p -S /usr/local/soft/mysql/5.7.24.3306/sock/mysql.sock

3、

use mysql

update mysql.user set authentication_string=password('123456') where user='root'

flush privileges

mkdir -p /usr/local/soft/mysql/back/5.7.23_3306 &&cp -Rf /usr/local/soft/mysql/5.7.23_3306/* /usr/local/soft/mysql/back/5.7.23_3306

mkdir -p /usr/local/soft/mysql/back/5.7.24_3306 &&cp -Rf /usr/local/soft/mysql/5.7.24_3306/* /usr/local/soft/mysql/back/5.7.24_3306

CREATE USER 'sdyc'@'%' IDENTIFIED BY '123456'

GRANT SELECT, INSERT, UPDATE, DELETE, EXECUTE ON sd_data.* TO 'sdyc'@'%'

GRANT SELECT, INSERT, UPDATE, DELETE, EXECUTE ON card_data.* TO 'sdyc'@'%'

SHOW GRANTS FOR sdyc

CREATE USER 'back'@'localhost' IDENTIFIED BY '123456'

GRANT SELECT, RELOAD, PROCESS, SHOW DATABASES, SUPER, LOCK TABLES, REPLICATION CLIENT ON . TO 'back'@'localhost'

SHOW GRANTS FOR back

REVOKE SELECT, INSERT, UPDATE, DELETE, EXECUTE ON sd_data.* FROM 'sdyc'@'%'

MySQL各种权限（共27个）（以下 *** 作都是以root身份登陆进行grant授权，以p1@localhost身份登陆执行各种命令。）1.usage连接（登陆）权限，建立一个用户，就会自动授予其usage权限（默认授予）。mysql>grantusageon*.*to‘p1′@’localhost’identifiedby‘123′该权限只能用于数据库登陆，不能执行任何 *** 作；且usage权限不能被回收，也即REVOKE用户并不能删除用户。2.select必须有select的权限，才可以使用selecttablemysql>grantselectonpyt.*to‘p1′@’localhost’mysql>select*fromshop3.create必须有create的权限，才可以使用createtablemysql>grantcreateonpyt.*to‘p1′@’localhost’4.createroutine必须具有createroutine的权限，才可以使用{create|alter|drop}{procedure|function}mysql>grantcreateroutineonpyt.*to‘p1′@’localhost’当授予createroutine时，自动授予EXECUTE,ALTERROUTINE权限给它的创建者：mysql>showgrantsfor‘p1′@’localhost’+—————————————————————————+Grantsforp1@localhost+————————————————————————–+|GRANTUSAGEON*.*TO‘p1′@’localhost’IDENTIFIEDBYPASSWORD‘*23AE809DDACAF96AF0FD78ED04B6A265E05AA257′||GRANTSELECT,CREATE,CREATEROUTINEON`pyt`.*TO‘p1′@’localhost’||GRANTEXECUTE,ALTERROUTINEONPROCEDURE`pyt`.`pro_shop1`TO‘p1′@’localhost’|+————————————————————————————-+5.createtemporarytables(注意这里是tables，不是table)必须有createtemporarytables的权限，才可以使用createtemporarytables.mysql>grantcreatetemporarytablesonpyt.*to‘p1′@’localhost’[mysql@mydev~]$mysql-hlocalhost-up1-ppytmysql>createtemporarytablett1(idint)6.createview必须有createview的权限，才可以使用createviewmysql>grantcreateviewonpyt.*to‘p1′@’localhost’mysql>createviewv_shopasselectpricefromshop7.createuser要使用CREATEUSER，必须拥有mysql数据库的全局CREATEUSER权限，或拥有INSERT权限。mysql>grantcreateuseron*.*to‘p1′@’localhost’或：mysql>grantinserton*.*top1@localhost8.insert必须有insert的权限，才可以使用insertinto…..values….9.alter必须有alter的权限，才可以使用altertablealtertableshopmodifydealerchar(15)10.alterroutine必须具有alterroutine的权限，才可以使用{alter|drop}{procedure|function}mysql>grantalterroutineonpyt.*to‘p1′@’localhost‘mysql>dropprocedurepro_shopQueryOK,0rowsaffected(0.00sec)mysql>revokealterroutineonpyt.*from‘p1′@’localhost’[mysql@mydev~]$mysql-hlocalhost-up1-ppytmysql>dropprocedurepro_shopERROR1370(42000):alterroutinecommanddeniedtouser‘p1′@’localhost’forroutine‘pyt.pro_shop’11.update必须有update的权限，才可以使用updatetablemysql>updateshopsetprice=3.5wherearticle=0001anddealer=’A'12.delete必须有delete的权限，才可以使用deletefrom….where….(删除表中的记录)13.drop必须有drop的权限，才可以使用dropdatabasedb_namedroptabletab_namedropviewvi_namedropindexin_name14.showdatabase通过showdatabase只能看到你拥有的某些权限的数据库，除非你拥有全局SHOWDATABASES权限。对于p1@localhost用户来说，没有对mysql数据库的权限，所以以此身份登陆查询时，无法看到mysql数据库：mysql>showdatabases+——————–+|Database|+——————–+|information_schema||pyt||test|+——————–+15.showview必须拥有showview权限，才能执行showcreateview。mysql>grantshowviewonpyt.*top1@localhostmysql>showcreateviewv_shop16.index必须拥有index权限，才能执行[create|drop]indexmysql>grantindexonpyt.*top1@localhostmysql>createindexix_shoponshop(article)mysql>dropindexix_shoponshop17.excute执行存在的Functions,Proceduresmysql>callpro_shop1(0001,@a)；+———+|article|+———+|0001||0001|+———+mysql>select@a+——+|@a|+——+|2|+——+18.locktables必须拥有locktables权限，才可以使用locktablesmysql>grantlocktablesonpyt.*top1@localhostmysql>locktablesa1readmysql>unlocktables19.references有了REFERENCES权限，用户就可以将其它表的一个字段作为某一个表的外键约束。20.reload必须拥有reload权限，才可以执行flush[tables|logs|privileges]mysql>grantreloadonpyt.*top1@localhostERROR1221(HY000):IncorrectusageofDBGRANTandGLOBALPRIVILEGESmysql>grantreloadon*.*to‘p1′@’localhost’QueryOK,0rowsaffected(0.00sec)mysql>flushtables21.replicationclient拥有此权限可以查询masterserver、slaveserver状态。mysql>showmasterstatusERROR1227(42000):AccessdeniedyouneedtheSUPER,REPLICATIONCLIENTprivilegeforthisoperationmysql>grantReplicationclienton*.*top1@localhost或：mysql>grantsuperon*.*top1@localhostmysql>showmasterstatus+——————+———-+————–+——————+|File|Position|Binlog_Do_DB|Binlog_Ignore_DB|+——————+———-+————–+——————+|mysql-bin.000006|2111|||+——————+———-+————–+——————+mysql>showslavestatus22.replicationslave拥有此权限可以查看从服务器，从主服务器读取二进制日志。mysql>showslavehostsERROR1227(42000):AccessdeniedyouneedtheREPLICATIONSLAVEprivilegeforthisoperationmysql>showbinlogeventsERROR1227(42000):AccessdeniedyouneedtheREPLICATIONSLAVEprivilegeforthisoperationmysql>grantreplicationslaveon*.*top1@localhostmysql>showslavehostsEmptyset(0.00sec)mysql>showbinlogevents+—————+——-+—————-+———–+————-+————–+|Log_name|Pos|Event_type|Server_id|End_log_pos|Info|+—————+——-+————–+———–+————-+—————+|mysql-bin.000005|4|Format_desc|1|98|Serverver:5.0.77-log,Binlogver:4||mysql-bin.000005|98|Query|1|197|use`mysql`createtablea1(iint)engine=myisam|……………………………………23.Shutdown关闭MySQL：[mysql@mydev~]$mysqladminshutdown重新连接：[mysql@mydev~]$mysqlERROR2002(HY000):Can’tconnecttolocalMySQLserverthroughsocket‘/tmp/mysql.sock’(2)[mysql@mydev~]$cd/u01/mysql/bin[mysql@mydevbin]$./mysqld_safe&[mysql@mydevbin]$mysql24.grantoption拥有grantoption，就可以将自己拥有的权限授予其他用户（仅限于自己已经拥有的权限）mysql>grantGrantoptiononpyt.*top1@localhostmysql>grantselectonpyt.*top2@localhost25.file拥有file权限才可以执行select..intooutfile和loaddatainfile… *** 作，但是不要把file,process,super权限授予管理员以外的账号，这样存在严重的安全隐患。mysql>grantfileon*.*top1@localhostmysql>loaddatainfile‘/home/mysql/pet.txt’intotablepet26.super这个权限允许用户终止任何查询；修改全局变量的SET语句；使用CHANGEMASTER，PURGEMASTERLOGS。mysql>grantsuperon*.*top1@localhostmysql>purgemasterlogsbefore‘mysql-bin.000006′27.process通过这个权限，用户可以执行SHOWPROCESSLIST和KILL命令。默认情况下，每个用户都可以执行SHOWPROCESSLIST命令，但是只能查询本用户的进程。mysql>showprocesslist+—-+——+———–+——+———+——+——-+——————+|Id|User|Host|db|Command|Time|State|Info|+—-+——+———–+——+———+——+——-+——————+|12|p1|localhost|pyt|Query|0|NULL|showprocesslist|+—-+——+———–+——+———+——+——-+——————+另外，管理权限（如super，process，file等）不能够指定某个数据库，on后面必须跟*.*mysql>grantsuperonpyt.*top1@localhostERROR1221(HY000):IncorrectusageofDBGRANTandGLOBALPRIVILEGESmysql>grantsuperon*.*top1@localhostQueryOK,0rowsaffected(0.01sec)

---