[2k8] 로그인, 사용자 및 권한, 롤 관리 - ver.dbrang

/********************************************************************************************
-- Title : [2k8] 로그인, 사용자 및 권한, 롤 관리 - ver.dbrang
-- Key word : login user permission role
********************************************************************************************/
/*
-- 관련 사용자 뷰
*/
exec sp_login0;
exec sp_user0;
exec sp_server_permission0;
exec sp_database_permission0;
exec sp_database_role0;
exec sp_database_role_member0;


/*
-- 인증 모드
*/
-- 인증 모드 확인
USE [master]
GO

EXEC xp_instance_regread
       N'HKEY_LOCAL_MACHINE'
     , N'Software\Microsoft\MSSQLServer\MSSQLServer', N'LoginMode';

-- 인증 모드 변경
-- SSMS) 개체 탐색기-서버명-속성-보안-서버인증에서 수정

/*
-- 로그인 및 사용자 관리
*/
--윈도우 로그인 등록(실행 전 윈도우 계정 등록)
-- SSMS) 개체탐색기-보안-로그인-새로그인
CREATE LOGIN [최명환\ttt]
FROM WINDOWS
WITH DEFAULT_DATABASE = mydb;

--SQL 로그인 등록
CREATE LOGIN login_fff
WITH PASSWORD = 'fff',
  CHECK_POLICY = ON,
  CHECK_EXPIRATION = OFF;

-- 로그인 확인
-- SSMS : 개체탐색기-서버-보안-로그인
select name, TYPE, type_desc, default_database_name
FROM sys.server_principals
WHERE TYPE IN ('U', 'S');

EXEC sp_login0;

-- CONNECT SQL 자동 부여 확인
SELECT a.class_desc, a.permission_name, a.state_desc, b.name, b.type_desc
     , b.is_disabled, b.default_database_name
FROM master.sys.server_permissions a
LEFT OUTER JOIN master.sys.server_principals b
ON a.grantee_principal_id = b.principal_id;

-- 로그인에 서버 권한 부여
GRANT ADMINISTER BULK OPERATIONS TO login_fff;

-- 서버 권한 부여 확인
SELECT grantor = (SELECT name FROM sys.server_principals
                  WHERE principal_id = grantor_principal_id)
     , a.class_desc, a.permission_name, a.state_desc
     , b.name "grantee", b.type_desc, b.is_disabled, b.default_database_name
FROM master.sys.server_permissions a
LEFT OUTER JOIN master.sys.server_principals b
ON a.grantee_principal_id = b.principal_id;

-- 로그인 수정
ALTER LOGIN ttt WITH password = 'ttt';

-- 로그인 삭제
DROP LOGIN [최명환\ttt];
DROP LOGIN login_fff;

-- 로그인 등록
-- SSMS) 개체탐색기-서버-데이터베이스-보안-사용자-새사용자
create LOGIN login_ttt WITH password='ttt', default_database = tempdb;
CREATE LOGIN login_fff WITH password='fff', default_database = tempdb;

-- 사용자 등록
USE mydb;

CREATE USER usr_ttt FOR LOGIN login_ttt WITH DEFAULT_SCHEMA=dbo;
CREATE USER usr_fff FOR LOGIN login_fff WITH DEFAULT_SCHEMA=dbo;

-- 사용자 확인
SELECT name, TYPE, type_desc, default_schema_name
FROM mydb.sys.database_principals
WHERE TYPE IN ('U', 'S');

EXEC mydb.dbo.sp_user0;

-- 사용자 수정
ALTER USER ttt FOR LOGIN ttt WITH default_schema=defaultschema;

-- 사용자 삭제
DROP USER usr_ttt;
DROP USER usr_fff;
DROP LOGIN login_ttt;
DROP LOGIN login_fff;

/*
-- 사용자, 스키마 및 권한 관리
*/
-- 로그인 등록
USE master;

CREATE LOGIN login_ttt
WITH PASSWORD = 'ttt'
   , DEFAULT_DATABASE = [mydb]
   , CHECK_POLICY = ON
   , CHECK_EXPIRATION = OFF;

CREATE LOGIN login_fff
WITH PASSWORD = 'fff'
   , DEFAULT_DATABASE = [mydb]
   , CHECK_POLICY = ON
   , CHECK_EXPIRATION = OFF;

-- 사용자 등록
USE mydb;

CREATE USER usr_ttt FOR LOGIN login_ttt;
CREATE USER usr_fff FOR LOGIN login_fff;

-- 스키마 생성(소유자는 DEFAULT 스키마인 dbo)
CREATE SCHEMA sch_ttt;
CREATE SCHEMA sch_fff;

-- 스키마 확인
SELECT * FROM sys.schemas;

-- 사용자의 기본 스키마로 SCH_사용자로 설정
ALTER USER usr_ttt WITH DEFAULT_SCHEMA=sch_ttt;
ALTER USER usr_fff WITH DEFAULT_SCHEMA=sch_fff;

-- 스키마의 소유자를 사용자로 설정
ALTER AUTHORIZATION ON SCHEMA::sch_ttt TO usr_ttt;
ALTER AUTHORIZATION ON SCHEMA::sch_fff TO usr_fff;

-- 각 스키마로 테이블 생성
SQL_usr_ttt> CREATE TABLE sch_ttt.ttt(a INT, b INT, c INT);
SQL_usr_fff> CREATE TABLE sch_fff.fff(a INT, b INT);

-- 권한 설정
GRANT CREATE TABLE, SELECT TO usr_ttt;
GRANT CREATE TABLE TO usr_fff;
GRANT INSERT, UPDATE, DELETE TO usr_ttt;
GRANT SELECT ON sch_ttt.ttt(a, b) TO usr_fff;

-- 권한 확인
SELECT grantor = (SELECT name FROM sys.database_principals
                  WHERE principal_id = grantor_principal_id)
     , a.class_desc, a.permission_name, a.state_desc
     , SCHEMA_NAME(c.SCHEMA_ID) + N'.' + c.name  + N'.' + ISNULL(d.name, '') "granted_object"
     , b.name "grantee"
     , b.default_schema_name, b.type_desc, b.is_fixed_role
     , grantor = (SELECT name FROM sys.database_principals
                  WHERE principal_id = a.grantor_principal_id)
FROM sys.database_permissions a
INNER JOIN sys.database_principals b
ON a.grantee_principal_id = b.principal_id
LEFT JOIN sys.tables c
ON a.major_id = c.OBJECT_ID
LEFT JOIN sys.columns d
ON a.major_id = d.OBJECT_ID
AND a.minor_id = d.column_id
WHERE b.name <> 'PUBLIC';

-- 권한 해제
revoke TABLE, SELECT TO usr_ttt;
REVOKE CREATE TABLE TO usr_fff;
REVOKE INSERT, UPDATE, DELETE FROM usr_ttt;
REVOKE SELECT ON sch_ttt.ttt(a, b) FROM usr_fff;

/*
-- 롤 관리
*/

-- 롤 생성
USE mydb;

CREATE ROLE ROLE1;
CREATE ROLE ROLE2;

-- 롤에 권한 생성
GRANT CREATE TABLE, SELECT, INSERT, DELETE TO ROLE1;
GRANT CREATE TABLE TO ROLE2;
GRANT SELECT ON sch_ttt.ttt(a,b) TO ROLE2;

-- 롤에 사용자 추가
EXEC sp_addrolemember 'role1', 'usr_ttt';
EXEC sp_addrolemember 'role2', 'usr_fff';

-- 롤 확인
select * from sys.database_principals
where type = 'R'

-- 롤에 추가된 권한 확인(사용자 권한 확인과 동일)
SELECT grantor = (SELECT name FROM sys.database_principals
                  WHERE principal_id = grantor_principal_id)
     , a.class_desc, a.permission_name, a.state_desc
     , SCHEMA_NAME(c.SCHEMA_ID) + N'.' + c.name  + N'.' + ISNULL(d.name, '') "granted_object"
     , b.name "grantee"
     , b.default_schema_name, b.type_desc, b.is_fixed_role
     , grantor = (SELECT name FROM sys.database_principals
                  WHERE principal_id = a.grantor_principal_id)
FROM sys.database_permissions a
INNER JOIN sys.database_principals b
ON a.grantee_principal_id = b.principal_id
LEFT JOIN sys.tables c
ON a.major_id = c.OBJECT_ID
LEFT JOIN sys.columns d
ON a.major_id = d.OBJECT_ID
AND a.minor_id = d.column_id
WHERE b.name <> 'PUBLIC';

-- 롤에 추가된 사용자 확인
select role    = (select name from sys.database_principals
                  where principal_id = a.role_principal_id)
     , grantee = (select name from sys.database_principals
                  where principal_id = a.member_principal_id)
from sys.database_role_members a;

-- 개체 삭제(세그먼트개체→스키마→사용자→로그인)
DROP TABLE sch_ttt.tbl_ttt;
DROP TABLE sch_fff.tbl_fff ;
DROP SCHEMA sch_ttt;
DROP SCHEMA sch_fff;
DROP USER usr_ttt;
DROP USER usr_fff;
DROP LOGIN login_ttt;
DROP LOGIN login_fff;