[ORACLE] SQL DEVELOPER 8일차 - 사용자 관리

jeong·2021년 6월 13일
0

Oracle(SQL) 오라클

목록 보기
16/16

사용자 관리

오늘 알면 좋은 내용 : GRANT를 가지고 ROW를 이용해 시스템 권한 주는 것(컨넥트, 리소스), ALTER 비번 변경, 락 -> 언락 만들기

사용자(USER) - 계정(ACCOUNT)

사용자(USER) : DBMS 서버에 접속하여 사용할 수 있는 허락받은 사용자 - 계정(ACCOUNT)

계정 관리는 관리자(SYS 또는 SYSTEM)만 가능

계정 생성

형식)CREATE USER 계정명
KIM 계정 생성 - 관리자 접속 세션
CREATE USER KIM IDENTIFIED BY 1234;

계정의 비밀번호 변경

  • 계정의 비번은 기본적으로 180일의 유효기간으로 설정

    형식)ALTER USER 사용자명 IDENTIFIED BY 비밀번호
    KIM 계정의 비밀번호 변경 - 관리자 접속 세션
    ALTER USER KIM IDENTIFIED BY 5678;

=> 계정 확인

  • DBA_USERS : 사용자 정보를 제공하는 딕셔너리
    --SELECT USERNAME,ACCOUNT_STATUS,DEFAULT_TABLESPACE,CREATED FROM DBA_USERS WHERE USERNAME='KIM';

락

계정 상태 변경 : OPEN(계정 활성화) 또는 LOCK(계정 비활성 - 접속 불가능)
--오라클 접속시 계정의 비밀번호를 5번 틀리면 계정 상태가 자동으로 LOCK 상태로 변경되어 접속 불가능
형식)ALTER USER 계정명 ACCOUNT {LOCK|UNLOCK}

KIM 계정의 OPEN 상태를 LOCK 상태로 변경 - 관리자 접속 세션

--SELECT USERNAME,ACCOUNT_STATUS,DEFAULT_TABLESPACE,CREATED FROM DBA_USERS WHERE USERNAME='KIM';
--ALTER USER KIM ACCOUNT LOCK;
--SELECT USERNAME,ACCOUNT_STATUS,DEFAULT_TABLESPACE,CREATED FROM DBA_USERS WHERE USERNAME='KIM';

KIM 계정의 LOCK 상태를 OPEN 상태로 변경 - 관리자 접속 세션

--ALTER USER KIM ACCOUNT UNLOCK;
--SELECT USERNAME,ACCOUNT_STATUS,DEFAULT_TABLESPACE,CREATED FROM DBA_USERS WHERE USERNAME='KIM';

계정의 기본 테이블스페이스 변경

  • 테이블스페이스(TABLESPACE) : 데이타베이스 객체(테이블,시퀀스,인덱스 등)가 저장되는 영역
    -> 시스템 테이블스페이스(SYSTEM-기본)와 사용자 테이블스페이스(USERS)
    형식)ALTER USER 계정명 DEFAULT TABLESPACE 테이블 스페이스명

KIM 계정의 기본 테이블스페이스를 SYSTEM에서 USERS로 변경 - 관리자 접속 세션

--SELECT USERNAME,ACCOUNT_STATUS,DEFAULT_TABLESPACE,CREATED FROM DBA_USERS WHERE USERNAME='KIM';
--ALTER USER KIM DEFAULT TABLESPACE USERS;
--SELECT USERNAME,ACCOUNT_STATUS,DEFAULT_TABLESPACE,CREATED FROM DBA_USERS WHERE USERNAME='KIM';

계정 삭제

형식)DROP USER 계정명

KIM 계정 삭제
-- 관리자 접속 세션에 작성

--DROP USER KIM;
--SELECT 

USERNAME,ACCOUNT_STATUS,DEFAULT_TABLESPACE,CREATED FROM DBA_USERS WHERE USERNAME='KIM';

DCL(DATA CONTROL LANGUAGE) - 데이타 제어어

: 계정에게 권한을 부여하거나 회수하는 명령
--오라클 권한(PRIVILEGE) : 시스템 권한(관리자) 또는 객체 권한(일반 사용자)
--롤(ROLE) : 시스템 권한을 그룹화하여 표현한 명칭

시스템 권한 부여

--형식)GRANT {PRIVILEGE|ROLE},{PRIVILEGE|ROLE},... TO 계정명 [WITH ADMIN OPTION][IDENTIFIED BY 비밀번호]
--계정명 대신 PUBLIC 키워드를 사용할 경우 모든 사용자에게 일괄적으로 시스템 권한 부여
--권한을 부여할 계정이 없는 경우 계정 자동 생성
--계정이 자동으로 생성될 경우 반드시 IDENTIFIED BY 구문으로 비밀번호 설정

객체 권한 : 사용자의 외부 스키마에 있는 객체 명령 사용 권한

INSERT,UPDATE,DELETE,SELECT 등의 명령과 관련된 명령
형식)GRANT {ALL|PRIVILEGE,...} ON 객체명 TO 계정명 [WITH GRANT OPTION]

  • ALL : 객체와 관련된 모든 명령 사용 권한 표현
  • WITH GRANT OPTION : 부여받은 객체권한을 다른 계정에게 부여하거나 회수할 수 있는 기능 제공

KIM 계정이 SCOTT 스키마의 존재하는 DEPT 테이블의 모든 부서정보 검색 - SQLPLUS 사용 : 검색 불가능
-> SCOTT 스키마에 있는 DEPT 테이블에 대한 SELECT 명령 사용 권한이 없으므로 에러 발생
--SQL>SELECT * FROM SCOTT.DEPT;

KIM 계정이 객체 권한을 부여받은 후 SCOTT 스키마의 존재하는 DEPT 테이블의 모든 부서정보 검색 -SQLPLUS 사용 : 검색 가능

SELECT * FROM SCOTT.DEPT;

--다른 계정에게 부여한 객체 권한 확인 - USER_TAB_PRIVS_MADE : 객체 권한을 부여한 정보를 제공하는 딕셔너리
SELECT * FROM USER_TAB_PRIVS_MADE;

--다른 계정에게 부여한 객체 권한 확인 - USER_TAB_PRIVS_RECD : 
SELECT * FROM USER_TAB_PRIVS_RECD;

롤(ROLE)

: 관리자가 계정의 시스템 권한을 보다 효율적으로 부여하거나 회수하기 위해 사용하는 시스템 권한 그룹

  • 오라클에서는 기본적으로 제공되는 롤 존재
  • CONNECT : 8개의 시스템 권한의 그룹 - CREATE SESSION,CREATE TABLE,ALTER SESSION,CREATE SYNONYM 등
    --RESOURCE : 객체 관련 시스템 권한 그룹 - CREATE TABLE,CREATE SEQUENCE, CREATE TRIGGER 등
    --DBA : 시스템 관리에 필요한 모든 시스템 권한 그룹 - 관리자 생성 관련 롤:

관리자가 LEE 계정에게 CONNECT와 RESOURCE 롤 부여 - 관리자 접속 세션

-- LEE 계정이 없는 경우 시스템 권한 부여시 자동 생성 : 비밀번호 설정 
--GRANT CONNECT,RESOURCE TO LEE IDENTIFIED BY 5678;

PL/SQL(PROCEDUARAL LANGUAGE EXTENSION TO SQL)

: SQL에 없는 변수 선언,선택 처리,반복 처리 기능을 제공하는 언어

세부분의 영역으로 구분하여 PL/SQL 작성
1.DECLARE 영역(선언부) : DECLARE - 선택
2.EXECUTABLE 영역(실행부) : BEGIN - 필수
3.EXCEPTION 영역(예외처리부) : EXCEPTION - 선택
영역에서 하나의 명령을 구분하기 위해 ; 사용
마지막 영역은 END 키워드로 마무리 후 ; 사용
PL/SQL 실행을 위해 마지막에 /를 사용

메세지를 출력할 수 있도록 세션의 환경변수 설정값 변경

SET SERVEROUT ON;

메세지를 출력하는 함수 : PL/SQL 실행부에서 호출하여 사용
형식)DBMS_OUTPUT.PUT_LINE(출력메세지)

간단한 메세지를 출력하는 PL/SQL 작성

BEGIN
    DBMS_OUTPUT.PUT_LINE('HELLO, ORACLE!!!');
END;
/

오라클에서 쓰는 값의 표현 방법

숫자값 100 NUMBER
문자값 'AAA' VARCHAR2
날짜값 '00/12/13' DATE

변수 선언과 초기값 입력 - 변수 선언은 선언부
형식)변수명 [CONSTANT] 자료형 [NOT NULL][{:=|DEFAULT} 표현식]

CONSTANT : 변수에 저장된 초기값을 변경하지 못하도록 설정하는 키워드 - 상수 선언
NOT NULL : 변수에 NULL 저장 불가능
:= : 대입연산자
표현식 : 변수에 저장되는 값에 대한 표현 방법 - 값,변수(저장값),연산식(결과값),함수(반환값)

선언된 변수의 저장값 저장 - 실행부
형식)변수명 := 표현식
스칼라 변수 : 직접 오라클 자료형을 이용하여 변수 선언

스칼라 변수를 선언하여 값을 저장하고 화면에 변수값을 출력하는 PL/SQL 작성

DECLARE
    VEMPNO NUMBER(4) := 7788;
    VENAME VARCHAR2(20) := 'SCOTT';
BEGIN
    DBMS_OUTPUT.PUT_LINE('사원번호 / 사원아름');
    DBMS_OUTPUT.PUT_LINE('--------------------------------');
    DBMS_OUTPUT.PUT_LINE(VEMPNO||' / '||VENAME);
    DBMS_OUTPUT.PUT_LINE('--------------------------------');
    VEMPNO := 7893;
    VENAME := 'KING';
    DBMS_OUTPUT.PUT_LINE(VEMPNO||' / '||VENAME);
END;
/

레코드 변수 : 테이블 행에 대한 모든 컬럼값을 저장하기 위한 변수 - VO 클래스

형식)레코드변수 레코드타입명

  • 레코드 변수를 선언하기 전에 레코드 변수에 대한 자료형(테이블 타입) 선언
    형식)TYPE 레코드타입명 IS RECORD(필드명 {자료형|변수명%TYPE|테이블명.컬럼명%TYPE} [NOT NULL][{:=|DEFAULT} 표현식],필드명 {자료형|변수명%TYPE|테이블명.컬럼명%TYPE} [NOT NULL][{:=|DEFAULT} 표현식],...)

레코드 변수 사용방법

형식)레코드변수.필드명

다른 변수와 달리 여러 값 한번에 저장할 수 있어 편하다 

--EMP 테이블의 사원번호,사원이름,업무,급여,부서번호를 저장할 수 있는 레코드 변수를 선언하고 EMP 테이블에서 사원번호가
--7844인 사원의 사원번호,사원이름,업무,급여,부서번호를 검색하여 레코드 변수에 저장 후 화면에 출력하는 PL/SQL 작성

DECLARE
    /* 레코드 타입 선언 */
    TYPE EMP_RECORD_TYPE IS RECORD(VEMPNO EMP.EMPNO%TYPE,VENAME EMP.ENAME%TYPE,VJOB EMP.JOB%TYPE
        ,VSAL EMP.SAL%TYPE,VDEPTNO EMP.DEPTNO%TYPE);
    
    /* 레코드 변수 선언 */
    EMP_RECORD EMP_RECORD_TYPE;
BEGIN
    /* 검색행의 컬럼값을 레코드 변수의 필드에 저장 */
    SELECT EMPNO,ENAME,JOB,SAL,DEPTNO INTO EMP_RECORD.VEMPNO,EMP_RECORD.VENAME,EMP_RECORD.VJOB,
        EMP_RECORD.VSAL,EMP_RECORD.VDEPTNO FROM EMP WHERE EMPNO=7844;
        
    DBMS_OUTPUT.PUT_LINE('사원번호 = '||EMP_RECORD.VEMPNO);
    DBMS_OUTPUT.PUT_LINE('사원이름 = '||EMP_RECORD.VENAME);
    DBMS_OUTPUT.PUT_LINE('업무 = '||EMP_RECORD.VJOB);
    DBMS_OUTPUT.PUT_LINE('급여 = '||EMP_RECORD.VSAL);
    DBMS_OUTPUT.PUT_LINE('부서번호 = '||EMP_RECORD.VDEPTNO);
    DBMS_OUTPUT.PUT_LINE('--------------------------------');     
END;
/

레코드 타입 없이 테이블의 행을 이용하여 래코드 변수 선언 가능
-> 레코드 변수의 필드명과 자료형은 테이블의 컬럼명과 자료형을 이용하여 생성
형식)레코드변수 테이블명%ROWTYPE

--EMP 테이블의 사원번호,사원이름,업무,급여,부서번호를 저장할 수 있는 레코드 변수를 선언하고 EMP 테이블에서 사원번호가 7844인 사원의 
--사원번호,사원이름,업무,급여,부서번호를 검색하여 레코드 변수에 저장 후 화면에 출력하는 PL/SQL 작성
DECLARE
    EMP_RECORD EMP%ROWTYPE;
BEGIN
    /* 검색행(단일행)의 모든 컬럼값을 레코드 변수의 동일 이름의 필드에 자동 저장 */
    SELECT * INTO EMP_RECORD FROM EMP WHERE EMPNO=7844;
    
    DBMS_OUTPUT.PUT_LINE('사원번호 = '||EMP_RECORD.EMPNO);
    DBMS_OUTPUT.PUT_LINE('사원이름 = '||EMP_RECORD.ENAME);
    DBMS_OUTPUT.PUT_LINE('업무 = '||EMP_RECORD.JOB);
    DBMS_OUTPUT.PUT_LINE('급여 = '||EMP_RECORD.SAL);
    DBMS_OUTPUT.PUT_LINE('부서번호 = '||EMP_RECORD.DEPTNO);
END;
/
profile
jeong
배우는 초보개발자
이전 포스트

[ORACLE] SQL DEVELOPER 7일차 - 뷰(VIEW)

0개의 댓글