For organizations that index multiple BI landscapes using Sherlock® I’ll outline the steps to secure your Sherlock® data by System ID. This is accomplished by implementing row-level security on your data using access restrictions on the Sherlock® universe. Using these access restrictions, we can dynamically control access to BI landscape metadata, which can be very sensitive information for some Sherlock® customers.
In my example, I’ll demonstrate how to secure an Oracle database but the technique can easily be applied to the other database platforms that Sherlock® supports (IBM DB2, Microsoft SQL Server, Oracle and mySQL).
1. Create a user on Sherlock® database and give access to the tables.
2. Launch Universe Design Tool application and import Sherlock® universe
3. In Universe Design Tool – Tools – Manage Security – Manage Access Restrictions
4. Manage Access Restrictions window
5. Click New in the above screen and it would open new Restriction pop up window which holds Connection, Controls, SQL, Objects, Rows and Table Mapping tabs and by default it will point to Connection Access tab.
a. Give New Restriction a name.
b. Click New under Connection
A1. Give New Connection a Name.
B1. Select appropriate Database Middleware.
C1. Enter the User Name (from Step #1) that will access to Sherlock® schema.
D1. Enter Password for User.
E1. Enter service name for Sherlock® schema.
F1. Click next.
G1. Click Finish to add Create Connection.
H1. New Restriction window should show new Connection and your Restriction Name
I1. Click OK.
6. Click the “Add user or group”
7. Click the users / group from left side and add it into right side panel and OK.
8. Select available restrictions and available users and groups and Apply.
9. Click OK to save restriction.
10. Save and Export Universe back to repository.
Once these Access restrictions are applied then there are a couple more steps in order to make it work.
11. Create this table in Sherlock® schema.
CREATE TABLE SHERLOCK_SYS_SEC
(
SYSTEM_ID NUMBER(11,0) NOT NULL,
USER_NM VARCHAR2(255) NOT NULL,
CONSTRAINT SHERLOCK_SYS_SEC PRIMARY KEY (SYSTEM_ID, USER_NM)
);
12. To enforce row level security, insert row into table of all DB User accessing Sherlock® schema.
INSERT INTO SHERLOCK_SYS_SEC (SYSTEM_ID, USER_NM) VALUES (1, 'user1');
13. Contact EV Technologies Support for SQL script to apply to the Sherlock® schema. The script will update database views that will enforce an inner join to SHERLOCK_SYS_SEC to limit the data.
Here is an example of how they were updated.
CREATE OR REPLACE VIEW VCURR_ACCESS_RESTRICTION AS SELECT A.*
FROM ACCESS_RESTRICTION A, SHERLOCK_SYS_SEC S
WHERE A.SYSTEM_ID = S.SYSTEM_ID
AND S.USER_NM = SYS_CONTEXT ('USERENV', 'SESSION_USER')
AND A.CURRENT_IND = 1 AND A.DELETE_DT IS NULL;
14. Test Sherlock® universe to ensure access restrictions are working as expected. Warning: You will not be able to see the restriction since it applied at the database view level. All users need to be added to security table in order to query all the Sherlock® systems.