24. 기타 오브젝트(2)

1. SYNONYM

- 공식적으로 부여하는 테이블 별칭
- 보통 스키마.테이블명으로 조회하는 대신 테이블명만으로 조회되도록 설정하기 위해 사용

                                     
1) 생성
create [or replace] [public] synonym 별칭 for 원본이름;

** public : 시노님 생성자(소유자) 외에도 공용으로 사용가능한 별칭
            public 생략 시에는 시노님 소유자만 별칭 사용 가능 

2) 삭제 
drop [public] synonym 시노님명;


*주의)public synonym은 반드시 drop public synonym 명령어로 삭제!!


[ 실습 - 시노님을 통한 테이블 접근 ]
scott 계정에 dba 권한 부여)
system 계정 접속 후 아래 명령어 수행 후 scott 계정으로 재접속
grant dba to scott;


hr 계정 소유 테이블 조회 시도)
select * from employees;           -- error
select * from hr.employees;        -- 정상


시노님 생성)
create public synonym employees for hr.employees;
create synonym countries for hr.COUNTRIES;

hr 계정 소유 테이블 조회 시도)
select * from employees;           -- 정상


시노님 생성)
create public synonym employees for hr.employees;    -- public synonym 
create synonym countries for hr.COUNTRIES;           -- private synonym


scott 계정에서 조회 시도)
select * from employees;         -- 정상
select * from countries;         -- 정상


system 계정에서 조회 시도)
select * from employees;         -- 정상
select * from countries;         -- 불가(private 시노님이기 때문에 소유자인 scott만 사용 가능)



시노님 조회)
select * 
  from dba_synonyms
 where table_owner = 'HR';
 
 select *
  from dba_synonyms
 where owner != 'PUBLIC';
 

 시노님 삭제)
drop synonym;                      -- 에러
drop public synonym employees;     -- 정상

drop synonym countries;            -- 정상


예)
 인사팀                               총무팀
 사원테이블(이름,부서,연봉)   <- 조회불가                   
                         권한부여                                 
                                                                  
총무팀수행)
 select * from 사원;                --조회불가(정확한 테이블명이 전달되지 않음)
 select * from 인사팀.사원;            --조회가능
 
 
시노님생성)
 사원 -> 인사팀.사원 조회되도록 설정 ****


** oracle DB 원격 접속 관리(client -> server)
 - 반드시 client에 oracle 설치 필수
 - client -> server 간 방화벽 해제
 - client에 tnsnames.ora 파일에 server 정보 기입
 - target DB 정보 확인
   1) ip : cmd에서 ipconfig(리눅스에서는 ifconfig)로 확인 가능
   2) port : cmd에서 lsnrctl status 명령어로 확인 가능
   3) SID : cmd에서 lsnrctl status 명령어로 확인 가능

 tnanames.ora 파일은 아래 위치 존재
:\app\itwill\product\11.2.0\dbhome_1\network\admin

        ** oracle home 찾기(window)
           검색 > 시스템 환경 변수 편집 > 환경 변수 > 시스템 변수의 path 누르고 편집 

** 원격 접속
1) orange를 사용한 접속
2) sqlplus를 사용한 접속
C:\Users\itwill>sqlplus scott/oracle@hong    <------ tns_alias(tnsnames.ora 파일에 있는)


 



2. db link
- db와 db를 연결하는 링크
- db내에서 다른 db의 데이터를 조회, 수정 가능

1) 생성
create database link 링크명
connect to 유저명 identified by 패스워드
using 'tns_alias or description';

** tns_alias : client 쪽의 tnsnames.ora에 기재된 이름
** description


 [실습 - db link를 사용한 데이터 조회 ]
테이블 조회 시도)
select * 
  from emp_test11;    -- 조회 불가(local db에 emp_test11 테이블 없음)

db link 생성)
create database link dblink_hong
connect to scott identified by oracle
using 'hong';


db link 조회)
select *
  from dba_db_links;


db link를 사용한 target db 데이터 조회)
select *
  from emp_test11@dblink_hong;




[ 연습문제 ]
hong db에 있는 emp_test11 테이블을 local db에서 조회할 수 있도록 하는 시노님 생성



create public synonym emp_test11 for emp_test11@dblink_hong;


select *
  from dba_synonyms
 where table_name = 'EMP_TEST11';




3. 유저
- db 접속을 위해 생성하는 계정명
- 유저별로 테이블, 객체 권한 관리

1) 생성 
create user 유저명 identified by "패스워드"        --" " 쓰는 이유는 특수기호 또는 대소문자 구분 위해 사용
[default tablespace 테이블스페이스명]
[temporary tablespace 임시테이블스페이스명]
[quota 사이즈 on 테이블스페이스]
;

예)
create user scott identified by "oracle!"  
quota unlimited on users;


2) 제거
drop user 유저명 [cascade];

** cascade : 유저 소유의 객체까지 모두 삭제



3) 확인
select * from dba_users;







4. 권한
- 데이터를 조회, 수정할 수 있는 권한관리
- 일반적으로 소유하지 않은 테이블에 대한 조회, 수정 권한이 없음 => 필요한 권한 부여 시 가능

1) 권한 종류
  - 오브젝트 권한 : 각 테이블에 대한 조회, 입력, 삭제, 수정 권한
  - 시스템 권한 : 테이블 생성, 삭제, 수정 권한 




2) 권한 부여 -- DCL / dba권한을 갖지 않더라도 자기 권한을 남에게 줄 수 있다. 

grant 권한 to 유저 [with grant/admin option];

오브젝트 권한 부여 예)
grant select on emp to hr;                              --정상
grant select on student, exam_01 to hr;                 --error : 누락된 키워드

grant select, insert, update, delete on dept to hr;     --정상 : 동시에 여러개 전달 가능
grant select, insert, update, delete on dept to hr,oe;  --정상 : 동시에 여러명한테 전달 가능 but 테이블은 하나만 전달


시스템 권한 부여 예)
grant create table to hr;                               --정상
grant create session to hr, oe;                         --정상/없으면 접속이 안됨
grant alter any table, drop any table to hr;            --정상




3) 권한 회수

revoke 권한 from 유저;




[ 연습문제 ]
1) itwill user 생성
create user itwill identified by oracle;
grant create session to itwill;

2) system 계정에서 수행) scott 소유의 student, exam_01, department 테이블에 대해 조회 권한 부여
grant select on scott.student to itwill;
grant select on scott.exam_01 to itwill;
grant select on scott.department to itwill;


3) itwill user 접속


4) scott 소유의 student, exam_01, department 테이블 조회
   단, 조회 시 스키마 생략할 수 있도록 조치

select * from scott.student;        -- 조회 불가
system 계정으로 접속)
create public synonym student for scott.student;
create public synonym exam_01 for scott.exam_01;
create public synonym department for scott.department;

itwill 계정으로 접속)
select * from student;
select * from exam_01;
select * from department;









create user itwill identified by "oracle"  
quota unlimited on users;

create table itwill.test1(no number);
insert into itwill.test1 values(1);



grant select on student to itwill;
grant select on exam_01 to itwill;
grant select on department to itwill;
grant create session to itwill;


select *
  from test1;


SELECT *
  FROM student;

SELECT *
  FROM scott.exam_01;
  
SELECT *
  FROM scott.department;

 

 

 

 

 

 

create synonym student for scott.student;
create synonym exam_01 for scott.exam_01;
create synonym department for scott.department;
- db 접속을 위해 생성하는 계정명
- 유저별로 테이블, 객체 권한 관리

 

1) 생성 
create user 유저명 identified by "패스워드"        --  " " 쓰는 이유는 특수기호 또는 대소문자 구분 위해 사용
[default tablespace 테이블스페이스명]
[temporary tablespace 임시테이블스페이스명]
[quota 사이즈 on 테이블스페이스]
;

예)
create user scott identified by "oracle!"  
quota unlimited on users;

2) 제거
drop user 유저명 [cascade];

** cascade : 유저 소유의 객체까지 모두 삭제

3) 확인
select * from dba_users;