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.
Select the Administration > Access Management > Named Rights node in the Adobe Campaign explorer.
Create a new right by specifying your chosen label.
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.
The :base part is optional in Oracle.
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:
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.
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 |
DB2 UDB | TeraData | InfiniDB | Sybase IQ / Sybase ASE | Netezza | AsterData | |
---|---|---|---|---|---|---|
Connecting to remote database | CONNECT authority | 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 | CREATETAB authority | CREATE TABLE or TABLE keyword | CREATE privilege | RESOURCE authority and CREATE permission | TABLE privilege | CREATE privilege |
Creating indexes | INDEX privilege | CREATE INDEX or INDEX keyword | INDEX privilege | RESOURCE authority and CREATE permission | INDEX privilege | CREATE privilege |
Creating functions | IMPLICIT_SCHEMA authority or CREATEIN privilege | CREATE FUNCTION or FUNCTION keyword | CREATE ROUTINE privilege | RESOURCE authority or DBA authority for Java functions | FUNCTION privilege | CREATE FUNCTION privilege |
Creating procedures | IMPLICIT_SCHEMA authority or CREATEIN privilege | CREATE PROCEDURE or PROCEDURE keyword | CREATE ROUTINE privilege | RESOURCE authority | PROCEDURE privilege | CREATE FUNCTION privilege |
Removing objects (tables, indexes, functions, procedures) | DROPIN privilege or CONTROL privilege or owning the object | DROP < object > or object related keyword | DROP privilege | Owning the object or DBA authority | DROP privilege | Owning the object |
Monitoring executions | EXPLAIN authority | 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 or DATAACCESS authority | INSERT and UPDATE privileges | INSERT and UPDATE privileges | INSERT and UPDATE permissions | INSERT and UPDATE privileges | INSERT and UPDATE privileges |
Loading data into tables | LOAD authority | 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 | INSERT/UPDATE privileges or DATAACCESS authority | SELECT privilege | SELECT privilege | SELECT permission | SELECT privilege | SELECT privilege |
Accessing to metadata | No authorization required to use DESCRIBE statement | 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 |