rename role

oracle 没有重命名role的命令,自己写了一个.
role 主要包含系统权限(如create table等等),object权限(如grant select on table),
以及该role包含的role(grant role to role),还有包含该role的帐号.
这些内容通过以下四个系统表就可以查清除了

与role有关 dict
DBA_ROLE_PRIVS #包含role的帐号
ROLE_ROLE_PRIVS #被grant给role的role
ROLE_SYS_PRIVS #role 系统权限
ROLE_TAB_PRIVS #role object权限

以下为脚本,需要用dba权限来做
connect sys/sys as sysdba;

set feed off head off linesize 200 pagesize 50000
define old_role=HOED
define new_role=STED
spool cr_new_role_privs.sql
select 'grant '||granted_role||' to &&new_role'||decode(admin_option,'YES',' with admin option;',';')
from role_role_privs
where role = '&&old_role';

select 'grant '||privilege||' to &&new_role'||decode(admin_option,'YES',' with admin option;',';')
from role_sys_privs
where role = '&&old_role';

select 'grant '||privilege||decode(column_name,null,'','('||column_name||')')||' on '||owner||'.'||table_name||' to &&new_role'||';'
from role_tab_privs
where role = '&&old_role';

select 'grant &&new_role'||' to '||grantee||decode(admin_option,'YES',' with grant option;',';')
from dba_role_privs
where granted_role = '&&old_role';
spool off
@cr_new_role_privs.sql

删除role前,要先检查new role的权限是否正确,
检查方法可以先建一个帐号,把new role gant 给这个帐号,与旧帐号比对以下
drop role &&old_role;

权且无聊,写下备用.