Transcript 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
 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
NA
Europe
Sydney
Other
Reporting Warehouses
Users
New Global Data Warehouse
Processing Servers
NA
Europe
Sydney
Other
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
 E.g. LOAD PROCESS ID
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.
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
Questions?
Deepak Wadhwa
State Street Corp
[email protected]