OpenEdge ABL Multi-Tenancy

1. OpenEdge ABL Multi-Tenancy ABL Multi-Tenant Programming Mary Szekely OpenEdge Fellow June 2011

2. Agenda • Tenancy • Regular Tenant Programming Model • Groups • Super-tenant Programming Model • Questions

3. Multi-Tenant Database Tenant Types • A tenant is a separate organizational entity within a multi-tenant database with • It’s own private data segment for each multi-tenant table • Except for groups and Super-tenants • One or more ABL security domains • Its own users • Each multi-tenant database user belongs to some domain and hence some type of tenant • Default tenant • Regular tenant • Super-tenant

4. Multi-Tenant Users, Domains and Tenants • User logging in with no domain association • Belongs to the “blank” domain and normally has access as the “default” type of tenant • User logging in as a member of a domain that is not blank and not associated with a Super-tenant • Has access as a “regular” type of tenant • User logging in as a member of a domain that is associated with a “super” tenant • Is not a normal tenant user because he has no data segments of his own but can get temporary access to regular tenant data.

5. Multi-Tenant Database Table Types • Non-Multi-tenant tables or “shared” tables • Are tables in a non-multi-tenant database, or tables in a multi-tenant database that are not multi-tenant, such as: • the Sports database State table with AK, AZ etc, • temp-tables • schema tables • Can be accessed by users of any type of tenant subject to normal access privileges • i.e. they act like version 10 tables • Multi-tenant tables • Have been made multi-tenant in a multi-tenant database • Are in a single private data segment for each regular tenant • Except for groups where the group has the private segment • Have a default data segment for the default tenant • Mostly for use during migration

6. Multi-Tenant Database Users Access to Tenants • Default tenant users • Cannot access regular tenant data • Regular tenant users • Can access the private data segments of multi-tenant tables owned by that tenant • Access is subject to the user’s normal access rights • Cannotaccess the private segments of any other regular tenants • Super-tenant users • Cannot access regular tenant data unless the Super-tenant user uses new ABL language elements • New SET-EFFECTIVE-TENANT and TENANT-WHERE constructs allow temporary access to regular tenant data • Access is still subject to the Super-tenant user’s normal access rights

7. Data Access for 2 tenants, HomeDepot and Lowes Customers Orders … Items Customers Orders … Items … _file _field _tenant … State

8. Getting Tenants in the database • An OpenEdge Tool creates a Tenant by providing: • A record in the _tenant schema table that has the tenant’s Name, Id etc. • A related record in the _sec-authentication-domain schema table that has the domain name, access code, authentication system such as _oeusertable, oslocal, your-own-system etc. and tenant name _sec-authentication-system _oeusertable (_user) appauth _sec-authentication-domain _tenant “” |“” |_oeusertable| Default Default | 0 HomeDepot|access-cd|appauth|HomeDepot HomeDepot | 1 Lowes |access-cd|appauth| Lowes Lowes | 2

9. Tenants need Domains for Authentication • The domain is needed for tenants in order to provide multi-domain security with CLIENT-PRINCIPAL support • The tenant feature security is based on and extends ABL version 10 security support • Multi-tenant tables have a different instance for each tenant, so the login of a user of a tenant needs to include a domain name of the tenant • Similar to WINDOWS domain/userid type of login

10. Users Are Granted Access to Tenants by Domains Customers Customers Orders Orders … … Items Items … _file _field _tenant … state

11. Adapting client-side user authentication to include tenancy • If you: • Register your database users with a given tenancy, and • You wish to impose that same tenancy from the client, consistently every time they log in • You need to: • Include the domain name with your user information. • For example, have a column in your user account type table, for the domain name as well as the userid and password • Version 11 databases have a _domain-name column in the _user table if you happen to use _user records • The DBA is responsible for propagating the domain name to the user account area when a user account is created • In any case: • A user does not get to look at any regular tenant data without authenticating to some domain

12. CLIENT-PRINCIPAL and User tenancy • Authentication as a user with tenancy • Requires a authentication to a domain of the tenant which in turn requires a CLIENT-PRINCIPAL • CLIENT-PRINCIPAL is • A built-in ABL object from version 10 that encapsulates identity credentials for a user • A security “token”, like a credit-card • _user record databases have an implicit Client-Principal for backward compatibility • By using the new format “userid@domain” instead of just “userid”

13. One Possible Scenario for multi-tenant user client-side Authentication with CLIENT-PRINCIPAL • On the DB client, once you have a validated userid and know the desired domain is appropriate, you can: • Use the userid and domain name to create a CP • You will use this CP to assert to the ABL security system that the user is already verified for that domain. • Use the domain’s access code to SEAL the CLIENT-PRINCIPAL • Getting the access code will vary: • Store it encoded in the user account record, or • Have a mapping from the domain name itself to produce it, or • Keep it as an encoded field in the _sec-authentication-domain record etc. • Have a different scenario that doesn’t require it on the client

14. Code to SET-DB-CLIENT with CLIENT-PRINCIPAL using DB client-side validated userid and domain RUN myAuth.p(userid, domain, userPassword, output OK). IF NOT OK error.. CREATE CLIENT-PRINCIPAL hCP. hCP:INITIALIZE(qualifiedUserid). /*userid@domain*/ hCP:SEAL(DomainAccessCode). SET-DB-CLIENT(hCP). /*login with domain/access-code*/ • The user is now logged in to the database and has a tenancy consistent with the domain he authenticated to.

15. Another Scenario to SET-DB-CLIENT with CP where the DATABASE authenticates userid/pswd CREATE CLIENT-PRINCIPAL hCP. hCP:INITIALIZE(qualifiedUserid, ?, ?, userPassword). SET-DB-CLIENT(hCP). • Because the db client side did not validate the user or SEAL the CP • It is done on the DB server side during SET-DB-CLIENT, using the _sec-authentication-system record • Once more, the user is now logged in to the database and has a tenancy consistent with the domain

16. A SEALed CLIENT-PRINCIPAL supports SINGLE SIGN ON • The sealed CP • Can be used for any other database with the same domain names and access codes • Can be used by Appservers for Single Sign On (SSO) • Can be exported and imported

17. Appservers, Tenants, Single Sign On and Context-Switching • Appserver generates a context-Id on client login • The context-Id is passed back to clients and is automatically returned in each subsequent request as session:context-Id • In the Connection.p on the Appserver you can • Get the userid, password and domain • Create and seal a CLIENT-PRINCIPAL (CP) • Export the CP to a safe store (db, file, xml) under the context-Id • In the Activate.p of subsequent requests you can: • Use the session:context-Id to find and import the CP • Pass the CP to SET-DB-CLIENT in order to switch to this next user’s context • i.e. userid, domain and tenancy • The ABL automatically flushes previous request’s tenant data, and you now access data as the new tenant

18. What About all your RCODE? • No special ABL coding is required for a regular tenant user to access a multi-tenant table • Legacy code only needs recompile in version 11+ to be run as multi-tenant code by a regular tenant user • The ABL compiler does not need to know • What tenant will be executing the rcode it is compiling • Whether the rcode will be run on multi-tenant tables or not • or even on a multi-tenant enabled database or not • The ABL rcode that accesses a multi-tenant table • Is mapped at runtime to the appropriate tenant’s data segment • Each regular tenant’s ABL rcode is identical • But the data accessed is different

19. LOCKING Tenants for Create, Delete, Disable • Creating / Deleting / Disabling tenants • Doesn’t get an “umbrella” exclusive schema lock • Can be done online • A tenant may be disabled or exclusively locked • A user of a disabled tenant will get errors when • Trying to set-db-client to that tenant, or • Trying to access that tenant’s data • Always check the return code (yes/no) from SET-DB-CLIENT • The _user table works this way already

20. Not allocating a multi-tenant table segment • A tenant table segment may not be allocated • Most typical for the default segment of a multi-tenant table • Unless migration is done, there is no need for this segment normally • A user of the tenant will get allocation errors when trying to access that table • Typically when inadvertently trying to access data as the default tenant when the default segment is not allocated

21. Agenda • Tenancy • Regular Tenant Programming Model • Groups • Super-Tenant Programming Model • Questions

22. Regular Tenant Programming • DBA does most of the hard work • Creating tenants, domains and users, • Deciding areas, • etc. • DB connection code and userid authentication • Has to change as we just saw in the last section • Appserverconnection.p and activate.p • May need changes to set up the client’s tenant context • Almost everything else just “works”

23. Regular tenant ABL • For two tenants, HomeDepot and Lowes, you will get a different report from the same rcode FOR EACH Customer: DISPLAY CustNum Name. END. 1 Fred Smith 2 Joan Adlon 3 George Holmes Lowes 1 Albert Hall 2 Candace Jones 3 Carrie Abrahm HomeDepot Customer Customer

24. Regular tenant ABL FIND FIRST Customer. /*automatically gets the right tenant*/ DISPLAY CustNum Name. 4 New Cust 1 Fred Smith Lowes Lowes CREATE Customer. /*automatically goes to the right tenant*/ Name = “New Cust” DISPLAY CustNum Name. HomeDepot HomeDepot 4 New Cust 1 Albert Hall

25. Sequences - Multi-tenant • If the sequence is multi-tenant, it will increment independently in each tenant • For the two tenants in our hardware application, the custNums from a MT sequence: • Start with 1 for each tenant • Are non-unique across tenants • Ideal for use where any join tables have the same tenancy type Customer 1 Fred Smith 2 Joan Adlon 3 George Holmes Lowes 1 Albert Hall 2 Candace Jones 3 Carrie Abrahm HomeDepot

26. Sequences – shared across tenants • For the same database, the custNum from a shared or non-multi-tenant sequence will number consecutively across tenants • The custNum therefore is unique across tenants • Why would you ever want this? FOR EACH Customer, EACH Order of Customer. • If the Order table is shared, then the Order.CustNumwould be non-unique and useless unless the CustNum sequence is shared. Customer 1 Fred Smith 3 Joan Adlon 4 George Holmes Lowes 2 Albert Hall 5 Candace Jones 6 Carrie Abrahm HomeDepot

27. TENANT-ID() and TENANT-NAME() • These two functions: • Return the current session tenant Id and Name. • Take an optional Dbname parameter if there is more than one database in the session DISPLAY TENANT-NAME(). FOR EACH Customer: DISPLAY CustNum Name. END. 1 Fred Smith 2Joan Adlon 3 George Holmes Lowes Lowes HomeDepot 1Albert Hall 2Candace Jones 3Carrie Abrahm HomeDepot Customer Customer

28. TENANT-ID() and TENANT-NAME() contd • Regular tenant code might use these two functions to: • Display the current session tenant information in a report • Populate a column in a temp-table • Populate a multi-tenant table column to make its foreign key unique • Regular tenant code may not use these two functions in a WHERE clause: /* NOT OKAY TO DO THIS!!! */ FOR EACH Customer WHERE TENANT-NAME() = “Lowes”: • The ABL already knows what tenant a regular tenant belongs to • And there is no “hidden” column in any table or index that can be used to select on in a regular tenant WHERE clause. • Because tenants are like mini-databases, it is equivalent to saying: /* NOT OKAY TO DO THIS!!! */ FOR EACH Customer WHERE DBNAME = “Sports”:

29. Agenda • Tenancy • Regular Tenant Programming Model • Groups • Super-tenant Programming Model • Questions

30. Groups of tenants (only tables have groups) • A DB has 3 tenants, HomeDepot, LowesNY and LowesBos • LowesNY and LowesBos are in the same group for Items FOR EACH Item: DISPLAY ItemNum Item-Desc. END. 1 Shovel bos 3Extension cable bos 4 Hammer ny 7 Green Paint bos 8 Faucet ny 9 Lamp bos LowesBOS And LowesNY, as GROUP LowesItm Item Item 2Lawn Mower 5Screw Driver 6Table HomeDepot

31. Data Access for 3 tenants, HomeDepot and LowesBos, LowesNy and 1 Item table group Customers … Orders Items Customers … Orders Customers … Orders Items for both LowesBos and Ny … _file _tenant _field … State

32. Within a Group, there is no individual tenancy inherent in each record • A user of any tenant in a group can create, read and update any row in the table that is grouped • Therefore there is no one tenant owner for a group record • You must use shared sequences with groups • Or you will get collisions in the keys Item LowesBOS And LowesNY, as GROUP LowesItm 1 Shovel bos 3Extension cable bos 4 Hammer ny 7 Green Paint bos 8 Faucet ny 9 Lamp bos

33. BUFFER-GROUP-ID() and BUFFER-GROUP-NAME functions and buffer-handle methods • The buffer must be populated • The record in it must be for a tenant and table that are in a group • Otherwise, they return UNKNOWN • E.g. As a user for the LowesNY tenant: FIND FIRST Item. /* returns Shovel tho it’s a BOS item */ BUFFER-GROUP-NAME(Item) /* returns LowesItm */ Item LowesBOS And LowesNY, as GROUP LowesItm 1 Shovel bos 3Extension cable bos 4 Hammer ny 7 Green Paint bos 8 Faucet ny 9 Lamp bos

34. Agenda • Tenancy • Regular Tenant Programming Model • Groups • Super-tenant Programming Model • Questions

35. Why are Super-tenants needed? • Super-tenants exist to allow housekeeping cross-tenant tasks such as • Saas administration i.e. billing, moving tenants.. • Migration from previous database versions • Handling of aggregate information across tenants • Super-tenants have no data of their own • Super-tenants have special ABL to allow them to: • Get access to regular tenant data • Execute legacy code

36. SET-EFFECTIVE-TENANT function • Available only to a Super-tenant user • Allows a Super-tenant user to act on behalf of a regular tenant • So you don’t have to SETUSERID or SET-DB-CLIENT to actually become a real user of that tenant • You can give the tenant name or Id, and a dbname if needed SET-EFFECTIVE-TENANT(“HomeDepot”). FIND FIRST Customer. DISPLAY CustNum Name. RUN myCustApp.p etc. • All FINDs,CREATEs,DELETEs,FOR EACHs, all ABL will use HomeDepot indexes and access HomeDepot tenant records HomeDepot 1 Albert Hall

37. GET-EFFECTIVE-TENANT-ID function andGET-EFFECTIVE-TENANT-NAME function • These two functions are analogous to TENANT-ID() and TENANT-NAME() • But they are used by Super-tenant users to retrieve the name and id of the most recent SET-EFFECTIVE-TENANT in the session • They take an optional dbname • For Example: SET-EFFECTIVE-TENANT(“HomeDepot”). GET-EFFECTIVE-TENANT-NAME() /* returns HomeDepot */

38. BUFFER-TENANT-ID() and BUFFER-TENANT-NAME functions and buffer-handle methods • These two functions are also analogous to TENANT-ID() and TENANT-NAME() • But are used by Super-tenant users with a buffer • since the session’s tenant-id and name are the Super-tenant user’s ids • as opposed to the buffer’s. • The buffer must be populated, or they return UNKNOWN. • For Example: SET-EFFECTIVE-TENANT(“HomeDepot”). FIND FIRST Customer. BUFFER-TENANT-NAME(Customer) /* returns HomeDepot */ • These two functions: • Are somewhat unpredictable when applied to a group table • Sometimes return an arbitrary member of the group

39. Using _tenant schema table to scan across tenants FOR EACH _Tenant WHERE _TenantId > 0 and _Tenant-Name < “M”: SET-EFFECTIVE-TENANT(_Tenant._TenantId). FOR EACH Customer: DISPLAY BUFFER-TENANT-ID(Cust) CustNum Name. RUN myCustApplication.p(CustNum). END. END. 2 1 Fred Smith 22Joan Adlon 2 3 George Lowes Customer 1 1 Albert Hall 1 2 Candace 1 3 Carrie HomeDepot

40. Using TENANT-WHERE to scan across tenants FOR EACH Customer TENANT-WHERE TENANT-ID() > 0 AND TENANT-NAME() < “M”: SET-EFFECTIVE-TENANT(BUFFER-TENANT-ID(Cust)). DISPLAY BUFFER-TENANT-ID(Cust) CustNum Name. RUN myCustApplication.p(CustNum). END. 2 1 Fred Smith 22Joan Adlon 2 3 George Lowes Customer 1 1 Albert Hall 1 2 Candace 1 3 Carrie HomeDepot

41. TENANT-WHERE with Sorting and Joins • Default order is by _tenant, overrideable by using a BY phrase FOR EACH Customer TENANT-WHERE TENANT-ID() > 0 BY Customer.Name. • Only 1 level of join can have the TENANT-WHERE phrase • The ABL automatically propagates the current tenancy to lower levels of join, where appropriate • So the join will contain records from the same tenant throughout the current tenant iteration FOR EACH Customer TENANT-WHERE TENANT-ID() > 0, EACH Order of Customer, EACH Order-line of Order.

42. Super-tenants and Migration • Scenario: • Log in as a Super-tenant user, with default effective-tenancy. • To move Customers from the default data segment into the correct tenant: DEFINE BUFFER bCust FOR Cust. FOR EACH Cust: SET-EFFECTIVE-TENANT(Cust.Ten-name). CREATE bCust. BUFFER-COPY Cust TO bCust. DELETE Cust. END. Customers Orders … Items Customers Orders … Items Customers Orders … Items _field _tenant _file … State

43. Super-tenant programming with groups and SKIP-GROUP-DUPLICATES FOR EACH Item TENANT-WHERE TENANT-ID() > 0: SET-EFFECTIVE-TENANT (BUFFER-TENANT-ID(Item)). DISPLAY ItemNum Item-Desc. END. • LowesItm group appears twice – once for LowesBos tenant and once for LowesNY • To skip the 2nd LowesItm group use SKIP-GROUP-DUPLICATES FOR EACH Item TENANT-WHERE TENANT-ID() > 0 SKIP-GROUP-DUPLICATES: 1 Shovel bos 3Extension cable bos 4 Hammer ny 7 Green Paint bos 8 Faucet ny 9 Lamp bos LowesBOS And LowesNY, as GROUP LowesItm 2Lawn Mower 5Screw Driver 6Table HomeDepot 1 Shovel bos 3Extension cable bos 4 Hammer ny 7 Green Paint bos 8 Faucet ny 9 Lamp bos LowesBOS And LowesNY, as GROUP LowesItm

44. TENANT-WHERE Advanced Topics and areas of concern when using groups • More on SKIP-GROUP-DUPLICATES • TENANT-WHERE with joins where table/tenant grouping doesn’t match • TENANT-WHERE with datasets where table/tenant grouping doesn’t match • TENANT-WHERE with groups and/or joins where sequences are not unique

45. ? Questions