oracle数据库赋权_实用的数据库赋权的存储过程，用于oracle

最新推荐文章于 2024-06-28 19:34:52 发布

weixin_39620578

最新推荐文章于 2024-06-28 19:34:52 发布

阅读量711

点赞数

文章标签： oracle数据库赋权

本文链接：https://blog.csdn.net/weixin_39620578/article/details/112993314

版权

存储过程对所有数据库内容进行赋权和同义词，赋权的帐号通过参数传入，本代码设定的帐号有6个。

----------Create on 2017-08-21 by i-24@qq.com publish on CSDN

----------ddl sql sample[exec GRANT_RIGHT_ALL('owner','ownerapp','ownermod','ownerdml','ownerread','ownerextr');]

CREATE OR REPLACE

PROCEDURE GRANT_RIGHT_ALL

(

OWNER VARCHAR2

,OWNERAPP VARCHAR2

,OWNERMOD VARCHAR2

,OWNERDML VARCHAR2

,OWNERREAD VARCHAR2

,OWNEREXTR VARCHAR2

) AS

V_OBJECT VARCHAR2(128);

----------put all table and view in cur_branch01

CURSOR CUR_BRANCH01 IS

SELECT OBJECT_NAME

FROM USER_OBJECTS T

WHERE OBJECT_TYPE IN ('TABLE', 'VIEW')

----------put all function, procedure, package, and package body in cut_branch02

CURSOR CUR_BRANCH02 IS

SELECT OBJECT_NAME

FROM USER_OBJECTS T

WHERE OBJECT_TYPE IN ('FUNCTION', 'PROCEDURE', 'PACKAGE', 'PACKAGE BODY')

AND OBJECT_NAME NOT IN ('GRANT_RIGHT','GRANT_RIGHT_ALL');

----------put all sequence in cur_branch03

CURSOR CUR_BRANCH03 IS

SELECT OBJECT_NAME

FROM USER_OBJECTS T

WHERE OBJECT_TYPE = 'SEQUENCE'

BEGIN

V_OBJECT := '';

OPEN CUR_BRANCH01;

LOOP

FETCH CUR_BRANCH01

INTO V_OBJECT;

EXIT WHEN CUR_BRANCH01%NOTFOUND;

----------rights and synonyms for the app account

IF LENGTH(TRIM(OWNERAPP)) <> 0

THEN

EXECUTE IMMEDIATE 'grant select, insert, update, delete on ' || V_OBJECT || ' to ' || OWNERAPP;

EXECUTE IMMEDIATE 'create or replace synonym ' || OWNERAPP || '.' || V_OBJECT || ' for ' || OWNER || '.' || V_OBJECT;

END IF;

----------rights and synonyms for the mod account

IF LENGTH(TRIM(OWNERMOD)) <> 0

THEN

EXECUTE IMMEDIATE 'grant select, insert, update, delete on ' || V_OBJECT || ' to ' || OWNERMOD;

EXECUTE IMMEDIATE 'create or replace synonym ' || OWNERMOD || '.' || V_OBJECT || ' for ' || OWNER || '.' || V_OBJECT;

END IF;

----------rights and synonyms for the dml account

IF LENGTH(TRIM(OWNERDML)) <> 0

THEN

EXECUTE IMMEDIATE 'grant select , insert, update, delete on ' || V_OBJECT || ' to ' || OWNERDML;

EXECUTE IMMEDIATE 'create or replace synonym ' || OWNERDML || '.' || V_OBJECT || ' for ' || OWNER || '.' || V_OBJECT;

END IF;

----------rights and synonyms for the read account

IF LENGTH(TRIM(OWNERREAD)) <> 0

THEN

EXECUTE IMMEDIATE 'grant select on ' || V_OBJECT || ' to ' || OWNERREAD;

EXECUTE IMMEDIATE 'create or replace synonym ' || OWNERREAD || '.' || V_OBJECT || ' for ' || OWNER || '.' || V_OBJECT;

END IF;

----------rights and synonyms for the extr account

IF LENGTH(TRIM(OWNEREXTR)) <> 0

THEN

EXECUTE IMMEDIATE 'grant select on ' || V_OBJECT || ' to ' || OWNEREXTR;

EXECUTE IMMEDIATE 'create or replace synonym ' || OWNEREXTR || '.' || V_OBJECT || ' for ' || OWNER || '.' || V_OBJECT;

END IF;

COMMIT;

END LOOP;

CLOSE CUR_BRANCH01;

OPEN CUR_BRANCH02;

LOOP

FETCH CUR_BRANCH02

INTO V_OBJECT;

EXIT WHEN CUR_BRANCH02%NOTFOUND;

----------rights and synonyms for the app account

IF LENGTH(TRIM(OWNERAPP)) <> 0

THEN

EXECUTE IMMEDIATE 'grant execute on ' || V_OBJECT || ' to ' || OWNERAPP;

EXECUTE IMMEDIATE 'create or replace synonym ' || OWNERAPP || '.' || V_OBJECT || ' for ' || OWNER || '.' || V_OBJECT;

END IF;

COMMIT;

END LOOP;

CLOSE CUR_BRANCH02;

OPEN CUR_BRANCH03;

LOOP

FETCH CUR_BRANCH03

INTO V_OBJECT;

EXIT WHEN CUR_BRANCH03%NOTFOUND;

----------rights and synonyms for the app account

IF LENGTH(TRIM(OWNERAPP)) <> 0

THEN

EXECUTE IMMEDIATE 'grant select on ' || V_OBJECT || ' to ' || OWNERAPP;

EXECUTE IMMEDIATE 'create or replace synonym ' || OWNERAPP || '.' || V_OBJECT || ' for ' || OWNER || '.' || V_OBJECT;

END IF;

----------rights and synonyms for the dml account

IF LENGTH(TRIM(OWNERDML)) <> 0

THEN

EXECUTE IMMEDIATE 'grant select on ' || V_OBJECT || ' to ' || OWNERDML;

EXECUTE IMMEDIATE 'create or replace synonym ' || OWNERDML || '.' || V_OBJECT || ' for ' || OWNER || '.' || V_OBJECT;

END IF;

COMMIT;

END LOOP;

CLOSE CUR_BRANCH03;

END;

全部内容的赋权和同义词执行效率较低，按时间只操作最近24小时的内容

----------Create on 2017-08-21 by i-24@qq.com publish on CSDN

----------ddl sql sample[exec GRANT_RIGHT('owner','ownerapp','ownermod','ownerdml','ownerread','ownerextr');]

CREATE OR REPLACE

PROCEDURE GRANT_RIGHT

(

OWNER VARCHAR2

,OWNERAPP VARCHAR2

,OWNERMOD VARCHAR2

,OWNERDML VARCHAR2

,OWNERREAD VARCHAR2

,OWNEREXTR VARCHAR2

) AS

V_OBJECT VARCHAR2(128);

----------all new means created within 24 hours

----------put new table and view in cur_branch01

CURSOR CUR_BRANCH01 IS

SELECT OBJECT_NAME

FROM USER_OBJECTS T

WHERE OBJECT_TYPE IN ('TABLE', 'VIEW')

AND CREATED >= SYSDATE - 1.0;

----------put new function procedure package and package body in cut_branch02

CURSOR CUR_BRANCH02 IS

SELECT OBJECT_NAME

FROM USER_OBJECTS T

WHERE OBJECT_TYPE IN ('FUNCTION', 'PROCEDURE', 'PACKAGE', 'PACKAGE BODY')

AND CREATED >= SYSDATE - 1.0

AND OBJECT_NAME NOT IN ('GRANT_RIGHT','GRANT_RIGHT_ALL');

----------put new sequence in cur_branch03

CURSOR CUR_BRANCH03 IS

SELECT OBJECT_NAME

FROM USER_OBJECTS T

WHERE OBJECT_TYPE = 'SEQUENCE'

AND CREATED >= SYSDATE - 1.0;

BEGIN

V_OBJECT := '';

OPEN CUR_BRANCH01;

LOOP

FETCH CUR_BRANCH01

INTO V_OBJECT;

EXIT WHEN CUR_BRANCH01%NOTFOUND;

----------rights and synonyms for the app account

IF LENGTH(TRIM(OWNERAPP)) <> 0

THEN

EXECUTE IMMEDIATE 'grant select, insert, update, delete on ' || V_OBJECT || ' to ' || OWNERAPP;

EXECUTE IMMEDIATE 'create or replace synonym ' || OWNERAPP || '.' || V_OBJECT || ' for ' || OWNER || '.' || V_OBJECT;

END IF;

----------rights and synonyms for the mod account

IF LENGTH(TRIM(OWNERMOD)) <> 0

THEN

EXECUTE IMMEDIATE 'grant select, insert, update, delete on ' || V_OBJECT || ' to ' || OWNERMOD;

EXECUTE IMMEDIATE 'create or replace synonym ' || OWNERMOD || '.' || V_OBJECT || ' for ' || OWNER || '.' || V_OBJECT;

END IF;

----------rights and synonyms for the dml account

IF LENGTH(TRIM(OWNERDML)) <> 0

THEN

EXECUTE IMMEDIATE 'grant select , insert, update, delete on ' || V_OBJECT || ' to ' || OWNERDML;

EXECUTE IMMEDIATE 'create or replace synonym ' || OWNERDML || '.' || V_OBJECT || ' for ' || OWNER || '.' || V_OBJECT;

END IF;

----------rights and synonyms for the read account

IF LENGTH(TRIM(OWNERREAD)) <> 0

THEN

EXECUTE IMMEDIATE 'grant select on ' || V_OBJECT || ' to ' || OWNERREAD;

EXECUTE IMMEDIATE 'create or replace synonym ' || OWNERREAD || '.' || V_OBJECT || ' for ' || OWNER || '.' || V_OBJECT;

END IF;

----------rights and synonyms for the extr account

IF LENGTH(TRIM(OWNEREXTR)) <> 0

THEN

EXECUTE IMMEDIATE 'grant select on ' || V_OBJECT || ' to ' || OWNEREXTR;

EXECUTE IMMEDIATE 'create or replace synonym ' || OWNEREXTR || '.' || V_OBJECT || ' for ' || OWNER || '.' || V_OBJECT;

END IF;

COMMIT;

END LOOP;

CLOSE CUR_BRANCH01;

OPEN CUR_BRANCH02;

LOOP

FETCH CUR_BRANCH02

INTO V_OBJECT;

EXIT WHEN CUR_BRANCH02%NOTFOUND;

----------rights and synonyms for the app account

IF LENGTH(TRIM(OWNERAPP)) <> 0

THEN

EXECUTE IMMEDIATE 'grant execute on ' || V_OBJECT || ' to ' || OWNERAPP;

EXECUTE IMMEDIATE 'create or replace synonym ' || OWNERAPP || '.' || V_OBJECT || ' for ' || OWNER || '.' || V_OBJECT;

END IF;

COMMIT;

END LOOP;

CLOSE CUR_BRANCH02;

OPEN CUR_BRANCH03;

LOOP

FETCH CUR_BRANCH03

INTO V_OBJECT;

EXIT WHEN CUR_BRANCH03%NOTFOUND;

----------rights and synonyms for the app account

IF LENGTH(TRIM(OWNERAPP)) <> 0

THEN

EXECUTE IMMEDIATE 'grant select on ' || V_OBJECT || ' to ' || OWNERAPP;

EXECUTE IMMEDIATE 'create or replace synonym ' || OWNERAPP || '.' || V_OBJECT || ' for ' || OWNER || '.' || V_OBJECT;

END IF;

----------rights and synonyms for the dml account

IF LENGTH(TRIM(OWNERDML)) <> 0

THEN

EXECUTE IMMEDIATE 'grant select on ' || V_OBJECT || ' to ' || OWNERDML;

EXECUTE IMMEDIATE 'create or replace synonym ' || OWNERDML || '.' || V_OBJECT || ' for ' || OWNER || '.' || V_OBJECT;

END IF;

COMMIT;

END LOOP;

CLOSE CUR_BRANCH03;

END;

weixin_39620578

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
oracle数据库赋权_实用的数据库赋权的存储过程，用于oracle

存储过程对所有数据库内容进行赋权和同义词，赋权的帐号通过参数传入，本代码设定的帐号有6个。----------Create on 2017-08-21 by i-24@qq.com publish on CSDN----------ddl sql sample[exec GRANT_RIGHT_ALL('owner','ownerapp','ownermod','ownerdml','ownerr...
复制链接

扫一扫