Row-Level Security: A Must in a Global Warehouse Environment

1. Row-Level Security: A Must in a Global Warehouse Environment Deepak Wadhwa State Street Corp dwadhwa1@gmail.com

2. Background • In IT since 1987 • Worked as application DBA since 1990 • Certified on DB2 on Mainframe • Switched to Open Systems & Oracle in 2004 • Currently working for State Street Corp • In Past worked for Air Canada, IBM….

3. Background – Company • State Street Corp - Founded in 1792 • US$19.0 trillion in assets under custody and administration • US$1.9 trillion under management • 27,000 employees all over the world • Operations in 25 countries • More than 100 geographic markets

4. Background - Application • Stores confidential accounting data from all operational centers. • Compliance in some countries requires data to be viewed locally only • Data is updated live (e.g. stock exchange trades) • Global users accessing the database 24/6 creating ad-hoc and canned reports • Database size – 20 Terabyte

5. Background – Technical • Oracle 10g using Sun Solaris • 5 Node Real Application Cluster • Over 250 tables • Tables/indices partitioned • Biggest table contains 1.5 Billion rows • Over 1000 Userids/clients accessing database

6. RAC at StateStreet

7. Reasons for Row Level Security - Business • Privacy Laws • Compliance laws • Confidential agreements

8. Reasons for Row Level Security - Technical • Ease of development – Developers are not worried about underlying security • Central Security – every user logging into the database has to pass through the security gateway • Database level Security inherited by all applications

9. Reasons for Row Level Security - Technical • Less code on the application side • Fewer objects to maintain • Ease of maintenance – just few objects are needed for implementation

10. Reason for RLS at Statestreet Processing Severs Reporting Warehouses Users NA Europe Sydney Other

11. New Global Data Warehouse Processing Servers 5 Node RAC Reporting Warehouse NA Europe Sydney Other

12. Benefits of RLS to StateStreet • Reduced Hardware Cost • Reduced Application development cost • Reduced Complexity

13. Benefits of RLS to StateStreet • Reduced Maintenance cost • Better reporting structure • Quick mark to market • User satisfaction

14. Row Level Security – First step • Identify what data needs to be secured • Client data • Account numbers • Region • Department • Country • E.g Client data

15. Determine Data element -Step 2 • Once Data has been identified that needs to be secured, need to identify the data element • This data column should be present on all tables that need to be secured • If the data column is not present on all tables, then views can be used to join and filter data • Create list of tables/views that need to be secured • Table: Fund_table? • Txn table?

16. Database details • DW database • Fund • TXN • Position_dly

17. Determine IDs – Step 3 • Once data element has been determined and the tables/views have been identified, determine what IDs need what access. • This will list applications and what data they are limited to • Also list any ID’s that need access to ALL data • E.g. AppAll – Need access to all data • AppUS – Only US based clients • AppAsia – Only Asia based clients

18. Create Entitlements – Step 4 • Create entitlements groups that would contain specific values that group is entitled to • E.g • AppUS – AAAA • AppEuro – BBBB • AppAll - $$$$

19. Create Security Schema – Step 5 • Create security table/view under this schema that will hold the data to enforce the security policy • This would be updated by authorized users to add/remove client_id’s from the entitlements • User_row_sec table • User_name • Appl_name • Data_type • Data_item

20. Create User Context PKG & BODY – Step 6 • Create a user package that will be used by Logon trigger • Purpose is to check user entitlements and set appropriate global session-wide variables. • Security.user_cont_pack package

21. Create Application Context – Step 7 • Create an application context domain • This will use the user context package created in the previous step. • Create or replace context sec_user_cont USING user_cont_pack;

22. Create Logon Trigger – Step 8 • Logon trigger is required at the database level • This trigger will be triggered for all users logging into the database • Session level parameters will be set using the above pieces • Security.logon_trig

23. Create Security Policy function – Step 9 • Security Policy function will use session wide variables set by the Logon trigger to form predicates for all user queries implicitly. • This will detail what security each logon ID has. • Security.sec_fund_id_policy_func

24. Attach Security Policy to Objects _ Step 10 • Attach this policy to all objects identified in Step 2 • These objects (tables or views) must contain the column that we need security on. • E.g Fund, Txn, Position_dly

25. Optional features – Step 11 • Exempt Access policy. This is for process_id or user_id’s that need to be excluded from this process. • SYS is always excluded • E.g. LOAD PROCESS ID

26. Demo 1 • User with access to all data demo • AppAll has all Funds entitlements ($$$$) • Use AppALL to get all data

27. Demo 2 • User with limited data access demo • AppUS has access to funds in US only (AAAA, CCCC, DDDD) • Use AppUS to get only US based clients

28. Demo 3 • New User Demo • AppEur is new ID, doesn’t have access to any funds • Check what queries they can execute • Modify the security table (for Fund BBBB) • Recheck the queries

29. Sanity Checks - 1 • There might be cases where users might complain about missing data. To make sure that it’s not related to Row Level security, execute the following • Select * from all_policies • This will give if the table/view has a policy setup for it.

30. Sanity Check - 2 • If the table/view has the security policy attached, Check if you have access to all data by executing the following • Select Sys_context(‘sec_user_cont’, ‘all_funds_clients_list’) from Dual;

31. Sanity Check – 3 • If application name is not present, then check if you have specific entitlements attached to it • Select sys_context(‘sec_user_cont’, ’all_clients_list’) from dual; • If the above doesn’t show the entitlement list, then no enititlements have been granted.

32. RLS - Considerations • When looking at queries, it may not show the RLS predicate, check the explain plan to see if the security tables are accessed. • If the query filters out most of the data due to RLS, make sure it’s the first table accessed in the access path

33. RLS - Considerations • Create a batch process to insert mass amount of data for initial setup of clients • Give access to view to users to check what they are entitled to

34. RLS - Considerations • Entitlements are added by the security group with due diligence for security, audit and approval process. • New tables/views are added to the security policy as part of the create/role grant process.

35. Conclusion • Using RLS gives the corporation the flexibility to add data/processes/application without worrying about the exposure • It’s easy to use and maintain • Security group controls the access

36. Row-Level Security: A Must in a Global Warehouse Environment Deepak Wadhwa State Street Corp dwadhwa1@gmail.com Questions?