Oracle 권한 부여 스크립트
이 항목에서 제공된 스크립트는 MicrosoftSQL Server 복제를 사용하여 데이터를 게시할 Oracle 데이터베이스를 구성할 때 사용됩니다. 이 스크립트는 설치 후 <drive>:\\Program Files\Microsoft SQL Server\<InstanceName>\MSSQL\Install\oracleadmin.sql 디렉터리에서 사용할 수 있습니다. Oracle 데이터베이스를 구성하는 방법은 Oracle 게시자 구성을 참조하십시오.
[!참고]
이 스크립트에는 트랜잭션 복제에 사용되는 트리거에 필요한 GRANT CREATE ANY TRIGGER TO &&AdminLogin; 문이 포함되어 있습니다. 스냅숏 복제만 사용하려면 스크립트에서 이 줄을 제거하십시오.
Oracle SQL*Plus 유틸리티에서 스크립트를 실행하려면
SQL Server 배포자에서 명령 프롬프트 창을 엽니다.
SQL*Plus를 사용하여 Oracle 데이터베이스에 연결하고 기본 설치 디렉터리에서 oracleadmin.sql 스크립트를 실행하려면 다음 구문을 입력합니다.
sqlplus system/P@$$W0rd@orcl @"c:\Program Files\Microsoft SQL Server\<InstanceName>\MSSQL\Install\oracleadmin.sql"이 예제에서는 네트워크 이름 "orcl"을 사용하여 Oracle 데이터베이스에 연결하기 위해 기본 제공 Oracle 계정 system이 사용됩니다.
메시지가 표시되면 사용자 이름, 사용자 암호 및 기본 테이블 공간을 지정합니다.
--***********************************************************************
-- Copyright (c) 2003 Microsoft Corporation
--
-- File:
-- oracleadmin.sql
--
-- Purpose:
-- PL/SQL script to create a database user with the required
-- permissions to administer SQL Server publishing for an Oracle
-- database.
--
-- &&ReplLogin == Replication user login
-- &&ReplPassword == Replication user password
-- &&DefaultTablespace == Tablespace that will serve as the default
-- tablespace for the replication user.
-- The replication user will be authorized to allocate UNLIMITED space
-- on the default tablespace, which must already exist.
--
-- Notes:
--
-- This script must be run from an Oracle login having the
-- authorization to create a new user and grant unlimited tablespace on
-- any existing tablespace. The login must also be able to grant to the
-- newly created login the following authorizations:
--
-- create public synonym
-- drop public synonym
-- create sequence
-- create procedure
-- create session
-- create table
-- create view
--
-- Additionally, the following properties are also required for
-- transactional publications.
--
-- create any trigger
--
-- All of the privileges may be granted through a role, with the
-- exception of create table, create view, and create any trigger.
-- These must be granted explicitly to the replication user login.
-- In the script, all grants are granted explicitly to the replication
-- user.
--
-- In addition to these general grants, a table owner must explicitly
-- grant select authorization to the replication user on a table before
-- the table can be published.
--
*********************************************************************
ACCEPT ReplLogin CHAR PROMPT 'User to create for replication: ';
ACCEPT ReplPassword CHAR PROMPT 'Replication user passsword: ' HIDE;
ACCEPT DefaultTableSpace CHAR DEFAULT 'SYSTEM' PROMPT 'Default tablespace: ';
-- Create the replication user account
CREATE USER &&ReplLogin IDENTIFIED BY &&ReplPassword DEFAULT TABLESPACE &&DefaultTablespace QUOTA UNLIMITED ON &&DefaultTablespace;
-- It is recommended that only the required grants be granted to this
-- user.
--
-- The following 5 privileges are granted explicitly, but could be
-- granted through a role.
GRANT CREATE PUBLIC SYNONYM TO &&ReplLogin;
GRANT DROP PUBLIC SYNONYM TO &&ReplLogin;
GRANT CREATE SEQUENCE TO &&ReplLogin;
GRANT CREATE PROCEDURE TO &&ReplLogin;
GRANT CREATE SESSION TO &&ReplLogin;
-- The following privileges must be granted explicitly to the
-- replication user.
GRANT CREATE TABLE TO &&ReplLogin;
GRANT CREATE VIEW TO &&ReplLogin;
-- The replication user login needs to be able to create a tracking
-- trigger on any table that is to be published in a transactional
-- publication. The CREATE ANY privilege is used to obtain the
-- authorization to create these triggers. To replicate a table, the
-- table owner must additionally explicitly grant select authorization
-- on the table to the replication user.
--
-- NOTE: CREATE ANY TRIGGER is not required for snapshot publications.
GRANT CREATE ANY TRIGGER TO &&ReplLogin;