[HSKIM] 05. 권한 관리

05. 권한 관리

1. 인증 방법

- Windows 인증     : 로그인 계정에 대한 인증을 Windows 담당함

- SQL SERVER 인증 : SQL SERVER 자체 계정으로 인증 함.

2. 인증 모드

- Windows 인증 모드 : Windows 인증 방법만 허가함. 권고되는 인증 모드
- 혼합 인증 모드       : Windows, SQL SEVER 인증 방법을 모두 허가.

* 인증 모드는 설치 할때 설정하고 운영 중에도 변경은 가능하지만 서비스 재시작을 해야 반영이 된다.


  - 개체 탐색기에서 변경

 - 레지스터 에서 변경  ( 1 :  Windows 모드 , 2 : 혼합 모드)

3.역할

고정 서버 수준 역할

다음 표에서는 고정 서버 수준 역할과 해당 기능을 보여 줍니다.

준 역할	설명
sysadmin	sysadmin 고정 서버 역할의 멤버는 서버에서 모든 작업을 수행할 수 있습니다.
serveradmin	serveradmin 고정 서버 역할의 멤버는 서버 차원의 구성 옵션을 변경하고 서버를 종료할 수 있습니다.
securityadmin	securityadmin 고정 서버 역할의 멤버는 로그인 및 해당 속성을 관리합니다. 서버 수준 사용 권한을 GRANT, DENY 및 REVOKE할 수 있습니다.데이터베이스에 액세스할 수 있는 데이터베이스 수준 사용 권한도 GRANT, DENY 및 REVOKE할 수 있습니다. 또한 이 역할의 멤버는 SQL Server 의 로그인 암호를 다시 설정할 수 있습니다. 중요: 보안 관리자는 데이터베이스 엔진에 대한 액세스 권한을 부여하고 사용자 권한을 구성할 수 있으므로 대부분의 서버 사용 권한을 할당할 수 있습니다. securityadmin 역할은 sysadmin 역할과 동일하게 처리되어야 합니다.
processadmin	processadmin 고정 서버 역할의 멤버는 SQL Server의 인스턴스에서 실행 중인 프로세스를 종료할 수 있습니다.
setupadmin	setupadmin 고정 서버 역할의 멤버는 Transact-SQL 문을 사용하여 연결된 서버를 추가하거나 제거할 수 있습니다. ( Management Studio를 사용할 때 sysadmin 멤버 자격이 필요합니다.)
bulkadmin	bulkadmin 고정 서버 역할의 멤버는 BULK INSERT 문을 실행할 수 있습니다.
diskadmin	diskadmin 고정 서버 역할은 디스크 파일을 관리하는 데 사용됩니다.
dbcreator	dbcreator 고정 서버 역할의 멤버는 데이터베이스를 생성, 변경, 삭제, 복원할 수 있습니다.
public	모든 SQL Server 로그인은 public 서버 역할에 속합니다. 서버 보안 주체에게 보안 개체에 대한 특정 사용 권한이 부여되지 않았거나 거부된 경우 사용자는 해당 개체에 대해 public으로 부여된 사용 권한을 상속 받습니다. 모든 사용자가 개체를 사용할 수 있도록 하려는 경우에만 개체에 public 권한을 할당해야 합니다. public의 멤버 자격은 변경할 수 없습니다. 참고: public은 다른 역할과 다른 방식으로 구현되며 public 고정 서버 역할에서 사용 권한이 부여, 거부 또는 취소될 수 있습니다.

고정 데이터베이스 역할

역할 이름	Description
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 고정 데이터베이스 역할의 멤버는 데이터베이스 내에 있는 사용자 테이블의 데이터를 읽을 수 없습니다.

4. 로그인 계정

- Windows 계정 만들기
CREATE LOGIN 윈도우계정
  FROM WINDOWS
  WITH DEFAULT_DATABASE = master

- SQL SEVER 자체 계정 만들기
CRATE LOGIN 계정
 WITH PASSWORD = 'DKVAH!!' ,
         DEFAULT_DATABASE = master

5. 사용자 계정 만들기

CREATE USER <사용자계정>
   FOR LOGIN <로그인계정>

6. 스키마

데이터베이스 안의 스키마 안의 테이블.
데이터베이스 개체에 대한 네임스페이스이다. 즉, 데이터베이스 개체가 갖는 고유한 이름을 결정 짓는다.

 - 이점
1) 개체의 소유자와는 상관없이 스키마를 이용해 개체를 그룹화 하여 관리에 효율을 꾀할 수 있습니다.

2) 권한 관리의 경우 개체 개별 적으로 권한을 허용하지 않고 스키마에 권한을 허용하면 스키마에 포함된 모든 개체에 대해 자동으로 권한을 줄 수 있어 권한 관리가 효율적입니다.

3) 소유자 계정을 삭제하기 위해 소유자가 소유한 개체의 소유자를 변경하거나 개체를 삭제할 필요가 없습니다. SQL 서버 2000에서는 개체를 소유한 소유자의 계정을 삭제 할 수 없었습니다.

- dbo 스키마
 dbo 스키마는 모든 데이터베이스에 기본적으로 포함되어 있는 스키마다.
 개체 이름 앞에 스키마를 붙이지 않으면, 기본 스키마가 붙은 것으로 간주한다.

- 스키마 만들기
CREATE SCHEMA hyunsoo

- 스키마

- 스키마 속성

7. 활용

-로그인 계정 확인
SELECT name, type_desc, create_date, is_disabled
FROM sys.server_principals
WHERE type_desc IN ('SQL_LOGIN','WINDOWS_LOGIN')

-데이터베이스 사용자 확인
SELECT name, type_desc, create_date
FROM sys.database_principals
WHERE type_desc IN ('WINDOWS_USER', 'SQL_USER')

AND sid IS NOT NULL