Sql Sql Server T Sql Permissions

SQL Server의 모든 사용자 테이블에 권한을 부여하는 궁극적인 가이드

SQL Server에서 권한 관리는 데이터베이스의 무결성과 보안을 유지하는 데 매우 중요합니다. 일반적으로 발생하는 시나리오는 모든 사용자 테이블에 특정 데이터베이스 역할에 권한을 원활하게 부여해야 할 때입니다. 새로운 테이블을 추가할 때마다 여러 스크립트를 작성하거나 수동으로 권한을 조정하는 것에 좌절감을 느낀다면, 당신만이 아닙니다. 이 가이드에서는 SELECT, REFERENCES, INSERT, UPDATE, DELETE 권한을 데이터베이스 역할에 쉽게 부여할 수 있는 다목적 SQL 스크립트를 만드는 방법을 탐구할 것입니다.

도전 과제: 효율적으로 권한 부여하기

SQL Server Management Studio (SSMS)는 일반적으로 개별 데이터베이스 객체에 대한 스크립트를 생성하는데, 이는 여러 테이블을 처리할 때 지루할 수 있습니다. 또한 새로 추가된 테이블의 권한을 수정하는 것은 빠르게 시간이 소요되는 작업이 될 수 있습니다. 필요한 것은 스크립트를 매번 돌아보지 않고도 모든 사용자 테이블에 권한을 적용할 수 있도록 실행할 수 있는 강력한 “한 번에 끝내는” 스크립트입니다.

해결책: 권한 부여를 위한 저장 프로시저

권한 부여를 효과적으로 자동화하기 위해, 우리는 모든 사용자 객체를 통해 반복하는 커서를 사용하는 저장 프로시저를 생성할 수 있습니다. 프로세스를 관리 가능한 단계로 나누어 보겠습니다:

단계 1: 저장 프로시저 생성

먼저, 저장 프로시저를 정의합니다. 아래의 SQL 코드를 템플릿으로 사용하세요:

IF EXISTS (
    SELECT 1 FROM sysobjects
    WHERE name = 'sp_grantastic'
    AND type = 'P'
)
DROP PROCEDURE sp_grantastic
GO

CREATE PROCEDURE sp_grantastic
AS
DECLARE
 @object_name VARCHAR(30),
 @time VARCHAR(8),
 @rights VARCHAR(20),
 @role VARCHAR(20)

DECLARE c_objects CURSOR FOR
    SELECT name
    FROM sysobjects
    WHERE type IN ('P', 'U', 'V')
    FOR READ ONLY

BEGIN
    SELECT @rights = 'ALL',
           @role = 'PUBLIC'

    OPEN c_objects
    WHILE (1=1)
    BEGIN
        FETCH c_objects INTO @object_name
        IF @@SQLSTATUS <> 0 BREAK

        SELECT @time = CONVERT(VARCHAR, GetDate(), 108)
        PRINT '[%1!] hitting up object %2!', @time, @object_name
        EXECUTE('GRANT '+ @rights +' ON '+ @object_name+' TO '+@role)
    END

    PRINT '[%1!] fin!', @time

    CLOSE c_objects
    DEALLOCATE CURSOR c_objects
END
GO

GRANT ALL ON sp_grantastic TO PUBLIC
GO

단계 2: 구성 요소 이해하기

커서 선언: 커서 c_objects는 모든 사용자 테이블(U), 뷰(V), 저장 프로시저(P)를 순회하도록 설정됩니다.
권한 실행: 커서를 통해 가져온 각 객체에 대해 시스템은 GRANT 명령을 실행하여 정의된 권한을 지정된 역할에 적용합니다.
출력 정보: 프로시저는 실행 시간과 권한이 부여된 객체에 대한 로그를 출력합니다.

단계 3: 프로시저 실행하기

저장 프로시저를 생성한 후, 간단한 명령으로 실행할 수 있습니다:

EXEC sp_grantastic

이 명령만으로도 데이터베이스 역할은 모든 사용자 테이블에 필요한 권한을 원활하게 부여받게 됩니다.

결론

모든 사용자 테이블에 데이터베이스 역할에 권한을 부여하는 강력한 SQL 스크립트를 만드는 방법을 방금 배웠습니다. 이 접근 방식은 작업 흐름을 간소화할 뿐만 아니라 수동 개입 없이 항상 권한이 최신 상태로 유지되도록 보장합니다. 다음에 데이터베이스에 새 테이블을 추가할 때는 저장 프로시저를 다시 실행하기만 하면 됩니다.

행복한 코딩 되세요!