http://blog.naver.com/kwangii/150006612263
[10] Creating and Managing Tables (DDL)
1. Database Objects
TABLE, VIEW, SEQUENCE, INDEX, SYNONYM
2. Object Naming Rule
- 반드시 첫글자는 문자이어야 한다.
- 길이는 1 ~ 30 글자 이다.
- A-Z, a-z, 0-9, _, $, # 만을 사용할수 있다.
- 동일한 Object명이 존재해서는 안된다.(다른 user가 소유한 table과는 중복가능)
- 오라클 서버의 예약어는 사용할 수 없다.
3. CREATING TABLES( create 권한, 저장영역이 필요 )
CREATE [GLOBAL TEMPORARY] TABLE [schema.]table
( column datatype [DEFAULT expr] [,...] );
*GLOBAL TEMPORARY : 임시 table로 지정, 정의는 모든 세션에서 볼 수 있지만,
데이터는 데이터를 삽입하는 세션에서만 볼 수 있다.
* DEFAULT 값: 다른 열의 이름이나, 의사열은 잘못된 값이다.
* CREATE TABLE dept
( deptno NUMBER(2), dname VARCHAR2(14), loc VARCHAR2(13),
dymd DATE DEFAULT SYSDATE )
4. Data Dictionary
USER_ : 사용자가 소유하는 객체에 대한 정보
ALL_ : 사용자가 ACCESS 할 수 있는 모든 TABLE 에 대한 정보
DBA_ : DBA 롤을 할당 받은 사용자만 ACCESS 할 수 있다.
V$_ : 동적 성능(Performance) 뷰, DB SERVER 성능 및 잠금에 관한 정보.
* DICT 테이블 안에 모든 data dictionary 정보가 있다.
>> 유저가 소유한 개별 객체 유형을 봄.
SELECT * FROM USER_TABLES
>> 유저가 소유한 TABLE, VIEW, 동의어, 시퀀스를 봄.
SELECT DISTINCT object_type
FROM USER_OBJECTS ( USER가 ACCESS 할 수 있는 ALL_OBJECTS )
>> User가 소유한 TABLE을 설명.
SELECT * FROM USER_CATALOG
--> SELECT * FROM cat 이라고 써도 된다.
>> 모든 테이블 이름보기 SELECT * FROM TAB
>> 컬럼 정보 보기 USER_TAB_COLUMNS
5. Data Types
VARCHAR2(size) : Variable-length character data (1 ~ 4000 bytes)
CHAR(size) : Fixed-length character data (1 ~ 2000 bytes)
NUMBER(p,s) : p - precision, s - scale
DATE : B.C.4712-01-01 ~ A.D.9999.12.31
LONG : Variable-length character data (2GB)
- 한 table 당 하나 - constraints 사용 못함
- order by, group by에서 사용못함 - 서브쿼리에서 사용할 때 not copy
LONG RAW : Raw binary data of variable length up to 2 gigabytes
RAW(size) : 2000 byte
CLOB : Single-byte character data up to 4 gigabytes
BLOB : Binary data up to 4 gigabytes
BFILE : binary data stored in an external file; up to 4 gigabytes
6. Subquery를 사용해서 테이블 복사하기(새로운 table 의 datatype 정의는 필요없다)
CREATE TABLE dept30
AS
SELECT empno, ename, sal*12 ANNSAL, hiredate
FROM emp
WHERE deptno = 30
* computed field일때 반드시 컬럼 alias를 줘야 한다. (ANNSAL)
* computed 컬럼으로 만들어진 컬럼 타입 NUMBER일 때 기존의 자릿수는 사라지고
최대자리수로 잡힌다.
* column, type, data 모든것이 복사된다.(NOT NULL constraints 만 상속된다.)
* key constraint는 정의되지 않는다.
* WHERE조건을 false가 되게 만들면 데이터는 복사되지 않는다.
7. 테이블에 컬럼 추가 하기
ALTER TABLE dept30
ADD ( job VARCHAR2(9) )
-> 열을 추가할 때 모든 행은 초기에 null 값을 가진다.( not null 제약조건 X )
8. 테이블에 있는 기존 컬럼 수정하기
ALTER TABLE dept30
MODIFY ( ename VARCHAR2(15) )
* 제약사항.
- 숫자타입에서 자릿수를 늘리는건 가능하다.
- 숫자타입에서 자릿수를 줄일때는 모든 data가 null이거나 데이터가 한건도 없을 때만 가능하다.
- char <--> varchar2 로 바꿀때는 모든 data가 null이거나 데이터가 한건도 없을 때만 가능하다.
- default값도 바꿀 수 있다.(기존 default값은 유지된다.)
- column name 은 modify 가 안된다.
=> 열을 추가(ADD), 수정(MODIFY)할 수 있지만 table에서 삭제할 수 없다.
<oracle 8i 가능>
1.> ALTER TABLE table SET UNUSED (column);
OR
ALTER TABLE table SET UNUSED COLUMN column;
2.>ALTER TABLE table DROP UNUSED COLUMNS;
9. DROP TABLE
DROP TABLE dept30
10. OBJECT 이름 바꾸기
RENAME dept TO department
11. TRUNCATE TABLE
TRUNCATE TABLE department
* ROLLBACK을 절대로 할 수 없다.
* 로그없이 테이블의 전체 데이터가 삭제되고 COMMIT이 된다.
* 저장공간을 해제( DELETE 문은 해제할 수 없다.)
12. 테이블에 주석문 추가하기
COMMENT ON TABLE emp
IS 'Employee Information'
13. 컬럼에 주석문 추가하기
COMMENT ON TABLE emp COLUMN emp.ename
IS 'Employee Name'
14. 주석문 삭제하기
COMMENT ON TABLE emp IS ' '
15. 주석문을 볼수있는 data dictionary
* Column
ALL_COL_COMMENTS
USER_COL_COMMENTS
* Table
ALL_TAB_COMMENTS
USER_TAB_COMMENTS
[11] Including Constraints
1. 오라클의 Constraint Type
NOT NULL : null value를 허용하지 않음.
UNIQUE : 유일한 값을 가지면서 null도 허용한다.
PRIMARY KEY : NOT NULL, UNIQUE, TABLE당 1개 설정, 참조 당함
FOREIGN KEY : Primary에 연결되어 있어서 Primary에 있는 값들만 갖고 있다.(PK,UK 참조)
CHECK : 정해진 값 이외의 것이 들어오면 Error
2. Constraint 규칙
- Constraint을 생성할 때 이름을 주지 않으면 오라클서버는 SYS_Cn 이라고 이름을 자동 생성한다.
- Column level 과 Table level로 Constraint를 정의한다.
- constraint를 볼 수 있는 Data Dictionary는
USER_CONSTRAINTS,USER_CONS_COLUMNS 이다.
3. Constraint 정의
* Column Level
column [CONSTRAINT constraint_name] constraint_type,
* Table Level
column,...
[CONSTRAINT constraint_name] constraint_type (column, ...),
(column, ...),
* not null은 반드시 column level로 정의를 내려야 한다.
4. NOT NULL Constraint : column level
5. UNIQUE Constraint : index 자동 생성
CREATE TABLE dept (
deptno NUMBER(2),
dname VARCHAR2(14),
loc VARCHAR2(13),
CONSTRAINT dept_dname_uk UNIQUE (dname) )
6. PRIMARY KEY Constraint
CREATE TABLE dept (
deptno NUMBER(2),
dname VARCHAR2(14),
loc VARCHAR2(13),
CONSTRAINT dept_dname_uk UNIQUE (dname) ,
CONSTRAINT dept_deptno_pk PRIMARY KEY (deptno) )
7. FOREIGN KEY Constraint
CREATE TABLE emp (
empno NUMBER(4),
ename VARCHAR2(10) [CONSTRAINT epm_ename_nn] NOT NULL,
job VARCHAR2(9),
mgr NUMBER(4),
hiredate DATE,
sal NUMBER(7,2),
comm NUMBER(7,2),
deptno NUMBER(7,2) NOT NULL,
CONSTRAINT emp_deptno_fk FOREIGN KEY (deptno)
REFERENCES dept (deptno) )
--> dept.deptno를 emp.deptno가 Foreign key로 사용하겠다는 뜻.
* column level로 정의할 때는...(FOREIGN KEY 키워드는 생략)
deptno NUMBER(7,2) NOT NULL
CONSTRAINT emp_deptno_fk REFERENCES dept (deptno),
* ON DELETE CASCADE
Foreign key로 연결된 parent data가 삭제될 때 child data도 모두 삭제 되게 만든다.
8. CHECK Constraint
..., deptno NUMBER(2),
CONSTRAINT emp_deptno_ck
CHECK (deptno BETWEEN 10 AND 99), ...
--> deptno의 값은 10에서 99 사이에 있는 값들만 입력이 가능하다는 뜻.
* CURRVAL, NEXTVAL, LEVEL, ROWNUM등 Pseudocolumn엔 허용되지 않는다.
* SYSDATE, UID, USER, USERENV 함수들을 호출할수 없다.
* 제약조건을 추가(ADD), 삭제(DROP)할 수 있지만 수정할 수는 없다.
* 제약조건을 설정(ENABLE) 또는 해제(DISABLE)합니다.
* MODIFT 절을 사용하여 NOT NULL 제약조건을 추가합니다.
9. ADD Constraint
*ALTER TABLE table_name
ADD [CONSTRAINT constraint] type (column),
ALTER TABLE emp
ADD CONSTRAINT emp_mgr_fk
FOREIGN KEY(mgr) REFERENCES emp(empno)
* Constraint 수정은 할 수 없다.
* not null constraint일 경우 ADD로 하지 않고 MODIFY로 한다.
(기존 행에 null data 값이 없어야 한다.)
10. DROP Constraint
*ALTER TABLE table_name
DROP (PRIMARY KEY| UNIQUE (column) |
CONSTRAINT constraint) [CASCADE];
ALTER TABLE emp
DROP CONSTRAINT emp_mgr_fk
* Primary를 삭제할 때 Foreign Key관계(종속된 제약조건)의 Constraint까지 DROP 하고 싶으면...
ALTER TABLE dept
DROP PRIMARY KEY CASCADE
* CASCADE CONSTRAINTS 절은 DROP COLUMN 절과 함께 사용됩니다.
ALTER TABLE test1 DROP (pk) CASCADE CONSTRAINTS;
11. DISABLE CONSTRAINT(CREATE TABLE, ALTER TABLE 문에서 사용)
: ENABLE CONSTRAINT 하기 전까지 실행을 멈춘다.
ALTER TABLE emp
DISABLE CONSTRAINT emp_empno_pk CASCADE
12. ENABLE CONSTRAINT(CREATE TABLE, ALTER TABLE 문에서 사용)
ALTER TABLE emp
ENABLE CONSTRAINT emp_empno_pk
* ENABLE 할 때는... 모든 data를 체크하기 때문에 시간이 오래 걸린다.
*
13. USER_CONSTRAINTS Data Dictionary
SELECT constraint_name, constraint_type, search_condition
FROM user_constraints
WHERE table_name = 'EMP'
--------------------------------------------------------
CONSTRAINT_NAME C SEARCH_CONDITION
------------------------- - --------------------------
SYS_C00674 C EMPNO IS NOT NULL
SYS_C00675 C DEPTNO IS NOT NULL
EMP_EMPNO_PK P
--------------------------------------------------------
14. USER_CONS_COLUMNS Data Dictionary
SELECT constraint_name, column_name
FROM user_cons_columns
WHERE table_name = 'EMP'
--------------------------------------------------------
CONSTRAINT_NAME COLUMN_NAME
EMP_DEPTNO_FK DEPTNO
EMP_EMPNO_PK EMPNO
EMP_MGR_FK MGR
SYS_C00674 EMPNO
SYS_C00675 DEPTNO
--------------------------------------------------------
[12] Creating Views
* Oracle8부터는 View에도 Data를 갖고 있을 수 있다.
* View를 사용하는 이유?
- 데이터베이스 access를 제한한다.
- 복잡한 쿼리를 쉽게 만든다.
- 데이터의 독립을 허용하기 위해
- 동일한 데이터로부터 다양한 결과를 얻기 위해
* view를 만들 때 ORDER BY절을 사용할 수 없다.
* Simple View에는 DML 문장을 수행 할 수 있지만, 함수 또는 데이터 그룹을 포함하지 못한다.
(복합뷰<Complex VIEW> 는 함수 또는 데이터 그룹을 포함할 수 있다.)
* GROUP함수, GROUP BY절, DISTINCT keyword등을 사용한 view에는 행을 delete 할 수 없다.
* 뷰를 사용한 데이터 엑세스
- USER_VIEWS에서 뷰 정의를 검색한다.(select 문의 텍스트는 LONG 열에 저장된다.)
- 뷰의 기본 테이블에 대한 액세스 권한을 확인한다.
- 데이터를 기본 테이블에서 검색 또는 갱신한다.
1. CREATE VIEW 문장
CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW view
[ (alias[, alias]...) ]
AS subquery
[WITH CHECK OPTION [CONSTRAINT constraint]]
[WITH READ ONLY]
OR REPLACE : 만들려고 하는 view가 이미 존재할 때 recreate한다.
FORCE : base table이 존재하지 않아도 view를 강제로 만든다.
NOFORCE : base table이 반드시 존재해야만 view를 만들 수 있다.
WITH CHECK OPTION : view를 만들 때 where조건에 있는 value를
View를 통해서 DML 문장으로 수정하는 것을 막는다.
WITH READ ONLY : view를 통해서 DML문장을 수행하는 것을 막는다.
2. Creating View 예제1
CREATE VIEW empvu10
AS SELECT empno, ename, job
FROM emp
WHERE deptno = 10 ;
--> 이렇게 만들어진 view의 column이름은 empno, ename, job이 된다.
3. Creating View 예제2
CREATE VIEW salvu30
AS SELECT empno employee_number, ename name, sal salary
FROM emp
WHERE deptno = 30 ;
--> column alias를 준 employee_number, name, salary가 view의 column이름이 된다.
4. VIEW 수정하기 (OR REPLACE)
CREATE OR REPLACE VIEW empvu10
(employee_number, employee_name, job_title)
AS SELECT empno, ename, job
FROM emp
WHERE deptno = 10 ;
5. Complex VIEW
CREATE VIEW detp_sum_vu
(naem, minsal, maxsa, avgsal)
AS SELECT d.dname, MIN(e.sal), MAX(e.sal), AVG(e.sal)
FROM emp e, dept d
WHERE e.deptno = d.deptno
GROUP BY d.dname ;
6. WITH CHECK OPTION 사용예
CREATE OR REPLACE VIEW empvu20
AS SELECT *
FROM emp
WHERE deptno = 20
WITH CHECK OPTION CONSTRAINT empvu20_ck ;(deptno 가 20만 작업 가능)
( constraint name을 주지 않으면 SYS_Cn으로 만들어 진다. )
* 예를들어 다음과 같은 update문장을 수행하면 error가 난다.
UPDATE empvu20
SET deptno = 10
WHERE empno = 7788 ;
--> ORA-01402: view WITH CHECK OPTION where-clause violation
7. WITH READ ONLY 사용예
CREATE OR REPLACE VIEW empvu10
(employee_number, employee_name, job_title)
AS SELECT empno, ename, job
FROM emp
WHERE deptno = 10
WITH READ ONLY ;
* 예를들어 다음과 같은 DELETE문장을 수행하면 error가 난다.
DELETE FROM empvu10
WHERE employee_number = 7782 ;
--> ORA-01752: Cannot delete from view without exactly one key-preserved table
8. View 없애기
DROP VIEW empvu10 ;
9. 만들어진 View를 볼때
SELECT * FROM salvu30 ;
* 인라인 view : FROM 절에 사용, 객체가 아님.
10. View를 통한 DML 작업 수행에 관한 규칙
*뷰가 다음을 포함한 경우 행을 제거할 수 없슴.
- 그룹함수 - GROUP BY절 - DISTINCT 키워드 - 의사열 ROWNUM 키워드
*뷰가 다음을 포함한 경우 데이터를 수정할 수 없슴.
- 위에서 언급된 모든 조건(제거할 수 없슴)
- ROWNUM 의사 열 : 하위 질의에서 반환되는 가 행에 1에서 시작하는 순차값을 할당.
*뷰가 다음과 같은 경우 데이터를 추가할 수 없슴.
- 뷰가 위에서 언급한 포함하는 경우.(제거, 수정)
- 기본 테이블에서 뷰에 의해 선택되지 않은 열에 NOT NULL 제약 조건이 있는 경우.
11. "TOP-N" 분석 수행
SELECT [column_list], ROWNUM
FROM (SELECT [column_list] FROM table
ORDER BY TOP-N_column)
WHERE ROWNUM <= N ( where 절은 < 또는 <= )
[13] Other Database Objects (Sequence, Index, Synonym)
1. Sequence?
- unique한 숫자를 자동으로 부여한다.
- 공유 가능하다.
- primary key 성격을 갖는다.
- cache 설정을 할 수 있기 때문에 빠른 속도를 낼 수 있다.
2. CREATE SEQUENCE 문장
CREATE SEQUENCE sequence
[INCREMENT BY n] : default 1
[START WITH n] : default 1
[{MAXVALUE n | NOMAXVALUE}]
[{MINVALUE n | NOMINVALEU}]
[{CYCLE | NOCYCLE}]
[{CACHE n | NOCACHE{] ; : default 20개의 cache
INCREMENT BY n : n만큼 증가 한다.
START WITH n : 처음 시작하는 숫자는 n이다.
MAXVALUE n : MAX값은 n이다.
NOMAXVALUE : MAX값은 무한대다. (10^27 OR -1) --> default
MINVALUE n : MIN값은 n이다.
NOMINVALUE : MIN값은 무한대다. (-(10^26) OR 1 ) --> default
CYCLE | NOCYCLE : MAX나 MIN값으로 갔을때 다시 처음부터 새로
시작할 건지를 설정한다. default는 NOCYCLE
CACHE n | NOCACHE : n만큼의 CACHE를 줄 것인지를 설정한다.
--> default는 cache 20으로 잡는다.
3. CREATE SEQUENCE 예제
CREATE SEQUENCE dept_deptno
INCREMENT BY 1
START WITH 91
MAXVALUE 100
NOCACHE
NOCYCLE ;
4. USER_SEQUENCES data dictionary
SELECT sequence_name, min_value, max_value, increment_by, last_number
FROM USER_SEQUENCES ;
* last_number는 다음에 sequence 가능 한 숫자를 보여준다.
5. NEXTVAL 과 CURRVAL
NEXTVAL : Sequence가 1개 증가된다.
CURRVAL : 현재 sequence 값을 보여준다.
* 사용가능
- SELECT문장에서 SELECT list
- INSERT문장에서 SELECT list, values절 list
- UPDATE문장에서 SET절
* 사용불가
- View의 SELECT list
- SELECT문장에서 DISTINCT keyword
- SELECT문장에서 GROUP BY, HAVING, ORDER BY절
- SELECT, DELETE, UPDATE문장 안에 있는 subquery
- CREATE TABLE, ALTER TABLE문장에서 DEFAULT expression
* CACHE가 20으로 정의 했을 때...
① INSERT INTO dept
VALUES (dept_deptno.NEXTVAL, ...) --> 1
② SELECT last_number FROM user_squences --> 21
③ SELECT dept_deptno.CURRVAL FROM dual --> 1
④ SELECT dept_deptno.NEXTVAL FROM dual --> 2
6. SEQUENCE 사용예
INSERT INTO dept( deptno, dname, loc )
VALUES (dept_deptno.NEXTVAL, 'MARKETING', 'SAN DIEGO')
--> 증가된 sequence값이 deptno에 저장된다.
* 현재의 sequence값을 보려면...
SELECT dept_deptno.CURRVAL FROM dual ;
* SELECT에서 sequence값 증가시키기
SELECT dept_deptno.NEXTVAL FROM dual ;
7. SEQUENCE 수정하기
ALTER SEQUENCE dept_deptno
INCREMENT BY 1
MAXVALUE 999999
NOCACHE
NOCYCLE ;
* sequence에 대한 ALTER privilege를 갖고 있어야 한다.
* maxvalue가 current value보다 적을 땐 반드시 drop하고 re-create해야 한다.
* START WITH 문을 변경할 수 없습니다.( 삭제 후 다시 생성 )
8. SEQUENCE 제거하기
DROP SEQUENCE dept_deptno ;
9. INDEX ?
- Retrieve를 빨리 하기 위해서
- DISK I/O를 줄일수 있다.
- 테이블과는 독립적이다.
* PRIMARY KEY or UNIQUE constraint 생성시 오라클 서버에서 자동으로 생성되는것과, CREATE INDEX를 사용해서 만들어지는 index가 있다.
* INDEX는 많이 만든다고 좋은게 아니다.
* INDEX는 이렇때 만드는게 좋다.
- WHERE절이나 JOIN조건에 자주나오는 column
- column의 범위가 클때
- null value를 많이 갖고 있는 column
- 전체 row에서 2~4% 정도의 row를 찾을때
10. CREATE INDEX
CREATE INDEX index
ON table (column[, column]...) ;
CREATE INDEX emp_ename_idx
ON emp (ename) ;
11. USER_INDEXES and USER_IND_COLUMNS data dictionary
SELECT ic.index_name, ic.column_name, ic.column_position, ix.uniqueness
FROM user_indexes ix, user_ind_columns ic
WHERE ic.index_name = ix.index_name
AND ic.table_name = 'EMP' ;
12. INDEX 제거하기
DROP INDEX emp_ename_idx ;
13. SYNONYM ?
user가 synonym을 사용하면 스키마를 별도로 명시할 필요가 없어진다.
14. CREATE SYNONYM
CREATE [PUBLIC] SYNONYM synonym
FOR object ;
* PUBLIC은 모든 user에게 synonym권한을 주는 것이고, DBA권한이 있는 사람만
사용이 가능하다.
* PUBLIC을 안쓰면 만든 사람만 사용한다.
CREATE SYNONYM d_sum
FOR dept_sum_vu ;
--> dept_sum_vu라는 view테이블을 d_sum으로 사용 하겠다.
15. PUBLIC SYNONYM
CREATE PUBLIC SYNONYM dept
FOR alice.dept
--> alice가 만든 dept테이블을 dept로 모든 유저에게 명시 하겠다.
16. SYNONYM 제거
DROP SYNONYM d_sum ;
* public으로 정의된 SYNONYM은 DBA만 제거가 가능하다.
[14] Controlling User Access (GRANT, REVOKE)
* 시스템 권한(System Privilege): 데이터 베이스를 액세스할 수 있다.
* 객체 권한(Object Privilege) : 데이터 베이스 객체 내용을 조작할 수 있다.
1. System Privilege? --> DBA권한
- Create new users ( CREATE USER )
- Remove users ( DROP USER )
- Remove tables ( DROP ANY TABLE )
- Backup tables ( BACKUP ANY TABLE )
2. Creating User
CREATE USER scott
IDENTIFIED BY tiger ;
3. system privilege에서의 GRANT
GRANT privilege [, privilege...]
TO user [, user...]
[WITH ADMIN OPTION] ;
* WITH ADMIN OPTION : dba가 권한을 주는 user에게도 admin 권한을 줄 수 있다.
* user system privilege(DBA 가 USER에게 할당 할 수 있는 권한)
CREATE SESSION : 테이터베이스에 connect하는 권한
CREATE TABLE : 테이블 만드는 권한
CREATE SEQUENCE : sequence 만드는 권한
CREATE VIEW : view 만드는 권한
CREATE PROCEDURE : stored prcedure, function 만드는 권한
GRANT create table, create sequence, create view
TO scott
--> scott에게 table, sequence, view만드는 권한을 준다.
4. ROLE : 사용자에게 부여할 수 있는 관련 권한을 하나로 묶어 명명한 그룹
Grant를 줄 role user를 만든후 그 role user에 grant를 주고,
role user의 권한을 각각의 user에게 넘겨준다.
① SQL> CREATE ROLE manager ;
Role created.
② SQL> GRANT create table, create view TO manager ;
Grant succeeded.
③ SQL> GRANT mananger TO brake, clock ;
Grant succeeded.
5. User Password 변경하기
ALTER USER user IDENITIFIED BY password ;
예) ALTER USER scott IDENTIFIED BY lion ;
6. Object Privileges?
* 객체마다 다르다.
* 소유자는 객체에 대한 모든 권한을 갖는다.
* 소유자는 자신의 객체에 대한 특정 권한을 부여할 수 있다.
5. User Password 변경하기
ALTER USER user IDENITIFIED BY password ;
예) ALTER USER scott IDENTIFIED BY lion ;
6. Object Privileges?
* 객체마다 다르다.
* 소유자는 객체에 대한 모든 권한을 갖는다.
* 소유자는 자신의 객체에 대한 특정 권한을 부여할 수 있다.
7. Object Privilege에서의 GRANT
GRANT object_priv [(columns)| ALL]
ON object
TO {user|role|PUBLIC}
[WITH GRANT OPTION] ;
* sue, rich 라는 user에게 emp 테이블을 select 권한을 준다.
GRANT select
ON emp
TO sue, rich ;
* scott, manager라는 user에게 dept 테이블의 dname,loc 컬럼을 update할 수 있게 권한을 준다.
GRANT update (dname, loc)
ON dept
TO scott, manager ;
* WITH GRANT OPTION
GRANT select, insert
ON dept
TO scott
WITH GRANT OPTION ;
--> scott에게 dept 테이블의 select, insert권한을 주면서 scott가 다른 유저에게도
이 권한을 줄 수 있게 한다.
* PUBLIC : 모든 유저에게 권한을 부여한다.
GRANT select
ON alice.dept
TO PUBLIC ;
--> 모든유저에게 alice가 만든 dept 테이블의 select권한을 준다.
* 모든 유저에게 모든 object권한을 주기
GRANT ALL
ON emp
TO PUBLIC ;
8. 권한 없애기 (REVOKE)
REVOKE {privilege [, privilege...] | ALL}
ON object
FROM {user[, user...]|role|PUBLIC}
[CASCADE CONSTRAINTS]
REVOKE select, insert
ON dept
FROM scott ; --> scott에게서 dept테이블의 select, insert권한을 없앤다.
* CASCADE CONSTRAINTS : 이 옵션을 않쓰면 revoke할 때 forien key
관계의 table을 revoke할 수 없다.
9. Privilege Grant를 볼 수 있는 Data Dictionary
ROLE_SYS_PRIVS : System privilege 권한에 대한 정보
ROLE_TAB_PRIVS : table(object) privilege 권한에 대한 정보
USER_ROLE_PRIVS : role정보
USER_TAB_PRIVS_MADE : 내가 다른 사람에게 준 TABLE 권한에 대한 정보
USER_TAB_PRIVS_RECD : 내가 다른 사람에게 받은 TABLE 권한에 대한 정보
USER_COL_PRIVS_MADE : 내가 다른 사람에게 준 COLUMN 권한에 대한 정보
USER_COL_PRIVS_RECD : 내가 다른 사람에게 준 COLUMN 권한에 대한 정보
|
|
|
