5/30 오후

* Synonym : 동의어

>> 먼저, 권한 주기

Command Line에서 :

SQL> conn / as sysdba

Connected.

SQL> GRANT create synonym TO hr;

Grant succeeded.

>> 돌아와서,

CREATE SYNONYM e

FOR employees;

SELECT * FROM e;

>> Synonym 삭제

DROP SYNONYM e;

SQL 1 끝.

SQL 2 시작.

Controlling User Access

• System 권한 : DB System에 ~를 할 수 있는 권한.
  

- CREATE TABLE

- CREATE VIEW

- CREATE SYNONYM

• Object 권한 : 계정 간의, ...의 ~를 Access할 수 있는 권한.

- SELECT 

- UPDATE

* System 권한

• 100개가 넘는다.

* Creating Users : 유저 만들기

CREATE USER user

IDENTIFIED BY password;

※ Oracle Dictionary의 종류

  dba_###s  ← 권한이 있는 DBA만 열람.

   └ all_###s

      └ user_###s  ← 범위가 가장 작음.

C:\oraclexe\app\oracle\product\11.2.0\server\sqlplus\admin 경로에 있는

glogin.sql 파일에 내용 추가하기

SET linesize 120  ← 예전에 추가한 내용

SET sqlp "_USER> "

이제 SQL*PLUS에서 접속하면 계정명을 쉽게 식별할 수 있다.

*

>> 권한 부여

GRANT 권한, ... TO 대상

                    └ 사용자, Role, Public

[WITH ADMIN OPTION] -> System 권한, Role

[WITH GRANT OPTION] -> Object 권한

-> 권한 부여 위임

>> 권한 회수

REVOKE 권한, ... FROM 대상, ...

Admin : DBA - HR - Scott

Grant : HR - Scott - Demo

* 객체 권한

  Access    ON Schema.object

• Select    ON scott.emp
• Insert
• Update
• Delete
• Alter
• Index
• Reference

* Role : 권한을 묶어서 이름을 부여한 것.

- 즉, 예를 들어 G1이라는 Role이 있다면, 그 안에 create session, create table, create view가 있을 수 있다.

- 권한 관리가 편리해진다.

• Role 생성하기 :

CREATE ROLE role;

• Role에 권한 추가 :

GRANT 권한, Role, ... TO role;

• Role에서 권한 제거 :

REVOKE 권한, Role, ... FROM role;

• 현재 사용자의 권한 확인하기 :

SELECT * FROM session_privs;

• 암호 바꾸기

ALTER USER scott

IDENTIFIED BY lion;

>> Role의 종류 중 아래의 3가지는 매우 중요하다.

• CONNECT
• RESOURCE
• DBA

※ 컬럼 폭 조정하기

-> col [컬럼명] for a##;

* Public 동의어 : PUBLIC SYNONYM

- View 생성

- Public 권한 부여

- Public 동의어 생성

- 이미 사용해 본 Public 동의어 : dual Table (원래 소유자는 sys)

Managing Schema Objects

* ALTER TABLE Statement

SELECT * FROM dept80;

ALTER TABLE dept80

ADD job_id VARCHAR2(15);  ← 열(column) 추가하기

DESC dept80;

ALTER TABLE dept80

MODIFY last_name VARCHAR2(20);  ← 데이터 타입 변경하기(안 되는 경우가 많음)

SELECT * FROM dept80;

ALTER TABLE dept80

MODIFY job_id DEFAULT 'IT_PROG';  ← 기본으로 들어갈 값 변경하기

UPDATE dept80

SET job_id = default;  ← 설정된 기본값으로 바꾸기

ALTER TABLE dept80

DROP COLUMN hire_date;  ← 컬럼 삭제하기

* SET UNUSED Option

저장 공간을 차지하고 있지만, 해당 컬럼을 보이지 않게 하여 사용하지 않게 하는 것.

ALTER TABLE dept80

SET UNUSED COLUMN job_id;  ← job_id 열 숨기기

DESC dept80;

select * from dept80;

ALTER TABLE dept80

DROP UNUSED column;  ← Unused 컬럼 모두 삭제

* Adding a Constraint Syntax

CREATE TABLE emp

AS

SELECT * FROM employees;

DESC emp;

※ 제약 조건 중 not null은 복사됨. (임의의 이름, Type은 C)

SELECT constraint_name, constraint_type FROM user_constraints

WHERE table_name = 'EMP';  ← 제약 조건 확인하기

ALTER TABLE emp

ADD primary key(employee_id);  ← Primary Key 만들기(이름 없이)

ALTER TABLE emp

DROP primary key;  ← Primary Key 삭제하기

ALTER TABLE emp

ADD CONSTRAINT emp_id_pk primary key(employee_id);  ← Primary Key 만들기(이름 지정하기)

SELECT constraint_name, constraint_type FROM user_constraints

WHERE table_name = 'EMP';  ← 다시 제약 조건 확인하기

* ON DELETE절

CREATE TABLE dept AS

SELECT * FROM departments;

ALTER TABLE dept

ADD CONSTRAINT dept_deptid_pk primary key(department_id);

ALTER TABLE emp

ADD CONSTRAINT emp_deptid_fk foreign key(department_id)

REFERENCES dept(department_id)

ON DELETE SET NULL;

※ ON DELETE 의미 : 부모가 지워질 때

- 처음부터 지정해 주지 않으면 수정이 되지 않는다.

SELECT table_name, constraint_name, constraint_type, status FROM user_constraints

WHERE table_name IN('EMP', 'DEPT');

SELECT * FROM dept;

UPDATE emp

SET department_id = 220

WHERE department_id = 10;  ← 불가

DELETE FROM dept

WHERE department_id = 80;  ← ON DELETE 설정 때문에 삭제가 가능하며,

SELECT * FROM emp;  ← 80번 부서에서 근무하는 사원들의 부서 번호가 NULL이 됨.

rollback;