목차

1. DCL 개요
2. 유저와 권한
3. ROLE을 이용한 권한 부여

1. DCL 개요

• 유저를 생성하고 권한을 제어할 수 있는 DCL(DATA CONTROL LANGUAGE) 명령어

2. 유저와 권한

• 대부분의 데이터베이스는 데이터 보호와 보안을 위해서 유저와 권한을 관리함
• Oracle 을 설치하면 기본적으로 제공되는 유저들인 SYS,SYSTEM, SCOTT 유저는 아래와 같음
• ORALCE 과 SQL Server의 사용자에 대한 아키텍트는 다른 면이 많다.

• SQL Server Architecture

• Oracle Architecture

2.1. SQL SERVER 로그인 방식

2.1.1. WINDOWS 인증 방식

• Windows 에 로그인한 정보를 가지고 SQL Server 에 접속하는 방식
• SQL Server가 Windows에서 제공하는 자격 증명을 신뢰하므로 Windows 인증을 사용한 연결을 트러스트된 연결이라고 함

2.1.2. 혼합 모드(Windows 인증 또는 SQL 인증)

• 기본적으로 Windows 인증으로도 SQL Server 에 접속 가능
• Oracle 인증과 같은 방식으로 사용자 아이디와 비밀번호로 SQL Server 에 접속하는 방식

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

• 유저 생성후, 접속을 위한 권한 부여 필요,
• 모든 DDL 문장 수행을 위한 권한 부여 필요
• 개별로 부여 하기 보다는 ROLE 을 통한 권한 부여

* Oracle
SQL> show user
USER is "SCOTT"

SQL> create user pjs identified by korea7 ;
create user pjs identified by korea7
                              *
ERROR at line 1:
ORA-01031: insufficient privileges

SQL> conn /as sysdba
Connected.
SQL> show user
USER is "SYS"
SQL> grant create user to scott ;
Grant succeeded.

SQL> conn scott/tiger
Connected.
SQL> show user
USER is "SCOTT"
SQL> create user pjs identified by korea7 ;

User created.

SQL> conn pjs/korea7  << 유저 생성했다고 해서 바로 접속 할수 없다. 접속을 위한 권한 부여 필요 
ERROR:
ORA-01045: user PJS lacks CREATE SESSION privilege; logon denied

SQL> conn / as sysdba
Connected.
SQL> grant create session to scott with admin option ;
Grant succeeded.

SQL> conn scott/tiger
Connected.
SQL> grant create session to pjs ;
Grant succeeded.

SQL> conn pjs/korea7   << 드뎌 접속 성능 
Connected.
SQL>
SQL> select * from tab ;

no rows selected


SQL> create table menu ( menu_seq number not null, title varchar2(10)) ;  << 접속 됐다고 테이블을 생성할 수 있는건 아니다.
create table menu ( menu_seq number not null, title varchar2(10))
*
ERROR at line 1:
ORA-01031: insufficient privileges

SQL> conn /as sysdba
Connected.

SQL> grant create table to pjs ;

Grant succeeded.

SQL> conn pjs/korea7  
Connected.
SQL> create table menu ( menu_seq number not null, title varchar2(10)) ; << 드뎌 테이블 생성 

Table created.

나. OBJECT 에 대한 권한 부여

• 오브젝트 권한와 오브젝트와의 관계
• 개별 오브젝트에 대한 작업을 위해서는 오브젝트 권한 부여 필요
  

SQL> conn scott/tiger
Connected.
SQL> select * from pjs.menu ;
select * from pjs.menu
                  *
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> conn pjs/korea7
Connected.
SQL> insert into menu values(1, 'Fighting');
insert into menu values(1, 'Fighting')
            *
ERROR at line 1:
ORA-01950: no privileges on tablespace 'USERS'


SQL> show user ;
USER is "PJS"

SQL> conn / as sysdba
Connected.
SQL> alter user pjs quota 100M on users ;
User altered.

SQL> conn pjs/korea7
Connected.
SQL> insert into menu values(1, 'Fighting');

1 row created.

SQL> commit ;

Commit complete.

SQL> grant select on menu to scott ;  << 조회(SELECT) 권한 부여 
Grant succeeded.

SQL> conn scott/tiger
Connected.  
SQL> select * from pjs.menu ; << 드뎌 조회 가능 

  MENU_SEQ TITLE
------------ ----------
         1 Fighting

SQL> update pjs.menu set title ='KKK' where menu_seq = 1 ; << 업데이트 시도 - 실패 ( 권한 부족 ) 
update pjs.menu set title ='KKK' where menu_seq = 1
           *
ERROR at line 1:
ORA-01031: insufficient privileges

3. ROLE을 이용한 권한 부여

• 유저와 권한들 사이에서 중개 역할을 하는 ROLE 제공
• ROLE 을 생성하고, ROLE 에 각종 권한들을 부여한 후, ROLE 을 다른 ROLE 혹은 유저에 부여
• ROLE 을 만들어 사용하는 것이 권한을 직접 부여하는 것보다 빠르고 안전하게 관리 가능

SQL>CONN SYSTEM/MANAGER;
연결되었다.

REVOKE CREATE SESSION, CREATE TABLE FROM PJS ;
권한이 취소되었다.

CONN PJS/KOREA7
ERROR: 사용자 PJS은 CREATE SESSION 권한을 가지고 있지 않음, 로그온이 거절되었다.

CONN SYSTEM/MANAGER
연결되었다.

CREATE ROLE LOGIN_TABLE
롤이 생성되었다.

GRANT CREATE SESSION, CREATE TABLE TO LOGIN_tABLE ; 
권한이 부여되었다.

GRANT LOGIN_tABLE TO PJS ; 

CONN PJS/KOREA7
연결되었다.

CREATE TABLE MENU2 ( MENU_SEQ NUMBER NOT NULL, TITLE VARCHAR2(10)) ;
테이블이 생성되었다.

CONN SYSTEM/MANAGER
연결되었다.

DROP USER PJS CASCADE ;
사용자가 삭제되었다.
--> PJS 유저가 만든 MENU 테이블도 같이 삭제되었다.

CREATE USER PJS IDENTIFIED BY KOREA7;
사용자가 생성되었다.

GRANT CONNECT, RESOURCE TO PJS ;
권한이 부여되었다.

CONN PJS/KOREA7;
연결되었다.

CREATE TABLE MENU (MENU_SEQ NUMBER NOT NULL, TITLE VARCHAR2(10)) ;
테이블이 생성되었다.

문서에 대하여

• 이 문서는 오라클클럽 오라클 성능 트러블슈팅 스터디 모임에서 작성하였습니다.
• {*}이 문서의 내용은 한국 데이터베이스 진흥원에서 출간한 'SQL 전문가 가이드'를 참고하였습니다.*