GTSubset unable to generate trigger scripts for Oracle tables

Products

• CA Test Data Manager (Data Finder / Grid Tools)

Issue/Introduction

When trying to generate "Oracle Masking Insert Triggers" using Subset,  getting the following error:

Error running SQL: select count(*) from gtsrc_reference_data where rd_ref_id = 'FIRSTNAME' 

 

Environment

All supported TDM releases.

Cause

Need to generate the synonyms so the user can access the scramble data without having to prefix the schema name, or address the permission issues to the scramble database.

Resolution

To confirm this is the issue,  do the following in Subset with the user that is trying to generate the masking triggers:

select * from scramble.gtsrc_reference_data

If a result set returns,  then proceed to the next step.  If you get another error,  check with your DBA to determine if your connected user has access to the scramble database,  or if the scramble database has been installed to your environment.

Next step is to run this SQL statement in the subset window:

 select * from gtsrc_reference_data 

If you get the error "table or source does not exist", this means the synonyms have not been created with the scramble database. The synonyms are created when you use our database installer package that comes with TDM.

You will then need to run the following SQL scripts with a user that has access to your scramble database.  

---DDL START---

 

spool scramble-post-config.log

set echo on

WHENEVER SQLERROR EXIT SQL.SQLCODE

 

GRANT SELECT,UPDATE,DELETE,INSERT ON  scramble."GTSRC_CONTROL_STEPS" TO PUBLIC;

GRANT SELECT,UPDATE,DELETE,INSERT ON  scramble."GTSRC_CONTROL" TO PUBLIC;

GRANT SELECT,UPDATE,DELETE,INSERT ON  scramble."GTSRC_XREF" TO PUBLIC;

GRANT SELECT,UPDATE,DELETE,INSERT ON  scramble."GTSRC_XREF2" TO PUBLIC;

GRANT SELECT,UPDATE,DELETE,INSERT ON  scramble."GTSRC_SHUFFLE" TO PUBLIC;

GRANT SELECT,UPDATE,DELETE,INSERT ON  scramble."GTSRC_REFERENCE_DATA" TO PUBLIC;

GRANT SELECT,UPDATE,DELETE,INSERT ON  scramble."GTSRC_REFERENCE_LOV" TO PUBLIC;

GRANT SELECT ANY TABLE TO SCRAMBLE;

GRANT SELECT   ON  scramble."GTSRC_REFERENCE_DATA" TO PUBLIC;

GRANT EXECUTE  ON  scramble."GTSRC_SCRAMBLE2"      TO PUBLIC;

GRANT EXECUTE  ON  scramble."GTSRC_SCRAMBLEN2"     TO PUBLIC;

GRANT EXECUTE  ON  scramble."GTSRC_SCRAMBLED2"     TO PUBLIC;

GRANT EXECUTE  ON  scramble."GTSRC_SCRAMBLED3"     TO PUBLIC;

GRANT EXECUTE  ON  scramble."GTSRC_CHECKSUM"       TO PUBLIC;

GRANT EXECUTE  ON  scramble."GTSRC_SETCOUNT"       TO PUBLIC;

GRANT EXECUTE  ON  scramble."GTSRC_REPLACE"        TO PUBLIC;

GRANT EXECUTE  ON  scramble."GTSRC_SHUFFLEID"      TO PUBLIC;

 

CREATE OR REPLACE PUBLIC SYNONYM GTSRC_CONTROL_STEPS FOR scramble."GTSRC_CONTROL_STEPS";

CREATE OR REPLACE PUBLIC SYNONYM GTSRC_CONTROL FOR scramble."GTSRC_CONTROL";

CREATE OR REPLACE PUBLIC SYNONYM GTSRC_XREF FOR scramble."GTSRC_XREF";

CREATE OR REPLACE PUBLIC SYNONYM GTSRC_XREF2 FOR scramble."GTSRC_XREF2";

CREATE OR REPLACE PUBLIC SYNONYM GTSRC_SHUFFLE FOR scramble."GTSRC_SHUFFLE";

CREATE OR REPLACE PUBLIC SYNONYM GTSRC_SHUFFLEID FOR scramble."GTSRC_SHUFFLEID";

CREATE OR REPLACE PUBLIC SYNONYM GTSRC_REPLACE FOR scramble."GTSRC_REPLACE";

CREATE OR REPLACE PUBLIC SYNONYM GTSRC_REFERENCE_DATA FOR scramble."GTSRC_REFERENCE_DATA";

CREATE OR REPLACE PUBLIC SYNONYM GTSRC_REFERENCE_LOV FOR scramble."GTSRC_REFERENCE_LOV";

CREATE OR REPLACE PUBLIC SYNONYM GTSRC_CHECKSUM FOR scramble."GTSRC_CHECKSUM";

CREATE OR REPLACE PUBLIC SYNONYM GTSRC_SETCOUNT FOR scramble."GTSRC_SETCOUNT";

CREATE OR REPLACE PUBLIC SYNONYM GTSRC_SCRAMBLE2 FOR scramble."GTSRC_SCRAMBLE2";

CREATE OR REPLACE PUBLIC SYNONYM GTSRC_SCRAMBLED2 FOR scramble."GTSRC_SCRAMBLED2";

CREATE OR REPLACE PUBLIC SYNONYM GTSRC_SCRAMBLED3 FOR scramble."GTSRC_SCRAMBLED3"

CREATE OR REPLACE PUBLIC SYNONYM GTSRC_SCRAMBLEN2 FOR scramble."GTSRC_SCRAMBLEN2";

exit---DDL END---

 

After you have completed executing the SQL commands above,  you should now be able to generate your masking triggers.

Additional Information

For Build Windows DataPump Masked Export, https://knowledge.broadcom.com/external/article/7894/unable-to-use-the-scramble-functionality.html