사용자 로그인 보안과 스키마

 

 

 

 

 

=================================

=================================

=================================

 

 

 

 

출처ㅣ http://blog.naver.com/blue7red?Redirect=Log&logNo=100049856529

1.SQL 서버의 보안

(1)인증모드

-SQL 서버에 접속할 때 사용하는 인증

-Windows 인증모드와 혼합모드가 있다.

 

<1>Windows 인증모드

-SQL서버에 접속하기위해서 별도의 사용자 및 비밀번호가 필요없고 ,

Windows에 접속한 사용자로 데이터베이스에 연결해서 사용하는 것

-보안에는 강하다.

 

<2>혼합 인증모드

- 윈도우즈 인증모드를 계속 사용할 수 있으면서도 ,동시에 별도의 허락된 계정으로 SQL서버에 접속할 수 있는 모드

-외부에서 접속하기에 좀 더 편리하지만 ,보안에는 상대적으로 취약하다.

-최상위관리자로 'sa'가 있다.

-SQL 서버2005에서 디폴트로 sa의 접속을 막아두었다.

(사용할 수 있도록 하려면 

속성에서 암호정책강제적용 체크박스를 풀고 암호 입력하고

상태탭으로 가서 데이터베이스 엔진연결권한에 허용으로 ,

로그인에 사용으로 변경하고 확인하면 된다.)

 

(2)인증모드의 사용

 

[윈도우즈 인증모드]

윈도우즈 인증을 위한 새로운 사용자 생성

컴퓨터관리 - 사용자 계정 추가(아이디,비번설정) - 소속그룹설정(Adminstrators그룹에 추가) 

Administors 그룹에 속하지 않은 사용자에 의해서는 SQL서버에 윈도우즈인증으로  접속할 수 없다.

 

 

[혼합인증모드]

 

 

(3)로그인

-SQL 서버에 접속하기 위해서는 로그인 계정이 필요하다.

-비유:놀이공원의 입장권에 해당한다.

-SQL서버에 접속할 수 있다는 것이지 데이터베이스에 접근할 수 있다는 건은 아니다.

 

[기본적으로 존재한는 로그인]

-sysadmin서버 역할을 갖는다.

컴퓨터이름\Administrator

BUILTIN\Administrators   //관리자그룹에 속한 소속원

sa                                  //2005에서는 따로 허용을 해야만 한다.

SYSTEM

 

[생성구문]

CREATE LOGIN login_name { WITH  <option_list> | FROM < sources>}

 

(4)사용자

-각 데이터베이스 별로 접근할 수 있는 계정을 말한다.

-SQL 서버에 접속할 수 있는 로그인이 모든 DB에도 자동으로 접근할 수 있다면 심각한 문제를 야기할 수 있다.

 

[model DB에 기본 존재하는 사용자]

dbo       DB의 소유자로 해당DB의 모든 권한이 있다

guest     모든 로그인에대해서 최소한의 접근이 가능하다

sys,INFORMATION_SCHEMA     DB내부개체참조를 위한 사용자

 

[생성구문]

CREATE USER user_name [{{ FOR | FROM}

{LOGIN  login_name | CERTIFICATE cert_name | ASYMMETRIC KEY asynm_key_name }

| WITHOUT LOGIN ]

[WITH DEFAULT_SCHEMA = schema_name]

 

(5)사용권한

- 개체를 사용하거나 , 명령문을 수행하는 권한

-DB사용자라고 무조건 DB의 모든 개체를 수정,삭제할 수 있도록 허용되어 있지는 않다.

- 권한 부여 GRANT , 거부는 DENY , 거부는 REVOKE

 

[권한의 종류]

<개체 사용권한>

-DB개체(테이블,뷰 등)에 대해서 SELECT,INSERT,DELETE 등을 수행할 수 있는 권한

 

<문 사용권한>

-특정한 명령문을 수행할 수 있는지의 여부를 결정한다.

 

<내재적 사용권한>

-명시적으로 사용권한을 부여하지 않아도 암시적으로 부여된 사용권한

-예로 DB소유자는 암시적으로 DB의 모든 권한을 가지고 있다.

 

(6)역할

-사용자 또는 로그인의 집합이거나

-권한의 집합(권한부여를 편리하게 하기 위해서)

 

 

[역할의 종류]

<고정 서버 역할>

-서버를 관리하는 권한의 집합

-종류로는 sysadmin , dbcreator 등이 있다

-구문형식

  EXEC sp_addsrvrolemember '로그인이름','역할이름'

 

<고정 데이터베이스 역할>

-DB수준에서 권한의 할당을 지정할 경우에 사용한다

-종류로는 db_datareader , db_owner , public 등이 있다

 

-구문형식

  EXEC sp_addrolemember '역할이름','DB사용자이름'

 

 

(7)보안 실습

[윈도우즈 사용자 생성]

sqlAdmin이라는 아이디 생성( Administrators그룹에 추가)

sqlUser라는 아이디 생성 (일반사용자)

noUser이란 아이디 생성 (일반사용자)

 

[SSMS에서 로그인생성]

로그인에서 아이디 비번을 주면 생성된다.

 생성될 때  테이블별로 세세하게 권한을 설정해줄 수도 있고 , 역할로 한번에 권한을 줄 수도 있다.

 

 

//SQL로그인아이디와 사용자아이디는 동일하게 하는 것이 좋다.

 

USE master ;
RESTORE DATABASE sqlDB FROM DISK ='c:\sqldb.bak' WITH REPLACE ;

 

//noUser라는 윈도우즈 로그인  생성 

CREATE LOGIN [BRAIN\noUser]
FROM WINDOWS
WITH DEFAULT_DATABASE = sqlDB;

 

 

//sqlUser에 대해서 sqlDB에 대해서 dataReader역할을 주고 난 후에

//sqlUser로 로그인한 상태에서

USE sqlDB;
SELECT * FROM userTbl; //실행가능

 

//인서트는 역할범위에 들지 않기 때문에 오류가 난다.

INSERT INTO userTbl VALUES ('JUN','지우니',2003,'경기','011','2223333',100);

 

 

//sa로 접속한 상태에서

//새로그인을 sql서버 인증으로 newUser1에 sysadmin역할로 생성 ==>모든 권한을 부여받는다.

//새로그인을 sql서버 인증으로 newUser2에 userTbl테이블에서 datawriter역할로 생성 ==>insert만 가능하다.

 

//newUser2로 로그인한 상태에서

USE sqlDB;
SELECT * FROM userTbl; //datawriter역할은 insert만 가능하기 때문에 select할 수 없다.

INSERT INTO userTbl VALUES ('JUN','지우니',2003,'경기','011','2223333',100); //가능하다.
UPDATE userTbl SET name = '우지운' WHERE userID = 'JUN';//update도 내부적으로는 select를 하게되므로 select권한이 없는

newUser2의 쿼리는 실패한다.
DELETE userTbl WHERE userID = 'JUN'; //delete도 마찬가지로 내부적으로 select를 하므로 쿼리는 실패한다.

 

 

//sa로 접속한 후에 newUser2의 역할을 userTable에 대한 datareader권한도 주고서

//다시 newUser2로 로그인한 상태에서 아래의 쿼리를 실행해보면 모두 실행가능하다.

USE sqlDB;
UPDATE userTbl SET name = '우지운' WHERE userID = 'JUN';
SELECT * FROM userTbl;
DELETE userTbl WHERE userID = 'JUN';

 

//sa로 접속한 후 newUser2 로그인을 삭제한다.

DROP LOGIN newUser2; // 삭제를 하더라도

 

//sqlDB와 로그인이 끊어진 사용자목록을 볼 수 있다.

USE sqlDB;
GO
EXEC sp_change_users_login @Action='Report';
GO

USE sqlDB;

 

//사용자 삭제
DROP USER newUser2;

 

 

//sa로 로그인한 상태에서

//로그인 생성

CREATE LOGIN newUser3
WITH PASSWORD = 'password',  -- 원래는 복잡하게 생성
  DEFAULT_DATABASE = [sqlDB],
  CHECK_POLICY = OFF,  -- 암호 정책 강제 적용은OFF
  CHECK_EXPIRATION = OFF; -- 암호 만료 강제 적용은off
GO

 

//사용자 생성
USE sqlDB;
CREATE USER newUser3
 FOR LOGIN newUser3; 

 

 

//테이블에 대해서 사용권한 추가

USE sqlDB;
GRANT SELECT, UPDATE, INSERT, DELETE ON buyTbl TO newUser3;

USE sqlDB;

 

//열별로 사용권한 제한 
GRANT ALL ON buyTbl TO newUser3;

 

//특정열에 대한 권한 제한 
DENY ALL ON buyTbl(price,amount) TO newUser3;

 

SELECT * FROM userTbl;

SELECT userid, name, addr, mobile1, mobile2 FROM userTbl;

USE sqlDB;

 

//권한 박탈 
REVOKE ALL ON userTbl TO newUser3;
REVOKE ALL ON buyTbl TO newUser3;

 

 

CREATE LOGIN newUser4
WITH PASSWORD = 'password', 
  DEFAULT_DATABASE = [sqlDB],
  CHECK_POLICY = ON,  
  CHECK_EXPIRATION = OFF; 
GO
CREATE LOGIN newUser5
WITH PASSWORD = 'password', 
  DEFAULT_DATABASE = [sqlDB],
  CHECK_POLICY = ON,  
  CHECK_EXPIRATION = OFF; 
GO
USE sqlDB;
CREATE USER newUser4 FOR LOGIN newUser4; 
GO
CREATE USER newUser5 FOR LOGIN newUser5; 

USE sqlDB;
EXEC sp_droprolemember orderROLE, newUser3;
EXEC sp_droprolemember orderROLE, newUser4;
EXEC sp_droprolemember orderROLE, newUser5;

 

//역할 제거 
DROP ROLE orderROLE;

 

 

2.스키마

(1)스키마의 개요

-SQL 서버 2005에서 새로이 적용된 개념

-데이터베이스 내에 있는 개체(테이블,뷰,인덱스,프로시저 등)들을 관리하기 위한 묶음

-이전버전과의 비교

이전버전 : 서비이름.데이터베이스이름.사용자이름.테이블이름   Brain.SqlDB.user1.sqltbl

이전버전의 문제점 : 테이블을 생성한 사용자를 삭제하려고 할 때

해당 로그인생성한 테이블을 다른 테이블로 옮긴 후 사용자를 삭제해야 했다.

 

-스키마 개념의 예

특정사용자의 소유라는 개념이 사라지고 스키마를 여러 사용자가 소유할 수 있는 개념

 

사용자별로 기본스키마를 설정할 수 있다. 

기본스키마는 여러 소유자가 중복적으로 설정할 수 있다.

스키마의 소유자는 한  명밖에 없다.

 

 

기본스키마가 dbo로 되어 있다. 이전 버전과의 호환을 위해서 두는 개념이다.

 

CREATE DATABASE schemaDB;

USE schemaDB;
SELECT user_name() -- 현재 사용자 이름

CREATE TABLE table1 (id INT);
GO
SELECT * FROM dbo.table1;
GO
DROP TABLE table1;

 

 

(2)스키마의 생성 및 활용

 

//로그인생성

CREATE LOGIN user1
WITH PASSWORD = 'password', 
  DEFAULT_DATABASE = [schemaDB],
  CHECK_POLICY = ON,  
  CHECK_EXPIRATION = OFF; 
GO
CREATE LOGIN user2
WITH PASSWORD = 'password', 
  DEFAULT_DATABASE = [schemaDB],
  CHECK_POLICY = ON,  
  CHECK_EXPIRATION = OFF; 
GO
CREATE LOGIN user3
WITH PASSWORD = 'password', 
  DEFAULT_DATABASE = [schemaDB],
  CHECK_POLICY = ON,  
  CHECK_EXPIRATION = OFF; 
GO

 

//사용자와 로그인  연결 
USE schemaDB;
CREATE USER user1 FOR LOGIN user1; 
GO
CREATE USER user2 FOR LOGIN user2; 
GO
CREATE USER user3 FOR LOGIN user3; 
GO

USE schemaDB;
GO

 

//스키마 생성
CREATE SCHEMA SCHEMA_B;

SELECT * FROM sys.schemas;

CREATE TABLE table1(id INT);

CREATE TABLE table2(id INT);

INSERT INTO table2 VALUES('3001');
CREATE TABLE table3 (id INT);

 

SELECT * FROM table2;

INSERT INTO table1 VALUES ('1001');
SELECT * FROM SCHEMA_B.table2;

SELECT * FROM table1;

SELECT * FROM SCHEMA_A.table1;

 

//user1

USE schemaDB;
DROP USER user1; //user1이 스키마를 소유하고 있으므로 user1이 삭제가 안된다.

 

//스키마의 소유자 변경

ALTER SCHEMA SCHEMA_A TRANSFER SCHEMA_B.table3;

 

DROP SCHEMA SCHEMA_B; //스키마에 테이블이 존재하므로 삭제할 수 없다.

DROP TABLE SCHEMA_B.table2; //테이블을 먼저 삭제해야 한다.
GO

 

//스키마 삭제
DROP SCHEMA SCHEMA_B;

 

 

 

=================================

=================================

=================================