BEGIN
FOR cur_rec IN (SELECT role
FROM dba_roles@lnk_previousyearbalance) LOOP
BEGIN
EXECUTE IMMEDIATE 'CREATE ROLE ' || cur_rec.role ;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
END LOOP;
END;
/
BEGIN
FOR cur_rec IN (SELECT grantee,privilege
FROM DBA_SYS_PRIVS@lnk_previousyearbalance where grantee not in ('SYS','SYSTEM','SYSMAN','TSMSYS','WMSYS','RECOVERY_CATALOG_OWNER','RESOURCE','OUTLN','ORACLE_OCM','OEM_MONITOR','OEM_ADVISOR','MGMT_USER','IMP_FULL_DATABASE','EXP_FULL_DATABASE','DBA','CONNECT','AQ_ADMINISTRATOR_ROLE','DBSNMP','SCHEDULER_ADMIN')) LOOP
BEGIN
EXECUTE IMMEDIATE ('Grant ' || cur_rec.privilege || ' to ' || cur_rec.grantee );
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
END LOOP;
END;
/
BEGIN
FOR cur_rec IN (SELECT grantee,privilege,table_name
FROM dba_tab_privs@lnk_previousyearbalance Where Grantor='LDBO') LOOP
BEGIN
EXECUTE IMMEDIATE 'Grant ' || cur_rec.privilege || ' on ' || cur_rec.table_name || ' to ' || cur_rec.grantee ;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
END LOOP;
END;
/
BEGIN
FOR cur_rec IN (SELECT grantee,privilege,table_name,column_name
FROM dba_col_privs@lnk_previousyearbalance Where Grantor='LDBO') LOOP
BEGIN
EXECUTE IMMEDIATE 'Grant '|| cur_rec.PRIVILEGE || '('|| cur_rec.COLUMN_NAME ||') on '|| cur_rec.TABLE_NAME || ' to ' || cur_rec.GRANTEE ;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
END LOOP;
END;
/
BEGIN
FOR cur_rec IN (SELECT grantee,granted_role
FROM dba_role_privs@lnk_previousyearbalance Where Grantee!='SYSTEM' and Grantee!='SYS' and Grantee!='DBSNML' and Grantee!='REPADMIN') LOOP
BEGIN
EXECUTE IMMEDIATE 'Grant '|| cur_rec.granted_role || ' to ' || cur_rec.GRANTEE ;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
END LOOP;
END;
/
No comments:
Post a Comment