어제 문제
--1. scott과 hr 소유의 모든 테이블에 대해 public synonym을 생성하는 스크립트를 한번에 작성하여 전체 실행
-- SCOTT과 HR 소유의 모든 테이블 조회
select owner,table_name from dba_tables where owner in ('SCOTT','HR');
-- public synonym 작성하는 쿼리
select 'create or replace public synonym '||table_name||' for '||owner||'.'||table_name||';'
from dba_tables
where owner in ('SCOTT','HR')
order by owner;--2. 코테
--[ 문제 설명 ]
--places 테이블은 공간 임대 서비스에 등록된 공간의 정보를 담은 테이블입니다.
--테이블 구조는 다음과 같습니다.
--name type 설명
--id number 공간아이디
--name varchar 이름
--host_name varchar 공간소유자 유저명
--place_reviews 테이블은 공간을 임대한 사람이 남긴 후기를 담은 테이블입니다.
--구조는 다음과 같습니다.
--name type 설명
--id number 게시글아이디(기본키)
--place_id number 공간아이디
--create_at date 후기남긴날짜
--reviewer_id number 후기남긴유저아이디
--comments varchar 후기내용
--임대 서비스에서는 후기가 하나도 안 달린 공간, 50개 미만인 공간, 100개 미만인 공간,
--100개 이상인 공간을 각각 몇 개인지 구하려 합니다.
--공간 별로 후기가 몇 개인지 조회하는 SQL문을 작성.
--이때 후기가 하나도 안 달린 공간은 0, 50개 미만인 공간은 < 50, --100개 미만인 공간은 < 100, 100개 이상인 공간이 >= 100으로 표시해야 합니다.
--또한 결과는 후기수가 작은 순서대로 보여주세요.
select * from places;
select * from place_reviews;
select p.id,
count(pr.id) as 리뷰수,
case when count(pr.id)=0 then '0'
when count(pr.id)<50 then '< 50'
when count(pr.id)<100 then '< 100'
else '>= 100'
end as 표시
from places p,
place_reviews pr
where p.id=pr.place_id(+)
group by p.id
order by count(pr.id);
-- 행으로 결과 출력 - order by는 빼고 from절로 들어가도 됨
select count(decode(표시,'0',1)) as "0",
count(decode(표시,'< 50',1)) as "< 50",
count(decode(표시,'< 100',1)) as "< 100",
count(decode(표시,'>= 100',1)) as ">= 100"
from (select p.id,
count(pr.id) as 리뷰수,
case when count(pr.id)=0 then '0'
when count(pr.id)<50 then '< 50'
when count(pr.id)<100 then '< 100'
else '>= 100'
end as 표시
from places p,
place_reviews pr
where p.id=pr.place_id(+)
group by p.id);
-- 컬럼으로 정답 출력 - order by는 빼고 from절로 들어가도 됨
select 표시, count(표시) as 공간수
from (select p.id,
count(pr.id) as 리뷰수,
case when count(pr.id)=0 then '0'
when count(pr.id)<50 then '< 50'
when count(pr.id)<100 then '< 100'
else '>= 100'
end as 표시
from places p,
place_reviews pr
where p.id=pr.place_id(+)
group by p.id)
group by 표시
order by decode(표시, '0',1, '< 50',2, '< 100',3, '>= 100',4);수업 내용
— 6. 권한
— 데이터를 조회, 수정할 수 있는 권한 관리
— 일반적으로 소유하지 않은 테이블에 대한 조회, 수정 권한이 없음 => 필요한 권한 부여 시 가능
— 오브젝트 권한 -> 직접회수 불가능, 간접회수 가능
— 시스템 권한 -> 직접회수 가능, 간접회수 불가능
— 정리: 오브젝트 권한은 간접회수 해야되고, 시스템권한은 직접회수 해야된다.
— 시스템권한이 중요하니깐 직접회수, 나머지는 다 반대
— 1) 권한 종류
— 오브젝트 권한 : 각 테이블에 대한 조회, 입력, 삭제, 수정 권한
— 시스템 권한 : 테이블 생성, 삭제, 수정 권한
— 2) 권한 부여
— 테이블은 한 쿼리에 한 개밖에 지정 못함
— 권한과 유저는 한 쿼리에 여러 개 지정 가능
grant 권한 to 유저 [with grant/admin option];
— with grant option : 제3자에게 오브젝트 권한 부여/회수 가능
— with admin option : 제3자에게 시스템 권한 또는 Role(권한의 묶음) 부여/회수 가능
— 오브젝트 권한 부여 예)
— 예) HR에게 emp테이블에 대한 select 권한을 줌
grant select on emp to HR;
— 예) HR과 OE에게 dept테이블에 대한 select, insert, update, delete 권한을 줌
grant select, insert, update, delete on dept to HR, OE;
— 시스템 권한 부여 예)
grant create table to hr;
grant alter any table, drop any table to hr;
grant create session to hr, oe; — 접속하려면 세션을 만들 수 있어야함. 즉, 접속을 허용하는 명령어
— 유저 만들기랑 create session이랑은 세트인 느낌
— 3) 권한 회수
revoke 권한 from 유저;
-- [실습 - 중간관리자를 통한 권한 관리]
-- DBA : SYSTEM
-- 중간관리자 : itwill
-- 업무계정 : HONG
-- 0. HONG 계정 생성
create user HONG identified by oracle
quota unlimited on users;
grant create session to HONG;
-- 1. 중간관리자에게 SCOTT.MOVIE 테이블에 대한 조회권한 부여 및 일임
grant select on scott.movie to itwill with grant option;
-- 2. 중간관리자에게 SCOTT.CARD_HISTORY 테이블에 대한 INSERT,UPDATE,DELETE권한 부여 및 일임
grant insert, update, delete on SCOTT.CARD_HISTORY to itwill with grant option;
-- 3. 중간관리자에게 CREATE TABLE에 대한 권한 부여 및 일임
grant create any table to itwill with admin option;
-- 4. 중간관리자에게 CREATE VIEW에 대한 권한 부여 및 일임
grant create any view to itwill with admin option;
-- 5. itwill 계정에서 hong에게 권한 부여
grant select on SCOTT.MOVIE to hong;
grant insert,update,delete on SCOTT.card_history to hong;
grant create table to hong;
grant create view to hong;
-- 6. SYSTEM계정에서 HONG 계정에 부여된 SCOTT.CARD_HISTORY 에 대한 INSERT 권한 직접회수 시도
revoke insert on SCOTT.CARD_HISTORY from hong; -- error.허가하지 않은 권한을 revoke할 수 없습니다
-- 7. SYSTEM계정에서 HONG 계정에 부여된 CREATE TABLE 권한 직접회수 시도
revoke create any table from hong; -- itwill이 hong에게 부여한 권한인데 직접 회수 됨
-- 8. SYSTEM계정에서 ITWILL 계정에 부여된 SCOTT.MOVIE 에 대한 조회권한 회수 시 HONG도 함께 회수되는지
revoke select on SCOTT.MOVIE from itwill;
select * from scott.movie; -- itwill 의 권한을 뺏었는데 hong도 뺏김
-- 9. SYSTEM계정에서 ITWILL 계정에 부여된 CREATE VIEW 권한 회수 시 HONG도 함께 회수되는지
revoke create any view from itwill;
create view test999 as select 'test' as test from dual;
select * from test999;
-- itwill의 create view를 뺏었지만 hong은 안 뺏김— [ROLE]
— 권한의 묶음
— ROLE을 부여/회수 시 ROLE 안에 포함된 모든 권한이 동시에 부여/회수됨.
— ROLE을 변경 시 ROLE을 부여받은 유저의 권한도 변경됨. (동기)
— ROLE로 부여받은 권한은 세션 재접속 이후에 반영된다. (개별적으로 부여한 건 즉시반영됨)
— 1) 생성
create role 롤이름;
— 2) 권한 담기/빼기
grant 권한 to 롤이름;
revoke 권한 from 롤이름;
— 3) 롤 부여/회수
grant 롤이름 to 유저명;
revoke 롤이름 from 유저명;
-- [실습 - ROLE을 통한 권한 부여]
-- systemp계정에서 수행
-- 1. role 생성
create role rl_sel1;
-- 2. role에 권한 담기
grant select on HR.EMPLOYEES to rl_sel1;
-- 3. role을 hong에게 부여
grant rl_sel1 to hong;
grant select on SCOTT.GOGAK to hong;
-- 4. hong계정에서 테스트 - 권한 부여 전에 미리 접속해둔 상태에서 실행
select * from hr.employees; -- 조회 안됨
select * from SCOTT.gogak; -- 조회 됨
-- 세션 재접속 후
select * from hr.employees; -- 조회 됨
select * from SCOTT.gogak; -- 조회 됨
-- 5. system계정에서 role의 권한 회수
revoke select on HR.EMPLOYEES from rl_sel1;
-- 6. hong 계정에서 확인
select * from HR.EMPLOYEES; -- 조회 안됨. 즉시 회수됨— [권한 조회 뷰]
— 1) role 확인
select * from dba_roles;
— 2) 권한 확인
select *
from dba_views
where view_name like ‘DBA_%PRIVS%’;
— – 1. DBA_TAB_PRIVS : 오브젝트 권한 부여 확인
— 직접 부여한 오브젝트 권한 확인 – grantee에 유저명 전달
— 롤을 통해 부여한 오브젝트 권한 확인 – grantee에 롤이름 전달
grant select on SCOTT.EMP to itwill;
grant select,insert,update,delete on SCOTT.DEPT to rl_sel1;
select GRANTEE as 확인유저,
OWNER as 테이블소유자,
TABLE_NAME as 테이블명,
GRANTOR as 권한부여자,
PRIVILEGE as 권한,
GRANTABLE as 일임여부
from DBA_TAB_PRIVS
where 1=1
and TABLE_NAME = ‘EMP’
and GRANTEE = ‘ITWILL’ — 유저명 또는 롤이름
— and GRANTEE = ‘RL_SEL1’;
— – 2. DBA_SYS_PRIVS
— 직접 부여한 시스템 권한 확인 – grantee에 유저명 전달
— 롤을 통해 부여한 시스템 권한 확인 – grantee에 롤이름 전달
select *
from DBA_SYS_PRIVS
where grantee = ‘DBA’; — role에 포함된 시스템 권한 확인 가능
— – 3. DBA_ROLE_PRIVS : 부여된 롤 확인
select *
from DBA_ROLE_PRIVS
where GRANTEE = ‘HONG’; — hong에게 부여된 롤 확인
— 7. 인덱스
— ‘조회’ 성능을 높이기 위한 객체
— 오히려 insert, update, delete 는 느려진다.
— 데이터(행)가 저장된 위치를 가지고 있는 객체
— OLTP(실시간)업무일 때 필수적
— where절에 자주 사용되는 컬럼일수록 인덱스 설계가 필요
— 불필요한 인덱스는 저장공간 낭비
— PK 생성 시 자동으로 인덱스 걸린다.
— 예) 인덱스 형태
select studno, rowid
from student
order by 1;
select grade, substr(jumin,7,1), rowid
from student
order by 1,2;
— 예) 인덱스를 활용한 조회 성능 예제
select *
from EMP
where empno = 7698;
— 1) 생성
create [unique] index 인덱스명 on 테이블명(컬럼1 [asc|desc], 컬럼2[asc|desc], …);
— 컬럼이 unique하다면 unique 인덱스로 만들 수 있다
— 2) 삭제
drop [unique] index 인덱스명;
— 3) 확인
select TABLE_OWNER,
TABLE_NAME,
INDEX_NAME,
UNIQUENESS
from DBA_INDEXES;select TABLE_OWNER,
TABLE_NAME,
INDEX_NAME,
COLUMN_NAME,
COLUMN_POSITION,
DESCEND
from DBA_IND_COLUMNS;-- 예제) 다음의 테이블을 scott 소유로 생성 후 위 인덱스 조회 쿼리를 하나의 인덱스에 대해
-- 구성컬럼을 나열하여 출력
create table ind_test1
(
col1 number,
col2 number,
col3 number,
col4 varchar2(10),
col5 date,
primary key(col1, col2, col3)
);
select i.index_name,
i.table_owner,
i.table_name,
listagg(ic.column_name,',') within group(order by ic.column_position) as index_column
from DBA_INDEXES i,
DBA_IND_COLUMNS ic
where i.INDEX_NAME = ic.INDEX_NAME
and i.table_name = ic.table_name
and i.table_owner = ic.table_owner
and i.table_owner = 'SCOTT'
group by i.table_owner, i.table_name, i.index_name
order by 2,3,1;— listagg : 여러 개의 문자열 결합
listagg(대상[, 구분자]) within group(order by 정렬컬럼명);
— 구분자 생략 시 구분자 없이 이어붙임
select listagg(ename,'-') within group(order by ename)
from EMP
where deptno=10;— [기타 SQL 문법]
— 1. 정규식 표현식(regexp_XXX)
— 다양한 문자열 패턴을 일반화 하기 위함
— 2. 윈도우 함수
— 3. pivot/unpivot
— 4. 계층형질의
— 5. 그룹함수