[오라클/SQL] Sequence 생성/삭제, Role 권한 위임

Sequence(시퀀스)란?

    Sequence 는 연속적으로 입력되는 데이터 어떤 순서 값을 부여할 때 사용한다.

    예를 들어 가입하는 신규 회원을 식별하는 식별자를 회원 테이블에서 자동으로 회원번호를 매기는 경우이다.

    일반적으로 Primary Key가 적용된 컬럼에     순서적으로 번호를 부여할 때 시퀀스를 주로 사용한다. 일련 번호지만 한 자리 건너,

    중간에 새로운 번호로  시작 등으로 해줄 수 있다. 'CREATE     SEQUENCE 시퀀스_명'식으로 시퀀스를 생성해준다. 

    보통 PK값에 중복값을 방지하기위해 사용합니다

    ▪ NEXTVAL을 써주면 자동으로 다음 번호가 매겨지고, 
    ▪ INCREMENT BY 뒤에 증가_값을 지정할 수 있는데 디폴트 증가_값은 1이다. 
    ▪ START WITH로 디폴트 시작_값은 1부터지만 시작_값이 1이 아니게 지정할 수도 있다 등의 옵션을 사용할 수 있다.  

 

Sequence 생성

CREATE SEQUENCE [시퀀스명]
START WITH [시작숫자] -- 시작숫자의 디폴트값은 증가일때 MINVALUE 감소일때 MAXVALUE
INCREMENT BY [증감숫자] --증감숫자가 양수면 증가 음수면 감소 디폴트는 1
NOMINVALUE OR MINVALUE [최솟값] 

NOMAXVALUE OR MAXVALUE [최대값] 

CYCLE OR NOCYCLE 
--CYCLE 설정시 최대값에 도달하면 최소값부터 다시 시작 NOCYCLE 설정시 최대값 생성 시 시퀀스 생성중지
CACHE OR NOCACHE 
--CACHE 설정시 메모리에 시퀀스 값을 미리 할당하고 NOCACHE 설정시 시퀀스값을 메로리에 할당하지 않음

 

Sequence 수정

ALTER SEQUENCE [시퀀스명] INCREMENT BY [증감숫자];ㅣ

 

Sequence 삭제

DROP SEQUENCE [시퀀스명]

 

예제

테이블 생성
create table tasks (id number primary key, title varchar2(20) not null);

1.시퀀스(1)생성
create sequence tasks_id_seq
start with 10
increment by 3
minvalue 10
maxvalue 100
cycle;

insert into tasks (id, title) values(tasks_id_seq.NEXTVAL, 'IT PROG');
insert into tasks (id, title) values(tasks_id_seq.NEXTVAL, 'IT MAN');
insert into tasks (id, title) values(tasks_id_seq.NEXTVAL, 'IT SALES');
insert into tasks (id, title) values(tasks_id_seq.NEXTVAL, 'IT PROG');
insert into tasks (id, title) values(tasks_id_seq.NEXTVAL, 'IT DEVELOP');

SELECT * FROM tasks;

2. 시퀀스(2)생성
create sequence dept_id_seq
start with 20
increment by 3; 

insert into tasks values(dept_id_seq.NEXTVAL, 'IT DEVELOP');
insert into tasks values(dept_id_seq.NEXTVAL, 'IT MAN');
insert into tasks values(dept_id_seq.NEXTVAL, 'IT PROG');


3. 삭제
drop sequence tasks_id_seq;

 

 

사용자 롤(Role), 권한 위임

    Windows AD 에서 그룹을 만들고 그룹에 권한을 준 뒤 그룹에 사용자를 넣어서  해당 사용자들이 해당 그룹에 주어진  권한을 사용하는 개념이다.

    Linux/UNIX 에서도 그룹을 생성한 뒤, 사용자를 넣고, 그룹이 할 수 있는 권한을 부여하면, 해당 그룹이 가지고 있는 권한을 사용 할 수 있다.

    Oracle 에서는 사용자들을 Role_based로 관리한다고 한다.

     

    Role에 줄 수 있는 권한종류
    User : create user, alter user, drop user
    Session : create session, alter session
    Table : create table, create any table, alter any table, drop any table,

                   insert any table, update any table, delete any table, select any table
    Index : create any index, alter any index, drop any index  View, Sequence, Synonym, Profile,  Role 

 

이제 사용자 계정 생성과 계정 묶기, 권한 주기 등을 알아보자.

여기서는 특히 롤을 알아보는데 사용자들이 가질 수 있는 권한들을 묶어서 여러 롤로 만들어 두고, 

각 롤에 사용자를 넣어서, 해당 롤에 들어 있는 사용자들은 해당 롤에 주어진 권한만 사용하게 해주는 것을 말한다.  

<sys>
create ROLE user_create;
grant create user, alter user to user_create
grant user_create to scott, hr;
								// scott, hr 으로 권한 부여

<scott>
create user tester4 identified by tester4;
								// sys에서 권한을 받았기 때문에 생성가능 
drop role user_create;
								// ORA-01031: 권한이 불충분합니다
-- drop 권한부여하지 않음

<oe>
create user tester4 identified by tester4;
								// 1행에 오류: ORA-01031: 권한이 불충분합니다

<sys>
drop role user_create;