Tuesday, 23 May 2017

How to grant tables,views,synonyms and packages to other schema in Oracle Apps R12.2.X ?

TABLES,VIEW,SYNONYM FOR OKL:
============================

DECLARE
CURSOR C_GRANT is
SELECT OBJECT_NAME
      ,OWNER
FROM ALL_OBJECTS
WHERE OBJECT_TYPE in ('TABLE','VIEW','SYNONYM')
   AND STATUS='VALID'
   AND OWNER NOT IN 'XXEC'
   AND OBJECT_NAME LIKE 'OKL%';
BEGIN
for GRANT_OBJ in C_GRANT
loop
AD_ZD.GRANT_PRIVS('ALL',GRANT_OBJ.OWNER||'.'||GRANT_OBJ.OBJECT_NAME,'XXEC');
end loop;
commit;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('OOPS SOMETHONG WENT WRONG PLEASE FIND BELOW ERROR MSG');
DBMS_OUTPUT.PUT_LINE(SQLCODE||','||SQLERRM);
END;
/


TABLES,VIEW,SYNONYM FOR OKC:
============================

DECLARE
CURSOR C_GRANT is
SELECT OBJECT_NAME
FROM ALL_OBJECTS
WHERE OBJECT_TYPE in ('TABLE','VIEW','SYNONYM')
   AND STATUS='VALID'
   AND OWNER NOT IN 'XXEC'
   AND OBJECT_NAME LIKE 'OKC%';
BEGIN
for GRANT_OBJ in C_GRANT
loop
AD_ZD.GRANT_PRIVS('ALL',GRANT_OBJ.OBJECT_NAME,'XXEC');
end loop;
commit;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('OOPS SOMETHONG WENT WRONG PLEASE FIND BELOW ERROR MSG');
DBMS_OUTPUT.PUT_LINE(SQLCODE||','||SQLERRM);
END;
/

GRANTING OKL PACKAGES TO XXEC:
==============================

DECLARE
CURSOR C_GRANT is
SELECT OBJECT_NAME
FROM ALL_OBJECTS
WHERE OBJECT_TYPE in ('PACKAGE')
   AND STATUS='VALID'
   AND OWNER NOT IN 'XXEC'
   AND OBJECT_NAME LIKE 'OKL%';
BEGIN
for GRANT_OBJ in C_GRANT
loop
AD_ZD.GRANT_PRIVS('EXECUTE',GRANT_OBJ.OBJECT_NAME,'XXEC');
end loop;
commit;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('OOPS SOMETHONG WENT WRONG PLEASE FIND BELOW ERROR MSG');
DBMS_OUTPUT.PUT_LINE(SQLCODE||','||SQLERRM);
END;
/


GRANTING OKC PACKAGES TO XXEC:
==============================

DECLARE
CURSOR C_GRANT is
SELECT OBJECT_NAME
FROM ALL_OBJECTS
WHERE OBJECT_TYPE in ('PACKAGE')
   AND STATUS='VALID'
   AND OWNER NOT IN 'XXEC'
   AND OBJECT_NAME LIKE 'OKC%';
BEGIN
for GRANT_OBJ in C_GRANT
loop
AD_ZD.GRANT_PRIVS('EXECUTE',GRANT_OBJ.OBJECT_NAME,'XXEC');
end loop;
commit;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('OOPS SOMETHONG WENT WRONG PLEASE FIND BELOW ERROR MSG');
DBMS_OUTPUT.PUT_LINE(SQLCODE||','||SQLERRM);
END;
/

No comments:

Post a Comment

Query to find request set and its responsibility

  SELECT FA.application_name,        fr.responsibility_name program_attached_to,        frg.request_group_name,        fcp.request_set_name,...