Oracle DB 정리내용

Oracle DB 정리

1) Create directory in Oracle

   create or replace directory foo_dir as '/tmp';
  
   create directory some_dir;
   grant read, write on directory some_dir to micky_mouse;


2).SQL-Plus
 
   CONN / AS SYSDBA
   ALTER USER scott IDENTIFIED BY tiger ACCOUNT UNLOCK;

   CREATE OR REPLACE DIRECTORY test_dir AS '/u01/app/oracle/oradata/';
   GRANT READ, WRITE ON DIRECTORY test_dir TO scott;

   (1). Table Exports / imports

   expdp scott/tiger@db10g tables=EMP,DEPT directory=TEST_DIR dumpfile=EMP_DEPT.dmp logfile=expdpEMP_DEPT.log

   impdp scott/tiger@db10g tables=EMP,DEPT directory=TEST_DIR dumpfile=EMP_DEPT.dmp logfile=impdpEMP_DEPT.log

   (2). Schema Exports/Imports

   expdp scott/tiger@db10g schemas=SCOTT directory=TEST_DIR dumpfile=SCOTT.dmp logfile=expdpSCOTT.log

   impdp scott/tiger@db10g schemas=SCOTT directory=TEST_DIR dumpfile=SCOTT.dmp logfile=impdpSCOTT.log
      
   (3). Database Exports/Imports

   expdp system/password@db10g full=Y directory=TEST_DIR dumpfile=DB10G.dmp logfile=expdpDB10G.log

   impdp system/password@db10g full=Y directory=TEST_DIR dumpfile=DB10G.dmp logfile=impdpDB10G.log

3). [ORACLE] GRANT / CREATE / ALTER / SHOW / REVOKE 용법
   
   SHOW { TABLE [ tablename | viewname ] | INDEX [ tablename ] | TRIGGER [ tablename ] | STATUS | DATABASE | USER }


   DESC { tablename | viewname }


   CREATE DATABASE { dbname }


   DROP DATABASE { dbname }


   CREATE USER id password [ SUPERUSER ]


   DROP USER id


   ALTER USER id SET { PASSWORD newpassword | [ NO ]SUPERUSER }


   GRANT { ALL | SELECT | INSERT | UPDATE | DELETE | ALTER | TRIGGER | SHOW } ON { dbname.tablename | dbname.* | *.* | * } TO { username | PUBLIC }


   GRANT CREATE { ALL | TABLE | VIEW | INDEX | TRIGGER } ON { dbname | * } TO { username | PUBLIC }


   REVOKE { ALL | SELECT | INSERT | UPDATE | DELETE | ALTER | TRIGGER | SHOW } ON { dbname.tablename | dbname.* | *.* | * } TO { username | PUBLIC }


   REVOKE CREATE { ALL | TABLE | VIEW | INDEX | TRIGGER } ON { dbname | * } TO { username | PUBLIC }



   example )

   시스템 권한(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?
     * 객체마다 다르다.
     * 소유자는 객체에 대한 모든 권한을 갖는다.
     * 소유자는 자신의 객체에 대한 특정 권한을 부여할 수 있다.
  
   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 권한에 대한 정보