Đặt quyền trên tất cả các đối tượng cho người dùng SQL


1

Tôi có một quy trình lặp qua tất cả các đối tượng trong cơ sở dữ liệu và gán cho chúng quyền thích hợp cho đối tượng đó.Tôi muốn biết nếu có một cách tốt hơn để làm điều này?Tôi sử dụng một cơ sở dữ liệu mô hình để tạo cơ sở dữ liệu mới vì vậy tôi phải chạy nó mỗi khi tôi tạo một cơ sở dữ liệu mới.Đây là một ý tưởng về giao diện của nó (lưu ý: có một đoạn bị thiếu ngay từ đầu làm giảm tất cả người dùng và tạo lại những người dùng cần thiết; điều này là cần thiết vì thay đổi của SID):

CREATE PROCEDURE usp_SetPermissions 
AS 
    BEGIN

DECLARE @CurrentId INT
DECLARE @ObjectName NVARCHAR(128)
DECLARE @Message NVARCHAR(160)
DECLARE @Error INT
DECLARE @Sql NVARCHAR(256)

CREATE TABLE #tmpDbObjects 
(
     ID INT IDENTITY(1,1),
     ObjectName NVARCHAR(128),
     Completed BIT
)

INSERT #tmpDbObjects(ObjectName, Completed)
SELECT DISTINCT [Name], 0 As Completed 
FROM sys.objects 
WHERE [type] = 'U' AND is_ms_shipped <> 1

WHILE EXISTS (SELECT 1 FROM #tmpDbObjects)
    BEGIN
     -- Pick first uncompleted object
     SELECT TOP 1 @CurrentId = ID,
            @ObjectName = ObjectName
     FROM #tmpDbObjects

     -- Grant permissions to DB user
     SET @Sql = 'GRANT SELECT, INSERT, UPDATE, DELETE ON dbo.' + QUOTENAME(@ObjectName) + ' TO ' + QUOTENAME(DB_NAME()) 
     EXEC sp_sqlexec @Sql

     -- Update object completion 
     DELETE #tmpDbObjects
     WHERE [Id] = @CurrentId

     -- Clear variables
     SET @Sql = NULL
     SET @CurrentId = NULL
    END

INSERT #tmpDbObjects(ObjectName, Completed)
SELECT DISTINCT [Name], 0 As Completed 
FROM sys.objects 
WHERE [type] = 'P' AND is_ms_shipped <> 1

WHILE EXISTS (SELECT 1 FROM #tmpDbObjects)
      BEGIN
     -- Pick first uncompleted object
     SELECT TOP 1 @CurrentId = ID,
            @ObjectName = ObjectName
     FROM #tmpDbObjects

     -- Grant permissions to DB user
     SET @Sql = 'GRANT EXEC ON dbo.' + QUOTENAME(@ObjectName) + ' TO ' + QUOTENAME(DB_NAME()) 
EXEC sp_sqlexec @Sql

     -- Update object completion 
     DELETE #tmpDbObjects
     WHERE [Id] = @CurrentId

     -- Clear variables
     SET @Sql = NULL
     SET @CurrentId = NULL
    END

Kịch bản này tiếp tục làm những điều tương tự cho tất cả các khung nhìn, chức năng, v.v. trong cơ sở dữ liệu.Bất kỳ ý tưởng để tăng tốc điều này lên hoặc có cách nào tốt hơn để làm điều này?

6

Một dòng để cấp quyền ở cấp lược đồ cho một vai trò

GRANT EXECUTE SELECT, INSERT, UPDATE, DELETE ON SCHEMA::dbo TO SomeRole

Và một dòng thứ hai để thêm người dùng vào vai trò

EXEC sp_addrolemember 'SomeRole', 'whatever user'

Và làm điều này trong mô hình để tất cả các cơ sở dữ liệu mới kế thừa.

Lý do, bạn chỉ nên đặt quyền một lần :

  • Một lược đồ là một thùng chứa cho các đối tượng.
  • Các đối tượng mới kế thừa quyền từ lược đồ
  • Một vai trò là container cho người dùng
  • Người dùng mới được thêm vào một vai trò và kế thừa

Như bạn đã tìm thấy, di chuyển hoặc khôi phục cơ sở dữ liệu có thể mất quyền đối tượng khi được gán trực tiếp cho người dùng.Vậy tại sao lại đặt mình vào vị trí đó?

Bạn cũng có thể CREATE LOGIN with a SID vì vậy nó giống nhau trên tất cả các máy chủ của bạn và bạn cũng không nhận được người dùng mồ côi.

Nếu bạn hỏi câu hỏi chính xác, chúng tôi có thể đã tiết kiệm cho bạn một số mã hóa ...

Cá nhân và cuối cùng, tôi muốn nói rằng đây là một thực hành tồi về "cần phải có", chứ không phải "chăn làm bất cứ điều gì"

  0

Vì vậy, một cái gì đó như: 'NẾU KHÔNG EXISTS (CHỌN 1 TỪ sys.database_principals WHERE [type] = 'R' AND [name] = 'ReadOnlyUsers')' 'TẠO VAI TRÒ [ReadOnlyUsers]' '' GRANT? (Chọn? Hoặc?) TRÊN SCHema :: dbo TO [ReadOnlyUsers] '' GO' 'EXEC sp_addrolemember 'ReadOnlyUsers', 'ReadOnlyUser'' Cách phân biệt EXEC, CẬP NHẬT, XÓA, v.v. theo dbo mặc dù?Ví dụ: ReadOnlyUser vẫn cần EXEC lưu trữ procs. 20 sep. 112011-09-20 15:27:51

  0

@David George: quyền truy cập lược đồ áp dụng tự động bất kể đối tượng: vì vậy, một Proc được EXEC và các bảng được S/I/U/D.GRANT của bạn sẽ là GRANT EXEX, CHỌN, CẬP NHẬT, vv TRÊN ... 20 sep. 112011-09-20 16:33:55

  0

vì vậy tôi sẽ chạy 'GRANT EXEC, SELECT, UPDATE, INSERT, DELETE trên SCHema :: dbo TO [ReadWriteUsers]' Liệu điều đó có cho phép người dùng đó có khả năng DROP một thủ tục không? 20 sep. 112011-09-20 18:54:03

  0

Không, họ cần ALTER TABLE hoặc như vậy cho điều đó 20 sep. 112011-09-20 22:29:39