권한과 보안

-- 서버에 로그인을 만듭니다.
CREATE LOGIN lab_app WITH PASSWORD = '…';
GO

-- 이 데이터베이스에 그 로그인의 사용자를 만듭니다.
CREATE USER app_user FOR LOGIN lab_app;
GO

SELECT name AS 이름, type_desc AS 종류, authentication_type_desc AS 인증
FROM sys.database_principals WHERE name = 'app_user';

EXECUTE AS USER = 'app_user';      -- 여기서부터 그 사용자입니다
SELECT COUNT(*) FROM Board.POSTS;
REVERT;                             -- 원래 신분으로 돌아옵니다

GRANT SELECT ON Board.POSTS TO app_user;
GO

EXECUTE AS USER = 'app_user';
SELECT COUNT(*) FROM Board.POSTS;                -- 이제 됩니다
INSERT INTO Board.POSTS (…) VALUES (…);        -- 이것은요
REVERT;

-- 표 권한은 거둡니다.
REVOKE SELECT ON Board.POSTS FROM app_user;
GO

CREATE OR ALTER PROCEDURE Board.P_POST_COUNT
    @board_num int
AS
BEGIN
    SET NOCOUNT ON;
    SELECT COUNT(*) AS 글수 FROM Board.POSTS WHERE board_num = @board_num;
END
GO

GRANT EXECUTE ON Board.P_POST_COUNT TO app_user;
GO

EXECUTE AS USER = 'app_user';
SELECT COUNT(*) FROM Board.POSTS;               -- 직접 조회
EXEC Board.P_POST_COUNT @board_num = 1;      -- 프로시저로
REVERT;

CREATE OR ALTER PROCEDURE Board.P_POST_COUNT_DYN
    @board_num int
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @sql nvarchar(200) =
        N'SELECT COUNT(*) AS 글수 FROM Board.POSTS WHERE board_num = @b';
    EXEC sp_executesql @sql, N'@b int', @b = @board_num;
END
GO
GRANT EXECUTE ON Board.P_POST_COUNT_DYN TO app_user;

CREATE OR ALTER PROCEDURE Board.P_POST_COUNT_DYN2
    @board_num int
WITH EXECUTE AS OWNER            -- 소유자의 권한으로 실행합니다
AS
BEGIN
    …
END

CREATE ROLE app_reader;
GRANT SELECT ON SCHEMA::Board TO app_reader;   -- 스키마 통째로
ALTER ROLE app_reader ADD MEMBER app_user;
GO

EXECUTE AS USER = 'app_user';
SELECT COUNT(*) FROM Board.POSTS;
REVERT;

-- 권한을 준 뒤에 표를 새로 만듭니다.
CREATE TABLE Board.T_NEW (num int IDENTITY PRIMARY KEY, memo nvarchar(50));
INSERT INTO Board.T_NEW (memo) VALUES (N'나중에 만든 표');
GO

EXECUTE AS USER = 'app_user';
SELECT COUNT(*) FROM Board.T_NEW;
REVERT;

-- 역할로는 스키마 전체를 읽을 수 있는 상태입니다.
DENY SELECT ON Board.POSTS TO app_user;
GO

EXECUTE AS USER = 'app_user';
SELECT COUNT(*) FROM Board.POSTS;      -- 막은 표
SELECT COUNT(*) FROM Board.COMMENTS;   -- 같은 스키마의 다른 표
REVERT;

-- 준 것을 목록으로 봅니다.
SELECT USER_NAME(p.grantee_principal_id) AS 받는이,
       p.state_desc AS 상태, p.permission_name AS 권한,
       CASE p.class
            WHEN 0 THEN N'데이터베이스'
            WHEN 1 THEN OBJECT_SCHEMA_NAME(p.major_id) + N'.' + OBJECT_NAME(p.major_id)
            WHEN 3 THEN N'스키마 ' + SCHEMA_NAME(p.major_id)
       END AS 대상
FROM sys.database_permissions p
WHERE p.grantee_principal_id > 4
ORDER BY 받는이, 대상;

EXECUTE AS USER = 'app_user';

SELECT 'POSTS' AS 표, permission_name AS 권한
FROM fn_my_permissions('Board.POSTS', 'OBJECT')
WHERE subentity_name = ''                -- 열 단위 권한은 뺍니다
  AND permission_name IN ('SELECT','INSERT','UPDATE','DELETE')
UNION ALL
SELECT 'COMMENTS', permission_name
FROM fn_my_permissions('Board.COMMENTS', 'OBJECT')
WHERE subentity_name = ''
  AND permission_name IN ('SELECT','INSERT','UPDATE','DELETE');

REVERT;