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

download report

Transcript 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
 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
 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
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
Reason for RLS at Statestreet
Processing Severs
Reporting Warehouses
New Global Data Warehouse
Processing Servers
5 Node RAC Reporting
Benefits of RLS to StateStreet
Reduced Hardware Cost
Reduced Application development
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
 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
 E.g
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
 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
 Use AppUS to get only US based clients
Demo 3
 New User Demo
 AppEur is new ID, doesn’t have access to any
 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
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
 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
Deepak Wadhwa
State Street Corp
[email protected]