Re: 오늘의 마지막 문제. hr 계정에 모든 테이블들을 king3 가 다 조회할 수 있도록 객체권한을 부여하시오.

[21기_이풍혁]

1. [SYSDBA계정] king3 에게 hr의 모든 테이블 권한 부여

권한 부여 쿼리 생성)

select 'grant select on hr.' || table_name || ' to king3;' from dba_tables where OWNER = 'HR';

쿼리 실행)

grant select on hr.COUNTRIES to king3;

grant select on hr.DEPARTMENTS to king3;

grant select on hr.EMPLOYEES to king3;

grant select on hr.JOBS to king3;

grant select on hr.JOB_HISTORY to king3;

grant select on hr.LOCATIONS to king3;

grant select on hr.REGIONS to king3;

---

2. [SYSDBA계정] hr의 모든 테이블 public synonym 생성

synonym 쿼리 생성)

select 'create public synonym ' || table_name || ' for hr.' || table_name || ';' from dba_tables where OWNER = 'HR';

synonym 쿼리 실행)

create public synonym COUNTRIES for hr.COUNTRIES;

create public synonym DEPARTMENTS for hr.DEPARTMENTS;

create public synonym EMPLOYEES for hr.EMPLOYEES;

create public synonym JOBS for hr.JOBS;

create public synonym JOB_HISTORY for hr.JOB_HISTORY;

create public synonym LOCATIONS for hr.LOCATIONS;

create public synonym REGIONS for hr.REGIONS;

---

3. [king3 계정]  public synonym 으로 hr 계정의 모든 테이블 건수 조회

PS C:\Users\poonghyeok> sqlplus king3/tiger@172.16.3.166/orcl

SQL*Plus: Release 19.0.0.0.0 - Production on 수 9월 3 16:36:46 2025
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

마지막 성공한 로그인 시간: 수 9월  03 2025 16:29:28 +09:00

다음에 접속됨:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> select count(*) from COUNTRIES;

  COUNT(*)
----------
        25

SQL> select count(*) from DEPARTMENTS;

  COUNT(*)
----------
        27

SQL> select count(*) from EMPLOYEES;

  COUNT(*)
----------
       107

SQL> select count(*) from JOBS;

  COUNT(*)
----------
        19

SQL> select count(*) from JOB_HISTORY;

  COUNT(*)
----------
        10

SQL> select count(*) from LOCATIONS;

  COUNT(*)
----------
        23

SQL> select count(*) from REGIONS;

  COUNT(*)
----------
         4