Sql server 2005遷移至Oracle系列之五：角色、使用者、及許可權
--基本上完美處理了insert、update、delete、select、control、execute等許可權
--不能對映alter許可權的處理
set nocount on
--清理臨時表
begin
if not object_id('tempdb..#perminssion') is null
drop table #perminssion
if not object_id('tempdb..#usergroup') is null
drop table #usergroup
end
begin
--所有的顯示許可權
select *
into #perminssion
from (
select distinct user_name(grantee_principal_id) as grantee_name,grantee_principal_id
,class,class_desc
,case class
when 0 then db_name()
when 1 then object_name(major_id)
when 3 then schema_name(major_id)
when 4 then user_name(major_id)
end
as object_name
,(case when b.type is null and class = 3 then 'SCM' else b.type end ) as object_type
,permission_name,state_desc
,isnull(b.is_ms_shipped,0) as is_ms_shipped
from sys.database_permissions a left join sys.objects b on a.major_id = b.object_id
) t
where class in (1,3) --資料庫、物件、構架
and isnull(is_ms_shipped,0) = 0
and not (class = 3 and object_name = 'sys') --dbo架構
and object_name not like 'sp%'
and object_name not like 'sys%'
and object_name not in ('fn_diagramobjects')
and not (class = 1 and object_type is null)
and permission_name not in ('VIEW DEFINITION')
order by grantee_name,object_name
--資料庫使用者的隸屬關係
select *
into #usergroup
from
(
select c.name as username,c.type,c.principal_id as user_principal_id,a.name as role_name,a.principal_id as role_principal_id,1 as is_role
from sys.database_principals a inner join sys.database_role_members b on a.principal_id = b.role_principal_id inner join sys.database_principals c on b.member_principal_id = c.principal_id
union all
select name as username,type,principal_id as user_principal_id,'' as role_name,'' as role_principal_id,0 as is_role
from sys.database_principals
where type = 's'
union all
select name as username,type,principal_id as user_principal_id,'public' as role_name,0 as role_principal_id,1 as is_role
from sys.database_principals
where type = 's'
)t
where username not in ('guest')
order by username,is_role,role_name
/*
select * from #usergroup
select * from #perminssion
*/
end
--指令碼開始部分
begin
print replicate(char(9),0) + '--' + replicate('*',50) + '該指令碼適用於 oracle資料庫系統' + replicate('*',50)
print replicate(char(9),0) + '--' + replicate('*',50) + '該指令碼適用於 oracle資料庫系統' + replicate('*',50)
print replicate(char(9),0) + 'DECLARE'
print replicate(char(9),1) + 'VA_EXIST_USER INT:=0;'
print replicate(char(9),1) + 'VA_EXIST_PRIV INT:=0;'
print replicate(char(9),1) + 'VA_EXIST_OBJ INT:=0;'
print replicate(char(9),0) + 'BEGIN'
end
--變數宣告部分
begin
declare @iscreaterole int --（0.不生成角色；1.生成角色）
set @iscreaterole = 1
declare @iscreateuser int --（0.不生成使用者；1.生成使用者）
set @iscreateuser = 1
declare @isobjectgrant int --（0.不生成物件及系統許可權；1.生成物件及系統許可權）
set @isobjectgrant = 1
declare @role_name sysname
declare @role_name_grant sysname
declare @username sysname
declare @usernameold sysname
declare @is_role int
declare @grantee_name sysname
declare @class int
declare @object_name sysname
declare @object_type sysname
declare @permission_name sysname
declare @state_desc sysname
declare @grantorrevoke sysname
declare @oraclepermission sysname
declare @exec nvarchar(1000)
declare @execgrant nvarchar(1000)
declare @sub_object_name sysname
declare @sub_object_type sysname
end
--建立必要的角色
if @iscreaterole = 1
begin
declare cr_group cursor for
select distinct role_name
from #usergroup
where username not in ('dbo','sys','95013')
and username in (select distinct username from #usergroup where is_role = 0 )
and role_name not in ('public','guest')
and is_role = 1
order by role_name
open cr_group
fetch next from cr_group into @role_name
while @@fetch_status = 0
begin
print replicate(char(9),1) + '--建立角色[' + upper(@role_name) + ']'
print replicate(char(9),1) + 'BEGIN'
print replicate(char(9),2) + 'VA_EXIST_USER := 0;'
print replicate(char(9),2) + 'SELECT COUNT(ROLE) INTO VA_EXIST_USER FROM DBA_ROLES WHERE ROLE = ''' + upper(@role_name) + ''';'
print replicate(char(9),2) + 'IF VA_EXIST_USER > 0 THEN '
print replicate(char(9),3) + 'EXECUTE IMMEDIATE (''DROP ROLE ' + upper(@role_name) + ''');'
print replicate(char(9),3) + 'EXECUTE IMMEDIATE (''CREATE ROLE ' + upper(@role_name) + ''');'
print replicate(char(9),2) + 'ELSE'
print replicate(char(9),3) + 'EXECUTE IMMEDIATE (''CREATE ROLE ' + upper(@role_name) + ''');'
print replicate(char(9),2) + 'END IF;'
print replicate(char(9),1) + 'END;'
--處理sqlserver固定資料庫角色許可權，可按需新增定義
begin
/*
db_owner db_accessadmin db_securityadmin db_ddladmin
db_backupoperator db_datareader db_datawriter db_denydatareader db_denydatawriter
*/
if @role_name = 'db_owner'
begin
print replicate(char(9),1) + 'BEGIN'
print replicate(char(9),2) + 'EXECUTE IMMEDIATE (''GRANT DBA TO DB_OWNER'');'
print replicate(char(9),1) + 'END;'
end
else if @role_name = 'db_datareader'
begin
print replicate(char(9),1) + 'BEGIN'
print replicate(char(9),2) + 'EXECUTE IMMEDIATE (''GRANT SELECT ANY TABLE TO DB_DATAREADER'');'
print replicate(char(9),1) + 'END;'
end
end
print replicate(char(9),1) + ''
fetch next from cr_group into @role_name
end
close cr_group
deallocate cr_group
end
--建立必要使用者並新增到角色
if @iscreateuser = 1
begin
declare cr_usergroup cursor for
select distinct username,role_name,is_role
from #usergroup
where username not in ('dbo','sys','95013')
and username in (select distinct username from #usergroup where is_role = 0 )
and role_name not in ('public','guest')
order by username,is_role,role_name
set @usernameold = ''
set @role_name_grant = ''
open cr_usergroup
fetch next from cr_usergroup into @username,@role_name,@is_role
while @@fetch_status = 0
begin
if @is_role = 0
begin
if @role_name_grant <> ''
begin
print replicate(char(9),1) + '--新增使用者[' + upper(@usernameold) + ':' + @usernameold + ']到角色[' + upper(@role_name_grant) + ']中'
print replicate(char(9),1) + 'BEGIN'
print replicate(char(9),2) + 'EXECUTE IMMEDIATE (''GRANT ' + upper(@role_name_grant) + ' TO ' + upper(@usernameold) + ''');'
print replicate(char(9),2) + 'EXECUTE IMMEDIATE (''ALTER USER ' + upper(@usernameold) + ' DEFAULT ROLE ' + upper(@role_name_grant) + ''');'
print replicate(char(9),1) + 'END;'
print replicate(char(9),1) + ''
set @role_name_grant = ''
end
print replicate(char(9),1) + '--建立使用者[' + upper(@username) + ':' + @username + ']'
print replicate(char(9),1) + 'BEGIN'
print replicate(char(9),2) + 'VA_EXIST_USER := 0;'
print replicate(char(9),2) + 'SELECT COUNT(USER_ID) INTO VA_EXIST_USER FROM DBA_USERS WHERE USERNAME = ''' + upper(@username) + ''';'
print replicate(char(9),2) + 'IF VA_EXIST_USER = 0 THEN '
print replicate(char(9),3) + 'EXECUTE IMMEDIATE (''CREATE USER ' + upper(@username) + ' IDENTIFIED BY ' + lower(@username) + ''');'
print replicate(char(9),2) + 'ELSE'
print replicate(char(9),3) + 'BEGIN'
print replicate(char(9),4) + 'SELECT USER_ID INTO VA_EXIST_USER FROM DBA_USERS WHERE USERNAME = ''' + upper(@username) + ''';'
print replicate(char(9),4) + 'IF VA_EXIST_USER < 80 AND VA_EXIST_USER > 0 THEN'
print replicate(char(9),5) + 'NULL;'
print replicate(char(9),4) + 'ELSE'
print replicate(char(9),5) + 'EXECUTE IMMEDIATE (''DROP USER ' + upper(@username) + ' CASCADE '');'
print replicate(char(9),5) + 'EXECUTE IMMEDIATE (''CREATE USER ' + upper(@username) + ' IDENTIFIED BY ' + lower(@username) + ''');'
print replicate(char(9),4) + 'END IF;'
print replicate(char(9),3) + 'END;'
print replicate(char(9),2) + 'END IF;'
print replicate(char(9),2) + 'EXECUTE IMMEDIATE (''GRANT CREATE SESSION,ALTER SESSION,DEBUG CONNECT SESSION,DEBUG ANY PROCEDURE,UNLIMITED TABLESPACE TO ' + upper(@username) + ''');'
print replicate(char(9),1) + 'END;'
print replicate(char(9),1) + ''
set @usernameold = @username
end
else
begin
if @usernameold = @username
set @role_name_grant = case @role_name_grant when '' then @role_name else @role_name_grant + ',' + @role_name end
end
fetch next from cr_usergroup into @username,@role_name,@is_role
end
close cr_usergroup
deallocate cr_usergroup
if @role_name_grant <> ''
begin
print replicate(char(9),1) + '--新增使用者[' + upper(@usernameold) + ':' + @usernameold + ']到角色[' + upper(@role_name_grant) + ']中'
print replicate(char(9),1) + 'EXECUTE IMMEDIATE (''GRANT ' + upper(@role_name_grant) + ' TO ' + upper(@usernameold) + ''');'
print replicate(char(9),1) + ''
set @role_name_grant = ''
end
end
--建立物件及系統許可權
--構架許可權對映到物件許可權上
--只考慮insert、delete、update、execute、alter、control許可權
--對alter的許可權暫時解釋不是很明確
if @isobjectgrant = 1
begin
print replicate(char(9),1) + '--處理角色及使用者的系統許可權和物件許可權'
declare cr cursor for
select grantee_name,class,object_name,object_type,permission_name,state_desc
from #perminssion
order by state_desc desc
open cr
fetch next from cr into @grantee_name,@class,@object_name,@object_type,@permission_name,@state_desc
while @@fetch_status = 0
begin
set @exec = ''
set @grantorrevoke = case @state_desc
when 'DENY' then 'REVOKE '
when 'GRANT' then 'GRANT '
when 'GRANT_WITH_GRANT_OPTION' then 'GRANT '
else null
end
--構架
if @class = 3
begin
declare cr_obj cursor for
select name,type
from sys.objects a
where isnull(is_ms_shipped,0) = 0
and (a.type in ('U','P','FN','IF','TF')) --表、儲存過程、標量函式、檢視
and (a.name not like 'sp_%')
and (a.name not in ('sysdiagrams','fn_diagramobjects'))
and (a.schema_id = schema_id('dbo')) --dbo架構
order by type,name
open cr_obj
fetch next from cr_obj into @sub_object_name,@sub_object_type
while @@fetch_status = 0
begin
set @exec = ''
if @sub_object_type in ('U','V')
set @oraclepermission = case
when @permission_name in ('CONTROL') then 'ALL'
when @permission_name in ('ALTER','INSERT','DELETE','SELECT','UPDATE') then @permission_name
else null
end
else if @sub_object_type in ('P','FN','IF','TF')
set @oraclepermission = case
when @permission_name in ('CONTROL','EXECUTE') then 'ALL'
when @permission_name in ('ALTER') then null
else null
end
if @grantorrevoke = 'GRANT'
set @exec = @grantorrevoke + @oraclepermission + ' ON DBO.' + upper(left(@sub_object_name,30)) + ' TO ' + upper(@grantee_name) + ''
else if @grantorrevoke = 'REVOKE'
begin
set @exec = @grantorrevoke + @oraclepermission + ' ON DBO.' + upper(left(@sub_object_name,30)) + ' FROM ' + upper(@grantee_name) + ''
end
if @exec <> ''
begin
if @grantorrevoke = 'REVOKE'
begin
print replicate(char(9),1) + 'BEGIN'
print replicate(char(9),2) + 'VA_EXIST_PRIV := 0;'
if @oraclepermission = 'ALL'
print replicate(char(9),2) + 'SELECT COUNT(*) INTO VA_EXIST_PRIV FROM DBA_TAB_PRIVS WHERE WNER = ''DBO'' AND TABLE_NAME = ''' + upper(left(@sub_object_name,30)) + ''' AND GRANTEE = ''' + upper(@grantee_name) + ''';'
else
print replicate(char(9),2) + 'SELECT COUNT(*) INTO VA_EXIST_PRIV FROM DBA_TAB_PRIVS WHERE WNER = ''DBO'' AND TABLE_NAME = ''' + upper(left(@sub_object_name,30)) + ''' AND PRIVILEGE = ''' + upper(@oraclepermission) + ''' AND GRANTEE = ''' + upper(@grantee_name) + ''';'
print replicate(char(9),2) + 'IF VA_EXIST_PRIV > 0 THEN '
print replicate(char(9),3) + 'EXECUTE IMMEDIATE (''' + @exec + ''');' + CHAR(9) + '--物件型別是: ' + @sub_object_type
print replicate(char(9),2) + 'END IF;'
print replicate(char(9),1) + 'END;'
end
else if @grantorrevoke = 'GRANT'
begin
print replicate(char(9),1) + 'BEGIN'
print replicate(char(9),2) + 'VA_EXIST_OBJ := 0;'
print replicate(char(9),2) + 'SELECT COUNT(*) INTO VA_EXIST_OBJ FROM DBA_OBJECTS WHERE WNER = ''DBO'' AND OBJECT_NAME = ''' + upper(left(@sub_object_name,30)) + ''';'
print replicate(char(9),2) + 'IF VA_EXIST_OBJ > 0 THEN '
print replicate(char(9),3) + 'EXECUTE IMMEDIATE (''' + @exec + ''');' + CHAR(9) + '--物件型別是: ' + @sub_object_type
print replicate(char(9),2) + 'END IF;'
print replicate(char(9),1) + 'END;'
end
end
fetch next from cr_obj into @sub_object_name,@sub_object_type
end
close cr_obj
deallocate cr_obj
end
--資料庫物件
if @class = 1
begin
if @object_type = 'U'
set @oraclepermission = case
when @permission_name in ('CONTROL','ALTER') then 'ALL'
when @permission_name in ('INSERT','DELETE','SELECT','UPDATE') then @permission_name
else null
end
else if @object_type in ('P','FN','IF','TF')
set @oraclepermission = case
when @permission_name in ('CONTROL','ALTER') then 'ALL'
when @permission_name in ('EXECUTE') then @permission_name
else null
end
if @grantorrevoke = 'GRANT'
set @exec = @grantorrevoke + @oraclepermission + ' ON DBO.' + upper(left(@object_name,30)) + ' TO ' + upper(@grantee_name) + ''
else if @grantorrevoke = 'REVOKE'
set @exec = @grantorrevoke + @oraclepermission + ' ON DBO.' + upper(left(@object_name,30)) + ' FROM ' + upper(@grantee_name) + ''
if @exec <> ''
begin
if @grantorrevoke = 'REVOKE'
begin
print replicate(char(9),1) + 'BEGIN'
print replicate(char(9),2) + 'VA_EXIST_PRIV := 0;'
if @oraclepermission = 'ALL'
print replicate(char(9),2) + 'SELECT COUNT(*) INTO VA_EXIST_PRIV FROM DBA_TAB_PRIVS WHERE WNER = ''DBO'' AND TABLE_NAME = ''' + upper(left(@object_name,30)) + ''' AND GRANTEE = ''' + upper(@grantee_name) + ''';'
else
print replicate(char(9),2) + 'SELECT COUNT(*) INTO VA_EXIST_PRIV FROM DBA_TAB_PRIVS WHERE WNER = ''DBO'' AND TABLE_NAME = ''' + upper(left(@object_name,30)) + ''' AND PRIVILEGE = ''' + upper(@oraclepermission) + ''' AND GRANTEE = ''' + upper(@grantee_name) + ''';'
print replicate(char(9),2) + 'IF VA_EXIST_PRIV > 0 THEN '
print replicate(char(9),3) + 'EXECUTE IMMEDIATE (''' + @exec + ''');' + CHAR(9) + '--物件型別是: ' + @object_type
print replicate(char(9),2) + 'END IF;'
print replicate(char(9),1) + 'END;'
end
else if @grantorrevoke = 'GRANT'
begin
print replicate(char(9),1) + 'BEGIN'
print replicate(char(9),2) + 'VA_EXIST_OBJ := 0;'
print replicate(char(9),2) + 'SELECT COUNT(*) INTO VA_EXIST_OBJ FROM DBA_OBJECTS WHERE WNER = ''DBO'' AND OBJECT_NAME = ''' + upper(left(@object_name,30)) + ''';'
print replicate(char(9),2) + 'IF VA_EXIST_OBJ > 0 THEN '
print replicate(char(9),3) + 'EXECUTE IMMEDIATE (''' + @exec + ''');' + CHAR(9) + '--物件型別是: ' + @object_type
print replicate(char(9),2) + 'END IF;'
print replicate(char(9),1) + 'END;'
end
end
end
fetch next from cr into @grantee_name,@class,@object_name,@object_type,@permission_name,@state_desc
end
close cr
deallocate cr
print replicate(char(9),1) + ''
end
--指令碼結束部分
begin
print replicate(char(9),0) + 'END;'
print replicate(char(9),0) + '--' + replicate('*',50) + '該指令碼適用於 oracle資料庫系統' + replicate('*',50)
print replicate(char(9),0) + '--' + replicate('*',50) + '該指令碼適用於 oracle資料庫系統' + replicate('*',50)
end
原文：http://www.cnblogs.com/jinzhenshui/articles/1360224.html
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/16436858/viewspace-598138/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Sql server 2005遷移至Oracle系列之一：生成表SQLServerOracle
- Sql server 2005遷移至Oracle系列之二：生成儲存過程SQLServerOracle儲存過程
- Oracle使用者角色許可權管理Oracle
- Oracle使用者、許可權、角色管理Oracle
- Oracle角色許可權之Default RoleOracle
- Oracle使用者及角色的許可權管理[Oracle基礎]Oracle
- Sql server 2005遷移至Oracle系列之四：在Oracle中建立位或運算函式-bitorSQLServerOracle函式
- [學習]ORACLE使用者、角色、許可權Oracle
- Oracle使用者、許可權、角色管理【轉】Oracle
- Sql server 2005遷移至Oracle系列之三：在Oracle中建立sql中的常見函式SQLServerOracle函式
- Oracle的物件許可權、角色許可權、系統許可權Oracle物件
- SQL Server中查詢使用者的物件許可權和角色的方法SQLServer物件
- 【使用者管理】oracle使用者、角色、許可權管理Oracle
- Oracle 查詢許可權角色Oracle
- 關於SQL Server資料庫中的使用者許可權和角色管理SQLServer資料庫
- SQL Server 許可權管理SQLServer
- 檢視Oracle使用者的許可權或角色Oracle
- SQL Server許可權問題SQLServer
- SQL Server 2008固定資料庫角色的許可權SQLServer資料庫
- Sql Server關於許可權、角色以及登入名、使用者名稱的總結SQLServer
- 使用者角色許可權管理架構架構
- Oracle中使用者、角色、與許可權涉及的相關表及檢視Oracle
- 如何檢視oracle使用者具有的許可權和角色Oracle
- sql server的許可權查詢SQLServer
- Oracle 使用者、物件許可權、系統許可權Oracle物件
- Oracle12c多租戶管理使用者、角色、許可權Oracle
- Oracle使用者訪問許可權與PUBLIC角色的關係Oracle訪問許可權
- 資料庫安全之許可權與角色資料庫
- 檢視角色裡包含的系統許可權、物件許可權和角色物件
- oracle使用者許可權Oracle
- oracle 使用者許可權Oracle
- 【許可權管理】Oracle中檢視、回收使用者許可權Oracle
- SQL Server SA許可權最新入侵方法SQLServer
- Oracle檢視當前登陸使用者的許可權或者角色Oracle
- RabbitMQ使用教程（二）RabbitMQ使用者管理,角色管理及許可權設定MQ
- Oracle檢視使用者預設表空間、臨時表空間、系統許可權、物件許可權、角色許可權舉例說明Oracle物件
- Oracle使用者與許可權Oracle
- 使用者許可權 plsql OracleSQLOracle