UNMASKED object privilege for a table or view can display the original data.UNMASKED object privilege for all tables and views that you create in this schema using a MASK statement.
Select this user and open the Database Explorer from this screen using the
button above the database user table. In the connect dialog you use the generated user and password from your database user.
CREATE TABLE statement. Just replace <YOUR_SCHEMA_NAME> with your created Database User Name.CREATE COLUMN TABLE "<YOUR_SCHEMA_NAME>"."Customer_OpenSQLSchema" (
id INTEGER,
customer VARCHAR(20),
cc VARCHAR(20),
ssn VARCHAR(20),
PRIMARY KEY (id)
)
WITH MASK (cc USING '####-####-####-####') ;
INSERT statements and display the data as shown in the screenshot.INSERT INTO "<YOUR_SCHEMA_NAME>"."Customer_OpenSQLSchema" VALUES (1, 'Jamie', '1234-5678-9012-3456', '123-00-4567');
INSERT INTO "<YOUR_SCHEMA_NAME>"."Customer_OpenSQLSchema" VALUES (2, 'Julie', '2222-2222-2222-2222', '222-00-2222');
INSERT INTO "<YOUR_SCHEMA_NAME>"."Customer_OpenSQLSchema" VALUES (3, 'Bob', '3333-3333-3333-3333', '333-00-3333');
INSERT INTO "<YOUR_SCHEMA_NAME>"."Customer_OpenSQLSchema" VALUES (4, 'Denys', '4444-4444-4444-4444', '444-00-4444');
INSERT INTO "<YOUR_SCHEMA_NAME>"."Customer_OpenSQLSchema" VALUES (5, 'Philip', '5555-5555-5555-5555', '555-00-5555');
INSERT INTO "<YOUR_SCHEMA_NAME>"."Customer_OpenSQLSchema" VALUES (6, 'Joe', '6666-6666-6666-6666', '666-00-6666');
INSERT INTO "<YOUR_SCHEMA_NAME>"."Customer_OpenSQLSchema" VALUES (7, 'Juan', '7777-7777-7777-7777', '777-00-7777');
INSERT INTO "<YOUR_SCHEMA_NAME>"."Customer_OpenSQLSchema" VALUES (8, 'Robert', '8888-8888-8888-8888', '888-00-8888');
INSERT INTO "<YOUR_SCHEMA_NAME>"."Customer_OpenSQLSchema" VALUES (9, 'Donald', '9999-9999-9999-9999', '999-00-9999');
INSERT INTO "<YOUR_SCHEMA_NAME>"."Customer_OpenSQLSchema" VALUES (10,'Pam', '1010-1010-1010-1010', '101-10-1010');
INSERT INTO "<YOUR_SCHEMA_NAME>"."Customer_OpenSQLSchema" VALUES (11,'Linda', '1111-1111-1111-1111', '111-00-1111');
SELECT * FROM "<YOUR_SCHEMA_NAME>"."Customer_OpenSQLSchema";
MASK directly in the CREATE TABLE statement. The data preview shows the values unmasked because this user used has the UNMASKED object privilege.ALTER TABLE statement to add the masking also to the social security number (ssn) in our example. To show you also another option, we keep the last two digits from the right remain visible.ALTER TABLE statement does exactly that:ALTER TABLE "<YOUR_SCHEMA_NAME>"."Customer_OpenSQLSchema"
ADD MASK (ssn USING '###-##-##' || RIGHT(ssn,2)) ;
In Data Warehouse Cloud you can use this table as a remote table by defining a view based on the table. In the Data Builder you create a new Graphical View. Drag and drop the created table "Customer_OpenSQLSchema" from the open SQL schema under the SOURCES tab on the left. In the upcoming dialog box click on
to generate the remote table.



CREATE VIEW statement similar to the CREATE TABLE statement we have used before, but you can also use a database function just to show you another variant. The advantage is that it can be reused in different use cases using the same logic.CREATE FUNCTION "<YOUR_SCHEMA_NAME>"."MASK_CC"
(input VARCHAR(20)) RETURNS output VARCHAR(20)
LANGUAGE SQLSCRIPT AS
BEGIN
output = LEFT(:input,4) || '-####-####-##' || RIGHT(:input,2);
END;
CREATE VIEW "<YOUR_SCHEMA_NAME>"."Customer_SpaceSchema_Masked"
AS SELECT * FROM "BOOKSPACE"."Customer_SpaceSchema_View"
WITH MASK ("cc" USING "<YOUR_SCHEMA_NAME>"."MASK_CC"("cc"));

You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
| User | Count |
|---|---|
| 4243 | |
| 3355 | |
| 2602 | |
| 2152 | |
| 1982 | |
| 1255 | |
| 1164 | |
| 1122 | |
| 1100 |