auto_grant_privilege

create or replace procedure auto_grant_privilege as
  /*
  CREATED Time: 20150803 
  AUDITOR     : HURP
  FUNCATION   : Find the new object of program account and grant to maintain user;
  DESCRIBE    : 本过程将表SHSNC.PRO_ACCOUNT_TAB里指定的程序账号下所有新增的表自动授权给维护账号
  1.自定义表SHSNC.PRO_ACCOUNT_TAB存放程序账号；
  2.程序账号管理，如果想新增程序账号直接往该插入一条数据即可；
    例：insert into shsnc.PRO_ACCOUNT_TAB VALUES('TBCS',null);
  3.本过程涉及三个角色，OTHER_QRY_ROLE、OTHER_DML_ROLE、DDL_ROLE;
  4.通过该过程自动将程序账号新增对象授权给上述3个角色授权；
  */
  v_sql1             varchar2(2000);
  v_sql2             varchar2(2000);
  v_sql3             varchar2(2000);
  v_sql4             varchar2(2000);
  v_sql5             varchar2(2000);
  v_sql6             varchar2(2000);
  v_sql7             varchar2(2000);
  v_owner            varchar2(10) := 'SHSNC';
  v_tname            varchar2(30) := 'PRO_ACCOUNT_TAB';
  v_retrole          varchar2(30);
  v_retowner         varchar2(10);
  v_retname          varchar2(30);
  v_retrolename      varchar2(50);
  v_retcount         number;
  v_sysrole_count    number;
  v_defrole_count    number;
  v_retacctname      varchar2(30);
  v_cur              sys_refcursor;
  v_cur1             sys_refcursor;
  v_cur2             sys_refcursor;
  v_cur3             sys_refcursor;
  v_qryproact_sql    varchar2(2000) := 'select count(*) from dba_tables where table_name=:1 AND OWNER=:2';
  v_qry_sys_role_sql varchar2(2000) := 'select count(*) from dba_roles where role in (select role_name from ' ||
                                       v_owner || '.' || v_tname ||
                                       ' where role_name is not null)';
  v_qry_def_role_sql varchar2(2000) := 'select count(*) from ' || v_owner || '.' ||
                                       v_tname ||
                                       ' where role_name  is not null';


begin


  --判断存放程序账号的表是否存在，如果不存在自动创建；
  execute immediate v_qryproact_sql
    into v_retcount
    using v_tname, v_owner;


  if v_retcount < 1 then
    execute immediate 'create table ' || v_owner || '.' || v_tname ||
                      ' (account_name varchar2(100),role_name varchar2(40))';
  end if;


  --判断角色是否存在，如果不存在自动创建
  begin
    execute immediate v_qry_sys_role_sql
      into v_sysrole_count;
    execute immediate v_qry_def_role_sql
      into v_defrole_count;
    dbms_output.put_line('v_sysrole_count:=' || v_sysrole_count);
    dbms_output.put_line('v_defrole_count:=' || v_defrole_count);
    if v_sysrole_count < v_defrole_count then
      v_sql1 := 'select role_name from ' || v_owner || '.' || v_tname ||
                ' where role_name not  in (select role from dba_roles) and role_name is not null';
      open v_cur for v_sql1;
      loop
        fetch v_cur
          into v_retrole;
        EXIT WHEN v_cur%NOTFOUND;
        execute immediate 'create role ' || v_retrole;
      end loop;
      close v_cur;
    end if;
  end;


  --从自定义的表里查找程序账号
  v_sql2 := 'select account_name from ' || v_owner || '.' || v_tname ||
            ' where account_name is not null';
  open v_cur1 for v_sql2;
  loop
    fetch v_cur1
      into v_retacctname;
    EXIT WHEN v_cur1%NOTFOUND;
    dbms_output.put_line('v_retacctname:= ' || v_retacctname);
    --从自定义的表里查指定的角色
    v_sql3 := 'select role_name from ' || v_owner || '.' || v_tname ||
              ' where role_name is not null';
    open v_cur2 for v_sql3;
    loop
      fetch v_cur2
        into v_retrolename;
      EXIT WHEN v_cur2%NOTFOUND;
      dbms_output.put_line('v_retrolename:= ' || v_retrolename);
      --判断指定的程序账号是否有新增的表，将新增的表赋权给相应的角色     
      v_sql4 := 'select owner,table_name from dba_tables where (owner,table_name) not in (select owner,table_name from dba_tab_privs where grantee=:B5) and owner=:B6';
      open v_cur3 for v_sql4
        using v_retrolename, v_retacctname;
      loop
        fetch v_cur3
          into v_retowner, v_retname;
        dbms_output.put_line('v_retowner:=' || v_retowner ||
                             ' v_retname:=' || v_retname);
        EXIT WHEN v_cur3%NOTFOUND;
        case
          when v_retrolename = 'OTHER_QRY_ROLE' then
            v_sql5 := 'grant select on ' || v_retowner || '.' || v_retname ||
                      ' to ' || v_retrolename;
            execute immediate v_sql5;
          when v_retrolename = 'OTHER_DML_ROLE' then
            v_sql6 := 'grant insert,delete,update on ' || v_retowner || '.' ||
                      v_retname || ' to ' || v_retrolename;
            execute immediate v_sql6;
          when v_retrolename = 'DDL_ROLE' then
            v_sql7 := 'grant alter on ' || v_retowner || '.' || v_retname ||
                      ' to ' || v_retrolename;
            execute immediate v_sql7;
        end case; 
        end loop;
      close v_cur3;
      dbms_output.put_line('ok');
    end loop;
    close v_cur2;
  end loop;
  close v_cur1;
end;
/




测试相关语句：
create table shsnc.PRO_ACCOUNT_TAB(account_name varchar2(100));
insert into shsnc.PRO_ACCOUNT_TAB VALUES('TBCS',null);
insert into shsnc.PRO_ACCOUNT_TAB VALUES('COMMON',null);
insert into shsnc.PRO_ACCOUNT_TAB VALUES(null,'OTHER_DML_ROLE');
insert into shsnc.PRO_ACCOUNT_TAB VALUES(null,'OTHER_QRY_ROLE');
insert into shsnc.PRO_ACCOUNT_TAB VALUES(null,'DDL_ROLE');




SQL> select owner,table_name from dba_tables where (owner,table_name) not in (select owner,table_name from dba_tab_privs) and owner='TBCS';


OWNER                          TABLE_NAME
------------------------------ ------------------------------
ECARE                          ES_LOG_SYSTEMLOG_201508


select table_name into v_table from dba_tables where table_name='SHSNC' AND OWNER='PRO_ACCOUNT_TAB';
select * from  shsnc.PRO_ACCOUNT_TAB;


select count(*) from dba_tab_privs where grantee='DDL_ROLE' AND GRANTEE='TBCS';


revoke select on tbcs.AF_FRAUD_ACCOUNT_NODEAL_TASK from other_qry_role;
revoke insert,delete,update on tbcs.AF_FRAUD_ACCOUNT_NODEAL_TASK from other_dml_role;
revoke alter on tbcs.AF_FRAUD_ACCOUNT_NODEAL_TASK from ddl_role;


select owner,table_name from dba_tables where (owner,table_name) not in (select owner,table_name from dba_tab_privs) and owner in (select * from shsnc.PRO_ACCOUNT_TAB);
SELECT COUNT(*) FROM DBA_TAB_PRIVS WHERE GRANTEE='OTHER_DML_ROLE' AND owner='TRANSDATA';
SELECT COUNT(*) FROM DBA_TAB_PRIVS WHERE GRANTEE='OTHER_QRY_ROLE' AND owner='DDL';


select count(*) from dba_roles where role in (select role_name from  shsnc.PRO_ACCOUNT_TAB where role_name is not null);
select count(*) from shsnc.PRO_ACCOUNT_TAB where role_name  is not null


select owner,table_name from dba_tables where (owner,table_name) not in (select owner,table_name from dba_tab_privs where grantee='DDL_ROLE') and owner='TBCS';

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29446986/viewspace-1764980/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29446986/viewspace-1764980/