一 安全體系
登錄、安全賬戶（用戶）、角色和組是 Microsoft? SQL Server? 2000 安全機(jī)制的基礎(chǔ)。連接到 SQL Server 的用戶必須使用特定的登錄標(biāo)識符 (ID) 標(biāo)識自己。因此，用戶只能查看經(jīng)授權(quán)可以查看的表和視圖，并且只能執(zhí)行經(jīng)授權(quán)可以執(zhí)行的存儲過程和管理功能。在SQL Server中，login（登錄）是用于進(jìn)入服務(wù)器時(shí)進(jìn)行身份識別的，每個(gè)user和login相關(guān)聯(lián)，保證能進(jìn)入服務(wù)器，每個(gè)user屬于各個(gè)role，擁有相應(yīng)的數(shù)據(jù)庫權(quán)限，整個(gè)體系如圖：
二 系統(tǒng)存儲過程和命令
1、存儲過程：
登錄： sp_grantlogin, sp_revokelogin, sp_denylogin
sp_addlogin, sp_droplogin
sp_helplogins
用戶： sp_grantdbaccess, sp_revokedbaccess
sp_adduser, sp_dropuser (這兩個(gè)SP是為了向后兼容，請使用前2個(gè)代替)
sp_helpuser
角色：
服務(wù)器角色 sp_addsrvrolemember, sp_dropsrvrolemember
數(shù)據(jù)庫角色 sp_addrole, sp_droprole
sp_addapprole, sp_dropapprole
sp_helprole
sp_addrolemember, sp_droprolemember
sp_helprolemember
2、命令：grant, revoke, deny
三 固定角色
1、固定服務(wù)器角色：
固定服務(wù)器角色
描述
sysadmin 可以在 SQL Server 中執(zhí)行任何活動(dòng)
serveradmin 可以設(shè)置服務(wù)器范圍的配置選項(xiàng)，關(guān)閉服務(wù)器
setupadmin 可以管理鏈接服務(wù)器和啟動(dòng)過程
securityadmin 可以管理登錄和 CREATE DATABASE 權(quán)限，還可以讀取錯(cuò)誤日志和更改密碼
processadmin 可以管理在 SQL Server 中運(yùn)行的進(jìn)程
dbcreator 可以創(chuàng)建、更改和除去數(shù)據(jù)庫
diskadmin 可以管理磁盤文件
bulkadmin 可以執(zhí)行 BULK INSERT 語句
2、固定數(shù)據(jù)庫角色：
固定數(shù)據(jù)庫角色
描述
db_owner 在數(shù)據(jù)庫中有全部權(quán)限
db_accessadmin 可以添加或刪除用戶 ID
db_securityadmin 可以管理全部權(quán)限、對象所有權(quán)、角色和角色成員資格
db_ddladmin 可以發(fā)出 ALL DDL，但不能發(fā)出 GRANT、REVOKE 或 DENY 語句
db_backupoperator 可以發(fā)出 DBCC、CHECKPOINT 和 BACKUP 語句
db_datareader 可以選擇數(shù)據(jù)庫內(nèi)任何用戶表中的所有數(shù)據(jù)
db_datawriter 可以更改數(shù)據(jù)庫內(nèi)任何用戶表中的所有數(shù)據(jù)
db_denydatareader 不能選擇數(shù)據(jù)庫內(nèi)任何用戶表中的任何數(shù)據(jù)
db_denydatawriter 不能更改數(shù)據(jù)庫內(nèi)任何用戶表中的任何數(shù)據(jù)
四 權(quán)限
包括兩種類型的權(quán)限，即對象權(quán)限和語句權(quán)限：
對象操作權(quán)限總結(jié)
表 SELECT, INSERT, UPDATE, DELETE, REFERENCE
視圖 SELECT, UPDATE, INSERT, DELETE
存儲過程 EXECUTE
函數(shù) 表值函數(shù) SELECT, INSERT, UPDATE, DELETE, REFERENCE
標(biāo)量值函數(shù) EXECUTE 和 REFERENCES
內(nèi)嵌表函數(shù) EXECUTE 和 REFERENCES
列 SELECT, UPDATE
注意：REFERENCE充許在GRANT、DENY、REVOKE語句中向有外鍵參照表中插入一行數(shù)據(jù)。
語句權(quán)限總結(jié)
CREATE DATABASE 創(chuàng)建數(shù)據(jù)庫
CREATE TABLE 創(chuàng)建表
CREATE VIEW 創(chuàng)建視圖
CREATE RULE 創(chuàng)建規(guī)則
CREATE DEFAULT 創(chuàng)建缺省
CREATE PROCEDURE 創(chuàng)建存儲過程
BACKUP DATABASE 備份數(shù)據(jù)庫
BACKUP LOG 備份事務(wù)日志
五 實(shí)用腳本
在sql server中，登錄到某數(shù)據(jù)庫必須有兩個(gè)條件：
1、為SQL Server建立一個(gè)login；
2、為該login在數(shù)據(jù)庫中建立user，給該用戶關(guān)聯(lián)role
/* DBA 的新增登錄授權(quán)腳本*/
USE my_database
GO
--1. 新建登錄
IF NOT EXISTS(SELECT 1 FROM master.dbo.syslogins WHERE name = 'wh_login' AND loginname = N'wh_login')
EXEC sp_addlogin 'wh_login', '123', 'plm25'
GO
--2. 新建用戶
IF NOT EXISTS(SELECT * FROM sysusers WHERE name = N'lewis_user')
EXEC sp_grantdbaccess 'wh_login', 'lewis_user'
GO
--3. 分配權(quán)限
--語句權(quán)限
GRANT CREATE TABLE TO lewis_user
--對象權(quán)限
GRANT SELECT ON fb_app_module TO lewis_user
--授予所有權(quán)限
GRANT ALL TO lewis_user
--4. 授予角色
EXEC sp_addrolemember N'db_datareader', N'lewis_user'
EXEC sp_addrolemember N'db_datawriter', N'lewis_user'
EXEC sp_addrolemember N'db_accessadmin', N'lewis_user'
EXEC sp_addrolemember N'db_ddladmin', N'lewis_user' --建立存儲過程權(quán)限
--5. 更改登錄的默認(rèn)數(shù)據(jù)庫
EXEC sp_defaultdb @loginame = N'login', @defdb = N'default_DB_name'
--6. 更改登錄的默認(rèn)語言
EXEC sp_defaultlanguage @loginame = N'login', @language = N' french'
/*DBA 的刪除登錄授權(quán)腳本*/
--1. 拒絕授權(quán)
REVOKE SELECT ON [dbo].[fb_app_module] TO [lewis_user] CASCADE
REVOKE CREATE TABLE TO lewis_user
EXEC sp_droprolemember N'db_datareader', N'lewis_user'
--不能更改 public 角色的成員資格。也不需要移除。
--2. 刪除用戶
IF EXISTS(SELECT * FROM sysusers WHERE name = N'lewis')
EXEC sp_revokedbaccess 'lewis_user'
GO
--3. 刪除登錄
IF EXISTS(SELECT 1 FROM master.dbo.syslogins WHERE name = 'wh_login' AND loginname = N'wh_login')
EXEC sp_droplogin 'wh_login'
GO
/*DBA 的查看授權(quán)腳本*/
--1、查看服務(wù)器的登錄
sp_helplogins 查看login和user
SELECT * FROM master.dbo.syslogins WHERE loginname = @login_name
--2、查看數(shù)據(jù)庫的用戶
sp_helpuser
SELECT * FROM dbo.sysusers where issqlrole = 1 /*數(shù)據(jù)庫角色*/
SELECT * FROM dbo.sysusers where islogin = 1 /*登錄*/
SELECT * FROM dbo.sysusers where issqluser = 1 /*SQL Server用戶*/
SELECT * FROM dbo.sysusers where isntname = 1 /*NT用戶*/
SELECT * FROM dbo.sysusers where isapprole = 1 /*服務(wù)器角色*/
--3、查看用戶有什么權(quán)限和角色????
sp_helprolemember 'db_datareader'
--4、用戶擁有的數(shù)據(jù)庫中的對象????
--5、查看用戶、登錄、角色等綜合信息
select DISTINCT username = o.name,
loginname = (case when (o.sid = 0x00) then NULL
else l.loginname end),
rolegroup = user_name(o.gid),
userID = o.uid,
o.hasdbaccess,
o.uid
from dbo.sysusers o left join master.dbo.syslogins l on l.sid = o.sid
where ((o.issqlrole != 1 and o.isapprole != 1) or (o.sid = 0x00) and o.hasdbaccess = 1)
and o.isaliased != 1 and (o.name = N'@your_user_name')
六 示例
-- ===================================================
-- Procedure Name: fb_plm_access_control
-- Function : set PLM Suite application user
-- Failure return: 1
-- Success return: 0
--
-- Parameters :
-- @login_name : application user name, create a account if the user does not exists, otherwise remain it.
-- @password : for the exists user, if the password is null, then remain its old password,
-- : for the new user, it can not be null.
-- @db_name : PLM Suite database name
-- ===================================================
IF EXISTS (select * from sysobjects where id = object_id(N'fb_plm_access_control') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE fb_plm_access_control
GO
CREATE PROCEDURE fb_plm_access_control
(
@login_name NVARCHAR(100),
@password NVARCHAR(100) = null,
@db_name NVARCHAR(100)
)
--WITH ENCRYPTION
AS
DECLARE @result INT
DECLARE @user_exists INT
DECLARE @execute_sp_role NVARCHAR(100)
DECLARE @create_table_role NVARCHAR(100)
DECLARE @object_name NVARCHAR(120)
DECLARE @object_type NVARCHAR(10)
DECLARE @sql VARCHAR(200)
DECLARE @error_msg VARCHAR(100)
BEGIN
-- check input paramters.
IF (@login_name IS NULL) OR (LTRIM(@login_name) = '') OR (lower(@login_name) = 'sa')
BEGIN
RAISERROR ('The login_name can not be null or sa.' , 16, 1) WITH NOWAIT
RETURN 1
END
IF NOT EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = @db_name)
BEGIN
SET @error_msg = 'The database ' + @db_name + ' does not exits.'
RAISERROR (@error_msg, 16, 1) WITH NOWAIT
RETURN 1
END
-- if user does not exits, create it
IF NOT EXISTS (SELECT * FROM master.dbo.syslogins WHERE loginname = @login_name)
BEGIN
SET @user_exists = 0
IF (@password IS NULL) OR (@password = '')
BEGIN
RAISERROR ('For new application user, the password can not be null.' , 16, 1) WITH NOWAIT
RETURN 1
END
EXEC sp_addlogin @loginame = @login_name, @passwd = @password, @defdb = @db_name, @deflanguage = @@language
IF @@ERROR <> 0
RETURN 1
END
ELSE
BEGIN
SET @user_exists = 1
IF (@password IS NOT NULL) AND (LTRIM(@password)<>'')
BEGIN
EXEC sp_password @old = null, @new = @password, @loginame = @login_name
IF @@ERROR <> 0
RETURN 1
END
END
-- create the execute sp role if it does not exists
SET @execute_sp_role = N'db_procexecutor'
IF NOT EXISTS (SELECT * FROM dbo.sysusers WHERE name = @execute_sp_role AND issqlrole = 1)
BEGIN
EXEC sp_addrole @rolename = @execute_sp_role
IF @@ERROR <> 0
RETURN 1
END
-- create the create table role if it does not exists
SET @create_table_role = N'db_createtable'
IF NOT EXISTS (SELECT * FROM dbo.sysusers WHERE name = @create_table_role AND issqlrole = 1)
BEGIN
EXEC sp_addrole @rolename = @create_table_role
IF @@ERROR <> 0
RETURN 1
END
-- grant privilege to the role
DECLARE cur_sp_fun CURSOR
FAST_FORWARD
FOR
SELECT name, xtype
FROM sysobjects
WHERE xtype in (N'P', N'FN', N'IF', N'TF')
AND uid = (select uid from sysusers where name = USER_NAME())
OPEN cur_sp_fun
FETCH NEXT FROM cur_sp_fun INTO @object_name, @object_type
WHILE @@FETCH_STATUS = 0
BEGIN
IF @object_type = N'P'
BEGIN
SET @sql='GRANT EXECUTE ON '+@object_name+' TO '+@execute_sp_role
END
IF @object_type = N'FN'
BEGIN
SET @sql='GRANT REFENENCES ON '+@object_name+' TO '+@execute_sp_role
SET @sql=' GRANT EXECUTE ON '+@object_name+' TO '+@execute_sp_role
END
IF @object_type IN (N'IF',N'TF')
BEGIN
SET @sql='GRANT SELECT ON '+@object_name+' TO '+@execute_sp_role
END
EXEC (@sql)
IF @@ERROR <> 0
BEGIN
SET @result = 1
BREAK
END
FETCH NEXT FROM cur_sp_fun INTO @object_name, @object_type
END
CLOSE cur_sp_fun
DEALLOCATE cur_sp_fun
IF @result = 1
RETURN 1
SET @sql = 'GRANT CREATE TABLE TO ' + @create_table_role
EXEC (@sql)
IF @@ERROR <> 0
RETURN 1
-- grant access database privilege to application user
IF @user_exists = 1
EXEC sp_revokedbaccess @name_in_db = @login_name
IF @@ERROR <> 0
RETURN 1
EXEC sp_grantdbaccess @loginame = @login_name, @name_in_db = @login_name
IF @@ERROR <> 0
RETURN 1
-- grant role to application user
EXEC sp_addrolemember @rolename = N'db_datareader', @membername = @login_name
IF @@ERROR <> 0
RETURN 1
EXEC sp_addrolemember @rolename = N'db_datawriter', @membername = @login_name
IF @@ERROR <> 0
RETURN 1
EXEC sp_addrolemember @rolename = N'db_datawriter', @membername = @login_name
IF @@ERROR <> 0
RETURN 1
EXEC sp_addrolemember @rolename = @create_table_role, @membername = @login_name
IF @@ERROR <> 0
RETURN 1
EXEC sp_addrolemember @rolename = @execute_sp_role, @membername = @login_name
IF @@ERROR <> 0
RETURN 1
RETURN 0
END
GO |