/**********************************************************************************************
-- Title : [2k5] 스키마 생성 및 사용, 활용
-- Reference : hanbitbook.co.kr
-- Key word : schema 스키마 alter authorization
**********************************************************************************************/
drop database schemadb;
go
create database schemadb;
go
--초기화
drop schema schema_a;
drop schema schema_b;
drop table schema_a.table1, schema_a.table2, schema_a.table3;
drop user user1a;
drop user user2a;
drop user uesr3a;
drop login user1a;
drop login user2a;
drop login user3a;
go
--로그인 생성
create login user1a
with password = 'p@ssw0rd',
default_database = [schemadb],
check_policy = on,
check_expiration = off;
go
create login user2a
with password = 'p@ssw0rd',
default_database = [schemadb],
check_policy = on,
check_expiration = off;
go
create login user3a
with password = 'p@ssw0rd',
default_database = [schemadb],
check_policy = on,
check_expiration = off;
go
use schemadb;
go
--사용자 생성
create user user1a for login user1a;
create user user2a for login user2a;
create user user3a for login user3a;
go
--스키마 생성
create schema schema_a; --소유자는 default 스키마인 dbo
go
create schema schema_b; --소유자는 default 스키마인 dbo
go
select * from sys.schemas;
go
--user1a의 기본 스키마로 schema_a 설정
alter user [user1a] with default_schema=[schema_a];
go
--user1a가 소유한 스키마에 schema_a 추가
alter authorization on schema::[schema_a] to [user1a];
go
--다음 역할에 user1a 추가
exec sp_addrolemember N'db_ddladmiN', N'user1a';
exec sp_addrolemember N'db_datareader', N'user1a';
exec sp_addrolemember N'db_datawriter', N'user1a';
go
--user2a의 기본 스키마로 schema_b 설정
alter user [user2a] with default_schema=[schema_b];
go
--다음 역할에 user2a 추가
exec sp_addrolemember N'db_ddladmiN', N'user2a';
exec sp_addrolemember N'db_datareader', N'user2a';
exec sp_addrolemember N'db_datawriter', N'user2a';
go
--user3a의 기본 스키마로 schema_a 설정
alter user [user3a] with default_schema=[schema_b];
go
--다음 역할에 user3a 추가
exec sp_addrolemember N'db_ddladmiN', N'user3a';
exec sp_addrolemember N'db_datareader', N'user3a';
exec sp_addrolemember N'db_datawriter', N'user3a';
go
--user1a으로 로긴
execute as user = 'user1a';
go
select suser_sname();
go
create table table1(id int); --schema_a.table1 생성
go
revert;
go
--user2a으로 로긴
execute as user = 'user2a';
go
select suser_sname();
go
create table table2(id int); --schema_b.table2 생성
go
revert;
go
--user3a으로 로긴
execute as user = 'user3a';
go
select suser_sname();
go
insert into table2 values('3001'); --schema_b.table2에 저장 됨
go
create table table3 (id int); --schema_b.table3 생성
go
revert;
go
--user2a에서 user3a가 입력한 데이터 확인
execute as user = 'user2a';
go
select suser_sname();
go
select * from table2; --읽힌다
go
revert;
go
--user1a에서 table1에 입력, table2의 데이터 확인
execute as user = 'user1a';
go
select suser_sname();
go
insert into table1 values ('1001'); --저장된다
go
select * from schema_b.table2; --읽힌다. 소유자는 dbo인데.. 왜 읽힐까?
go
--dbo인증에서
revert;
go
select suser_sname();
go
select * from table1; --안된다.
select * from schema_a.table1; --읽힌다. 왜 읽힐까?
select * from schema_b.table2; --읽힌다. 왜 읽힐까?
go
--데이터베이스 보안 주체는 데이터베이스의 스키마을(를) 소유하며 삭제할 수 없습니다.
drop user user1a;
go
--schema_b.table3 스키마 소유를 schema_a로 변경
alter schema schema_a transfer schema_b.table3;
go
--schema_b 스키마 삭제(schema_b가 table2를 아직 소유하고 있어 삭제 안됨)
drop schema schema_b;
go
drop table schema_b.table2;
go
drop schema schema_b; --이제야 삭제됨.
go