Row level security a must in a global warehouse environment
1 / 36

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

  • Uploaded on

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

I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
Download Presentation

PowerPoint Slideshow about ' Row-Level Security: A Must in a Global Warehouse Environment ' - libby

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]


  • 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






New global data warehouse
New Global Data Warehouse

Processing Servers

5 Node RAC Reporting Warehouse





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


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.


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