231226 Linux 복습 겸 문제

1. tablespace 생성하세요.
  tablespace 이름  : oltp_tbs
  datafile 위치 및 이름  : /u01/app/oracle/oradata/ora11g/oltp_tbs01.dbf
  datafile 사이즈  : 10m
  datafile 자동 확장 활성화
  extent 관리 : local uniform size 1m
  segment space management: auto

 

1) tablespace 있는지 확인

SELECT * FROM dba_tablespaces WHERE tablespace_name = 'OLTP_TBS';
SELECT * FROM dba_data_files WHERE tablespace_name = 'OLTP_TBS';

 

2) 있다면, 해당되는 tablespace 삭제

DROP TABLESPACE oltp_tbs INCLUDING CONTENTS AND DATAFILES;

3) tablespace 생성

CREATE TABLESPACE oltp_tbs
DATAFILE '/u01/app/oracle/oradata/ora11g/oltp_tbs01.dbf' SIZE 10M AUTOEXTEND ON
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1m
SEGMENT SPACE MANAGEMENT AUTO;

4) 생성확인

SELECT * FROM dba_tablespaces WHERE tablespace_name = 'OLTP_TBS';
SELECT * FROM dba_data_files WHERE tablespace_name = 'OLTP_TBS';

2. user 생성하세요.
  user 이름 : sawon01
  user 비밀번호 : oracle
  default tablespace : oltp_tbs
  temporary tablespace : temp
  default tablespace quota : unlimited

  user 생성하세요.
  user 이름 : sawon02
  user 비밀번호 : oracle
  default tablespace : oltp_tbs
  temporary tablespace : temp
  default tablespace quota : 10m

1) user 생성

create user sawon01
identified by oracle
DEFAULT TABLESPACE oltp_tbs
TEMPORARY TABLESPACE temp
QUOTA unlimited on oltp_tbs;

create user sawon02
identified by oracle
DEFAULT TABLESPACE oltp_tbs
TEMPORARY TABLESPACE temp
QUOTA 10m on oltp_tbs;

2) 생성된 user 확인

SELECT * FROM dba_users where in ('SAWON01', 'SAWON02');
SELECT * FROM dba_ts_quotas where in ('SAWON01', 'SAWON02');

3. SAWON_ROLE 을 생성한 후 SAWON_ROLE에  권한 부여하세요.
   시스템 권한 : create session, create table, create view, create sequence, create procedure
   객체 권한   : hr.employees에 대한 select, insert, update, delete
                hr.departments에 대한 select, insert, update, delete

1) 해당 ROLE 이 있는지 확인. 있다면, DROP 하기

SELECT * FROM dba_roles WHERE role = 'SAWON_ROLE';
DROP ROLE sawon_role;

2) SAWON_ROLE 생성

create role SAWON_ROLE;

 

#1) 시스템 권한

GRANT create session, create table, create view, create sequence, create procedure TO sawon_role;
select * from dba_sys_privs where grantee = 'SAWON_ROLE';

#2) 객체권한

GRANT select, insert, update, delete ON hr.employees TO sawon_role;
grant select on HR.departments to sawon_role;

#3) ROLE 확인

select * from dba_tab_privs where grantee = 'SAWON_ROLE';
select * from dba_role_privs where granted_role = 'SAWON_ROLE';
select * from role_sys_privs where role = 'SAWON_ROLE';
select * from role_tab_privs where role = 'SAWON_ROLE';

4. sawon01, sawon02 유저에게 sawon_role 부여하세요.

#1) 권한 부여

GRANT sawon_role TO sawon01, sawon02;

#2) 부여된 권한 확인

SELECT * FROM dba_role_privs WHERE granted_role = 'SAWON_ROLE';

5. sawon01, sawon02 접속해서  시스템 권한 , 객체 권한 확인
<sawon01 session>

SELECT * FROM session_privs;
SELECT * FROM user_sys_privs;
SELECT * FROM user_tab_privs;
SELECT * FROM session_roles;
SELECT * FROM role_sys_privs;
SELECT * FROM role_tab_privs;
SELECT * FROM user_role_privs;

<sawon02 session>

SELECT * FROM session_privs;
SELECT * FROM user_sys_privs;
SELECT * FROM user_tab_privs;
SELECT * FROM session_roles;
SELECT * FROM role_sys_privs;
SELECT * FROM role_tab_privs;
SELECT * FROM user_role_privs;

 

테이블	해석
SELECT * FROM session_privs;	현재 session 에 부여된 시스템 권한.
SELECT * FROM user_sys_privs;	현재 사용자에게 부여된 시스템 권한.
SELECT * FROM user_tab_privs;	현재 사용자에게 부여된 테이블과 뷰에 대한 객체 권한.
SELECT * FROM session_roles;	현재 session 에 활성화된 역할(관련된 권한을 가진 이름)을 표시.
SELECT * FROM role_sys_privs;	역할에게 부여된 시스템 권한
SELECT * FROM role_tab_privs;	테이블과 뷰에 대한 역할에게 부여된 객체 권한
SELECT * FROM user_role_privs;	현재 사용자에게 부여된 역할

6. sawon01 유저는 사원 정보를 저장하기 위한 테이블을 생성 합니다.

#) 테이블 생성

테이블이름	테이블 저장되는 테이블 스페이스	컬럼	제약조건
dept	oltp_tbs	dept_id number(3) dept_name varchar2(30)	dept_id 컬럼 : primary key 제약조건 이름 : dept_dept_id_pk

#) 테이블 생성

테이블 이름	테이블 저장되는  테이블 스페이스	컬럼	제약 조건
emp	oltp_tbs	emp_id number(3) name varchar2(30) sal number(10) day date dept_id number(3)	emp_id 컬럼 : primary key, 제약조건 이름 : emp_emp_id_pk dept_id 컬럼 : foreign key, 제약조건 이름 : emp_dept_id_fk, 참조 : dept테이블에 dept_id

 #1) sawon01의 dept 테이블이 있다면, 삭제

DROP TABLE sawon02.dept CASCADE CONSTRAINTS PURGE;

#2) dept  table 생성

CREATE TABLE sawon01.dept 
( dept_id NUMBER(3) CONSTRAINT dept_dept_id_pk PRIMARY KEY,
  dept_name VARCHAR2(30) )
TABLESPACE oltp_tbs;

 

#3) emp 테이블 있다면, 삭제

DROP TABLE sawon01.emp CASCASE CONSTRAINTS PURGE;

 

#4) emp table 생성

CREATE TABLE sawon01.emp 
( emp_id number(3) CONSTRAINT emp_emp_id_pk PRIMARY KEY,
  name varchar2(30) , 
  sal number(10) ,
  day date, 
  dept_id number(3) CONSTRAINT emp_dept_id_fk REFERENCES sawon01.dept(dept_id) )
TABLESPACE oltp_tbs;

7. hr.departments 테이블에 있는 department_id, department_name 컬럼들의 모든 데이터를 sawon01.dept 테이블로 데이터 로드한 후 영구히 저장하세요.(데이터이관작업)

1) INSERT 문 실행 후 COMMIT(저장), 확인

INSERT INTO sawon01.dept (dept_id, dept_name) 
SELECT department_id, department_name FROM hr.departments;

COMMIT;

SELECT * FROM sawon01.dept;

8. hr.employees 테이블에 있는 employee_id, last_name, salary, hire_date, department_id 컬럼들의 모든 데이터를 sawon01.emp 테이블로 로드한 후 영구히 저장하세요.

1) INSERT 문 실행 후 COMMIT(저장), 확인 

INSERT INTO sawon01.emp (emp_id, name, sal, day, dept_id) 
SELECT employee_id, last_name, salary, hire_date, department_id FROM hr.employees;

COMMIT;

SELECT * FROM sawon01.emp;

9. sawon01.dept 테이블에 소속 사원이 있는 부서 정보만 출력 하세요.

SELECT *
FROM sawon01.dept d
WHERE EXISTS (
    SELECT 1
    FROM sawon01.emp e
    WHERE e.dept_id = d.dept_id
);

10. sawon01.dept 테이블에 소속 사원이 없는 부서 정보만 출력하세요.

SELECT d.dept_id, d.dept_name
FROM sawon01.dept d
WHERE NOTEXISTS (
    SELECT 1
    FROM sawon01.emp e
    WHERE e.dept_id = d.dept_id
);

11. sawon01 session을 통해서 사원들의 사원 번호, 사원 이름, 급여, 부서 이름을 출력하세요.

SELECT e.emp_id, e.name, e.sal, d.dept_name
FROM sawon01.emp e = sawon01.dept d 
WHERE e.dept_id = d.dept_id;

12. sawon01을 통해서 부서이름별로 부서의 총액 급여, 평균급여를 보는 뷰(dept_agg_view)를 생성하세요.

CREATE VIEW dept_agg_view 
AS
SELECT d.dept_id, agg.total_salary, agg.avg_salary
FROM ( SELECT dept_id, SUM(sal) AS total_salary, ROUND(AVG(sal), 2) AS avg_salary
       FROM sawon01.emp
       GROUP BY dept_id ) agg
JOIN sawon01.dept d ON agg.dept_id = d.dept_id;

13. 생성한 뷰의 정보를 확인하세요.

SELECT * FROM sawon01.dept_agg_view;

14. sawon01(계정) 사원번호를 입력 값으로 받아서 그 사원의 이름, 급여, 부서 이름을 출력하는 프로시저를 생성하세요. 
    만약에 없는 사원 번호 값이 들어 오면 "The sawon does not exist."라고 처리해 주세요.
    프로시저 이름은 emp_proc
#) 실행해보기

set serveroutput on

execute emp_proc(100)
--출력화면) 
NAME : King, SAL : 24000, DEPT_NAME: Executive

execute emp_proc(500)
-- 출력화면)  
The sawon does not exist.

CREATE OR REPLACE PROCEDURE emp_proc(p_emp_id NUMBER) 
AS
  v_emp_name emp.name%type;
  v_salary emp.sal%type;
  v_dept_name dept.dept_name%type;
BEGIN
  -- 사원 정보 조회
  SELECT e.name, e.sal, d.dept_name
  INTO v_emp_name, v_salary, v_dept_name
  FROM emp e
  JOIN dept d ON e.dept_id = d.dept_id
  WHERE e.emp_id = p_emp_id;

  -- 조회된 정보 출력
  DBMS_OUTPUT.PUT_LINE('NAME: ' || v_emp_name || ', SAL: ' || v_salary || ', DEPT_NAME: ' || v_dept_name);
EXCEPTION
  -- 예외 처리: 사원이 존재하지 않는 경우
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('The sawon does not exist.');
  WHEN others THEN
    DBMS_OUTPUT.PUT_LINE(sqlerrm);
END emp_proc;
/

15. emp_proc 소스를 확인하세요.

SELECT text FROM user_source WHERE NAME = 'EMP_PROC' ORDER BY LINE;

16.  emp_proc 프로시저에 대한 execute 권한을 sawon02에게 부여 해주세요

GRANT EXECUTE ON sawon01.emp_proc TO sawon02;

SELECT * FROM user_tab_privs;

17. sawon02 유저는 emp_proc 프로시저에 대한 객체 권한을 확인 한 후 실행해 보세요.

SELECT * FROM user_tab_privs;
DESC sawon01.emp_prog

#) 실행해보기

set serveroutput on

execute emp_proc(100)
NAME : King, SAL : 24000, DEPT_NAME: Executive

execute emp_proc(500)
The sawon does not exist.

18. sawon01 유저는 sawon02 유저에게 부여한 emp_proc 프로시저에 대한 객체 권한을 취소해주세요.

REVOKE EXECUTE ON sawon01.emp_proc TO sawon02;

19. sawon_profile 를 생성하세요.

패스워드 3번 실패하면 계정 잠금

패스워드 주기는 30일

패스워드 주기 유예 기간 5일

이전 패스워드를 재 사용하려면 30일 이후에 최소한 한번은 바꿔야 한다.

패스워드에 복잡성 체크 verify_function_11g 설정

접속한 후 10분 동안 아무작업을 수행하지 않으면 자동으로 kill

 

#1) 확인

SELECT * FROM dba_object WHERE object_name = 'verify_function_11g';
SELECT text FROM dba_source WHERE name ='verify_function_11g' ORDER BY LINE;

#2) 생성

CREATE PROFILE sawon_profile LIMIT
  FAILED_LOGIN_ATTEMPTS 3
  PASSWORD_LOCK_TIME unlimited
  PASSWORD_LIFE_TIME 30
  PASSWORD_GRACE_TIME 5  
  PASSWORD_REUSE_TIME 30
  PASSWORD_REUSE_MAX 1
  IDLE_TIME 10
  PASSWORD_VERIFY_FUNCTION verify_function_11g;

ALTER SYSTEM SET resource_limit = true;

20. 프로파일 확인하세요.

SELECT * FROM dba_profiles WHERE profile = 'sawon_profile';

21. sawon_profile을 수정하세요.

(문제 19번에 맞춰서 수정하세요.)

접속한후 5분동안 아무작업을 수행하지 않으면 자동으로 kill

동일한 유저이름으로 2명만 접속할수 있게 설정

#) 변경

ALTER PROFILE sawon_profile LIMIT
  IDLE_TIME 5
  SESSIONS_PER_USER 2;

SELECT * FROM dba_profiles WHERE profile = 'sawon_profile';

22. sawon_profile을 sawon01, sawon02 유저에게 설정하세요.

ALTER USER sawon01 PROFILE sawon_profile;
ALTER USER sawon02 PROFILE sawon_profile;

#) 확인

SELECT profile FROM dba_users WHERE user IN ('SAWON01', 'SAWON02');

23. sawon_profile을 삭제한 후 sawon01, sawon02 유저의 프로파일을 확인 하세요.

DROP PROFILE sawon_profile cascade;

#) 확인

SELECT profile FROM dba_users WHERE user IN ('SAWON01', 'SAWON02');