6강 - ALTER / DROP / COMMENT / DCL - GRANT,REVOKE / ROLE

 

1. ALTER의 추가

① ‘새로운’ 컬럼 추가(NULL값으로 채워짐)

** L 테이블(최악의 테이블)

b. ALTER TABLE 테이블명 ADD 컬럼명1 TYPE, 컬럼명2 TYPE ;

c. DEFAULT값 지정

   = ALTER TABLE 테이블명

       ADD 컬럼명1 TYPE DEFAULT(값);

d. CHECK KEY 지정

   = ALTER TABLE 테이블명

  ADD( 컬럼명1 TYPE CONSTRAINT 제약조건명 CHECK(조건식) );

 

② ‘기존’ 컬럼에 제약조건 추가

a. 기존 컬럼의 PRIMARY KEY 지정

   = ALTER TABLE 테이블명

       ADD CONSTRAINT 제약조건명 PRIMARY KEY(컬럼명);

 

 

 

2. ALTER의 변경

= ‘기존’ 컬럼의 데이터 TYPE 변경

a. MODIFY

   - 데이터가 없을때 = 문자 <-> 숫자 <-> 날짜

                                 = 확대, 축소 모두 가능

   - 데이터가 있을때 = CAHR <-> VARCHAR2

                                 = 확대만 가능(현재 존재하는 데이터와 비교했을 때)

   - ALTER TABLE 테이블명

     MODIFY 컬럼명 변경할 타입;

 

3. ALTER의 삭제

① 컬럼 삭제

a. ALTER TABLE 테이블명 DROP COLUMN 컬럼명;

b. SET UNUSED(임시삭제)

   = 임시삭제 : 진짜 지워진건 아니지만 보이지 않음

                  ALTER TABLE 테이블명

                  SET UNUSED COLUMN 컬럼명;

   = 임시로 삭제한 컬럼(SET UNUSED)을 실제로 삭제

       ALTER TABLE 테이블명

       DROP UNUSED COLUMN;

   ** SET UNUSED한 컬럼은 SYS에서 확인 가능

       = SELECT *FROM DBA_UNUSED_COL_TABS;

 

② 제약조건 삭제

a. 방법1

   = ALTER TABLE 테이블명

      DROP CONSTRAINT 제약조건 명(EX,MANAGER1_PNO_PK);

 

b. 방법2

   = ALTER TABLE 테이블명

     DROP CONSTRAINT PRIMARY KEY;

c. 외래키로부터 참조받고 있는 PK 삭제 방법(=CASCADE)

   = ALTER TABLE 테이블명

     DROP CONSTRAINT PRIMARY KEY CASCADE;

 

4. DROP

a. 테이블을 삭제

b. 휴지통

    - 테이블 지우기(휴지통으로 보내기)

                  = DROP TABLE 테이블명;

                  = 지우면 BIN$FsZeh6uGSgml7PDjDl9eiw==$0 이런거 생김

    - 휴지통 확인(테이블,제약조건..지운거 들어있음)

                  = SELECT *FROM RECYCLEBIN;

    - 휴지통 복원

                  = 방법 1 : FLASHBACK TABLE 테이블명 TO BEFORE DROP;

                  = 방법 2 : FLASHBACK TABLE "BIN$xbTCtzcmRDaf6oiuOCo73A==$0" TO     

                                  BEFORE DROP;

    - 휴지통 비우기

                  = PURGE RECYCLEBIN;

c. 휴지통으로 가지말고 바로 지우기

 = DROP TABLE 테이블명 PURGE;

 

5. TRUNCATE / DELETE의 차이 => 둘다 레코드 삭제

a. TRUNCATE = LOG파일에 기록 X

                       = 빠르다

                       = ROLLBACK 불가능-> RECOVERY 해야함

                       = TRUNCATE TABLE 테이블명 WHERE 조건절;

a. DELETE = LOG파일에 기록 O

                  = 느리다

                  = ROLLBACK, COMMIT 필요

                  = DELETE 테이블명 WHERE 조건절;

 

6. COMMENT

① 테이블에 COMMENT

a. 일반 테이블의 COMMENT 검색

   = SELECT *FROM USER_TAB_COMMENTS;

b. DICTIONARY(만들어져 있는 테이블)들에 대한 COMMENT 검색

   = SELECT *FROM DICTIONARY;

c. COMMENT추가

   = COMMENT ON TABLE 테이블명

  IS ‘추가할 내용’;

d. COMMENT지우기(NULL로 주기)

   = COMMENT ON TABLE 테이블명

  IS ‘’;

 

② 컬럼에 COMMENT

a. 컬럼의 COMMENT 검색

   = SELECT *FROM USER_TAB_COMMENTS;

b. 컬럼의 COMMENT 추가

   = COMMENT ON COLUMN 테이블명.컬럼명

    IS '추가할 내용';

 

7. DCL

a. GRANT : USER나 OBJECT에 권한을 부여할때 사용

b. REVOKE : USER나 OBJECT에 권한을 회수할때 사용

 

8. 권한의 종류

a. SYSTEM권한 = DBA가 USERS에게 주는 권한

                          = 많은 권한이 존재

   = SYS에서만 가능

b. OBJECT권한 = ‘DBA에게 SYSTEM권한을 받은 USER’가 ‘일반 USER’에게 주는

                             권한

                          = 추가(INSERT),변경(UPDATE),삭제(DELETE),검색(SELECT)의

                             4가지의 권한

   = 해당USER / SYS 둘다 에서 가능

 

9. 사용자 만들기(SYS에서)

a. 사용자 만들기

   = CREATE USER 사용자명

  IDENTIFIED BY 비번명

  DEFAULT TABLESPACE 테이블스페이스명(users)

  TEMPORARY TABLESPACE 임시저장소명;

b. 사용자 검색

   = SELECT *FROM DBA_USERS;

10. SYSTEM권한 주기(SYS에서)

a. 종류 - 전체 종류 보기 : SELECT *FROM SYSTEM_PRIVILEGE_MAP;

ex)

- SESSION(CONNECT) : 로그인권한

           - RESOURCE : CREATE,ALTER 등등 권한

b. 권한 주기

   = GRANT CREATE 권한명1, 권한명2.. TO 사용자명;

c. 사용자 권한 주기

   = GRANT CREATE USER TO 사용자명; USER->권한명

d. 권한 뺏기

   = REVOKE 권한명1, 권한명2.. FROM 사용자명;

e. 접근 방법

   - CMD에서 TEST로 로그인하고 PERSONNEL을 불러오려면

      = SELECT *FROM KIM.PERSONNEL;

     ** KIM=>스키마(KIM의 권한이 적혀있는 열쇠)라고 한다

11. OBJECT권한 주기(KIM에서)

a. 종류 - INSERT, DELETE, SELECT, UPDATE

b. 권한 주기

   - 한 사람에게

     = GRANT 권한명1,권한명2.. ON 테이블명 TO 사용자명;

   - 여러 사람에게

     = GRANT 권한명1,권한명2.. ON 테이블명 TO 사용자명1,사용자명2;

   - 모든사람에게

     = GRANT 권한명1,권한명2.. ON 테이블명 TO PUBLIC;

c. 권한 뺏기

   - 한 사람에게

     = REVOKE 권한명1,권한명2.. ON 테이블명 FROM 사용자명;

   - 여러 사람에게

     = REVOKE 권한명1,권한명2.. ON 테이블명 FROM 사용자명1,사용자명2;

   - 모든사람에게

     = GRANT 권한명1,권한명2..  ON 테이블명 FROM PUBLIC;
                 ** PUBLIC으로 권한준거는 PUBLIC으로만 권한 뺏을 수 있다

d. 권한 확인

    = SELECT *FROM USER_TAB_PRIVS_MADE;

12. OBJECT권한 주기(SYS에서)

a. 권한 주기

     = GRANT 권한명1,권한명2.. ON 주인사용자명.테이블명

   TO 사용자명1,사용자명2;

b. 권한 뺏기

     = REVOKE 권한명1,권한명2.. ON 주인사용자명.테이블명

   FROM 사용자명1,사용자명2;

13. ROLE (SYS에서) - SYSTEM권한

a. 네트워크에서의 그룹

b. 순서 : ROLE만들기

            -> GRANT명령어로 'ROLE에' 권한 부여

            -> ROLE을 'USER에게' GRANT명령어로 권한 부여

c. ROLE만들기

   = CREATE ROLE ROLE이름;

d. GRANT명령어로 'ROLE에' 권한 부여

   = GRANT CREATE 권한명1,권한명2.. TO ROLE이름;

   = 권한 회수 : REVOKE 권한명1 FROM ROLE이름;

e. ROLE을 'USER에게' GRANT명령어로 권한 부여

   = GRANT ROLE이름 TO 사용자명1,사용자명2;

   = 권한 회수 : REVOKE ROLE이름 FROM 사용자명;

f. 확인

  - ROLE에 부여된 SYSTEM권한 정보

                = SELECT *FROM ROLE_SYS_PRIVS WHERE ROLE =’ROLE이름’;

 

  - ROLE에 부여된 TABLE 권한 정보

                = SELECT *FROM ROLE_TAB_PRIVS WHERE TABLE_NAME ‘TABLE이름’;

  - 현재 USER에게 부여된 ROLE 권한

                = SELECT *FROM USER_ROLE_PRIVS;

 

14. ROLE (KIM에서) - OBJECT권한

= CREATE ROLE ROLE이름;

= GRANT SELECT(권한1),권한2 ON 사용자이름.테이블명 TO ROLE이름;