oracle表授权脚本,授予 Oracle 权限的脚本

授予 Oracle 权限的脚本Script to Grant Oracle Permissions

03/14/2017

本文内容

适用于：Applies to: SQL ServerSQL Server(所有支持的版本)SQL ServerSQL Server (all supported versions)适用于：Applies to: SQL ServerSQL Server(所有支持的版本)SQL ServerSQL Server (all supported versions)

本主题中提供的脚本用于配置使用 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\\MSSQL\Install\oracleadmin.sql 。This script is also available in the following directory after installation: :\\Program Files\Microsoft SQL Server\ \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

在 SQL Server 分发服务器中，打开一个命令提示符窗口。On the SQL Server Distributor, open a Command Prompt window.

若要使用 SQL*PLUS 连接到 Oracle 数据库并从其默认安装目录执行 oracleadmin.sql 脚本，请键入以下语法：To use SQL*PLUS 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\\MSSQL\Install\oracleadmin.sql"

在此示例中，使用内置 Oracle 帐户 system 连接到网络名称为“orcl”的 Oracle 数据库。In this example, the built-in Oracle account system is used to connect to an Oracle database with a network name of "orcl".

在得到提示后，请指定用户名称、用户密码和默认的表空间。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