SQL 사용자의 모든 개체에 대한 사용 권한 설정


1

데이터베이스의 모든 개체를 반복하고 해당 개체에 적절한 사용 권한을 할당하는 프로 시저가 있습니다.이 작업을 수행하는 더 좋은 방법이 있는지 알고 싶습니다.모델 데이터베이스를 사용하여 새 데이터베이스를 작성하므로 새 데이터베이스를 만들 때마다이 데이터베이스를 실행해야합니다.다음과 같은 아이디어가 있습니다 (참고 : 모든 사용자를 삭제하고 필요한 사용자를 다시 작성하는 처음부터 누락 된 청크가 있으며 이는 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

이 스크립트는 데이터베이스의 모든보기, 함수 등에 대해 유사한 작업을 계속 수행합니다.과속에 대한 아이디어가 있습니까 아니면 더 좋은 방법이 있나요?

6

스키마 레벨에서 권한을 롤에 부여하는 단일 행

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

역할에 사용자를 추가하는 두 번째 줄

EXEC sp_addrolemember 'SomeRole', 'whatever user'

그리고 모델에서 이것을 수행하여 모든 새 데이터베이스가 상속합니다.

이유는 한 번만 사용 권한을 설정해야합니다.

  • 스키마는 객체의 컨테이너입니다.
  • 새 개체는 스키마에서 사용 권한을 상속받습니다.
  • 역할은 사용자를위한 컨테이너입니다.
  • 새 사용자가 역할에 추가되고 상속됩니다.

데이터베이스를 마이그레이션 또는 복원하면 사용자에게 직접 할당 될 때 개체 사용 권한이 손실 될 수 있습니다.그렇다면 왜 그 입장에 처해야합니까?

CREATE LOGIN with a SIDCREATE LOGIN with a SID 하므로 모든 서버에서 동일하며 분리 된 사용자도 없습니다.

올바른 질문을했다면 코딩을 해줄 수 있었을 것입니다 ...

개인적으로나 마침내 나는 이것이 "해야 할 필요성"에 관한 나쁜 습관이라고 말하고 싶다.

  0

그래서 뭔가처럼 :'존재하지 않는 경우 (SELECT 1 FROM sys.database_principals WHERE [type] = 'R'AND [name] = 'ReadOnlyUsers')'CREATE ROLE [ReadOnlyUsers]''EXEC sp_addrolemember 'db_datareader', 'ReadOnlyUser' GRANT? (select? or?) SCHEMA :: dbo TO [ReadOnlyUsers]''GO''EXEC sp_addrolemember 'ReadOnlyUsers', 'ReadOnlyUser'' 모든 개체가있는 경우 EXEC, UPDATE, DELETE 등을 구별하는 방법 그래도 아래 dbo?예를 들어 ReadOnlyUser는 여전히 저장된 procs를 EXEC해야합니다. 20 sep. 112011-09-20 15:27:51

  0

@ David George : 스키마 권한은 객체에 관계없이 자동으로 적용됩니다. 따라서 proc은 EXEC를 가져오고 테이블은 S/I/U/D를 얻습니다.귀하의 보조금은 EXEX, SELECT, UPDATE 등등 ... 20 sep. 112011-09-20 16:33:55

  0

그래서'GRANT EXEC, SELECT, UPDATE, INSERT, DELETE를 SCHEMA :: dbo TO [ReadWriteUsers] '에서 실행하겠습니까? 그러면 그 사용자가 잠재적으로 프로 시저를 삭제할 수있는 권한을 부여 할 수 있습니까? 20 sep. 112011-09-20 18:54:03

  0

아니요, ALTER TABLE이 필요합니다. 20 sep. 112011-09-20 22:29:39