数据库角色、登录用户、安全帐户管理

方法一:创建新的角色、登录用户、安全帐户实现数据库权限

/**********************************************************************
存储过程:  P_SYSTEM_GrantRoleToAllUserTable
功能:         为数据库添加角色,赋予该角色对所有用户表的查询条件、
                  添加登录用户、设置默认密码、默认数据库
                  为登录用户设置安全帐号
                  添加安全帐号为角色的成员

参数:        角色名称 (@RoleName)
                 登录用户名(@LoginUserName)
                 登录密码(@LoginUserPwd)
                 默认数据库(@DbName)
                 安全帐号(@SafeAccount)
   
返回:        无
调用方法: 
                  完成如下功能:
                  添加角色: "Test_Role" 对数据库 "Test" 所有表查询权限
                 添加登录用户:  在"TEST"数据库中添加,"LVPIN" 和密码 "B78ECA8C3C" 
                 为登录用户 "LVPIN"添加安全帐号"Test_SafeAccount"
                  添加安全帐号为角色成员
  
  调用如下:
  P_SYSTEM_GrantRoleToAllUserTable 'Test_Role','LVPIN','B78ECA8C3C','TEST','Test_SafeAccount'
 疑问联系:        zlp321001@hotmail.com
最后更改日期: 2005-12-29 
**********************************************************************/

Create PROC P_SYSTEM_GrantRoleToAllUserTable
@RoleName  varchar(20),
@LoginUserName  varchar(10),
@LoginUserPwd   varchar(10),
@DbName  varchar(20),
@SafeAccount  varchar(10)
WITH ENCRYPTION
AS
Begin
--创建角色
EXEC sp_addrole @RoleName
--授予数据库中所有用户表查询权限给某一个角色
declare @UserTablename varchar(20)
Declare Cur Cursor For
     select Name from sysobjects where xtype='u' and status>=0
declare @SQL Varchar(200)
Open Cur
Fetch Cur Into @UserTablename
While @@FETCH_STATUS=0
         BEGIN
                Set @sql='GRANT SELECT ON '+@UserTablename+' TO '+@RoleName+''
  Exec(@sql)
                Fetch Cur Into @UserTablename
         End
Close Cur
Deallocate cur
--添加登录,设置密码,默认数据库
EXEC sp_addlogin @LoginUserName,@LoginUserPwd,@DbName
--为登录在数据库中添加安全账户
EXEC sp_grantdbaccess @LoginUserName,@SafeAccount
--添加安全帐户为角色的成员
EXEC sp_addrolemember @RoleName,@SafeAccount

End
GO

/**********************************************************************
存储过程:  P_SYSTEM_DestroyGrantRoleToAllUserTable
功能:      删除安全帐户、删除登录用户、删除角色
参数:     无
返回:      无
调用方法: 
              调用如下:
               P_SYSTEM_DestroyGrantRoleToAllUserTable 'Test_SafeAccount','LVPIN','Test_Role'
 疑问联系:  zlp321001@hotmail.com
最后更改日期: 2005-12-29 
**********************************************************************/
CREATE PROC P_SYSTEM_DestroyGrantRoleToAllUserTable
@SafeAccount varchar(10),
@LoginUserName varchar(10),
@RoleName varchar(20)
WITH ENCRYPTION
AS
Begin
--从当前数据库中删除安全账户
EXEC sp_revokedbaccess @SafeAccount
--删除登录
EXEC sp_droplogin @LoginUserName
--删除角色 r_test
EXEC sp_droprole @RoleName
End
GO

方法二:利用数据库已有的角色db_datareader,实现数据库安全管理

/**********************************************************************
存储过程:  P_SYSTEM_GrantRoleToAllUserTable
功能:         为数据库添加角色,赋予该角色对所有用户表的查询条件、
                 添加登录用户、设置默认密码、默认数据库
                 为登录用户设置安全帐号
                添加安全帐号为角色的成员

参数:     登录用户名(@LoginUserName)
              登录密码(@LoginUserPwd)
             默认数据库(@DbName)
            安全帐号(@SafeAccount)
   
返回:      无
调用方法: 
          完成如下功能:
                添加登录用户:  在"TEST"数据库中添加,"LVPIN" 和密码 "B78ECA8C3C"
                对所有用户表,读取权限
 
  调用如下:
  P_SYSTEM_GrantRoleToAllUserTable 'LVPIN','B78ECA8C3C','TEST'
 疑问联系:  zlp321001@hotmail.com
最后更改日期: 2005-12-29 
**********************************************************************/
ALTER PROC P_SYSTEM_GrantRoleToAllUserTable
@LoginUserName  varchar(10),
@LoginUserPwd   varchar(10),
@DbName  varchar(20)
WITH ENCRYPTION
AS
Begin
--添加登录,设置密码,默认数据库
EXEC sp_addlogin @LoginUserName,@LoginUserPwd,@DbName
--为登录在数据库中添加安全账户
EXEC sp_grantdbaccess @LoginUserName,@LoginUserName
--添加安全帐户为角色的成员
EXEC sp_addrolemember 'db_datareader',@LoginUserName

End
GO


/**********************************************************************
存储过程:  P_SYSTEM_DestroyGrantRoleToAllUserTable
功能:      删除安全帐户、删除登录用户、删除角色
参数:     
              用户名(@LoginUserName)

返回:      无
调用方法: 
             调用如下:
                 P_SYSTEM_DestroyGrantRoleToAllUserTable 'LVPIN'
  疑问联系:  zlp321001@hotmail.com
最后更改日期: 2005-12-29 
**********************************************************************/
ALTER PROC P_SYSTEM_DestroyGrantRoleToAllUserTable
@LoginUserName varchar(10)
WITH ENCRYPTION
AS
Begin
--从当前数据库中删除安全账户
EXEC sp_revokedbaccess @LoginUserName
--删除登录
EXEC sp_droplogin @LoginUserName
End
GO

方法三:不通过存储过程完成,直接用语句实现:

--创建用户,继承角色db_datareader

--添加登录,设置密码,默认数据库
EXEC sp_addlogin 'LVPIN','B78ECA8C3C','Jrjinfo2003'
--为登录在数据库中添加安全账户
EXEC sp_grantdbaccess 'LVPIN','LVPIN'
--添加安全帐户为角色的成员
EXEC sp_addrolemember 'db_datareader','LVPIN'


--部署已经完成

--执行结果:
已创建新登录。
已向 'LVPIN' 授予数据库访问权。
'LVPIN' 已添加到角色 'db_datareader' 中。

--删除用户、角色
--从当前数据库中删除安全账户
EXEC sp_revokedbaccess 'LVPIN'
--删除登录
EXEC sp_droplogin 'LVPIN'


阅读更多
想对作者说点什么?

博主推荐

换一批

没有更多推荐了,返回首页