OBIEE Technical Conference Security Overview Dan Malone December 5,

OBIEE Technical Conference Security Overview Dan Malone December 5, 2008 1

Session Overview This is such a big topic that we have devoted 2 sessions to it. We will discuss how PeopleSoft security is used to drive security in the data warehouse and OBI. We will discuss OBI privileges and object permissions and how we modeled our security for Dashboards and Answers. We will also provide a brief overview on how we implemented CAS authentication and Single Sign On. December 5, 2008 2

Security From 30,000 Feet Identification Authentication Authorization Audit December 5, 2008 3

Consistent Security Across Applications PeopleSoft Data Warehouse OBIEE – BI Server – Presentation Services » Answers » Dashboards December 5, 2008 4

Identification/Authentication Identification – Common USERNAME across all Authentication – Web Single Sign-On (CAS) » PeopleSoft » OBIEE Presentation Services December 5, 2008 5

CAS Integration with OBI Need slides from David K. December 5, 2008 6

CAS Integration OC4J Servlet Container Soulwing CAS Client http://www.soulwing.org/ Gets USERNAME into Session December 5, 2008 Cal Poly Developed Filter OBI Single Sign-On Copies Session USERNAME into Request Header REMOTE USER Tells OBI to get REMOTE USER from Request Header 7

Single Sign-On Create Impersonator Admin account in Repository USER Session Variable Session Initialization Block select lower(':USER') from dual December 5, 2008 8

Issues with Web Single Sign-On Can not use database security – Proxy User How to perform administrative tasks – Include a local Role in Presentation Server Administrators – Method to login as Administrator user » Password on URL https://server/analytics/saw.dll?nquser Administrator&nqpassword password December 5, 2008 9

Authorization Privileges Web Catalog – Objects – Permissions Groups December 5, 2008 10

Authorization: Privileges Access Admin Catalog Dashboards Answers My Account Subject Area XXXX View XXXX December 5, 2008 11

Privileges: Things to Remember Most default to Everyone Don’t remove Personal Storage before creating a default Dashboard New Subject Area will not show up until someone starts Answers Privileges can not be migrated December 5, 2008 12

Privileges: Demo DEMO December 5, 2008 13

Authorization: Web Catalog Objects for Dashboards Folder – Dashboard » Page Request December 5, 2008 14

Authorization: Web Catalog Objects for Answers Subject Area Folder Request December 5, 2008 15

Authorization: Web Catalog Permissions No Access Traverse Read Change/Delete Full Control December 5, 2008 16

Authorization: Groups BI Server/Repository Security – Groups Presentation Services Security – Web Groups December 5, 2008 17

Authorization: Groups PeopleSoft Finance Roles PeopleSoft HCM Roles Other Application Roles December 5, 2008 Data Warehouse Roles Consolidated Roles Tables BI Server Groups Presentation Services Web Groups 18

Groups via Session Variables: Step 1 Set up Oracle Table/View for Groups CP USERNAME NAME VALUE [email protected] DISPLAYNAME Debbie [email protected] EMAIL [email protected] [email protected] GROUP ALL FINANCIAL TABLES RL [email protected] GROUP ALL RSOL TABLES RL [email protected] GROUP BI REQUEST DEVELOPER FIN RL [email protected] GROUP WAREHOUSE USER [email protected] DISPLAYNAME George [email protected] EMAIL [email protected] [email protected] GROUP ALL FINANCIAL TABLES RL [email protected] GROUP POLYDATA SUPPORT RL [email protected] GROUP WAREHOUSE USER December 5, 2008 19

PAUSE – Session Variables Tables Groups Other Variables Display Name Email Address Session Variables v December 5, 2008 20

Groups via Session Variables: Step 2 Session Initialization Block – Row-wise initialization – No Caching – Execution Precedence select name, value from dwadmin.obiee session variables where cp username lower(':USER') December 5, 2008 21

Session Variables Initialization Block December 5, 2008 22

Groups via Session Variables: Step 3 Create OBI Groups – BI Server » Group – Presentation Services » Web Group December 5, 2008 23

Groups: Things to Remember Do not manually grant BI Server Groups to Users Group and Web Group must be exactly the same name December 5, 2008 24

Groups: Demo DEMO December 5, 2008 25

Authorization: Dashboards Create a folder for each Subject Area Create a sub-folder for each Page – Requests Each Dashboard has the same permissions Each Page on the Dashboard has the same permissions December 5, 2008 26

Authorization: Things to Remember Object Owner ALWAYS has Full Control – Set Owner to Administrator Permission Inheritance Sort of. Apply changes to sub-folders – Web Based Tool Default: YES – Windows Based Tool Default: NO Special user: System Account December 5, 2008 27

Recommendations Keep it simple! Assign permissions to groups only Assign permissions at the folder level – Everything in a folder has the same permissions December 5, 2008 28

Authorization: Demo DEMO December 5, 2008 29

Row Level Security What data drives Row Level Security? – PeopleSoft DEPTID December 5, 2008 30

Row Level Security: Step 1 Create Oracle Table/View for DEPTIDs CP USERNAME NAME VALUE [email protected] DISPLAYNAME Debbie [email protected] EMAIL [email protected] [email protected] GROUP ALL FINANCIAL TABLES RL [email protected] GROUP ALL RSOL TABLES RL [email protected] GROUP BI REQUEST DEVELOPER FIN RL [email protected] GROUP WAREHOUSE USER [email protected] HR DEPTID 100100 [email protected] HR DEPTID 100200 [email protected] HR DEPTID 100300 [email protected] HR DEPTID 100400 [email protected] HR DEPTID 100500 December 5, 2008 31

PAUSE – Session Variables Tables Groups Other Variables Display Name Email Address HR DEPTIDs Session Variables v Finance DEPTIDs Finance FUNDs December 5, 2008 32

Session Variables Table CP USERNAME NAME VALUE [email protected] DISPLAYNAME Debbie [email protected] EMAIL [email protected] [email protected] GROUP ALL FINANCIAL TABLES RL [email protected] GROUP ALL RSOL TABLES RL [email protected] GROUP BI REQUEST DEVELOPER FIN RL [email protected] GROUP WAREHOUSE USER [email protected] HR DEPTID 100100 [email protected] HR DEPTID 100200 [email protected] FINANCE DEPTID 122900 [email protected] FINANCE DEPTID 122901 [email protected] FINANCE FUND GA002 December 5, 2008 33

Row Level Security: Step 2 Session Initialization Block – Same initialization block that we used for GROUPS – If done this way, the initialization block does not need to change December 5, 2008 34

Row Level Security: Step 3 Open the Logical Data Source – In the business model layer, not the physical layer December 5, 2008 35

Row Level Security: Step 4 Add the appropriate where statement to limit rows based on the new session variable. – Use the expression builder to generate the code. – Since the HR DEPTID is a dynamic session variable, it does not show up in the list of available variables. – Select the USER variable to generate the code, then change the variable name to HR DEPTID. December 5, 2008 36

Row Level Security: Demo DEMO December 5, 2008 37

Become Another User See what a dashboard looks like when a different user logs in – Don’t as for their password! All security is now based on session variables coming from Oracle tables When a user logs in we can change everything about them Exceptions – Cannot change a persons username – Object owner always has full control December 5, 2008 38

PAUSE – Session Variables Tables Groups Other Variables Display Name Email Address HR DEPTIDs Session Variables v v Session Variables Security Override Finance DEPTIDs Finance FUNDs December 5, 2008 39

Security Override Table Simple table with two columns – CP USERNAME – BECOME CP USERNAME December 5, 2008 40

Become Another User: Demo DEMO December 5, 2008 41

Security Audit WARNING http://propellerheadhats.com/ December 5, 2008 42

Security Audit – Requirements Need an easy way to find differences between two web catalogs – – – – Users Groups Permissions Privileges Check ownership of Web Catalog Objects We want to know why it works the way it does December 5, 2008 43

Security Audit – Has it been done before? Built-In? – NO! Consultants – “That’s been an internal challenge for us and we haven't been able to locate the files where that is stored” Google – No Luck December 5, 2008 44

Security Audit Web Catalog is just files and folders on the OS file system File/Folder name is based on OBI display name – URL encoded and lower case » Object Name object name Every file and folder of the catalog has an associated “.atr” file – object name – object name.atr December 5, 2008 45

Security Audit Binary Files – Linux command to hex dump a binary file » xxd xxd presentation server administrators 0000000: 0200 017c bc61 aacd bb2a 8a xxd presentation server administrators.atr 0000000: 8000 0c00 2200 0000 7072 6573 656e 0000010: 7469 6f6e 2073 6572 7665 7220 6164 0000020: 6e69 7374 7261 746f 7273 0600 01ff 0000030: ffff ffff ff01 0001 feff ffff ffff 0000040: 0300 0000 0e00 0000 6163 636f 756e 0000050: 6e64 6578 2131 0200 0000 0000 0000 December 5, 2008 .\ .a.*. 7461 6d69 ffff ffff 7469 .".presenta tion server admi nistrators. . .accounti ndex!1. 46

Security Audit – Users and Groups Users – – Groups – – catalog root /system/security/users/154/dmalone@calpoly%2eedu catalog root /system/security/users/154/dmalone@calpoly%2eedu.atr catalog root /system/security/groups/523/presentation server administrators catalog root /system/security/groups/523/presentation server administrators.atr Account IDs – – catalog root /system/accountids/699/32539c1d5ffdb65b catalog root /system/accountids/699/32539c1d5ffdb65b.atr December 5, 2008 47

Security Audit – Privileges catalog root /system/privs – /catalog » » » » /changepermissionsprivilege /changepermissionsprivilege.atr /maintenancemodeprivilege /maintenancemodeprivilege.atr – /generalprivs » » » » » » /global admin /global admin.atr /global answers /global answers.atr /global portal /global portal.atr – /security » » » » /administerprivs /administerprivs.atr /takeownershipprivs /takeownershipprivs.atr – / » / December 5, 2008 48

Security Audit – Privileges privilege file – The number of accounts granted this privilege is located at byte 12. – The account list starts at byte 13. » » » » Each account listed contains 13 bytes The first 2 bytes always seems to be 00 01 The next 8 bytes are the HEX ID of the account The next 2 bytes determine if the privilege is granted or explicitly denied FF FF - Granted (for the first entry in the list) 01 00 - Granted (for other entries in the list) 00 00 - Explicitly denied » The next byte always seems to be 00 privilege.atr file – Byte 5 contains the length of the display name. – Byte 9 is where the display name starts. December 5, 2008 49

Security Audit – Permissions object name.atr file – Byte 4 Contains the length of the object name that starts on Byte 8 – Byte 8 Start of the name of the object in nice form, including caps and spaces. – Byte (11 value of Byte 4) - Contains the HEX ID of the owner of this object - 8 Bytes – Byte (19 value of Byte 4) - Contains the number of permissions that have been assigned, in our case to groups. – Next, each of the permission is represented in a 13 byte block. » The first 2 bytes seems to always be 00 01 » The next 8 bytes of the 12 byte block contains the HEX ID of the user or group. » The next 2 bytes of the 12 byte block contains the permission granted. FF FF - Full Control 0F 00 - Change/Modify 03 00 - Read 02 00 - Traverse 00 00 - No Access » The last byte seems to always be 00 December 5, 2008 50

Security Audit – Perl saves the day Script traverses the ‘important’ branches of the web catalog Parses and collects security information Loads into Oracle tables – – – – – obiee security aud accounts obiee security aud group mem obiee security aud objects obiee security aud object perm obiee security aud privs December 5, 2008 51

Security Audit – Queries Objects without proper ownership Differences between two catalogs – – – – – Users and Groups Group memberships Object differences Object Permissions Privileges December 5, 2008 52

Security Audit: Demo DEMO December 5, 2008 53

Questions? December 5, 2008 54

Contact OBIEE Technical Conference: http://polydata.calpoly.edu/dashboards/obiee conf/index.html Email: [email protected] December 5, 2008 56