存储过程对所有数据库内容进行赋权和同义词,赋权的帐号通过参数传入,本代码设定的帐号有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;