SQL SEQUENCE 생성, 등록,INDEX 사용법,사용자관리 추가하는 방법

SQL  SEQUENCE 시퀀스 

 

 

◆시퀀스(SEQUENCE)

게시판의 글번호와 연관잇다.

여러 사용자들이 공유하는 데이터베이스 객체로서, 호출 될 때마다 중복되지 않은 고유한 숫자를 리턴하는 객체이다

값이 자동으로 1씩 OR 2개씩 증가되게 한다.

기본적으로 PK로 지정되어 있음.

**CHCHE 는 가급적 사용하지 말것.

 

** MAXVALUE: 생성 가능한 시퀀스 최대값

MINVALUE: cycle일 경우 새로 시작값. 감소하는 시퀀스인 경우는 최소값.

 

최초 시작은 START 값

CREATE SEQUENCE EMP_SEQ

INCREMENT BY 1

START WITH 100

MAXVALUE 9999

NOCACHE

NOCYCLE;

SELECT SEQUENCE_NAME, MIN_VALUE, MAX_VALUE, INCREMENT_BY

FROM USER_SEQUENCES;

 

-시퀀스 사용

NEXTVAL

: 지정된 시퀀스에서 순차적인 시퀀스 번호를 추출할 때 사용. 

시퀀스명.NEXTVAL

을 실행되면, 시퀀스 테이블에 INCREMENT 1 했던 것을 자동으로 저장시키고 저장시킨 값을 

한번쓰면 그만큼 자동으로 값 증가.

 

INSERT INTO EMP

VALUES (EMP_SEQ.NEXTVAL, '홍길동', '인사',NULL , SYSDATE, 2500, 300, 40);

 

**시퀀스를 초기값을 변경 시키려면 DROP 시켰다가 다시 만들어야 함.

 

CURRVAL

:  사용자가 방금 추출한 시퀀스 번호를 참조할 때 사용

시퀀스명.CURRVAL

반드시 NEXTVAL에 의해서 번호를 추출한 후에 사용해야 한다

SELECT EMP_SEQ.CURRVAL FROM DUAL;

 

 

 

-시퀀스 변경

증분, 최대값, 최소값, 순환여부 , 캐시여부를 변경할 수 있다

시퀀스가 변경되면 다음 번 시퀀스 번호 추출부터 변경사항이 적용된다

START WITH 옵션은 변경이 불가능하며, 필요시 시퀀스를 삭제하고 재 생성해야 한다.

MAXVALUE 값은 현재 시퀀스 번호보다 큰 번호로 지정해야 한다.

ALTER SEQUENCE EMP_SEQ

INCREMENT BY 2

MAXVALUE 10000

NOCACHE

NOCYCLE;

 

-시퀀스 삭제

DROP SEQUENCE EMP_SEQ;

ex.

create sequence dept_deptno_seq

start with 10

increment by 10

maxvalue 100

minvalue 5

cycle

nocache;

 

*4(4번 날린다.)

select dept_deptno_seq.nextval, dept_deptno_seq.currval

from dual;

 

100부터 -10 씩 떨어져서

 0이 되고, 0이 되면 다시 돌아거서 150부터 -10씩 떨어진다.

 START WITH 100

INCREMENT BY -10

MAXVALUE 150

MINVALUE 10

 

◆인덱스

중복된 값이 있으면 안되고, PK OR UK이어야 한다.

: 테이블에서 행을 검색할 때 검색 속도를 높이기 위해 Oracle 서버가 사용하는 스키마 객체이다.( 데이터의 실제 저장위치인 ROWID를 저장하고 관리함 )

인덱스 없이 데이터를 검색하면 테이블의 모든 데이터를 읽어 데이터를 선별한다.(Full Scan-위에서 부터 하나하나 다 검사함.)

 

-인덱스를 사용할 컬럼 성정

값의 범위가 넓은 컬럼 ( 즉, 컬럼내의 값이 다양할수록 좋다 )

NULL 값이 많은 컬럼 ( NULL 값은 인덱스에 포함되지 않기 때문에 인덱스 크기가 감소)

 

** * 인덱스가 반드시 성능을 향상시키는 것은 아니다. 테이블에 DML 작업을 수행하면 관련 인덱스도 변경되어야 하므로 오히려 속도가 저하될 수도 있다.

 

-인덱스가 필요 없는 경우

테이블이 작은 경우

테이블이 자주 변경되는 경우(ROWID가 변경됨)

 

-인덱스 삭제

+EX.

FULL SCAN

F10으로 확인.

 

INDEX SCAN

CREATE INDEX EMP_ENAME_IDX

ON EMP(ENAME);

 

 

-인덱스 사용하지 않는 예외 경우

◆ 사용자관리

-데이터베이스 보안 종류

1.시스템보안

사용자 계정 생성, 암호변경, 디스크 공간 할당, 시스템 작업등과 같이 시스템 수준에서의 데이터베이스 접근 및 사용을 관리하는 것.

인증(Authentication)관련

2.데이터보안

데이터베이스 객체에 대한 사용자들의 접근 및 사용을 관리하는 것이다.

권한(Authorization )관련

 

보안의 위한 서버작업

:데이터베이스 접근 제어

데이터베이스 특정 객체에 대한 접근 권한 부여

데이터베이스 객체에 대한 동의어 작성

 

 

1. 사용자 생성

CREATE USER USER01 

IDENTIFIED BY ORACLE;

패스워드 변경

ALTER USER USER01

IDENTIFIED BY USER01;

 

 

-권한

1.시스템 권한

- 사용자의 데이터베이스 접근권한 ( DBA 가 부여한다 )

 

- 사용자가 데이터베이스에 특별한 작업을 수행하는 것을 가능하게 해줌.

 

2.객체 권한

데이터베이스내의 객체의 내용을 조작하기 위한 권한

- 사용자가 특정 객체에 접근하고 조작하는 것을 가능하게 해줌.

 

 

-스키마(SCHEMA)

- 테이블, 뷰 , 시퀀스와 같은 객체들의 모음을 의미한다. 

- 데이터베이스 사용자가 소유하며, 사용자 이름과 동일한 이름을 갖는다.

 

-시스템권한

 

-권한부여

사용자가 생성되면 DBA는 특정한 시스템 권한을 사용자에게 부여해야 한다

 

GRANT 권한 TO 누구에게

 

-사용자에게 부여 가능한 시스템 권한 종류

 

접속 권한

GRANT CREATE SESSION

TO USER01;

 

테이블 만들 수 있는 권한

GRANT CREATE TABLE

TO USER01;

 

->

녹색 플러스 표시 클릭 후

위와 같이 값을 지정한다.

테스트-> 상태:성공 -> 저장

 

-user01 의 권한 확인

select * from session_privs;

 

 

-사용자관리

객체권한:

사용자는 자신의 스키마에 저장된 모든 객체에 대하여 권한을 갖는다(sys가 부여해줬을때)

다른 사용자 또는 롤(role)에게 자신이 소유한 권한을 부여할 수 있다.

 

 

 

 

scott서버에서 user01에게 dept테이블의 select 기능을 가능하게 선언해주는 sql문

grant select

on dept

to user01;

 

+

error:

스키마 써줘서 어느 서버의 dept인지 알려줘야함.

select * from scott.dept;

 

+ WITH GRANT OPTION : 권한을 부여 받은 사람이 부여 받은 권한을 다른 사용자에게 다시 부여할 수 있는 방법

 

grant update(dname)

on dept to user01;

 

+

 

LOC에 대한 권한은 부여되지 않았음.

UPDATE SCOTT.DEPT

SET DNAME = '인사'

WHERE DEPTNO = 40;

 

-객체 권한 회수

WITH GRANT OPTION 에 의해 다른 사람에게 부여된 권한도 연쇄적으로 회수된다

 

 

 

◆role

권한들의 묶음을 의미한다.

일반 사용자에게 권한을 부여 및 회수하듯이 롤(Role)에 권한 부여 및 회수 가능하다

 

 

connect:접속권한

resource:

**

 

 scott 계정 생성 및 접속권한 부여

<sys 계정에서 

1.계정만들기 2. unlock 3. 권한주기

새로 만들어야 하는 경우:

create user scott IDENTIFIED by tiger;

 

있는 경우 :

alter user scott IDENTIFIED by tiger;

 

lock을 풀지 않았을 경우:

alter user scott account unlock;

접속 및 권한 부여:

grant connect, resource to scott;

 

-scott계정으로 접속 emp, dept, salgarde 테이블 확인

반드시 기억 계정생성/ unlock /권한 부여

create user shop IDENTIFIED by shop;

alter user shop account unlock;

grant connect, resource to shop;

>

ex.

create user tester IDENTIFIED by test;

alter user tester account unlock;

grant connect ,resource to tester;