DataMasque Portal

Oracle connections

DataMasque masks Oracle tables in place.

Required privileges

A DataMasque connection for Oracle requires:

  • CREATE SESSION, ALTER SESSION, UNLIMITED TABLESPACE and CREATE TABLE system privileges.

  • SELECT on DBA_TABLES, DBA_INDEXES, DBA_CONSTRAINTS, DBA_DATA_FILES, DBA_TAB_COLUMNS, V$DATABASE and V$SESSION.

GRANT CREATE SESSION, ALTER SESSION, UNLIMITED TABLESPACE, CREATE TABLE TO <connection user>;
GRANT SELECT ON DBA_TABLES TO <connection user>;
GRANT SELECT ON DBA_INDEXES TO <connection user>;
GRANT SELECT ON DBA_CONSTRAINTS TO <connection user>;
GRANT SELECT ON DBA_DATA_FILES TO <connection user>;
GRANT SELECT ON DBA_TAB_COLUMNS TO <connection user>;
GRANT SELECT ON DBA_CONS_COLUMNS TO <connection user>;
GRANT SELECT ON V$DATABASE TO <connection user>;
GRANT SELECT ON V$SESSION TO <connection user>;

If the connection user is not the schema owner:

  • SELECT, ALTER and UPDATE privileges to the target table(s).
GRANT SELECT, ALTER, UPDATE ON <target tables> TO <connection user>;

Note:

  1. For build_temp_table tasks, you will require additional privileges on the target schema in order to create temporary tables: SELECT ANY TABLE, UPDATE ANY TABLE, CREATE ANY TABLE, ALTER ANY TABLE and DROP ANY TABLE system privileges.

  2. For truncate_table tasks, when the connection user is not the owner of the target tables, you will require DROP ANY TABLE system privilege.

  3. For mask_unique_key tasks, when the connection user is not the owner of the target tables, you will require CREATE ANY TABLE and DROP ANY TABLE system privileges.

  4. For subset tasks on Oracle 11g only, you will require EXECUTE privilege on DBMS_OBFUSCATION_TOOLKIT:

GRANT EXECUTE ON DBMS_OBFUSCATION_TOOLKIT TO <connection user>;

This is not required for Oracle 12c and later.

  1. For in-data discovery, DataMasque takes a sample of each table's rows. On Oracle it briefly changes one performance setting, db_file_multiblock_read_count, to 1 so that the sample is representative.

Granting SELECT on V$PARAMETER lets DataMasque read that setting's original value, and restore it once sampling finishes. The grant is optional, and the sample is just as representative without it. The only difference: without it DataMasque can't restore the setting, so it stays at 1 on that one connection until the connection closes. That can make that connection's later queries slightly slower, but it reverts automatically when the connection closes, and never affects other connections or the database as a whole.

GRANT SELECT ON V$PARAMETER TO <connection user>;

Connection parameters

For Oracle databases, the following fields are optional:
Schema The name of the schema that DataMasque will operate on. If not specified, the default schema (corresponding to the username) will be used.
Connection Encryption Settings Choose a connection encryption method: No Encryption (default), Oracle NNE, Oracle Wallet. If Oracle Wallet is selected, you must choose an Oracle wallet to enable wallet-based encryption. Oracle wallets can be uploaded using the Files interface.
Service name The Oracle service name (overrides SID). Required for encrypted connections.
Data Encoding An encoding to be used when retrieving data containing different character sets from the database. Note: This should match the encoding of the data stored, not the character set of the database. The list of supported encodings can be found below.

Oracle Data Encodings

The list of supported data encodings for Oracle databases is as follows:

Character Set Value (API)
Automatic null
UTF-8 UTF-8
UTF-16 UTF-16
ASCII ASCII
Windows-1252 (cp1252) Windows-1252
ISO-8859-1 (Latin-1) ISO-8859-1
ISO-8859-2 (Latin-2) ISO-8859-2
ISO-8859-5 (Cyrillic) ISO-8859-5
ISO-8859-7 (Greek) ISO-8859-7
ISO-8859-15 (Latin-9) ISO-8859-15
Shift_JIS (Japanese) Shift_JIS
Big5 (Traditional Chinese) Big5
GB2312 (Simplified Chinese) GB2312
GB18030 (Simplified Chinese) GB18030

Encrypted connections

For Oracle databases, encrypted connections can be achieved using two methods:

  1. Oracle Wallet
    • Oracle Wallets for your databases can be uploaded and managed through the Files interface.
    • The Oracle Wallet (once uploaded) or the Oracle NNE to use for a Connection can be chosen under advanced settings.
  2. Oracle Native Network Encryption (NNE).
    • Select Oracle NNE option to utilise Oracle NNE to encrypt data over the network.
    • The following parameters are set when Oracle NNE option is selected:
      • SQLNET.ENCRYPTION_CLIENT=REQUIRED
      • SQLNET.CRYPTO_CHECKSUM_CLIENT=REQUIRED

Supported Oracle Wallet certificate signature algorithms

DataMasque recommends signing the Oracle Wallet certificate using an RSA CA key with one of the following signing algorithms: sha256, sha384, or sha512. The sha1 algorithm is supported, but not recommended as it is insecure. The md5 algorithm is unsupported.

Oracle Wallets signed with ECC keys are currently unsupported, however support will be added in future releases of DataMasque.

Note: the default sign_alg is md5 in oracle 11g orapki/wallet manager, so it is recommended to explicitly specify sign_alg with sha256, sha384 or sha512.

Limitations

On Oracle, from_unique_imitate is not supported in the following scenarios:

  • On an IDENTITY column.
  • On a column used in a primary key constraint, if the table is index-organized (i.e. a table created with the ORGANIZATION INDEX option).

Supported data types

The following Oracle data types are supported.

Data type Description
char(n) Fixed-length character data of 'n' size.
varchar2(n) Variable-length character data of n size.
nvarchar2(n) Variable-length character data of length n characters or bytes using national character set.
nchar(n) Fixed-length character data of length n using national character set.
long Variable-length character data.
float, number Floating point number.
int Whole 32 bit number.
date Date not including time.
timestamp Timestamp including both a date and a time.
timestamp with time zone Timestamp including time zone.
timestamp with local time zone Timestamp including local time zone.
xml type XML data.

Unsupported key columns

Data Type Unsupported Format
float All
binary_float All
binary_double All
timestamp All