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

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

  • Uploaded on
  • Presentation posted in: General

Row-Level Security: A Must in a Global Warehouse Environment. Deepak Wadhwa State Street Corp 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

Deepak Wadhwa

State Street Corp


  • 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

  • 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

  • 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

  • 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

Reasons for Row Level Security - Business

  • Privacy Laws

  • Compliance laws

  • Confidential agreements

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

Processing Severs

Reporting Warehouses






New Global Data Warehouse

Processing Servers

5 Node RAC Reporting Warehouse





Benefits of RLS to StateStreet

  • Reduced Hardware Cost

  • Reduced Application development cost

  • Reduced Complexity

Benefits of RLS to StateStreet

  • Reduced Maintenance cost

  • Better reporting structure

  • Quick mark to market

  • User satisfaction

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

  • 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

  • DW database

    • Fund

    • TXN

    • Position_dly

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

  • 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

  • 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 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

  • Exempt Access policy. This is for process_id or user_id’s that need to be excluded from this process.

  • SYS is always excluded


Demo 1

  • User with access to all data demo

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

  • Use AppALL to get all data

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

  • 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

  • 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

  • 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

  • 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

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


  • 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 Environment

Deepak Wadhwa

State Street Corp


  • Login