찐빵의 IT World

① SELECT, INSERT, UPDATE, DELETE 한꺼번에 주는 법(1개의 데이터베이스에 해당 스키마)

데이터베이스 - 해당DB - 보안 - 사용자 - 원하는 사용자 더블클릭 - 일반탭에서 소유한 스키마에 원하는 스키마 클릭

이렇게 하면 해당 사용자가 해당 스키마로 만들어진 테이블들에 대한 권한을 가진다.

스키마 소유권 주기(스키마 소유권은 1스키마당 1사용자)

USE 해당DB
GO
ALTER AUTHORIZAION ON SCHEMA::[해당스키마] TO [사용자]
GO

② 한 스키마에 대해서 원하는 권한 선택하여 넣어주기

데이터베이스 - 해당DB - 보안 - 스키마 - 원하는 스키마 더블클릭 - 사용권한탭에서 사용자 검색하여 추가 - 아래에 원하는 권한 선택하여 주기

USE AdventureWorks2008
GO

GRANT SELECT ON SCHEMA::HumanResources TO UserA
GO

③ 추후 업데이트

1. 쿼리로 현재 구성된 모든 테이블에 select 를 주는 방법

2. db_datareader 권한을 부여하는 방법

두가지 권한의 차이는... 

1번으로 수행한 경우에는.. 수행 후에 디비에 테이블이 추가가 되면.. 추가된 테이블에 대해서는 권한을 주지 않았기 때문에 select가 불가능해집니다.

1번 방법: 현재 있는 모든 테이블 SELECT 권한 주는 방법

exec sp_msforeachtable 'GRANT select on ? to test'

2번 방법:

exec sp_addrolemember 'db_datareader','test'

2번 방법은 추가되는 테이블에도 계속 적용이 되기 때문에 보다 효율적

추가 : SELECT 된 모든 테이블의 권한을 뺐기

exec sp_msforeachtable 'REVOKE select on ? to test'

sp_msforeachtable SP에 관련된 라이고님의 좋은 글: http://laigo.kr/307

1. 로그인 계정 

보안 - 로그인 을 누르면 나오는 것들이 바로 로그인 계정이다. SQL Server에 로그온 할 때 사용하는 계정을 말한다.

down 로그인 계정을 더블클릭 - 사용자 매핑 

down 사용자 계정을 down 데이터 베이스에 매핑된 사용자로 체크를 해 주고,

down 사용자 계정으로 로그인 후, down 데이터베이스에 있는 테이블을 SELECT 해 보았지만 SELECT 권한이 없다고 나왔다.

 그래서 위 그림과 같이 아래에 멤버 자격에 db_datareader의 자격을 주었더니 정상적으로 SELECT가 가능했다.

2. USER 계정

로그인 계정과 매핑이 되는 사용자 계정이 데이터베이스에 만들어져 있어야 그 데이터베이스에 접근을 할 수 있다.

위의 경우 사용자 매핑을 한 것이라 할 수 있다.

이렇게 사용자 매핑을 하게 되면 아래 그림과 같이 해당 데이터베이스에 USER가 나타나게 된다. 

Down 데이터베이스 - 보안 - 사용자에 down이라는 USER가 생겼다.

이 down이라는 USER를 더블클릭하여 일반탭을 보면 위에 사용자 매핑 과정에서 했던 대로 저장이 되어 있다.

여기서 db_accessadmin의 역할 멤버 자격을 줘봤더니 로그인 계정이 나타나는 맨아래 있는 보안 - 사용자 - down을 더블클릭하여 속성을 보아도 똑같이 db_accessadmin의 자격이 주어져 있었다.

결론적으로 로그인 과 사용자 두가지는 동기화가 되어 지는 것이다.

 궁금한 것이 몇가지 생겨서 해본 실험

① 실험 1 - 사용자 매핑으로 하지 않고, down 데이터베이스에서 사용자를 직접 만들며 기본스키마를 db_datareader , 권한은 db_ddladmin 으로 주어 테이블을 생성하였다.

결과 : db_datareader.table1 이라는 테이블이 생성되었다. / 하지만 datareader가 기본 스키마라고 해서 select 권한이 있는 건 아니었다. 

② 사용자가 소유한 스키마 부여

사용자가 소유한 스키마에 db_reader를 줘봤다(원래는 공백이었다)

결과 : 그래도 SELECT는 불가능

③ 사용자 - down - 더블클릭 - 보안개체 - 테이블 T에 대한 선택 권한 부여 

결과 : 이 경우에는 SELECT 가능

데이터베이스의 스키마를(을) 소유하며 삭제할수 없습니다.라는 오류가 나올 때

②에서 소유한 스키마를 삭제 해주어야 한다.

방법

해당 데이터베이스 - 보안 - 스키마 에서 소유한 스키마를 더블클릭 - 스키마 소유자를 스키마 이름(기본값)으로 하던지 해서 바꿔준다.