VIEW 생성,VIEW 조회 권한 부여,SYNONYM 생성

1. 뷰를 생성하자.

SQL> show user
USER is "SYS"
SQL> create or replace view emp_dept_10
  2  as select * from scott.emp where deptno=10;

View created.

2. 특정유저만 생성한 뷰를 조회할수 있도록 유저생성,권한 부여를 한다.

SQL> create user test_viewer identified by test;

User created.

SQL> grant connect to test_viewer;

Grant succeeded.

SQL> grant select on emp_dept_10 to test_viewer;

Grant succeeded.

SQL> grant create synonym to test_viewer;

Grant succeeded.

SQL> select * from sys.emp_dept_10;

EMPNO   ENAME  JOB            MGR    HIREDATE   SAL  COMM   DEPTNO
-------   --------  ---------    -----   ----------   ----  -------  ---------
7782        CLARK   MANAGER   7839     09-JUN-81   2450               10
7839        KING      PRESIDENT            17-NOV-81  5000                10
7934        MILLER  CLERK         7782    23-JAN-82   1300                10

3. 뷰를 사용할 유저로 접속하여 synonym을 만들어 쓰자.

SQL> conn test_viewer/test;
Connected.
SQL>
SQL>
SQL> create synonym emp_dept for sys.emp_dept_10;

Synonym created.

SQL> select * from emp_dept;

EMPNO   ENAME  JOB            MGR    HIREDATE   SAL  COMM   DEPTNO
-------   --------  ---------    -----   ----------   ----  -------  ---------
7782        CLARK   MANAGER   7839     09-JUN-81   2450               10
7839        KING      PRESIDENT            17-NOV-81  5000                10
7934        MILLER  CLERK         7782    23-JAN-82   1300                10

SQL>