데이터베이스 및 개체 사용 권한 설명

10분

모든 관계형 데이터베이스 관리 플랫폼에는 DML(데이터 조작 언어) 작업을 제어하는 네 가지 기본 권한이 있습니다. 이러한 권한은 SELECT, INSERT, UPDATE, DELETE이며 모든 SQL Server 플랫폼에 적용됩니다. 네 사용 권한 모두 테이블과 뷰에 대해 부여하거나 철회 또는 거부할 수 있습니다. GRANT 문을 사용하여 권한을 부여하면 GRANT 문에서 참조된 사용자 또는 역할에 해당 권한이 부여됩니다. DENY 명령을 사용하여 사용자에게 권한을 거부할 수도 있습니다. 사용자에게 권한을 부여하고 동일한 권한을 거부하면 DENY가 항상 GRANT를 대체하며 특정 개체에 대한 사용자 액세스가 거부됩니다.

A T-SQL Example of a DENY overriding a GRANT

위의 예제에서는 사용자 Demo에게 dbo.Company 테이블에 대한 SELECT 권한이 부여된 후 SELECT 권한이 거부되었습니다. 사용자가 dbo.Company 테이블에서 선택하는 쿼리를 실행하려고 하면 SELECT 권한이 거부되었다는 오류가 표시됩니다.

테이블 및 뷰 사용 권한

테이블과 뷰는 데이터베이스 내에서 사용 권한을 부여할 수 있는 개체를 나타냅니다. 테이블과 뷰 내에서 지정된 보안 주체(사용자 또는 로그인)가 액세스할 수 있는 열을 추가로 제한할 수 있습니다. SQL Server와 Azure SQL Database에는 액세스를 추가로 제한하는 데 사용할 수 있는 행 수준 보안도 포함되어 있습니다.

사용 권한	정의
`SELECT`	사용자가 개체(테이블 또는 뷰) 내 데이터를 보도록 허용합니다. 사용 권한이 거부되면 사용자가 개체 내의 데이터를 볼 수 없게 됩니다.
`INSERT`	사용자가 데이터를 개체에 삽입하도록 허용합니다. 사용 권한이 거부되면 사용자가 개체에 데이터를 삽입할 수 없게 됩니다.
`UPDATE`	사용자가 개체 내 데이터를 업데이트하도록 허용합니다. 사용 권한이 거부되면 사용자가 개체 내의 데이터를 업데이트할 수 없게 됩니다.
`DELETE`	사용자가 개체 내 데이터를 삭제하도록 허용합니다. 사용 권한이 거부되면 사용자가 개체 내의 데이터를 삭제할 수 없게 됩니다.

Azure SQL Database와 Microsoft SQL Server에는 필요에 따라 부여하거나 철회 또는 거부할 수 있는 다른 권한이 있습니다.

사용 권한	정의
`CONTROL`	개체에 대한 모든 권한을 부여합니다. 이 권한이 있는 사용자는 개체 삭제를 포함하여 개체에 대해 원하는 모든 작업을 수행할 수 있습니다.
`REFERENCES`	사용자에게 개체의 외래 키를 볼 수 권한을 부여합니다.
`TAKE OWNERSHIP`	사용자가 개체의 소유권을 가져올 수 있도록 허용합니다.
`VIEW CHANGE TRACKING`	사용자가 개체에 대한 변경 내용 추적 설정을 보도록 허용합니다.
`VIEW DEFINITION`	사용자가 개체 정의를 보도록 허용합니다.

함수 및 저장 프로시저 사용 권한

테이블 및 뷰와 마찬가지로 함수와 저장 프로시저에는 부여하거나 거부할 수 있는 여러 가지 권한이 있습니다.

사용 권한	정의
`ALTER`	사용자에게 개체의 정의를 변경할 수 있는 권한을 부여합니다.
`CONTROL`	사용자에게 개체에 대한 모든 권한을 부여합니다.
`EXECUTE`	사용자에게 개체를 실행할 수 있는 권한을 부여합니다.
`VIEW CHANGE TRACKING`	사용자가 개체에 대한 변경 내용 추적 설정을 보도록 허용합니다.
`VIEW DEFINITION`	사용자가 개체 정의를 보도록 허용합니다.

EXECUTE AS

EXECUTE AS [user name] 또는 EXECUTE AS [login name](SQL Server 및 Azure SQL Managed Instance에서만 사용 가능) 명령을 사용하면 사용자 컨텍스트를 변경할 수 있습니다. 후속 명령 및 문은 해당 컨텍스트에 사용 권한이 부여된 새 컨텍스트를 사용하여 실행됩니다.

사용자에게 사용 권한이 있고 더 이상 사용 권한이 필요하지 않은 경우에는 REVOKE 명령을 사용하여 사용 권한(GRANT 또는 DENY)을 제거할 수 있습니다. revoke 명령은 특정 사용자에게 지정된 권한에 대한 GRANT 또는 DENY 권한을 제거합니다.

소유권 체인

체인이라는 개념은 사용 권한에 적용되며, 사용자는 체인을 통해 다른 개체로부터 사용 권한을 상속받을 수 있습니다. 체인의 가장 일반적인 예는 실행 중에 테이블에 액세스하는 함수 또는 저장 프로시저입니다. 프로시저와 테이블의 소유자가 같은 경우 사용자에게 테이블에 대한 액세스 권한이 직접 없더라도 저장 프로시저가 실행되고 테이블에 액세스할 수 있습니다. 이 액세스는 사용자가 저장 프로시저로부터 테이블에 대한 액세스 권한을 상속받기 때문에 제공되는 것이지만 저장 프로시저를 실행하는 동안 저장 프로시저 실행 컨텍스트 내에서만 사용할 수 있습니다.

아래 예제에서 데이터베이스 소유자 또는 서버 관리자 권한으로 실행하면 새 사용자가 생성되고 새로운 SalesReader 역할의 멤버로 추가된 다음, 모든 개체에서 선택하고 Sales 스키마의 프로시저를 실행할 수 있는 권한이 부여됩니다. 그런 다음 Sales 스키마에서 Production 스키마의 테이블에 액세스하는 저장 프로시저가 생성됩니다.

예제에서는 콘텐츠를 새 사용자로 변경하고 Production 스키마의 테이블에서 직접 선택하려고 시도합니다.

USE AdventureWorks2016;
GO

CREATE USER [DP300User1] WITH PASSWORD = 'Pa55.w.rd';
GO

CREATE ROLE [SalesReader];
GO

ALTER ROLE [SalesReader] ADD MEMBER [DP300User1];
GO

GRANT SELECT, EXECUTE ON SCHEMA::Sales TO [SalesReader];
GO

CREATE OR ALTER PROCEDURE Sales.DemoProc
AS
SELECT P.Name, 
    SUM(SOD.LineTotal) AS TotalSales,
    SOH.OrderDate 
FROM Production.Product P
    INNER JOIN Sales.SalesOrderDetail SOD ON (SOD.ProductID = P.ProductID)
    INNER JOIN Sales.SalesOrderHeader SOH ON (SOH.SalesOrderID = SOD.SalesOrderID)
GROUP BY P.Name, 
    SOH.OrderDate
ORDER BY TotalSales DESC;

GO

EXECUTE AS USER = 'DP300User1';

SELECT P.Name, 
    SUM(SOD.LineTotal) AS TotalSales,
    SOH.OrderDate 
FROM Production.Product P
    INNER JOIN Sales.SalesOrderDetail SOD ON (SOD.ProductID = P.ProductID)
    INNER JOIN Sales.SalesOrderHeader SOH ON (SOH.SalesOrderID = SOD.SalesOrderID)
GROUP BY P.Name, 
    SOH.OrderDate
ORDER BY TotalSales DESC;

위의 쿼리를 실행하면 사용자가 속한 역할에 Production 스키마에 대한 권한이 없으므로 사용자 DP300User1에게 SELECT 권한이 없다는 오류가 표시됩니다. 이제 다음 저장 프로시저를 실행해 보겠습니다.

EXECUTE AS USER = 'DP300User1';

EXECUTE Sales.DemoProc;

사용자의 역할에 Sales 스키마에 대한 EXECUTE 권한이 있으므로 DP300User1 사용자에게 Sales 스키마의 저장 프로시저에 대한 EXECUTE 권한이 있습니다. 테이블과 프로시저의 소유자가 같으므로 소유권 체인이 중단되지 않아 실행에 성공하고 결과가 반환됩니다.

저장 프로시저 내에서 동적 SQL을 사용하는 경우에는 권한 변경이 적용되지 않습니다. 동적 SQL이 권한 체인을 중단하는 이유는 동적 SQL이 호출하는 저장 프로시저의 컨텍스트 외부에서 실행되기 때문입니다. 동적 SQL을 사용하여 실행되도록 위의 저장 프로시저를 다음과 같이 변경하면 이 동작을 확인할 수 있습니다.

CREATE OR ALTER PROCEDURE Sales.DemoProc
AS
DECLARE @sqlstring NVARCHAR(MAX)

SET @sqlstring = '
SELECT P.Name, 
    SUM(SOD.LineTotal) AS TotalSales, 
    SOH.OrderDate 
FROM Production.Product P
    INNER JOIN Sales.SalesOrderDetail SOD ON (SOD.ProductID = P.ProductID)
    INNER JOIN Sales.SalesOrderHeader SOH ON (SOH.SalesOrderID = SOD.SalesOrderID)
GROUP BY P.Name, SOH.OrderDate'

EXECUTE sp_executesql @sqlstring
GO

--

EXECUTE AS USER = 'DP300User1'

EXECUTE Sales.DemoProc

DP300User1 사용자에게는 사용자가 쿼리를 직접 실행하려고 시도한 것처럼 사용자에게 Production.Product 테이블에 대한 SELECT 권한이 없다는 오류가 표시됩니다. 권한 체인이 적용되지 않으며, 동적 SQL을 실행하는 사용자 계정에 동적 SQL 내의 코드에서 사용되는 테이블과 뷰에 대한 권한이 있어야 합니다.

최소 권한 원칙

최소 권한의 원칙은 매우 단순합니다. 개념의 기본 아이디어는 사용자와 애플리케이션에 작업을 완료하는 데 필요한 사용 권한만 부여해야 한다는 것입니다. 애플리케이션에는 해당 작업을 완료하는 데 필요한 사용 권한만 있어야 합니다.

예를 들어 애플리케이션이 저장 프로시저를 통해 모든 데이터에 액세스하는 경우 애플리케이션에는 테이블에 대한 액세스 권한 없이 저장 프로시저를 실행할 수 있는 권한만 있어야 합니다.

동적 SQL

동적 SQL은 쿼리가 프로그래밍 방식으로 빌드된다는 개념입니다. 동적 SQL을 사용하면 저장 프로시저나 쿼리 자체 내에서 T-SQL 문을 생성할 수 있습니다. 아래에 간단한 예제가 나와 있습니다.

SELECT 'BACKUP DATABASE ' + name + ' TO DISK =''\\backup\sql1\' + name + '.bak'''
FROM sys.databases

위의 문은 서버에 있는 모든 데이터베이스를 백업하는 T-SQL 문 목록을 생성합니다. 일반적으로 생성된 T-SQL은 sp_executesql을 사용하여 실행되거나 실행을 위해 다른 프로그램에 전달됩니다.

계속