24일차 2023-04-10

oracle

 

1. 사용자 생성 및 권한

 

DBA역할이 있는 최고 관리자(SYSTEM/SYS)로 접속해야 사용자 생성 및 권한부여 가능

 

1-1. 사용자 생성 및  암호 설정

 

CREATE USER 아이디 IDENTIFIED BY 암호
*사용자는 생성된 후 어떠한 권한도 가지지 못한다

 

 

1-2). 사용자에 권한 혹은 역할 부여 

권한부여 구문)
       
Grant 시스템 권한1[,시스템 권한2,...] |[역할1[,역할2,..]  
To 사용자1[,사용자2,..]|[역할1[,역할2,..]
[WITH ADMIN OPTION] //받은 시스템 권한을 다른 사용자에게 부여할 수 있는 권한(있지만 거의 안 줌)


        - 사용자는 데이타베이스에 액세스 하기 위해 
   시스템 권한(SYSTEM PRIVILEGE)이 필요하고 <=(시스템 권한은 create table, alter table drop table 이런 것)
           데이타베이스의 개체의 내용을 조작하기 위해 
   객체 권한(OBJECT PRIVILEGE)이 필요하다 <=(객체 권한은 table의 데이터를 건들 수 있는 것. select, delete...)

- 시스템 권한의 종류는 200개 이상이고 
  개체권한은 28개 이다(10G버전 기준)

- 시스템 권한은 주로 DBA가 부여한다.(SYS/SYSTEM)

- DBA는 상급의 시스템권한을 부여한다

 

1.CREATE USER:사용자 생성
  2.DROP USER:사용자 제거
  3.DROP ANY TABLE:테이블 제거 <=(any는 웬만하면 주면 안 된다.(보안개념으로) 어떤 테이블도 삭제할 수 있는 권한)
  등

 

 -시스템 권한: 
SQL>SELECT * FROM SYSTEM_PRIVILEGE_MAP;

* ALTER ANY 계열: 
ALTER ANY TABLE, ALTER ANY PROCEDURE, ALTER ANY INDEX,
                                    ALTER ANY USER 등(자체 스키마뿐만 아니라 모든 스키마에서)
  CREATE ANY 계열: 
                             CREATE ANY TABLE,CREATE ANY PROCEDURE, CREATE ANY INDEX 등
     (자체 스키마 뿐만 아니라 모든 스키마에서)
  CREATE 계열: 
                            CREATE SESSION, CREATE TABLE, CREATE VIEW, CRETAE USER 등
    (자체 스키마에서만)


       CREATE SESSION:접속 가능(conn 아이디/암호)
       CREATE TABLE:테이블 생성 권한 
    
       CREATE VIEW :뷰 생성 권한
       CRETAE USER:사용자 생성 권한등

 

-개체 권한
-시스템 권한을 주면 (CREATE TABLE) 
 자동으로 개체 권한(INSERT/SELECT/DELETE/UPDATE등이 부여된다)

SQL> SELECT * FROM TABLE_PRIVILEGE_MAP;

SELECT/INSERT/UPDATE/DELETE 등

 

 *역할
       DBA:최고 권한
       CONNECT:DB에 액세스 할 수 있는 권한(보통 권한 2개 중 하나)
       RESOURCE:개체를 생성할 수 있는 권한(보통 권한 2개 중 둘 이 둘을 줌)

 

 

예 1] GRANT CREATE TABLE, CREATE SESSION, CREATE VIEW TO 사용자아이디 

   사용자에게 테이블을 생성하고 접속하고 VIEW를 생성할 수 있는 권한 부여

 
예 2] GRANT CONNECT, RESOURCE TO  사용자 아이디

  사용자에게 접속 및 테이블 생성 권한 동시에 주기(VIEW생성 권한은 없음)

 

1-3). 암호 변경 및 할당량 부여

       -암호 변경

ALTER USER 사용자아이디 IDENTIFIED BY 새 암호

       -할당량 부여
         
*CREATE TABLE권한을 GRANT 한 후에는 할당량을 부여해야 
테이블을 생성할 수 있다
  
ALTER USER  사용자아이디 QUOTA  할당량 On Users;
예] ALTER USER  Choi QUOTA  5M On Users;

 

1-4). 권한이 없는 다른 사용자에게 특정 테이블 조회 권한 부여하기

GRANT  SELECT ON 다른 사용자아이디. 테이블 TO 사용자 아이디

예] GRANT SELECT ON SCOTT.EMP TO CHOI;

-조회하기
SELECT * FROM SCOTT.EMP

 

1-5). 역할을 통해 권한 동시에 주기


1) ROLE 생성

- CREATE ROLE STAFF;//STAFF이라는 ROLE생성(아무 권한이 없음)
2) ROLE에 권한 부여
- GRANT CREATE TABLE, CREATE VIEW TO  STAFF;

3) 사용자의 ROLE  권한 부여
-GRANT STAFF TO 사용자 1, 사용자 2;
--> 사용자 1, 사용자 2라는 사용자에 STAFF이라는 ROLE부여.

 

1-6). 권한제거

REVOKE 권한 및 역할 FROM 사용자;

*단, WITH ADMIN OPTION으로 부여된 권한은 취소되지 않는다.

 

1-7). 사용자 삭제

Drop USER 사용자 CASCADE;

*사용자 목록 보기
SQL> DESC DBA_USERS
SQL> SELECT * FROM DBA_USERS
*사용자가 가진 시스템권란 보기
SQL> DESC dba_sys_privs
SQL> select privilege from dba_sys_privs where grantee='USER01'

*사용자 계정 잠금 풀면서 동시에 암호변경
sql> alter user 사용자계정 account unlock identified by 암호(풀면서 암호 설정)

sql> alter user 사용자계정 account unlock;계정푼거
sql>alter user 사용자계정 identified by 암호;암호부여

 

실습)

 

SYSTEM 계정에서 sql

 이제 user01에서 temp table생성 가능

 

grant create view to user01;--실행 //이전에 user01에서 

alter user user01 identified by user_01;--실행 이제 접속 풀고 다시 들어갈 때 비번 user_01

alter user user01 account lock;--실행 계정 잠금
alter user user01 account unlock;--실행 계정 잠금해제

4. 권한이 없는 다른 사용자에게 특정 테이블 조회 권한 부여하기

ex)

GRANT SELECT ON SCOTT.EMP TO user01;--하면 이제 emp 객체에 대한 권한 가짐

 

6. 권한제거

ex)

revoke SELECT ON SCOTT.EMP from user01;--권한 회수

7. 역할 생성, 부여

create role user_role;--역할 생성

grant connect, resource to user_role;--역할에게 역할부여

8. 계정 삭제

drop role user_role;--실행 계정 삭제
drop user user01 cascade;--실행 계정 삭제

user01 계정에서 sql

참고) 테이블 스페이스(인터넷, 카페에서 찾아보기)

( --오류 메시지에서 an extent(table에서 한 단위) 나왔음

*Cause:    User does not have privileges to allocate an extent in the
           specified tablespace.

-오라클의 논리적인 저장단위

 

 block < extent < segment <tablespace < databases

)

 

예제 sql 시작

create table temp(
    col1 number
);

 

create or replace view vw_temp
as
select * from temp;--오류 view 생성 권한이 없다

system에서 권한 주고 다시 하면 됨.

 

select * from scott.emp;--처음엔 에러 객체 권한 받아야 됨

 

 

 

 

2. [PL/SQL(PROCEDURAL LANGUAGE/SQL)<=나만의 함수 만들 때 사용/프로시져 만들때 사용

 

 DECLARE(선언부)
변수 및 상수, CURSOR, EXCEPTION선언등  //선언부-선택사항

 BEGIN(pl/sql 블락)

         SQL, PL/SQL문;   //실행부-필수사항

         EXCEPTION
         BEGIN

         예외처리 ; //예외처리부-선택사항

         END;

 END;
 /   ----->반드시 붙여라(전체 블락은 반드시 /로 끝내야 한다)

 

 -PL/SQL블락 내에서 한 명령문이 종료할 때마다 세미콜론(;)을 붙인다.
 -END뒤에 세미콜론(;)을 붙여 하나의 BLOCK이 끝났다는 것을 명시
 -마지막에 반드시 / 를 붙여야 한다.

 

3. 주석
--:한 줄 주석
/* 여러 줄 */:여러 줄 주석

 

4. 변수선언
         
변수명 [CONSTANT] 자료형 [NOT NULL] [:= 초기값 | DEFAULT 초기값 ];

constant 오면 상수

:=초기값 => 선언과 동시에 초기화

 

ex)

v_num NUMBER;
v_name VARCHAR2(10) :='홍길동';  //v_name VARCHAR2(10) DEFAULT '홍길동'; (두 가지 방법으로 초기화 및 선언)

V_PI CONSTANT  NUMBER(3,2) := 3.14; --전체 자릿수는 3이며 소수점 자릿수는 2

 

-한 라인에 하나의 식별자만 가능
-상수선언에서 CONSTANT는 자료형보다 먼저 기술
-대소문자를 구분하지 않는다.

 

5. 변수에 값 할당

-지정 연산자(:=)를 이용해서 값 할당.=> 자바의 대입 연산자와 같다

변수명 := 값;

 

 

6. 바인드 변수 선언

-호스트 환경에서  선언된 변수
-PL/SQL 프로그램의 내부나 외부에서 전달하기 위해서 사용
-PL/SQL문이 프로시저나 함수 안에 잇지 않는다면 
 호스트에서 선언된 변수(바인드 변수)를 
 PL/SQL문장에서 참조할 수 있다.

-호스트 변수와 PL/SQL 내에서의 변수를 구분하기 위해 
 호스트 변수 앞에 콜론(:)을 붙인다.

-선언만 할 수 있고 호스트 변수는 선언 시에 값을 할 당 못한다.

-선언 방법

VAR [IABLE] 변수명 자료형


사용 예 1]
SQL> VARIABLE return_var NUMBER
SQL> DECLARE -- 선언부 없으면 생략 가능
 1  BEGIN
 2            :return_var:=300;  
 3   END;
      /
SQL> PRINT return_var
혹은
SQL> SELECT :RETURN_VAR FROM DUAL;

여러 개인 경우
print 호스트변수 1 호스트변수 2 (스페이스바로 구분)

select :호스트변수 1,:호스트변수 2 from dual;
사용 예 2]


SQL> DECLARE 
    v_name VARCHAR2(10);
1  BEGIN
2          v_name:='Hello World';
3   END;
      /
SQL> PRINT v_name   --블락 안에서 선언된 변수는 호스트에서 알 수 없음

 

 

7. 연산자

-산술 연산자 : +,-,/,*
-비교 연산자: =,!=,<,>, <=,>=,IS NULL, BETWEEN, IN
-논리 연산자:AND , OR, NOT 등

 

8. PL/SQL문장 SELECT문장 사용 시 주의 사항

-SELECT 문은 하나의 행만 RETURN 해야 한다. 하나이상 이거나 없을 시
 PL/SQL은  TOO_MANY_ROWS와  NO_DATA_FOUND 예외를 발생시킴.
 여러 행을 리턴시에는 CURSOR를 사용.
 
-SELECT문장을 PL/SQL문에서 사용 시에는 INTO절을 사용.
-INTO절에서는 테이블의 열과 INTO절에서의 출력 변수의 수가 동일.

 

9. IF문

IF condition THEN

statements;

[ELSIF condition THEN statements;]  --ELSIF E가 반드시 빠져야 함

[ELSE statements;]

END IF;

 

ex)

 

10. FOR문
-인덱스 카운터는 자동으로 선언된 변수
-FOR문은 1씩밖에 증가 못한다.
-※항상 초기값이 종료값보다 작아야 한다.
    초기값이 더 크면 반복하지 않는다
        예] for(int i =초기값 ; i <=종료값 ;i++)
reverse도 마찬가지다

FOR 인덱스 카운터 IN [REVERSE] 초기값 .. 종료값 LOOP

                statement1;
                statement2;
END LOOP;

 

결과)

에러문제 나중에 기입(기입하면 삭제)

11. while 문

 

12. LOOP문

LOOP

statement1;
statement2;

EXIT  [WHEN condition];

END LOOP;


소스 예]

ACCEPT NUM PROMPT '숫자를 입력하세요?'
       10
DECLARE
HAP NUMBER :=0;
MYNUM NUMBER := &NUM;
10
BEGIN
LOOP
HAP := HAP +MYNUM;
           0+10 +9  +8.........1    
MYNUM := MYNUM-1;
                             0
EXIT WHEN MYNUM=0;
END LOOP;
DBMS_OUTPUT.PUT_LINE(&NUM || '까지의 누적합:' || HAP);
END;
/

결과) 1=1는 true 10 숫자 입력하면 10 출력 10-1=9는 짝수 아님, 한 번 더 루프 9 출력 9-1=8 은 짝수 여서 exit

commit은 저장

13. 예외처리

EXCEPTION: PL/SQL에서 발생하는 ERROR

-ORACLE Server 에러가 발생하면 이와 관련된 EXCEPTION이 자동 발생

- 예외처리 방법]
    
  DECLARE 
    
  BEGIN
  . . . 
  
  
  
       EXCEPTION
   BEGIN
   WHEN exception명1 THEN
statement1
   WHEN exception명2 THEN
  . statement2
WHEN OTHERS THEN
  . statement3
  END;
  END;

 

 

 14. NON-PREDEFINED EXCEPTION

- 사용자가 선언부에 EXCEPTION명을 정의하고 ORACLE Server에서 제공하는 
  error번호를 사용하여 Error와 연결한 후 예외처리부에서 Error처리
 예]
 DECLARE 
    exception명 EXCEPTION;
    PRAGMA EXCEPTION_INIT(exception명, 에러번호);
   BEGIN
  . . . 
   EXCEPTION
   WHEN exception명 THEN
  . . . 
  END;

  예]
  Ex)

CREATE OR REPLACE PROCEDURE 프로시저명(매개변수)

IS

 사용자정의예외명        EXCEPTION;

  PRAGMA           EXCEPTION_INIT(사용자정의예외명, 에러번호);

BEGIN
  실행명령문
   COMMIT;

EXCEPTION

  WHEN 사용자정의예외명 THEN

    ROLLBACK;

    DBMS_OUTPUT.PUT_LINE('에러메시지');

END;

결과)

 

15. USER DEFINE EXCEPTION

- 사용자가 정한 조건이 만족되지 않을 때, Error를 발생
 DECLARE 
  exception명 EXCEPTION;
  BEGIN
  
  RAISE exception명 ;
  . . . 
  EXCEPTION
  WHEN exception명 THEN
  . . . 
  END;

 

 

결과) 홀수만 입력 짝수면 '짝수는 안돼요' 출력