로그인 인증은 사용자가 SQL 서버에 접속하는 과정일 뿐이지 데이터베이스를 사용할 수 있는 것과는 다릅니다. 실제 데이터베이스를 사용하기 위해서는 인증된 로그인 계정이 해당 데이터베이스 사용자로 등록이 되어야 합니다. 우리가 비자를 가지고 미국에 입국 했다고 해서 미국방성 또는 백악관을 마음대로 들어 갈 수 없는것과 같은 이유 입니다. 또다른 허가가 필요한 것입니다. 이번강좌에서는 데이터베이스 사용자 관리에 대해 살펴보도록 하겠습니다.
데이터 베이스에 사용자를 추가하는 방법은 여러가지가 있습니다. 이중에서 다음의 방법을 알아보도록 하겠습니다.
o 로그인 계정 등록시 데이터베이스 사용자 계정 추가
o 새로운 데이터베이스 사용자 계정 추가
o T-SQL 문을 이용한 데이터베이스 사용자 계정 추가
1. 데이터베이스 사용자를 등록하지 않으면...
데이터베이스 사용자를 등록하지 않으면 어떻게 되는지 확인을 해 봄으로써 데이터베이스 사용자 등록의 필요성에 대해 알아보도록 하겠습니다. 우선 다음과 같이 user01 이라는 로그인 계정을 만들어 보도록 하겠습니다.
[그림 1]
[그림 1]과 같이 user01 이라는 로그인 계정을 만들고 QA를 이용해서 이 로그인 계정으로 SQL 서버에 로그인을 한 후 Pubs 데이터베이스에 접속을 해보고자 합니다. 그러나 [그림 2]와 같이 데이터베이스 목록에 Pubs 데이터베이스가 보이지 않습니다.
[그림 2]
[그림 2]에서 처럼 데이터베이스 목록에 Pubs 데이터베이스가 보이지 않는 이유는 user01 로그인 계정이 Pubs 데이터베이스의 사용자 계정으로 등록되지 않았기 때문입니다. 그런데 왜 master, Northwind, tempdb 데이터베이스는 목록에 나타날까요? 우린 분명 이들 데이터베이스에도 user01을 사용자 계정으로 등록한적이 없는데...
그 이유는 guest계정 때문입니다. 위 세개의 데이터베이스 master, Northwind, tempdb에는 guest 계정이 등록되어 있습니다. guest 계정은 임의의 계정 없이 해당 데이터베이스에 접근을 허용하는 역할을 하기 때문입니다. 다음 [그림 3]은 Northwind 데이터베이스에 guest 계정이 있음을 보여주고 있습니다.
[그림 3]
위 [그림 3]에 보이는 guest 계정을 제거해 버리는 [그림 2]에서의 데이터베이스 목록에는 Northwind 데이터베이스는 보이지 않게 됩니다. 제가 이 강좌를 준비하면서 Pubs 데이터베이스의 guest 계정을 제거 했기 때문에 [그림 2]에서 Pubs 데이터베이스가 보이지 않는 것입니다. guest 계정은 보안에 취약한 부분이므로 제거해 버리는 것이 좋습니다.
자, 그럼 user01 로그인 계정을 이용해 Pubs 데이터베이스에 접근이 가능하려면 어떻게 해야 할까요? 바로 Pubs 데이터베이스에 user01을 사용자 계정으로 등록해야 합니다. 이제 이 방법을 알아보도록 하겠습니다.
2. 로그인 계정 등록시 데이터베이스 사용자 계정 추가
우선 로그인 계정을 등록하는 시점에서 데이터베이스에 사용자 계정을 추가하는 방법을 알아보도록 하겠습니다. 다시 말하면 user01 로그인 계정을 등록 할 때 이 계정이 Pubs 데이터베이스 사용자 계정이 되도록 동시에 설정 하는 것입니다. 이것은 다음 [그림 4]에서 보이는 [데이터베이스 액세스] 탭을 이용하면 됩니다.
[그림 4]
[그림 4]에서 [데이터베이스 액세스] 탭을 누르면 다음 [그림 5]와 같이 액세스할 수 있는 데이터베이스를 지정할 수 있게 됩니다. 표시된 데이터베이스에서 user01 계정이 접근하도록 하고자 하는 데이터베이스를 [허용] 부분을 체크 하고 아래 부분의 역할을 지정하시면 됩니다.
[그림 5]
다음 [그림 6]은 Pubs 데이터베이스에 액세스 할 수 있도록 설정한 것입니다.
[그림 6]
[그림 6]에서 [확인] 버튼을 누르면 user01 계정이 Pubs 데이터베이스의 사용자 계정으로 등록이 완료됩니다. 그런데 [그림 6]에서 아랫 부분의 데이터베이스 역할부분이 궁금해 지네요. 다음은 온라인 설명서에 기록된 내용입니다.
| public | 모든 데이터베이스 사용자가 속하는 특수 데이터베이스 역할입니다. |
| db_owner | 데이터베이스 내에서의 유지 관리 및 구성 작업 등 모든 데이터베이스 역할의 작업을 수행합니다. 이 역할의 권한은 모든 다른 고정 서버 역할에 걸쳐 배치됩니다. |
| db_accessadmin | Windows NT 4.0 또는 Windows 2000 그룹과 사용자 및 SQL Server 사용자를 데이터베이스에 추가하거나 제거합니다. |
| db_datareader | 데이터베이스의 모든 사용자 테이블에서 모든 데이터를 봅니다. |
| db_datawriter | 데이터베이스의 모든 사용자 테이블에서 데이터를 추가, 변경 또는 삭제합니다. |
| db_ddladmin | 데이터베이스에서 개체를 추가, 수정 또는 삭제하고 모든 DDL을 실행합니다. |
| db_securityadmin | SQL Server 2000 데이터베이스 역할과 그 구성원을 관리하고 데이터베이스에서 명령문과 개체 사용 권한을 관리합니다. |
| db_backupoperator | 데이터베이스 백업 권한을 갖습니다. |
| db_denydatareader | 데이터베이스에서 데이터를 선택하는 권한을 거부합니다. |
| db_denydatawriter | 데이터베이스에서 데이터를 변경하는 권한을 거부합니다. |
다음에 기회가 된다면 데이터베이스 역할에 대해 다루어 보도록 하겠습니다.
3. 새로운 데이터베이스 사용자 계정 추가
사용자 계정을 추가하는 두번째 방법은 로그인 계정 등록과는 별도로 사용자 계정을 추가하는 것입니다. 다음 [그림 7]과 같이 데이터베이스(이 강좌의 경우 Pubs 데이터베이스)의 "사용자" 부분에서 마우스 오른쪽 버튼을 눌러 표시되는 단축 메뉴에서 "새 데이터베이스 사용자(U)..."를 선택하시면 됩니다.
[그림 7]
그러면 다음 [그림 8]과 같이 로그인 계정 중에서 데이터베이스 사용자 계정으로 등록할 것을 선택하고 데이터베이스 역할을 지정해주시면 됩니다. user02는 제가 조금전에 여러분들 몰래 등록한 로그인 계정입니다.
[그림 8]
위 [그림 8]에서 사용자 이름은 다른 이름으로 변경해도 됩니다. 다시 말하면 로그인 계정은 user02라 하더라도 사용자 이름을 jangrae로 할 수 있다는 것입니다.
4. T-SQL을 이용한 데이터베이스 사용자 계정 추가
EM에서 이루어진 대부분의 작업은 T-SQL문을 이용해서도 가능함을 아시죠? 마지막으로 T-SQL 문을 이용한 사용자 계정 등록 방법을 살펴보도록 하겠습니다. 만일 조금전에 user02를 Pubs 데이터베이스의 사용자 계정으로 등록한 과정을 T-SQL 문으로 구현한다면 다음과 같습니다.
|
USE Master EXEC sp_adduser 'user02', 'user02', 'public' |
사용자 계정을 제거 할 때는 EM에서는 선택해서 제거해 버리면 됩니다. T-SQL문을 이용할 때는 다음과 같이 합니다.
|
USE Pubs EXEC sp_dropuser 'user02' |
만일 user02 로그인 계정을 jangrae 라는 이름의 사용자 계정으로 등록하고자 한다면 다음과 같이 하면 됩니다.
|
USE Master EXEC sp_adduser 'user02', 'jangrae', 'public' |
다시 제거 할 때는
|
USE Pubs EXEC sp_dropuser 'jangrae' |
와 같이 하면 됩니다.
5. 정리
이번 강좌에서는 SQL 서버에 로그인 하여 특정 데이터베이스를 액세스 할 수 있도록 사용자 계정을 추가하는 방법을 살펴 보았습니다. 사용자 계정을 추가하는 방법은 살펴본 바와 같이 간단합니다. 하지만 로그인 계정과 사용자 계정은 보안과 관련된 사항이므로 정확한 정책을 수립한 상태에서 작업을 하셔야 합니다.
[출처] 디비누리 SQLWorld
