授與 Oracle 權限的指令碼

更新： 2006 年 4 月 14 日

此主題中所提供的指令碼用於 Oracle 資料庫的組態期間，該資料庫會使用 Microsoft SQL Server 複寫發行資料。安裝後，此指令碼在下列目錄中仍可用：<drive>:\\Program Files\Microsoft SQL Server\<InstanceName>\MSSQL\Install\oracleadmin.sql。如需設定 Oracle 資料庫的詳細資訊，請參閱＜設定 Oracle 發行者＞。

附註：
此指令碼包括由交易式複寫使用之觸發程序所需要的陳述式 GRANT CREATE ANY TRIGGER TO &&AdminLogin;。如果您僅使用快照式複寫，則從指令碼中移除此行。

從 Oracle SQL*Plus 公用程式執行指令碼

1. 在「SQL Server 散發者」上，開啟 [命令提示字元] 視窗。

2. 若要使用 SQL*PLUS 連接到 Oracle 資料庫並從其預設安裝目錄執行 oracleadmin.sql 指令碼，請鍵入下列語法：

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

   在此範例中，內建的 Oracle 帳戶 system 用來連接到網路名稱為「orcl」的 Oracle 資料庫。

3. 出現提示時，請指定使用者名稱、使用者密碼和預設資料表空間。

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

請參閱

概念

設定 Oracle 發行者

說明及資訊

取得 SQL Server 2005 協助

變更歷程記錄

版本	歷程記錄
2006 年 4 月 14 日	新增內容： 新增有關從 SQL*Plus 執行此指令碼的資訊。