create or replace procedure USERTABLE_ROLE_GRANT(test in varchar2) as
--declare
/*
1.获取用户名
2.获取用户表名
3.按用户分配所有表到响应角色里去
4.角色命名:username_alltables_select,username_alltables_CURD
创建角色关键语句
create role TEST_USER_ALL_TABLE;
grant select, insert, update, delete on GRADES to TEST_USER_ALL_TABLE;
*/
v_table_name ALL_TABLES%rowtype;
CURSOR c_usernames IS select username from dba_users
where account_status not like '%LOCKED%'
and username not like '%SYS%'
and username != 'DBSNMP'; --用户名对象
cursor c_tables(p_username in varchar2) is select * from all_tables where OWNER IN (upper(p_username));--获取表名
v_role_name varchar2(50); --角色名
v_roles dba_roles%rowtype;--角色表对象
v_roles_exist number(10);--临时变量,检测角色是否存在
v_sql varchar2(500);
BEGIN
FOR v_username IN c_usernames
LOOP
v_role_name := upper(v_username.username || '_alltables_' || 'CURD');--设定角色名
--execute IMMEDIATE 'select count(*) from dba_roles where role = :1' into v_roles_exist using v_role_name ;--判断角色是否已经创建
if v_roles_exist <=0 then
/*
<=0 如果角色不存在,创建角色
*/
dbms_output.put_line('创建角色: '||v_role_name);
else
dbms_output.put_line('存在角色:'||v_role_name);
end if;
dbms_output.put_line(v_username.username);
FOR tableobj IN c_tables(v_username.username) --用户下的表,遍历一下
LOOP
--创建对应角色名
v_sql := 'grant select, insert, update, delete on '||v_username.username||'.'||tableobj.table_name|| ' to '||v_role_name;
dbms_output.put_line(v_sql);
END loop;
END LOOP;
END;
这是一个双重遍历的语句块,但是用procedure运行和declare运行的结果不一样!!!用Procedure运行的时候会遍历异常(假设外层循环5次(5个用户),内层应该10次(10个用户表)每个用户,declare显示50次循环,而用Procedure运行只会出现10次运行),有没有大牛解除疑惑。
运行用户 master,权限dba_users,dba_roles的select权限,dba,connect权限