数据库环境变更(数据库迁移) ,用户角色权限的重新赋予。
--查看用户 ZWQRY% 创建语句
select 'create user '||username||' identified by values '||password||' default tablespace TBSZW_FOLD1_INDEX001 temporary tablespace temp ;'
from dba_users where username LIKE 'ZWQRY%' ;
--查看用户ZWQRY% 角色
SELECT DISTINCT(granted_role) FROM Dba_Role_Privs WHERE grantee LIKE 'ZWQRY%';
--以下是角色
CONNECT
RESOURCE
SELECT_ON_NO1
SELECT_ON_DISHI
SELECT_ON_NO2
SELECT_ON_NO3
SELECT_ON_NO4
DBA
--需要创建5个非系统角色
create role SELECT_ON_NO1 ;
create role SELECT_ON_DISHI;
create role SELECT_ON_NO2;
create role SELECT_ON_NO3;
create role SELECT_ON_NO4 ;
--查看新创建5个非系统角色有哪些系统权限。
select * from dba_sys_privs where grantee in ('SELECT_ON_NO1','SELECT_ON_NO2','SELECT_ON_NO3','SELECT_ON_NO4','SELECT_ON_DISHI'); 空
--查看新创建5个非系统角色有哪些对象权限。
select * from dba_tab_privs where grantee in ('SELECT_ON_NO1','SELECT_ON_NO2','SELECT_ON_NO3','SELECT_ON_NO4','SELECT_ON_DISHI');
--给5个非系统角色赋予对象权限
SELECT 'grant select on '||owner||'.'||table_name||' to '||grantee||';' from dba_tab_privs
where grantee in ('SELECT_ON_NO1','SELECT_ON_NO2','SELECT_ON_NO3','SELECT_ON_NO4','SELECT_ON_DISHI');
--给用户 ZWQRY% 赋予角色
SELECT 'grant '||granted_role||' to '||grantee||';' FROM dba_role_privs WHERE grantee LIKE 'ZWQRY%';
--查看用户 ZWQRY% 创建语句
select 'create user '||username||' identified by values '||password||' default tablespace TBSZW_FOLD1_INDEX001 temporary tablespace temp ;'
from dba_users where username LIKE 'ZWQRY%' ;
--查看用户ZWQRY% 角色
SELECT DISTINCT(granted_role) FROM Dba_Role_Privs WHERE grantee LIKE 'ZWQRY%';
--以下是角色
CONNECT
RESOURCE
SELECT_ON_NO1
SELECT_ON_DISHI
SELECT_ON_NO2
SELECT_ON_NO3
SELECT_ON_NO4
DBA
--需要创建5个非系统角色
create role SELECT_ON_NO1 ;
create role SELECT_ON_DISHI;
create role SELECT_ON_NO2;
create role SELECT_ON_NO3;
create role SELECT_ON_NO4 ;
--查看新创建5个非系统角色有哪些系统权限。
select * from dba_sys_privs where grantee in ('SELECT_ON_NO1','SELECT_ON_NO2','SELECT_ON_NO3','SELECT_ON_NO4','SELECT_ON_DISHI'); 空
--查看新创建5个非系统角色有哪些对象权限。
select * from dba_tab_privs where grantee in ('SELECT_ON_NO1','SELECT_ON_NO2','SELECT_ON_NO3','SELECT_ON_NO4','SELECT_ON_DISHI');
--给5个非系统角色赋予对象权限
SELECT 'grant select on '||owner||'.'||table_name||' to '||grantee||';' from dba_tab_privs
where grantee in ('SELECT_ON_NO1','SELECT_ON_NO2','SELECT_ON_NO3','SELECT_ON_NO4','SELECT_ON_DISHI');
--给用户 ZWQRY% 赋予角色
SELECT 'grant '||granted_role||' to '||grantee||';' FROM dba_role_privs WHERE grantee LIKE 'ZWQRY%';
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/26179376/viewspace-1985161/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/26179376/viewspace-1985161/