SQL Server 권한 부여

 SQL Server 사용시, 보통 GUI를 통해서 권한을 부여한다. GUI를 통해서 하면 Grant 옵션 따위 몰라도 상관 없다. 너무 편하다. 그러나, 간혹 해외 출장시 SQL Server가 영문으로 되어 있으면... 아마도 GUI가 익숙한 DB인에게는 매우 당황스러울 것이다. 이 때는 오히려 Command 에서 처리하는 것보다 GUI로 처리하는 것이 더욱 더 답답할 수가 있다. 뿐만 아니라, 다수의 계정에게 동일한 계정을 부여할 때, GUI 방식은 한계가 뚜렷하다. 

따라서, DB를 다루는 사람이라면 GUI보다 Command 방식을 사용해야 한다. 

★ 사용자 계정에게 특정 권한 부여하기

계정에게 특정 권한을 부여하는 GRANT 문장이다. 권한 부여 대상인 데이터베이스에 오른쪽 마우스 클릭 후, 속성 -> 사용 권한을 선택하면 볼 수 있는 화면이다. 사용권한이 아래 처럼 펼쳐져 있다. 참 직관적이다. 하지만.. Command에서는 영문으로 부여해야 한다. 이 때, 영문 SQL Server이면 참 난감하다. 그래서 우리는 더욱더 Command에 익숙해져야 한다.

사용 권한이 무수히 많으므로 그 중에 하나만 예로 들어보자. 참고로 사용 권한에 내용은 아래 주소를 참조하자.

https://docs.microsoft.com/ko-kr/sql/t-sql/statements/grant-database-permissions-transact-sql?view=sql-server-2017

아래 권한 부여 방식은 dev01에게 선택한 데이터베이스에서 테이블을 생성할 수 있는 권한을 부여하는 옵션이다. 참고로 WITH GRANT OPTION 으로 부여받은 권한을 다른 계정에게도 부여할 수 있다.

use [FinanceDB]
GO
GRANT CREATE TABLE TO [dev01]
GO

★ 데이터베이스 역할 멤버 자격 부여하기

SQL Server에서 기본적으로 존재하는 Role 이 있다. 한글로는 데이터베이스 역할 멤버 자격이다. 참고로 영문 SQL Server에서의 GUI는 아래와 같다. 그냥 참고만 하자.

역할 멤버 자격에 대한 설명은 아래와 같다.

고정 데이터베이스 역할 이름	설명
db_owner	db_owner 고정 데이터베이스 역할의 멤버는 데이터베이스에서 모든 구성 및 유지 관리 작업을 수행할 수 있고 SQL Server에서 데이터베이스를 삭제할 수도 있습니다. SQL 데이터베이스 및 SQL 데이터 웨어하우스에서 일부 유지 관리 작업은 서버 수준 권한이 필요하여 db_owners으로 수행할 수 없습니다.
db_securityadmin	db_securityadmin 고정 데이터베이스 역할의 멤버는 역할 멤버 자격을 수정하고 사용 권한을 관리할 수 있습니다. 이 역할에 보안 주체를 추가하면 원하지 않는 권한 상승이 설정될 수 있습니다.
db_accessadmin	db_accessadmin 고정 데이터베이스 역할의 멤버는 Windows 로그인, Windows 그룹 및 SQL Server로그인의 데이터베이스에 대한 액세스를 추가하거나 제거할 수 있습니다.
db_backupoperator	db_backupoperator 고정 데이터베이스 역할의 멤버는 데이터베이스를 백업할 수 있습니다.
db_ddladmin	db_ddladmin 고정 데이터베이스 역할의 멤버는 데이터베이스에서 모든 DDL(데이터 정의 언어) 명령을 실행할 수 있습니다.
db_datawriter	db_datawriter 고정 데이터베이스 역할의 멤버는 모든 사용자 테이블에서 데이터를 추가, 삭제 또는 변경할 수 있습니다.
db_datareader	db_datareader 고정 데이터베이스 역할의 멤버는 모든 사용자 테이블의 모든 데이터를 읽을 수 있습니다.
db_denydatawriter	db_denydatawriter 고정 데이터베이스 역할의 멤버는 데이터베이스 내의 사용자 테이블에 있는 데이터를 추가, 수정 또는 삭제할 수 없습니다.
db_denydatareader	db_denydatareader 고정 데이터베이스 역할의 멤버는 데이터베이스 내에 있는 사용자 테이블의 데이터를 읽을 수 없습니다.

위 표에서의 역할을 User계정에게 부여하려면 어떻게 해야 할까? Grant 를 쓰면 될 것이라고 생각되지만.. grant가 아닌 alter를 사용해야 한다. dev01 계정에 db_accessadmin 역할을 부여해보면 아래와 같다.

USE [FinanceDB]
GO
ALTER ROLE [db_accessadmin] ADD MEMBER [dev01]
GO

해당 계정으로부터 역할을 제거하는 구문은 아래와 같다.

USE [FinanceDB]
GO
ALTER ROLE [db_accessadmin] DROP MEMBER [dev01]
GO

★ 사용자 정의 데이터베이스 역할 만들기

 앞에서 설명한 역할을 우리가 직접 만들 수 있다. 그렇게 많이 사용하지는 않는다. 그냥 이런것도 있다고만 정리하자.

아래는 my_role 역할을 만들고, dev01을 만든 역할의 멤버로 추가하고, 만든 역할에 권한을 부여해보면 아래와 같다.

USE [FinanceDB]
GO
CREATE ROLE [my_role] AUTHORIZATION [dev01]
GO
USE [FinanceDB]
GO
ALTER AUTHORIZATION ON SCHEMA::[db_accessadmin] TO [my_role]
GO
USE [FinanceDB]
GO
ALTER AUTHORIZATION ON SCHEMA::[dbo] TO [my_role]
GO

★ 테이블, 뷰에 대한 권한 부여하기

 앞서 예제는 database 단위로 권한/역할을 부여했지만, 당연히 테이블, 뷰 단위로도 권한을 부여할 수 있다.

dev01에게 FinanceDB의 dbo.finance_category 테이블에 Alter 권한과 Delete 권한을 부여해보자.

use [FinanceDB]
GO
GRANT ALTER ON [dbo].[finance_category] TO [dev01]
GO
use [FinanceDB]
GO
GRANT DELETE ON [dbo].[finance_category] TO [dev01]
GO

테이블, 뷰에 대한 권한을 부여하는 것은 실제로도 매우 많이 사용된다. 어떤 고객사의 시스템 담당에게 우리의 솔루션 데이터베이스의 권한을 알려줬는데, 실수로 where 조건을 빼고 업데이트 쳤다고 한다. 이런 케이스를 대비해서라도, 반드시 필요한 오브젝트와 권한만 열어줘야 할 필요성이 있다. 관리하고 있는 데이터베이스의 전체 권한을 아무에게나 알려주지 말자. 특히 덜 성숙한 개발자, 관리자에게는 알 필요도 없고 알아서도 안된다고 주의를 줘야 한다. 필요하다면, SELECT 권한만 열어주자.