Referencing an External Schema

To reference an external schema, the user must have the proper access privileges. The privileges are set on a command line.

The ways for granting access privileges vary according to different databases. In the following syntax, database_username refers to the user name in the create_user.sql statement.
  • Oracle database: log in as system and grant the create any trigger and drop any trigger permissions to the user. For example,

    grant create any trigger to database_username

    grant drop any trigger to database_username

  • Sybase database: type the following command before creating catalog tables for the external schema:

    sp_role "grant", sa_role, database_username

    In addition, Oracle and Sybase users must have permission to access a source table in an external schema. If table relationships are used, the SELECT permission is required for both parent and child tables. The SELECT, INSERT, UPDATE, and DELETE permissions are required for accessing a destination table in an external schema.

  • SQL Server: log in as sa and then type the following command before creating catalog tables for the external schema:

    use master

    EXEC sp_addsrvrolemember 'database_username', 'sysadmin'

  • DB2 on IBM i/AS400: you can avoid table access problems by changing the ActiveDatabase user authority to *ALLOBJ.
Contents
Search Results