Oracle存过-对象权限创建回收、同义词创建删除

Oracle存过-对象权限创建回收、同义词创建删除

-- Oracle存过-对象权限创建回收、同义词创建删除

--得到对象授权语句
  --调用：CALL LOG.COMMON_PKG.get_tab_grant_privs_p('LOG','TZQ','INFO');
  PROCEDURE get_tab_grant_privs_p(pi_grantor   IN VARCHAR2,
                                  pi_grantee   IN VARCHAR2,
                                  pi_tablename IN VARCHAR2) IS
    CURSOR c1 IS
      SELECT 'GRANT ' || t.privilege || ' ON ' || t.table_name || ' TO ' ||
             t.grantee || decode(t.grantable
                                ,'YES'
                                ,' WITH GRANT OPTION') || ';' AS tab_privs
        FROM user_tab_privs t
       WHERE t.grantor = upper(pi_grantor)
         AND t.grantee = upper(pi_grantee)
         AND t.table_name = upper(pi_tablename);
    c1_rec c1%ROWTYPE;
  BEGIN
    FOR c1_rec IN c1 LOOP
      dbms_output.put_line(c1_rec.tab_privs);
    END LOOP;
  EXCEPTION
    WHEN OTHERS THEN
      dbms_output.put_line(SQLERRM);
  END;

  --得到对象回收授权语句
  --调用：CALL LOG.COMMON_PKG.get_tab_revoke_privs_p('LOG','TZQ','INFO');
  PROCEDURE get_tab_revoke_privs_p(pi_grantor   IN VARCHAR2,
                                   pi_grantee   IN VARCHAR2,
                                   pi_tablename IN VARCHAR2) IS
    CURSOR c1 IS
      SELECT 'REVOKE ' || t.privilege || ' ON ' || t.table_name || ' FROM ' ||
             t.grantee || ';' AS tab_privs
        FROM user_tab_privs t
       WHERE t.grantor = upper(pi_grantor)
         AND t.grantee = upper(pi_grantee)
         AND t.table_name = upper(pi_tablename);
    c1_rec c1%ROWTYPE;
  BEGIN
    FOR c1_rec IN c1 LOOP
      dbms_output.put_line(c1_rec.tab_privs);
    END LOOP;
  EXCEPTION
    WHEN OTHERS THEN
      dbms_output.put_line(SQLERRM);
  END;
  
  --得到创建同义词语句
  --调用：CALL LOG.COMMON_PKG.get_create_synonym_script_p('DW','VEHICLE');
  PROCEDURE get_create_synonym_script_p(--pi_owner       IN VARCHAR2,
                                        pi_table_owner IN VARCHAR2,
                                        pi_tablename   IN VARCHAR2) IS
    CURSOR c1 IS
      SELECT 'CREATE OR REPLACE SYNONYM ' || t.synonym_name || ' for ' ||
             t.table_owner || '.' || t.table_name || ';' AS create_synonym_script
        FROM user_synonyms t
       WHERE 1=1
         --AND t.owner = upper(pi_owner)
         AND t.table_owner = upper(pi_table_owner)
         AND t.table_name = upper(pi_tablename);
    c1_rec c1%ROWTYPE;
  BEGIN
    FOR c1_rec IN c1 LOOP
      dbms_output.put_line(c1_rec.create_synonym_script);
    END LOOP;
  EXCEPTION
    WHEN OTHERS THEN
      dbms_output.put_line(SQLERRM);
  END;
  
  --得到删除同义词语句
  --调用：CALL LOG.COMMON_PKG.get_drop_synonym_script_p('DW','VEHICLE');
  PROCEDURE get_drop_synonym_script_p(--pi_owner       IN VARCHAR2,
                                        pi_table_owner IN VARCHAR2,
                                        pi_tablename   IN VARCHAR2) IS
    CURSOR c1 IS
      SELECT 'DROP SYNONYM ' || t.synonym_name || ';' AS drop_synonym_script
        FROM user_synonyms t
       WHERE 1=1
         --AND t.owner = upper(pi_owner)
         AND t.table_owner = upper(pi_table_owner)
         AND t.table_name = upper(pi_tablename);
    c1_rec c1%ROWTYPE;
  BEGIN
    FOR c1_rec IN c1 LOOP
      dbms_output.put_line(c1_rec.drop_synonym_script);
    END LOOP;
  EXCEPTION
    WHEN OTHERS THEN
      dbms_output.put_line(SQLERRM);
  END;