一 安全體系
登錄、安全賬戶（用戶）、角色和組是 Microsoft? SQL Server? 2000 安全機制的基礎。連接到 SQL Server 的用戶必須使用特定的登錄標識符 (ID) 標識自己。因此，用戶只能查看經(jīng)授權可以查看的表和視圖，并且只能執(zhí)行經(jīng)授權可以執(zhí)行的存儲過程和管理功能。在SQL Server中，login（登錄）是用于進入服務器時進行身份識別的，每個user和login相關聯(lián)，保證能進入服務器，每個user屬于各個role，擁有相應的數(shù)據(jù)庫權限，整個體系如圖：
二 系統(tǒng)存儲過程和命令
1、存儲過程：
登錄： sp_grantlogin, sp_revokelogin, sp_denylogin
sp_addlogin, sp_droplogin
sp_helplogins
用戶： sp_grantdbaccess, sp_revokedbaccess
sp_adduser, sp_dropuser (這兩個SP是為了向后兼容，請使用前2個代替)
sp_helpuser
角色：
服務器角色 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、固定服務器角色：
固定服務器角色
描述
sysadmin 可以在 SQL Server 中執(zhí)行任何活動
serveradmin 可以設置服務器范圍的配置選項，關閉服務器
setupadmin 可以管理鏈接服務器和啟動過程
securityadmin 可以管理登錄和 CREATE DATABASE 權限，還可以讀取錯誤日志和更改密碼
processadmin 可以管理在 SQL Server 中運行的進程
dbcreator 可以創(chuàng)建、更改和除去數(shù)據(jù)庫
diskadmin 可以管理磁盤文件
bulkadmin 可以執(zhí)行 BULK INSERT 語句
2、固定數(shù)據(jù)庫角色：
固定數(shù)據(jù)庫角色
描述
db_owner 在數(shù)據(jù)庫中有全部權限
db_accessadmin 可以添加或刪除用戶 ID
db_securityadmin 可以管理全部權限、對象所有權、角色和角色成員資格
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ù)
四 權限
包括兩種類型的權限，即對象權限和語句權限：
對象操作權限總結
表 SELECT, INSERT, UPDATE, DELETE, REFERENCE
視圖 SELECT, UPDATE, INSERT, DELETE
存儲過程 EXECUTE
函數(shù) 表值函數(shù) SELECT, INSERT, UPDATE, DELETE, REFERENCE
標量值函數(shù) EXECUTE 和 REFERENCES
內(nèi)嵌表函數(shù) EXECUTE 和 REFERENCES
列 SELECT, UPDATE
注意：REFERENCE充許在GRANT、DENY、REVOKE語句中向有外鍵參照表中插入一行數(shù)據(jù)。
語句權限總結
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 備份事務日志
五 實用腳本
在sql server中，登錄到某數(shù)據(jù)庫必須有兩個條件：
1、為SQL Server建立一個login；
2、為該login在數(shù)據(jù)庫中建立user，給該用戶關聯(lián)role
/* DBA 的新增登錄授權腳本*/
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. 分配權限
--語句權限
GRANT CREATE TABLE TO lewis_user
--對象權限
GRANT SELECT ON fb_app_module TO lewis_user
--授予所有權限
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' --建立存儲過程權限
--5. 更改登錄的默認數(shù)據(jù)庫
EXEC sp_defaultdb @loginame = N'login', @defdb = N'default_DB_name'
--6. 更改登錄的默認語言
EXEC sp_defaultlanguage @loginame = N'login', @language = N' french'
/*DBA 的刪除登錄授權腳本*/
--1. 拒絕授權
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 的查看授權腳本*/
--1、查看服務器的登錄
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 /*服務器角色*/
--3、查看用戶有什么權限和角色????
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 |