[Day2] Oracle 2 - SELECT, DCL(Privilege), DML, Role

[Day2] Oracle 2 [9/26]

 

[복습]

- PL/SQL : 절차적인 언어 (오라클만)

- 계정 유무 확인

 ㄱ. scott 계정 존재 유무 확인하는 DQL 작성
        1) 관리자로 로그인 
        SELECT *
        FROM all_users;
        
        접두사 ( 뷰View )
        all_              users  : 모든 사용자 정보를 조회
        dba_              users  : 모든 사용자 정보를 조회 +  비밀번호 포함 및 추가 정보
        user_              users
        
   ㄴ.  존재하면 계정 삭제하고
        존재하지 않으면 계정 생성하는 DDL 작성
        
        DROP USER scott CASCADE;
        
   ㄷ.  scott 계정으로 접속할 때 필요한 권한 부여하는 DCL 작성

      GRANT CREATE SESSION TO scott; -- 권한 부여
      GRANT CONNECT TO scott;  -- 롤 부여

 

default 테이블 스페이스는 계정마다 다르다! (보안때문에) 

 

SID : 시스템 식별자 / 여러 오라클이 port 1521 찾아오도록 구분해주는 것 (XE)

1. 모든 사용자 계정 조회(검색) : DQL ( select )

[select 문 사용 ]

1) 조회 대상( subquery, 하나 이상의 테이블, 뷰(materialized view) )으로 부터 데이터 가져오는데 사용하는 문.

2) 조회 대상의 소유자, 조회 대상의 SELECT 권한

3) SELECT ANY TABLE 시스템권한을 가지고 있으면 모든 테이블, 뷰를 조회 가능

4) select 형식)

 

【형식】

[subquery_factoring_clause] subquery [for_update_clause];

【subquery 형식】

{query_block ¦

subquery {UNION [ALL] ¦ INTERSECT ¦ MINUS }... ¦ (subquery)}

[order_by_clause]

【query_block 형식】

SELECT [hint] [DISTINCT ¦ UNIQUE ¦ ALL] select_list

FROM {table_reference ¦ join_clause ¦ (join_clause)},...

[where_clause]

[hierarchical_query_clause]

[group_by_clause]

[HAVING condition]

[model_clause]

【subquery factoring_clause형식】

WITH {query AS (subquery),...}

 

5)(암기) SELECT 문 = 절+절+절+절... 7절

처리 순서를 적으세요.

 

 

1 [WITH] 생략 가능

6 SELECT 조회컬럼,,,,, *

2 FROM 조회 대상(서브쿼리,테이블,뷰)

3 [WHERE]

4 [GROUP BY]

5 [HAVING]

7 [ORDER BY]

5. SELECT 문 사용되는 키워드 : DISTINCT, ALL , AS

 

--[ Ora_Help ]

;

SELECT *

FROM user_users; -- 현재 접속한 사용자 정보 + Access 사용자 정보

FROM View;

FROM dba_users; -- SYS( DBA )

USERNAME USER_ID PASSWORD ACCOUNT_STATUS 추가 컬럼 정보

SYS 0 OPEN 21/02/05 SYSTEM TEMP

SYSTEM 5 OPEN 21/02/05 SYSTEM TEMP

ANONYMOUS 35 OPEN 14/11/25 SYSAUX TEMP

FROM all_users; -- 테이블, 뷰

USERNAME USER_ID CREATED 컬럼명

XS$NULL 2147483638 14/05/29

SCOTT 51 22/09/26

APEX_040000 47 14/05/29

APEX_PUBLIC_USER 45 14/05/29

-- NLS 날짜형식 RR YY 차이점(년도)/MM(월)/DD(일)

-- SCOTT 51 22/09/23

PRIVILEGE == 권한

ROLE ( 롤, 역할 ) == PRIVILEGE GROUP 권한의 그룹(집합)

 

DATABASE SCHEMA ( 스키마 ) ==

1. SCOTT 계정을 생성하면 자동으로 SCOTT이라는 이름의 스키마가 생성된다.

생성계정명 == 스키마명 ( 동일 )

2. 스키마는 생성된 계정과 관련된 모든 객체들을 스키마라한다.

USER 생성

다음과 같은 형식으로 USER를 생성한다.

【형식】

CREATE USER 사용자명

IDENTIFIED BY 비밀번호 또는 IDENTIFIED EXTERNALLY

[DEFAULT TABLESPACE 테이블스페이스명]

[TEMPORARY TABLESPACE 테이블스페이스명]

[PROFILE 프로파일명]

[QUOTA 할당량 ON 테이블스페이스명 또는 UNLIMITED ON 테이블스페이스명]

[PASSWORD EXPIRE]

[ACCOUNT LOCK 또는 UNLOCK];

[ **** 계정 수정 *** ]

변경할 수 있는 USER 속성

• ALTER USER 문을 사용하여 user속성을 변경할 수 있다.

• USER의 변경사항은 현재의 SESSION에는 영향을 주지 않고 다음에 접속한 SESSION부터 영향을 받는다.

• ALTER USER 문을 사용하여 user속성을 변경할 수 있는 속성은 다음과 같다.

항목 의미

password USER의 password를 변경할 수 있음

OS 인증 USER를 OS인증/Oracle Server 인증으로 변경할 수 있음

DEFAULT TABLESPACE DEFAULT TABLESPACE를 변경할 수 있음

TEMPORARY TABLESPACE TEMPORARY TABLESPACE를 변경할 수 있음

TABLESPACE당 QUOTA TABLESPACE당 할당량을 변경할 수 있음

만약, 할당량을 0으로 하면 기존 object에 대한 소유는 가능하지만

차후 추가 할당을 받지 못함

PROFILE DBA가 RESOURCE를 변경하고 싶을 때 USER의 PROFILE을 변경함

DEFAULT ROLE USER의 업무가 바뀐 경우에는 그에 해당하는 DEFAULT ROLE로 변경함

-- SYS 접속 ( 빨강 테두리 )

USER 삭제

【형식】

DROP USER 사용자명

[CASCADE]; 사전적 의미 : 작은 폭포, 폭포처럼 흐르다. **** [Cascade]SS

해당 계정의 소유한 모든 객체(스키마) 같이 삭제.

DROP USER scott CASCADE;

-- ORA-01940: cannot drop a user that is currently connected

-- ORA-01922: CASCADE must be specified to drop 'SCOTT'

-- User SCOTT이(가) 삭제되었습니다.

• user삭제시 현재 DB에 접속중인 사용자는 삭제할 수 없다.

현재 접속중인 사용자는 그 사용자의 SESSION이 끝나야 한다.

# sqlplus / as sysdba

SQL> DROP USER kim cascade;

User dropped.

-- SCOTT 계정 생성

CREATE USER scott IDENTIFIED BY tiger;

-- User SCOTT이(가) 생성되었습니다.

상태: 실패 -테스트 실패:

ORA-01045: user SCOTT lacks CREATE SESSION privilege; logon denied

-- CREATE SESSION 권한 부여. DCL( grant )

---

권한( privilege )의 2가지 종류

1) 시스템 권한 : 객체 생성,수정, 삭제하는 권한

2) 객체 권한 : 객체의 내용을 추가,수정,삭제 검색 권한

Grant 권한( Create Session ) TO 모든 사용자 계정; 대상( 사용자, 롤 )

롤( Role )                                          PUBLIC

GRANT CREATE SESSION TO scott;

--0 모든 시스템 권한 목록을 확인(조회) - view

SELECT *

FROM system_privilege_map;

FROM dba_sys_privs; (dba 붙은건 관리자만 사용가능!)

-- 롤( ROLE ) 이란 ? --

1) 효과적으로 권한을 관리하기 위한 그룹을 롤이라고 한다.

2) 사용자 다수, 권한 다수 -> 권한 부여, 권한 제거

 

예) 우리 회사에 신입사원 10명 입사 + 권한 ( 20 )

계정 생성

a + grant 신입롤 to a 영업부 신입롤(제거) + 영업롤(부여)

b 영업부

c 생산부

d 총무부

: + grant 신입롤 to z

:

신입롤 = 역할==롤== Role 에게 20 권한 부여

 

영업롤 = 30개권한 그룹

신입ROLE 생성

GRANT a,b,c,d,... TO신입롤(Role) ;

GRNAT 신입롤 TO PUBLIC;

 

 

+ GRANT CREATE SESSION TO student_role; // 롤에 권한 부여

+ GRANT student_role TO kim;                         // 사용자에게 롤을 부여

+ ALTER ROLE student_role                             // 롤 수정 

+ REVOKE student_role FROM kim;               // 사용자로부터 롤 회수

+ DROP ROLE student_role;                          // 롤 삭제

 

롤조회

 

 

오라클 설치하면 자동으로 미리 정의된 롤이 있음!

-> ( 시험 ) 오라클을 설치하면 자동으로 설치된 롤 확인하는 쿼리 작성하세요. ( 32 개 )

SELECT *

FROM dba_roles; -- 테이블명 또는 뷰명 또는 서브쿼리;

-- 오류발생 : ORA-00942: table or view does not exist

SELECT *

FROM user_users;

FROM dba_users; --dba_users 존재X 일반계정인 SCOTT은 dba_XXX 사용할 수 없다.

[ scott.sql 파일 ]

Rem 주석처리

-- 주석처리

SET 명령어 : 시스템의 환경 설정하는 명령어

SET TERMOUT OFF : 실행 결과를 화면에 출력여부

SET ECHO OFF : 실행된 스크립트 화면 O, X

SET TERMOUT ON

SET ECHO ON

 

GRANT CONNECT,RESOURCE,UNLIMITED TABLESPACE -- 롤

TO SCOTT

IDENTIFIED BY tiger;

-- DB 연결(접속) O

-- (시험) SCOTT 계정 정보 확인( 조회 )하는 쿼리~

SELECT *

FROM user_users;

 

-- SCOTT --

-- (시험) SCOTT이 소유한 테이블(table) 목록 조회. - X

SELECT *

FROM tabs;

 

-- [ DCL 문 ]

CREATE TABLE 테이블명;

ALTER TABLE 테이블명;

DROP TABLE DEPT;

-- ORA-00942: table or view does not exist

-- 부서(dept)테이블 생성

CREATE TABLE DEPT

(

DEPTNO NUMBER(2) CONSTRAINT PK_DEPT PRIMARY KEY, -- 부서번호 PK 컬럼( 고유한 컬럼 )

DNAME VARCHAR2(14) , -- 부서명 컬럼

LOC VARCHAR2(13) -- 지역명 컬럼

) ;

-- Table DEPT이(가) 생성되었습니다.

 

 

 

 

 

-- 부서 정보 추가( DML : insert ) 반드시 commit, rollback 해야 된다. ( 암기 )

-- INSERT문 형식적으세요.)

INSERT INTO 테이블명 [( 컬럼명... )] VALUES ( 컬럼값... );

COMMIT;

--

INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES (10,'ACCOUNTING','NEW YORK');

INSERT INTO DEPT VALUES (20,'RESEARCH','DALLAS');

INSERT INTO DEPT VALUES (30,'SALES','CHICAGO');

INSERT INTO DEPT VALUES (40,'OPERATIONS','BOSTON');

1 행 이(가) 삽입되었습니다.

1 행 이(가) 삽입되었습니다.

1 행 이(가) 삽입되었습니다.

1 행 이(가) 삽입되었습니다.

COMMIT;

커밋 완료.

--( 시험 ) dept 테이블의 구조를 확인하는 쿼리 작성하세요..

DESC 테이블명;

DESC dept;

DESC[RIBE] dept;

이름 널? 유형

------ -------- ------------

DEPTNO NOT NULL NUMBER(2)

DNAME VARCHAR2(14)

LOC VARCHAR2(13)

-- ( 시험 ) dept 테이블의 부서 정보를 조회(검색)하는 쿼리를 작성하세요.

SELECT * -- 모든 컬럼

FROM dept; 테이블명 , 뷰명, 서브쿼리

-- ( 시험 ) dept 테이블의 부서 정보를 조회(검색)하는 쿼리를 작성하세요. ( 부서번호, 부서명 만 조회 )

SELECT deptno, dname -- 컬럼명, 컬럼명,..

FROM dept;

SELECT dname -- 컬럼명, 컬럼명,..

FROM dept;

-- 관계

-- 사원(emp) 테이블

CREATE TABLE EMP

(

EMPNO NUMBER(4) CONSTRAINT PK_EMP PRIMARY KEY, -- 사원번호 PK (고유한 컬럼)

ENAME VARCHAR2(10), -- 사원명

JOB VARCHAR2(9), -- 잡

MGR NUMBER(4), -- 사수(관리자), 직속상사

HIREDATE DATE, -- 입사일자

SAL NUMBER(7,2), -- 기본급

COMM NUMBER(7,2), -- 수당

DEPTNO NUMBER(2) CONSTRAINT FK_DEPTNO REFERENCES DEPT -- 부서번호 DEPT(deptno) 참조 FK, 참조키

);

-- Table EMP이(가) 생성되었습니다.

INSERT INTO EMP VALUES

(7369,'SMITH','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,20);

INSERT INTO EMP VALUES

(7499,'ALLEN','SALESMAN',7698,to_date('20-2-1981','dd-mm-yyyy'),1600,300,30);

INSERT INTO EMP VALUES

(7521,'WARD','SALESMAN',7698,to_date('22-2-1981','dd-mm-yyyy'),1250,500,30);

INSERT INTO EMP VALUES

(7566,'JONES','MANAGER',7839,to_date('2-4-1981','dd-mm-yyyy'),2975,NULL,20);

INSERT INTO EMP VALUES

(7654,'MARTIN','SALESMAN',7698,to_date('28-9-1981','dd-mm-yyyy'),1250,1400,30);

INSERT INTO EMP VALUES

(7698,'BLAKE','MANAGER',7839,to_date('1-5-1981','dd-mm-yyyy'),2850,NULL,30);

INSERT INTO EMP VALUES

(7782,'CLARK','MANAGER',7839,to_date('9-6-1981','dd-mm-yyyy'),2450,NULL,10);

-- 오류 : ORA-01858: a non-numeric character was found where a numeric was expected

-- 숫자를 기대하는 곳에 숫자가 아닌 문자를 찾았다.

INSERT INTO EMP VALUES

(7788,'SCOTT','ANALYST',7566,to_date('13-7-87','dd-mm-yyyy' )-85,3000,NULL,20);

숫자 7788

문자, 문자열, 날짜 ''

INSERT INTO EMP VALUES

(7839,'KING','PRESIDENT',NULL,to_date('17-11-1981','dd-mm-yyyy'),5000,NULL,10);

INSERT INTO EMP VALUES

(7844,'TURNER','SALESMAN',7698,to_date('8-9-1981','dd-mm-yyyy'),1500,0,30);

INSERT INTO EMP VALUES

(7876,'ADAMS','CLERK',7788,to_date('13-7-87','dd-mm-yyyy')-51,1100,NULL,20);

INSERT INTO EMP VALUES

(7900,'JAMES','CLERK',7698,to_date('3-12-1981','dd-mm-yyyy'),950,NULL,30);

INSERT INTO EMP VALUES

(7902,'FORD','ANALYST',7566,to_date('3-12-1981','dd-mm-yyyy'),3000,NULL,20);

INSERT INTO EMP VALUES

(7934,'MILLER','CLERK',7782,to_date('23-1-1982','dd-mm-yyyy'),1300,NULL,10);

COMMIT;

--

INSERT INTO EMP VALUES

(7876,'ADAMS','CLERK',7788,to_date('13-JUL-87')-51,1100,NULL,20)

오류 보고 -

ORA-01858: a non-numeric character was found where a numeric was expected

--

ROLLBACK; -- DML( INSERT ) 모두 취소

DELETE FROM emp;

-- (시험) 모든 사원 정보 조회.

SELECT *

FROM emp;

 

CREATE TABLE BONUS

(

ENAME VARCHAR2(10) ,

JOB VARCHAR2(9) ,

SAL NUMBER,

COMM NUMBER

) ;

-- Table BONUS이(가) 생성되었습니다.

-- 급여등급 테이블

CREATE TABLE SALGRADE

(

GRADE NUMBER, -- 등급 1~5

LOSAL NUMBER,

HISAL NUMBER

);

INSERT INTO SALGRADE VALUES (1,700,1200);

INSERT INTO SALGRADE VALUES (2,1201,1400);

INSERT INTO SALGRADE VALUES (3,1401,2000);

INSERT INTO SALGRADE VALUES (4,2001,3000);

INSERT INTO SALGRADE VALUES (5,3001,9999);

COMMIT;

-- ( 시험 ) 기본급의 등급테이블 조회하는 쿼리 작성하세요.

SELECT *

FROM salgrade;

-- ( 시험 ) SCOTT이 소유한 모든 테이블 목록 조회하는 쿼리( DQL ) 작성하세요.

SELECT *

FROM tabs;

 

-- SCOTT : DEPT, EMP, BONUS, SALGRADE 4개의 테이블 생성 + 데이터 추가

-- 1) 모든 사원의 정보를 조회

-- (조건: 사원번호, 사원명, 입사일자 조회 )

DESC emp;

-- 컬럼명 확인 : EMPNO, ENAME, HIREDATE

SELECT empno, ename, hiredate

FROM emp;

-- 2) 모든 사원의 정보를 조회

-- (조건: 사원번호, 사원명, 입사일자, 기본급, 커미션 조회 )

SELECT empno, ename, hiredate, sal, comm

FROM emp;

-- 3) 모든 사원의 정보를 조회 ( 시험 )

-- (조건 : 사원번호, 사원명, 입사일자, 기본급, 커미션 조회 )

-- (조건 : 월급 = 기본급 + 커미션 )

-- SELECT 키워드 : distinct, all, as

SELECT empno, ename, hiredate, sal AS 기본급, comm 수당

, NVL2( comm, comm, 0 )

, sal + NVL2( comm, comm, 0 ) AS pay

, NVL2( sal + comm, sal + comm, sal ) AS "별 칭"

FROM emp;

-- 3-2) null 의미 ? null 없다 X, 미정

-- 주진영 몸무게 얼마예요 ? 몸무게 측정 X, 몸무게가 얼마인지 몰라요.

-- 오라클에서 NULL은 생략된, 알려지지 않은, 혹은 적용할 수 없는 값을 의미하는 예약어

-- 3-3) null 연산 값 = null

-- 3-4) null 처리 방법 ? 커미션이 미정(null) -> 0 라고 처리

-- null 처리하는 함수( function ) :

-- 1) NVL

-- 2) NVL2 ***

NVL2(a, b, c)

a 값이 null 이 아니면 b

a 값이 null 이면 c

-- 3) NULLIF

-- 4) COALESCE

-- 모든 사용자 계정 정보를 확인

SELECT *

FROM dba_users;

-- ORA-00942: table or view does not exist

-- SCOTT ( 파랑 )--

-- 모든 테이블 목록 조회 쿼리 작성

SELECT *

FROM user_tables; -- == FROM tabs; 뷰( View )

-- ( 시험 ) emp 테이블에서

-- 모든 사원 정보를 조회(검색)

-- 사원번호, 사원명, 입사일자, 급여( 기본급 + 커미션 )

--- (추가 문제) 정렬 - 오름차순, 내림차순 정렬

-- 1,2,3,4

-- a,b,c,d

-- ㄱ,ㄴ,ㄷ,ㄹ

-- pay(급여)를 많이 받는 사원부터 출력.(조회) : 급여 내림차순 정렬

-- sal(기본급) "

SELECT empno, ename, hiredate , sal, comm

, sal + NVL2( comm, comm, 0 ) pay

-- , NVL2( sal + comm, sal+comm, sal ) pay

-- -- , NVL() comm null 처리

-- , sal + NVL( comm, 0 ) pay

-- , NVL( sal+comm, sal ) pay

FROM emp

ORDER BY sal ; -- ASC 기본은 오름차순 정렬.

-- ORDER BY sal ASC;

-- ORDER BY sal DESC; -- 내림차순으로 정렬

-- FROM scott.emp; --스키마.객체명

-- ( 시험 ) emp 테이블에서

-- 각 부서번호로 오름차순 정렬을 하세요.

-- 부서번호 , 사원명, 입사일자, 월급(pay) 출력.

SELECT deptno, ename, hiredate, sal + NVL(comm, 0) pay

FROM scott.emp

ORDER BY deptno ASC, hiredate DESC;

ORDER BY 1 ASC;

ORDER BY deptno ASC;

-- 첫 번째 정렬 : 부서번호 오름차순 정렬 (1차 정렬)

-- 두 번째 정렬 : 같은 부서번호 내에서 입사일자를 내림차순 정렬 ( 2차 정렬 )

-- (문제) emp 테이블에서

-- 부서번호로 오름차순 정렬을 한 후 월급(pay) 기준으로 내림차순 정렬을 하세요.

-- ( deptno, ename, hiredate, pay 컬럼 )

SELECT deptno, ename, hiredate, sal + NVL(comm, 0) pay

FROM emp

ORDER BY deptno , pay DESC;

-- SELECT DISTINCT, AS , ALL

SELECT ALL job --별개의 분명한, 구별되는

FROM emp;

-- 사원들이 속해있는 [ job 종류 ] 조회 출력. + 오름차순 정렬

-- 중복 제거 해서 1번 출력

SELECT DISTINCT job --별개의 분명한, 구별되는

FROM emp

ORDER BY job ASC;

-- X

SELECT DISTINCT job , ename --별개의 분명한, 구별되는

FROM emp;

-- ( 시험 ) emp 테이블에서

-- 10번 부서원들만 조회(출력) -- 조건절

SELECT deptno, ename, hiredate, mgr

FROM emp

WHERE deptno = 10; -- 오라클 같다 = 연산자

WHERE deptno == 10; -- 304행, 15열에서 오류 발생, ORA-00936: missing expression

WHERE 조건절;

-- SYS -

GRANT CONNECT,RESOURCE,UNLIMITED TABLESPACE -- 롤

TO SCOTT

IDENTIFIED BY tiger;

-- Grant을(를) 성공했습니다.

CREATE USER

DROP USER

ALTER USER SCOTT DEFAULT TABLESPACE USERS;

-- User SCOTT이(가) 변경되었습니다.

ALTER USER SCOTT TEMPORARY TABLESPACE TEMP;

-- SYS --

SELECT *

FROM dba_users;

-- ( 시험 ) HR 계정 수정 - 비밀번호 lion 수정

CREATE USER;

DROP USER ;

ALTER USER hr IDENTIFIED BY lion;

-- User HR이(가) 변경되었습니다.

-- ACCOUNT_STATUS : LOCKED (잠금) -> hr 계정의 잠금 해제

-- ORA-28000: the account is locked

ALTER USER hr ACCOUNT UNLOCK;

ALTER USER hr IDENTIFIED BY lion ACCOUNT UNLOCK;

**Rem : 주석처리