Row level security a must in a global warehouse environment
This presentation is the property of its rightful owner.
Sponsored Links
1 / 36

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


  • 83 Views
  • Uploaded on
  • Presentation posted in: General

Row-Level Security: A Must in a Global Warehouse Environment. Deepak Wadhwa State Street Corp [email protected] Background. In IT since 1987 Worked as application DBA since 1990 Certified on DB2 on Mainframe Switched to Open Systems & Oracle in 2004

Download Presentation

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

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript


Row level security a must in a global warehouse environment

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

Deepak Wadhwa

State Street Corp

[email protected]


Background

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….


Background company

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


Background application

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


Background technical

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


Rac at statestreet

RAC at StateStreet


Reasons for row level security business

Reasons for Row Level Security - Business

  • Privacy Laws

  • Compliance laws

  • Confidential agreements


Reasons for row level security technical

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


Reasons for row level security technical1

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


Reason for rls at statestreet

Reason for RLS at Statestreet

Processing Severs

Reporting Warehouses

Users

NA

Europe

Sydney

Other


New global data warehouse

New Global Data Warehouse

Processing Servers

5 Node RAC Reporting Warehouse

NA

Europe

Sydney

Other


Benefits of rls to statestreet

Benefits of RLS to StateStreet

  • Reduced Hardware Cost

  • Reduced Application development cost

  • Reduced Complexity


Benefits of rls to statestreet1

Benefits of RLS to StateStreet

  • Reduced Maintenance cost

  • Better reporting structure

  • Quick mark to market

  • User satisfaction


Row level security first step

Row Level Security – First step

  • Identify what data needs to be secured

    • Client data

    • Account numbers

    • Region

    • Department

    • Country

  • E.g Client data


Determine data element step 2

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?


Database details

Database details

  • DW database

    • Fund

    • TXN

    • Position_dly


Determine ids step 3

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


Create entitlements step 4

Create Entitlements – Step 4

  • Create entitlements groups that would contain specific values that group is entitled to

  • E.g

    • AppUS – AAAA

    • AppEuro – BBBB

    • AppAll - $$$$


Create security schema step 5

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


Create user context pkg body step 6

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


Create application context step 7

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;


Create logon trigger step 8

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


Create security policy function step 9

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


Attach security policy to objects step 10

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


Optional features step 11

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


Demo 1

Demo 1

  • User with access to all data demo

  • AppAll has all Funds entitlements ($$$$)

  • Use AppALL to get all data


Demo 2

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


Demo 3

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


Sanity checks 1

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.


Sanity check 2

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;


Sanity check 3

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.


Rls considerations

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


Rls considerations1

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


Rls considerations2

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.


Conclusion

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


Row level security a must in a global warehouse environment1

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

Deepak Wadhwa

State Street Corp

[email protected]

Questions?


  • Login