오라클 DB 설계 및 활용 10일째 1번 내용

10일째 배울 내용은 아무래도 프로그래머 보다 관리자에게 중요한 권한을 주고 삭제 하는 것에 대해서 배우게 되겠네요.

*** 계정(사용자) 생성 및 권한 보안

SELECT * FROM SYSTEM_PRIVILEGE_MAP;
SELECT * FROM SESSION_PRIVS;

SELECT * FROM DBA_TABLESPACES
(USER 공간이 물리적으로 할당 되는 공간을 나타냄)

*** 계정 생성
CREATE USER USER1 IDENTIFIED BY PWD1;

모든 유저 확인
SELECT * FROM ALL_USERS;

SELECT USERNAME,USER_ID FROM DBA_USERS
WHERE USERNAME LIKE 'U%' ORDER BY USERNAME;

** 비밀번호 변경 (비밀 번호 잊었을 때..)
ALTER USER USER1 IDENTIFIED BY EAGLE;

현재는 계정을 만들기만 했기 때문에 접속은 불가 하다. 그렇기 때문에 권한을 주어야 하는 설정을 해 주어야 한다.

*** 테이블 스페이스 영역을 새로 만든다
CREATE TABLESPACE TEST DATAFILE
'C:\oracle\product\10.2.0\oradata\java\MYDATA.DBF' SIZE 100 M;

CREATE USER MY1 IDENTIFIED BY PWDMY DEFAULT TABLESPACE TEST
QUOTA UNLIMITED ON TEST TEMPORARY TABLESPACE TEMP;

*** DB 권한 부여 명령
GRANT CREATE SESSION, CREATE TABLE, CREATE SEQUENCE, CREATE VIEW, CREATE SYNONYM TO MY1; -- 권한 부여

TEST 에 모든 영역을 사용할 수 있다. 기본적으로 TEST 라는 테이블 스페이스
를 사용한다.

이제 MY1 이라는 계정을 들어가서 테스트를 해 보겠습니다.
MY1 계정에서 실행!
CREATE TABLE INWON(BUN NUMBER(2) PRIMARY KEY, IRUM VARCHAR2(10) NOT NULL);
INSERT INTO INWON VALUES(1,'다합격')
라고 실행하면 권한이 추가 되어 실행되는 것을 확인 할 수 있습니다.

(앞으로 모든 것은 계정명.테이블명.컬럼명으로 기억한다.)

SELECT * FROM MY1.INWON;

CREATE USER USER2 IDENTIFIED BY EAGLE2;
CREATE USER USER3 IDENTIFIED BY EAGLE3;

-- 일반 사용자에게 데이타 베이스를 관리할 수 있도록 하는 권한
GRANT CREATE SESSION, CREATE VIEW,CREATE SEQUENCE, CREATE SYNONYM, CREATE TABLE, CREATE TRIGGER, CRATE PROCEDURE TO 계정명

GRANT CREATE SESSION TO USER1; -- DB접근 가능
GRANT CREATE SESSION TO USER2 WITH ADMIN OPTION;
(WITH ADMIN OPTION 이란 자신의 계정의 권한을 다른사람에게도 부여하는
권한을 준다.)

GRANT CREATE SESSION TO USER1;

-- USER1 에서 비밀번호 변경하는 법
ALTER USER USER1 IDENTIFIED BY PWDUSER;
CREATE TABLE USER1_A(BUN NUMBER , IRUM CHAR(10));
(이걸 실행하면 USER1은 TABLE 을 CREATE할 권한이 없으므로 생성이 실패된다.)

-- 다시 SYSTEM 계정으로 간다
CREATE USER USER4 IDENTIFIED BY GOOD123;

GRANT CONNECT,RESOURCE TO USER4
(접속 과 자원 사용에 대한 권한을 부여한다.)
(DDL에 대한 권한을 부여한다. CREATE, ALTER, SELECT등의 권한)

-- 사용자에게 DML문 사용 권한을 설정하기
-- SCOTT 계정으로 들어간다.
GRANT SELECT ON SAWON TO USER1;
-- USER4 계정으로 들어간다.
SELECT * FROM SCOTT.SAWON;
(SCOTT계정의 내용을 확인 할 수 있다.)

-- SCOTT 계정으로 들어간다.
CREATE VIEW V_SCOTT AS SELECT SAWON_NO, SAWON_NAME, SAWON_PAY FROM SAWON;
라는 뷰를 만들어 준다.**
그리고
GRANT SELECT ON V_SCOTT TO USER1;
(USER1 에게 V_SCOTT의 권한을 부여한다.)
GRANT SELECT ON SCOTT.BUSER TO USER1;
(이렇게 각각의 테이블에 대한 권한을 줄 수 있습니다.)

GRANT SELECT, INSERT, UPDATE ON SAWON TO USER1, USER2;
(USER1, USER2 에게 SELECT, INSERT, UPDATE 권한을 부여한다)
GRANT ALL ON BUSER TO USER1;
(USER1 에게 모든 권한을 부여 한다. 위에 있는 말과 같다.)

-- USER1 계정으로 접속 하여 권한이 정상적으로 부여 되었는가 확인합니다.
UPDATE SCOTT.BUSER SET BUSER_NAME='기획실' WHERE BUSER.BUSER_NO=50;
(정상적으로 SCOTT 계정의 내용을 수정 할 수 있습니다.)

-- SCOTT 계정으로 가서 정상적으로 바뀌었나 확인한다.
SELECT * FROM BUSER;

SELECT * FROM USER_TAB_PRIVS_MADE
(현재 계정자가 GRANT 한 권한의 목록을 확인 할 때 사용하는 명령어)

SELECT * FROM USER_TAB_PRIVS_RECD
(현재 계정자가 누구에게 어떤 권한을 받았는지 확인 할 때 사용한다)

*----* 권한을 뺏을때 권한 삭제를 할 때 사용 하는 명령어, 권한 취소

REVOKE UPDATE, DELETE ON BUSER FROM USER1
(USER1에게 부여한 UPDATE, DELETE 권한을 삭제하였습니다.)
REVOKE ALL ON BUSER FROM USER2;
(USER2 에게 부여한 BUSER 테이블에 관한 모든 권한(ALL)을 삭제 한다.)

-- 이제부터 롤에 대해서 공부해 볼까요?

-- ROLE
CREATE USER HONGKILDONG IDENTIFIED BY HONG123;
GRANT CONNECT, RESOURCE TO HONGKILDONG;

CREATE ROLE INSA; ROLL을 생성
GRANT INSA TO HONGKILDONG;
(ROLL에 INSA 에 대한 롤 권한을 부여합니다.)
-- SCOTT 계정으로 들어갑니다.
GRANT SELECT ON SAWON TO INSA;
(SELECT 권한을 INSA ROLL에게 권한을 부여함)

SELECT * FROM SCOTT.SAWON;
(홍길동은 INSA에 속해 있으므로 권한이 부여됩니다.)
SELECT * FROM USER_ROLE_PRIVS;
(ROLE에 대한 정보를 표시한다.)

REVOKE INSA FROM USER1;
(ROLE 에서 권한 삭제, 권한제거, 권한 뺓음 한다.)
DROP ROLE INSA;
(ROLL을 제거 할 때 사용한다)
DROP USER HONGKILDONG;
(홍길동 유저를 삭제할 때 사용한다.)


1부는 여기 까지 입니다. 오 많이 나갔네요. ^^