Oracle 권한 부여 스크립트Script to Grant Oracle Permissions

이 항목에서 제공된 스크립트는 MicrosoftMicrosoft SQL ServerSQL Server 복제를 사용하여 데이터를 게시할 Oracle 데이터베이스를 구성할 때 사용됩니다.The script provided in this topic is used during the configuration of an Oracle database that will publish data using MicrosoftMicrosoft SQL ServerSQL Server replication. 이 스크립트는 설치 후 <드라이브>:\\Program Files\Microsoft SQL Server\<InstanceName>\MSSQL\Install\oracleadmin.sql 디렉터리에서도 사용할 수 있습니다.This script is also available in the following directory after installation: <drive>:\\Program Files\Microsoft SQL Server\<InstanceName>\MSSQL\Install\oracleadmin.sql. Oracle 데이터베이스 구성에 대한 자세한 내용은 Oracle 게시자 구성을 참조하세요.For more information about configuring the Oracle database, see Configure an Oracle Publisher.

참고

이 스크립트에는 트랜잭션 복제에 사용되는 트리거에 필요한 GRANT CREATE ANY TRIGGER TO &&AdminLogin;문이 포함되어 있습니다.This script includes the statement GRANT CREATE ANY TRIGGER TO &&AdminLogin;, which is required for the triggers used by transactional replication. 스냅숏 복제만 사용하려면 스크립트에서 이 줄을 제거하십시오.If you will use only snapshot replication, remove this line from the script.

Oracle SQL*Plus 유틸리티에서 스크립트를 실행하려면To run the script from the Oracle SQL*Plus utility

  1. SQL Server 배포자에서 명령 프롬프트 창을 엽니다.On the SQL Server Distributor, open a Command Prompt window.

  2. SQLPlus를 사용하여 Oracle 데이터베이스에 연결하고 기본 설치 디렉터리에서 oracleadmin.sql 스크립트를 실행하려면 다음 구문을 입력합니다.To use SQLPLUS to connect to the Oracle database and execute the oracleadmin.sql script from its default install directory, type the following syntax:

    sqlplus system/P@$$W0rd@orcl @"c:\Program Files\Microsoft SQL Server\<InstanceName>\MSSQL\Install\oracleadmin.sql"  
    

    이 예제에서는 네트워크 이름 "orcl"을 사용하여 Oracle 데이터베이스에 연결하기 위해 기본 제공 Oracle 계정 system 이 사용됩니다.In this example, the built-in Oracle account system is used to connect to an Oracle database with a network name of "orcl".

  3. 메시지가 표시되면 사용자 이름, 사용자 암호 및 기본 테이블 공간을 지정합니다.When prompted, specify the user name, user password, and default table space.

--***********************************************************************  
-- 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;  

관련 항목:See Also

Oracle 게시자 구성Configure an Oracle Publisher