Mapping table example

Here is an example on how to create a user-to-attribute mapping table suitable for a row security object.

Creating a table

Suppose that you wanted to set security in the Geography dimension so that some users only have access to certain regions. For example, user ID hulk.hogan@mycompany.com has access only to United States sales records, while user ID wayne.gretzky@mycompany.com has access to sales in Canada.

To create such a mapping table, execute a script similar to the following example directly in the data warehouse:

DROP TABLE schema.country_map;

CREATE TABLE schema.country_map (country STRING, username STRING);

INSERT INTO schema.country_map (country, username) VALUES ('Australia', 'paul.hogan@mycompany.com');
INSERT INTO schema.country_map (country, username) VALUES ('Canada', 'wayne.gretzky@mycompany.com');
INSERT INTO schema.country_map (country, username) VALUES ('France', 'brigitte.bardot@mycompany.com');
INSERT INTO schema.country_map (country, username) VALUES ('Germany', 'heidi.klum@mycompany.com');
INSERT INTO schema.country_map (country, username) VALUES ('United Kingdom', 'austin.powers@mycompany.com');
INSERT INTO schema.country_map (country, username) VALUES ('United States', 'hulk.hogan@mycompany.com');