DocumentationCampaignCampaign Classic v7 Documentation

Remote database access rights remote-database-access-rights

Last update: Tue Jun 18 2024 00:00:00 GMT+0000 (Coordinated Universal Time)

First, so that the user can carry out operations on an external database via FDA, the latter must have a specific named right in Adobe Campaign.

  1. Select the Administration > Access Management > Named Rights node in the Adobe Campaign explorer.

  2. Create a new right by specifying your chosen label.

  3. The Name field must take the following format user:base@server, where :

    • user corresponds with the name of the user in the external database.

    • base corresponds with the name of the external database.

    • server corresponds with the name of the external database server.

      note note
      NOTE
      The :base part is optional in Oracle.

  4. Save the named right then link it to your chosen user from the Administration > Access Management > Operators node of the Adobe Campaign explorer.

Then, to process the data contained in an external database, the Adobe Campaign user must have at least ‘Write’ rights on the database to be able to create worktables. These are deleted automatically by Adobe Campaign.

Generally speaking, the following rights are necessary:

  • CONNECT: connection to the remote database,
  • READ Data: read-only access to tables containing customer data,
  • READ ‘MetaData’: access to the server data catalogs to obtain the table structure,
  • LOAD: mass loading in work tables (required when working on collections and joins),
  • CREATE/DROP for TABLE/INDEX/PROCEDURE/FUNCTION (only for worktables generated by Adobe Campaign),
  • EXPLAIN (recommended): for monitoring performances in case of problems,
  • WRITE Data (depending on the integration scenario).

The database administrator needs to make these rights match with the rights specific to each database engine. For more information, refer to the section below.

FDA rights fda-rights

Snowflake
Redshift
Oracle
SQLServer
PostgreSQL
MySQL
Connecting to remote database
USAGE ON WAREHOUSE, USAGE ON DATABASE and USAGE ON SCHEMA privileges
Creating a user linked to the AWS account
CREATE SESSION privilege
CONNECT permission
CONNECT privilege
Creating a user tied to a remote host who has ALL PRIVILEGES
Creating tables
CREATE TABLE ON SCHEMA privilege
CREATE privilege
CREATE TABLE privilege
CREATE TABLE permission
CREATE privilege
CREATE privilege
Creating indexes
N/A
CREATE privilege
INDEX or CREATE ANY INDEX privilege
ALTER permission
CREATE privilege
INDEX privilege
Creating functions
CREATE FUNCTION ON SCHEMA privilege
USAGE ON LANGUAGE plpythonu privilege to be able to call external python scripts
CREATE PROCEDURE or CREATE ANY PROCEDURE privilege
CREATE FUNCTION permission
USAGE privilege
CREATE ROUTINE privilege
Creating procedures
N/A
USAGE ON LANGUAGE plpythonu privilege to be able to call external python scripts
CREATE PROCEDURE or CREATE ANY PROCEDURE privilege
CREATE PROCEDURE permission
USAGE privilege (procedures are functions)
CREATE ROUTINE privilege
Removing objects (tables, indexes, functions, procedures)
Owning the object
Owning the object or being a superuser
DROP ANY < object > privilege
ALTER permission
Table: owning the table Index: owning the index Function: owning the function
DROP privilege
Monitoring executions
MONITOR privilege on the required object
No privilege required to use EXPLAIN command
INSERT and SELECT privilege and necessary privilege to execute the statement for which the EXPLAIN PLAN is based on
SHOWPLAN permission
No privilege required to use EXPLAIN statement
SELECT privilege
Writing data
INSERT and/or UPDATE privileges (depending on write operation)
INSERT and UPDATE privileges
INSERT and UPDATE or INSERT and UPDATE ANY TABLE privileges
INSERT and UPDATE permissions
INSERT and UPDATE privileges
INSERT and UPDATE privileges
Loading data into tables
CREATE STAGE ON SCHEMA, SELECT and INSERT on the target table privileges
SELECT and INSERT privileges
SELECT and INSERT privileges
INSERT, ADMINISTER BULK OPERATIONS and ALTER TABLE permissions
SELECT and INSERT privileges
FILE privilege
Accessing to client data
SELECT on (FUTURE) TABLE(S) or VIEW(S) privilege(s)
SELECT privilege
SELECT or SELECT ANY TABLE privilege
SELECT permission
SELECT privilege
SELECT privilege
Accessing to metadata
SELECT on INFORMATION_SCHEMA SCHEMA privilege
SELECT privilege
No privilege required to use DESCRIBE statement
VIEW DEFINITION permission
No privilege required to use “\d table” command
SELECT privilege
TeraData
InfiniDB
Sybase IQ / Sybase ASE
Netezza
AsterData
Connecting to remote database
CONNECT privilege
Creating a user tied to a remote host who has ALL PRIVILEGES
No permission required to use the CONNECT statement
No privilege required
CONNECT privilege
Creating tables
CREATE TABLE or TABLE keyword
CREATE privilege
RESOURCE authority and CREATE permission
TABLE privilege
CREATE privilege
Creating indexes
CREATE INDEX or INDEX keyword
INDEX privilege
RESOURCE authority and CREATE permission
INDEX privilege
CREATE privilege
Creating functions
CREATE FUNCTION or FUNCTION keyword
CREATE ROUTINE privilege
RESOURCE authority or DBA authority for Java functions
FUNCTION privilege
CREATE FUNCTION privilege
Creating procedures
CREATE PROCEDURE or PROCEDURE keyword
CREATE ROUTINE privilege
RESOURCE authority
PROCEDURE privilege
CREATE FUNCTION privilege
Removing objects (tables, indexes, functions, procedures)
DROP < object > or object related keyword
DROP privilege
Owning the object or DBA authority
DROP privilege
Owning the object
Monitoring executions
No privilege required to use EXPLAIN statement
SELECT privilege
Only a system Administrator can execute sp_showplan
No privilege required to use EXPLAIN statement
No privilege required to use EXPLAIN statement
Writing data
INSERT and UPDATE privileges
INSERT and UPDATE privileges
INSERT and UPDATE permissions
INSERT and UPDATE privileges
INSERT and UPDATE privileges
Loading data into tables
SELECT and INSERT privileges to respectively use COPY TO and COPY FROM statements
FILE privilege
Be the owner of the table or ALTER permission. Depending on the -gl option, LOAD TABLE might only be performed if the user has the DBA authority
SELECT and INSERT privileges
SELECT and INSERT privileges
Accessing to client data
SELECT privilege
SELECT permission
SELECT privilege
SELECT privilege
Accessing to metadata
SHOW privilege
SELECT privilege
No permission required to use DESCRIBE statement
No privilege required to use “\d table” command
No privilege required to use SHOW command
recommendation-more-help
601d79c3-e613-4db3-889a-ae959cd9e3e1