[DataBase] Oracle 문법(계정 생성과 권한 부여,취소,조회,TABLE SPACE)

●유저 생성

계정 생성하기

CREATE USER 유저ID(필수)

IDENTIFIED BY VALUES "비밀번호"(필수) // IDENTIFIED BY 비밀번호(필수)|EXTERNALLY도가능하다

DEFAULT TABLESPACE 테이블스페이스 이름(선택)

TEMPORARY TABLESPACE 테이블 스페이스(그룹) 이름(선택)

QUOTA 테이블 스페이스 크기 ON 테이블 스페이스 이름(선택)

PROFILE 프로파일 이름(선택)

PASSWORD EXPIRE(선택)

ACCOUNT [LOCK / UNLOCK](선택);

 

• 유저ID : USER 이름
• BY password : USER가 데이터베이스에 의해 인증되도록 지정하며, 데이터베이스 USER 로그인시 사용하는 비밀번호 이다.
• EXTERNALLY : USER가 운영 체제에 의해서 인증되도록 지정한다.
• DEFAULT TABLESPACE는 USER 스키마를 위한 기본 테이블스페이스를 지정 한다. (만들어둔 테이블스페이스를 지정할 수 있다.)
• TEMPORARY TABLESPACE는 USER의 임시 테이블스페이스를 지정한다. (TEMP01.DBF 즉, 임시 데이터베이스 파일에 저장된다, 휘발성이다.)
• QUOTA절을 사용하여 USER가 사용할 테이블스페이스의 영역을 할당한다. (USERS01.DBF 즉, 유저 디폴트 데이터베이스 파일에 저장된다.)
• - PASSWORD EXPIRE : USER가 SQL*PLUS를 사용하여 데이터베이스에 로그인할 때 암호를 재설정 하도록 한다.(SYSTEM,SYS계정으로 해야한다) (USER가 데이터베이스에 의해 인증될 경우에만 적합한 옵션이다.)ex) ALTER USER SPIDERWEB PASSWORD EXPIRE;
• - ACCOUNT LOCK/UNLOCK : USER 계정을 명시적으로 잠그거나 풀 때 사용할 수 있다.(UNLOCK이 기본값이다.) (SYSTEM,SYS계정으로 해야한다) ex) ALTER USER SPIDERWEB ACCOUNT LOCK; ALTER USER SPIDERWEB ACCOUNT UNLOCK;
• - PROFILE : 자원 사용을 제어하고 USER에게 사용되는 암호 제어 처리 방식을 지정하는데 사용된다.
• 여기선 간단한 유저생성에 대해서만 알아보고 자세한 유저관리와 PROFILE 관리는 어드민에서 설명 하겠다.

(SYSTEM,SYS계정으로 해야한다)

(SYSTEM,SYS계정으로 해야한다)

(SYSTEM,SYS계정으로 해야한다)

 

계정 비밀번호 변경

ALTER USER 유저ID

IDENTIFIED BY 비밀번호;

 

(SYS 혹은 SYSTEM 과같은 계정에서 수행하도록 한다.)

계정 삭제

DROP USER "유저ID" CASCADE;

 

(CASCADE 사용시 유저와 객체(스키마) 모두제거가능)

---

 

모든 계정 조회

SELECT * FROM ALL_USERS

(WHERE USERNAME ='유저 이름'); //특정 유저 조회시

 

SELECT * FROM DBA_USERS

(WHERE USERNAME ='유저 이름'); //특정 유저 조회시

 

SELECT * FROM DBA_OBJECTS

(WHERE OWNER = '오너 이름(스키마 이름)'; //특정 스키마 조회시

---

 

●권한 부여 및 취소

 

시스템 권한 리스트

GRANT 시스템 권한 TO 유저명;

 

CREATE USER : 데이터 베이스 유저 생성 권한

SELECT ANY TABLE : 모든 유저의 테이블 조회 권한

CREATE SESSION : 데이터베이스 접속 권한

CREATE TABLE : 테이블 생성 권한

CREATE VIEW : view 생성 권한

CREATE PROCEDURE : procedure, function, package 생성 권한

CREATE SEQUENCE : sequence 생성 권한

SYSDBA : 데이터베이스 관리 최고 권한

SYSOPER : 데이터베이스 관리 권한

---

롤(ROLE)이란

-롤은 여러 권한을 하나의 이름으로 묶어 권한 관련 작업을 간편하게 하려고 사용하는 것.

 

-CONNECT ROLE

oracle 9i까지는

ALTER SESSION,

CREATE CLUSTER,

CREATE DATABASE LINK,

CREATE SEQUENCE,

CREATE SESSION,

CREATE SYNONYM,

CREATE TABLE,

CREATE VIEW

 

oracle 10g 부터

CREATE SESSION 만 가지고 있다.

 

-RESOURCE ROLE

객체 생성 권한을 부여할 때,

CREATE TRIGGER,

CREATE SEQUENCE,

CREATE TYPE,

CREATE PROCEDURE,

CREATE TABLE,

CREATE CLUSTER,

CREATE OPERATOR,

CREATE INDEXTYPE

 

-DBA ROLE

데이터베이스를 관리하는 시스템 권한을 대부분 갖고 있다.

 

롤(ROLE) 생성

1.SYSTEM 계정으로 접속

-CONN SYSTEM/비밀번호;

 

2.롤 생성

-CREATE ROLE 롤이름;

 

3.권한 포함시키기

-GRANT CONNECT, RESOURCE, CREATE VIEW, CREATE SYNONYM TO 롤이름;

 

4.사용자에게 생성했던 롤(롤 이름)부여

GRANT 롤이름 TO 사용자 이름;

 

롤(ROLE) 취소,삭제

REVOKE 롤 이름 FROM 롤을 부여한 사용자; //부여된 롤 취소

 

DROP ROLE 롤 이름; //롤 삭제

부여된 롤(ROLE) 확인

SELECT * FROM USER_ROLE_PRIVS;

 

SELECT * FROM DBA_ROLE_PRIVS WHERE GRANTEE = '유저이름'; //DBA_ROLE_PRIVS 사용하는 경우

---

테이블스페이스(Table Space)란

-데이터 저장 단위 중 가장 상위에 있는 단위를 테이블 스페이스라고 합니다.

 

데이터 저장 단위는 물리적, 논리적단위로 나눌 수 있습니다.

 

물리적 단위는 파일을 의미하고 논리적 단위는 데이터블록 -> 익스텐트 -> 세그먼트 -> 테이블스페이스 이렇게 나뉩니다.

 

데이터 블록 여러개가 모여 익스텐트 하나를 만들고, 익스텐트 여러개가 모여 하나의 세그먼트를 구성하는 식입니다.

테이블 스페이스는 가장 상위개념

 

테이블스페이스의 종류 및 특징

-System TableSpace:

Control File 및 딕셔너리 파일이 저장

(모든 데이터 사전 정보와 저장 프로시저(Stored procedure),패키지,데이터베이스 트리거(trigger)등을 저장)

 

-SYAUX TableSpace:

Oracle 10g 버전부터 등장한기능으로 주로 Oracle 서버의 성능 튜닝을 위한 데이터들이 저장

 

-일반 TableSpace:

가장 일반적으로 사용되는 TableSpace로 관리자가 필요에 의해서 만드는 TableSpace,DBA의 뜻대로 생성하고 지울 수 있음.

 

 

테이블스페이스 생성

create tablespace [테이블 스페이스명]

datafile 'D:\dev\oradata' --파일경로

size 10M --초기 데이터 파일 크기 설정

autoextend on next 10M -- 초기 크기 공간을 모두 사용하는 경우 자동으로 파일의 크기가 커지는 기능

maxsize 100M -- 데이터파일이 최대로 커질 수 있는 크기 지정 기본값 = unlimited tablespace

uniform size 1M -- EXTENT 한개의 크기를 설정

 

●유저에게 테이블스페이스 부여

ALTER USER 유저이름 QUOTA 100M ON USERS; //QUOTA로 직접 지정가능, USER01.DBF 유저 데이터베이스파일
GRANT UNLIMITED TABLESPACE TO 유저이름;

전체 테이블 스페이스 조회

select * from dba_tablespaces ;

 

전체 테이블 스페이스 경로 및 용량 조회

 

SELECT A.TABLESPACE_NAME "테이블스페이스명",

           A.FILE_NAME "파일경로",

           (A.BYTES - B.FREE) "사용공간",

            B.FREE "여유 공간", A.BYTES "총크기",

            TO_CHAR( (B.FREE / A.BYTES * 100) , '999.99')||'%' "여유공간"

FROM

          ( SELECT FILE_ID,

            TABLESPACE_NAME,

            FILE_NAME,

            SUBSTR(FILE_NAME,1,200) FILE_NM,

            SUM(BYTES) BYTES

        FROM DBA_DATA_FILES

GROUP BY FILE_ID,TABLESPACE_NAME,FILE_NAME,SUBSTR(FILE_NAME,1,200)

) A,

(

   SELECT TABLESPACE_NAME,

              FILE_ID,

              SUM(NVL(BYTES,0)) FREE

        FROM DBA_FREE_SPACE

GROUP BY TABLESPACE_NAME,FILE_ID

) B

WHERE A.TABLESPACE_NAME=B.TABLESPACE_NAME

        AND A.FILE_ID = B.FILE_ID;

 

테이블의 테이블 스페이스 변경

alter table [테이블명] move tablespace [테이블 스페이스명];

 

테이블 스페이스 속성 변경

-- 해당 테이블스페이스의 물리적인 파일의 이름 또는 위치변경

alter tablespace rename [A] to [B];

 

-- 해당 테이블스테이스의 용량을 1024메가로 변경

alter tablespace [테이블스페이스명] add datafile [추가할데이터파일명] size 1024M;

 

-- 해당 데이터파일경로에 해당하는 테이블스페이스의 크기가 FULL이 되면 자동으로 100메가씩 증가.

alter database datafile [데이터파일경로] 'autoextend on next 100m maxsize unlmited;

 

테이블 스페이스 삭제

-- 테이블스페이스 내의 객체(테이블,인덱스등)를 전체 삭제

drop tablespace [테이블 스페이스명] include contents;

 

--테이블스페이스의 모든 세그먼트를 삭제. (데이타가 있는 테이블 스페이스 제외)

drop tablespace [테이블 스페이스명] including contents;

 

--삭제된 테이블스페이스를 참조하는 다른 테이블스페이스의 테이블로부터 참조무결성 제약 조건을 삭제

drop tablespace [테이블 스페이스명] cascade constraints;

 

--테이블 스페이스의 물리적파일까지 삭제

drop tablespace [테이블 스페이스명] including contents and datafiles;

---

전체적인 시스템권한 부여 순서

GRANT [시스템 권한] TO [사용자이름/롤(role)이름/PUBLIC]

[WITH ADMIN OPTION];

 

1.시스템 권한(필수):

-오라클 데이터베이스에서 제공하는 시스템 권한을 지정한다.

-한 번에 여러 종류의 권한을 부여하려면 쉼표(,)로 구분하여 권한 이름을 여러 개 명시하면 된다.

 

2.사용자 이름/롤[role]이름/PUBLIC(필수):

-권한을 부여하려는 대상 지정,여러 사용자나 롤에 적용할 경우 마찬가지로 쉼표(,)로 구분

(PUBLIC은 현재 오라클데이터베이스의 모든 사용자에게 권한을 부여한다는 의미)

 

3.WITH ADMIN OPTION(선택):

-현재 GRANT문을 통해 부여받은 권한을 다른 사용자에게 부여할 수 있는 권한도 함께 부여받는다.

(현재 사용자가 권한이 사라져도, 권한을 재부여한 사용자의 권한은 유지된다.)

 

 

●시스템 권한 조회

1. 사용자에게 부여된 시스템 권한 확인
SELECT * FROM DBA_SYS_PRIVS
WHERE GRANTEE = '사용자명' ;

-- 2. 사용자에게 부여된 롤 확인(시스템 권한이 롤에 포함됨)
SELECT * FROM DBA_ROLE_PRIVS
WHERE GRANTEE = '사용자명' ;

-- 3. 사용자에게 부여된 롤에 부여된 시스템 권한 확인
SELECT * FROM DBA_SYS_PRIVS
WHERE GRANTEE = '롤명' ;

-- 4. 타 사용자에게 부여한 객체(테이블등) 권한 확인
SELECT * FROM DBA_TAB_PRIVS
WHERE OWNER = '테이블소유자명' ;
또는,
SELECT * FROM DBA_TAB_PRIVS
WHERE GRANTEE = '권한부여자명' ;

 

---

 

객체 권한

-객체 권한은 특정 사용자가 생성한 테이블,인덱스,뷰,시퀀스 등과 관련된 권한

전체적인 객체 권한  부여 및 권한 취소

GRANT [객체 권한/ALL PRIVILEGES]

ON [스키마,객체 이름]

TO [사용자 이름/롤(ROLE) 이름/PUBLIC]

[WITH GRANT OPTION];

 

1.[객체 권한/ALL PRIVILEGES](필수)

:객체 권한 지정, 한 번에 여러 종류의 권한을 부여하려면 쉼표(,)로 구분하여 권한을 여러 개 명시

ALL PRIVILEGES로하면 객체의 모든 권한을 부여

 

2.[스키마,객체 이름](필수)

:권한을 부여할 객체를 명시

 

3.[사용자 이름/롤(ROLE) 이름/PUBLIC](필수)

:권한을 부여하려는 대상, 사용자 이름이나 롤(ROLE)을 지정할 수 있다.

마찬가지로 여러 사용자나 롤에 적용할 경우 쉼표(,)로 구분

PUBLIC으로 하면 현재 오라클 데이터베이스의 모든 사용자에게 권한을 부여

 

4.[WITH GRANT OPTION](선택)

:현재 GRANT문을 통해 부여받은 권한을 다른 사용자에게 부여할 수 있는 권한도 함께 부여

현재 권한을 부여받은 사용자의 권한이 사라지면, 다른 사용자에게 재 부여된 권한도 함께 사라진다.

 

 

GRANT DELETE, INSERT, SELECT, UPDATE ON 테이블명 TO 유저ID;

REVOKE DELETE, INSERT, SELECT, UPDATE ON 테이블명 TO 유저ID;

 

●객체 권한 조회

SELECT * FROM USER_SYS_PRIVS WHERE username = '사용자 이름';

---

 

 

DBA 권한 부여

GRANT CONNECT, RESOURCE, DBA TO 유저ID;

 

 

출처:

coding-factory.tistory.com/411

pridiot.tistory.com/128