SQLD 자격증 정리

[SQLD] SQL활용 - DCL

sjoo 2021. 8. 4. 21:00

DCL

- DCL

유저를 생성하고 권한을 제어할 수 있는 명령어

 

- 접속방식

오라클 SQL Server
유저를 통한 데이터베이스 접속
즉, 아이디와 비밀번호 방식으로 인스턴스에 접속한 뒤 해당하는 스키마에 오브젝트 생성등의 권한을 부여 받음		 인스턴스에 접속하기 위해 로그인이라는 것을 생성하여 접속
인스턴스 내에 존재하는 다수의 데이터베이스에 연결하여 작업하기 위해 유저를 생성한 후 로그인과 유저를 매핑해주어야 한다.

 

- SQL Server 로그인

1. window 인증 방식 : 윈도우에 로그인한 정보를 가지고 SQL Server에 접속

2. 혼합모드 인증 방식 :  윈도우 또는 sql인증 방식으로 기본적으로 윈도우 인증으로도 SQL Server 접속 가능하며, 오라클의 인증과 같은 방식으로 사용자 아이디와 비밀번호로 서버에 접속

 

- 유저 생성과 시스템 권한 부여

유저를 생성하고 데이터베이스에 접속하게 되면 테이블, 뷰, 인덱스 등과 같은 오브젝트를 바로 생성할 수 없다.

사용자가 실행하는 모든 DDL문장은 그에 해당하는 적절한 권한이 있어야 실행이 가능하고 이러한 권한을 시스템 권한이라고 한다. 이러한 시스템 권한은 유저에게 일일히 설정하는 것은 너무 복잡하기 때문에ROLE 이라는 것을 이용하여 권한을 부여하게 된다. 

 

 

* 유저 생성

<Oracle>

1. 데이터베이스 연결

CONN SCOTT/TIGER;

2. 권한부여

GRANT CREATE USER TO SCOTT;

3. 유저생성

CREATE USER KBB IDENTIFIED BY PASSWORD;

 

 

<SQL Server>

※ 유저를 생성하기 전 먼저 로그인을 생성해야한다. 로그인 생성 권한을 가진 로그인은 기본적으로 sa이다.

 

1. sa로 로그인 한 후, SQL인증을 사용하는 KBB라는 로그인을 생성한다. 최초로 접속할 데이터베이스는  AdventureWorks로 설정한다.

CREATE LOGIN KBB WITH PASSWORD='PASS7', DEFAULT_DATABASE=AdventureWorks;

 

2. SQL Server에서의 유저는 데이터베이스마다 존재한다. 그러므로 유저를 생성하기 위해 생성하고자하는 유저가 속할 데이터베이스로 이동을 한 후 처리한다.

USE AdventureWorks;
GO CREATE USER KBB FOR LOGIN KBB WITH DEFAULT_SCHEMA=dbo;

 

 

※ 유저가 생성되었지만, 아무런 권한을 부여받지 못했기 때문에 로그인 시 CREATE SESSION 권한이 없다는 오류가 발생한다. 그렇기 때문에 권한 부여 후 로그인을 수행한다.

 

 

1. 데이터베이스 연결

CONN SCOTT/TIGER;

2. KBB에 권한부여

GRANT CREATE SESSION TO KBB;

3. KBB로 로그인

CONN KBB/PASSWORD;

 

* KBB 유저로 테이블 생성

SYSTEM 유저를 통하여 KBB 유저에게 CREATE TABLE 권한을 부여한 후 다시 테이블을 생성한다.

 

<Oracle>

// SYSTEM유저로 로그인
CONN SYSTEM/MANAGER;

// KBB유저에 테이블 생성 권한 부여
GRANT CREATE TABLE TO KBB;

// KBB유저로 로그인
CONN KBB/PASSWORD;

//테이블 생성
CREATE TABLE MENU (
MENU_SEQ NUMBER NOT NULL,
TITLE VARCHER2(10) );

 

<SQL Server>

GRANT CREATE TABLE TO KBB;

GRANT Control ON SCHEMA::dbo TO KBB;

CREATE TABLE MENU (
MENU_SEQ NUMBER NOT NULL,
TITLE VARCHER2(10) );

 

 

- OBJECT에 대한 권한 부여

오브젝트 권한은 특정 오브젝트인 테이블, 뷰 등에 대한 SELECT, INSERT, UPDATE 작업 명령어를 의미한다.

다음은 오브젝트 권한과 오브젝트와의 관계를 나타내는 표이다.

모든 유저는 각각 자신이 생성한 테이블 외에 다른 유저의 테이블에 접근하려면 해당 테이블에 대한 오브젝트 권한을 소유자로부터 부여받아야 한다. SQL Server도 같은 방식으로 동작한다. 한가지 다른점은 위에서 언급했듯이 유저는 단지 스키마에 대한 권한만을 가진다. 즉, 테이블과 같은 오브젝트는 유저가 소유하는 것이 아니고 스키마가 소유를 하게 되며 유저는 스키마에 대해 특정한 권한을 가지는것이다.

* 테이블에 접근할 수 있는 권한을 유저에게 주기

GRANT SELECT ON MENU TO SCOTT;



- ROLE을 이용한 권한 부여

유저를 생성하면 기본적으로 CREATE SESSION, CREATE TABLE, CREATE PROCEDURE 등 많은 권한을 부여해야한다.

데이터베이스 관리자는 유저가 생성될 때마다 각각의 권한들을 유저에게 부여하는 작업을 수행해야 하며, 간혹 권한을 빠뜨릴 수도 있으므로 각 유저별로 어떤 권한이 부여되었는지 관리해야 한다.

데이터베이스 관리자는 ROLE을 생성하고, ROLE에 각종 권한들을 부여한 후 ROLE을 다른 ROLE이나 유저에게 부여할 수 있다.   또한, 롤에 포함되어 있는 권한들이 필요한 유저에게는 해당 롤만을 부여함으로써 빠르고 정확하게 필요한 권한을 부여할 수 있게 된다.

ROLE에는 시스템 권한과 오브젝트 권한을 모두 부여할 수 있으며, ROLE은 유저에게 직접 부여될 수도 있고, 다른 ROLE에 포함되어 유저에게 부여될 수도 있다.

 

* 부여했었던 권한 취소

<Oracle>

REVOKE CREATE SESSION, CREATE TABLE FROM KBB;

 

<SQL Server>

REVOKE CREATE TABLE FROM KBB;

 

 

* ROLE 생성

CONN SYSTEM/MANAGER;

CREATE ROLE LOGIN_TABLE;

GRANT CREATE TABLE,CREATE SESSION TO LOGIN_TABLE;

GRANT LOGIN_TABLE TO KBB;

 

 

 

※ 오라클에서는 몇 가지 롤을 제공하고 있는데, 제일 많이 쓰이는 롤이 아래와 같이 CONNECT와 RESOURCE이다. 

CONNECT : CREATE SESSION 처럼 로그인 권한이 포함

RESOURCE : CREATE TABLE과 같은 오브젝트의 생성 권한이 포함



* 유저 삭제 명령(유저가 만든 테이블도 같이 삭제)

DROP USER KBB CASCADE;

 

* 사용자 생성 명령

CREATE USER KBB IDENTIFIED BY PASSWORD;

 

* ROLE을 이용한 권한 부여

GRANT CONNECT, RESOURCE TO KBB;

 

SQL Server에서는 롤을 생성하여 사용하기 보다는 기본적으로 제공되는 롤에 멤버로 참여하는 방식으로 사용된다. 하지만 오라클과 같이 롤을 자주 사용하지는 않는다. 

 

- 인스턴스 수준의 작업이 필요한 경우 : 서버 수준 역할명을 이용하여 로그인 및 사용자 권한을 제어

- 데이터베이스 수준의 작업이 필요한 경우 : 데이터베이스 수준 역할을 이용하여 부여