Transcript Progress OpenEdge Advanced Business Language Multi

OpenEdge ABL Multi-tenancy
Americas PUG Challenge
Mary Székely
OpenEdge Fellow
May
2012
Agenda
 Introduction
 Regular Tenant Programming Model
 Super-tenant Programming Model
 AppServer and Client-Principal
 Questions
2
© 2012 Progress Software Corporation. All rights reserved.
Multi-Tenant Database
 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
3
© 2012 Progress Software Corporation. All rights reserved.
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.
4
© 2012 Progress Software Corporation. All rights reserved.
Multi-Tenant Database Users Access to Tenants
 All users can access non-Multi-tenant (shared ) data as usual.
•
Schema tables and temp-tables are always shared
 Regular tenant users and Default 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
• Cannot access the private segments of any other 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
5
© 2012 Progress Software Corporation. All rights reserved.
Data Access for 2 tenants, HomeDepot and Lowes
Simple Multi-Tenant and Shared Data
HomeDepot
Customers
Tenancy Layer
Orders
Items
Lowes
Customers
Orders
Items
Default
…
deallocated, or newly migrated data
Shared
_file
State
6
…
© 2012 Progress Software Corporation. All rights reserved.
_field
…
_tenant
…
Meta Schema for Domains and Tenants in the
database
 An OpenEdge Tool creates a Tenant by providing:
• A record in the _tenant schema table
• A related record in the _sec-authentication-domain
• New in 11.1, the _sec_authentication-system table can
have user ABL .p or .cls authentication plugins
_sec-authentication-system
_oeusertable (_user)
appauth Applugin.p
_sec-authentication-domain
_tenant
“”
| “”
|_oeusertable| Default
HomeDepot | access-cd | appauth
Lowes
7
| access-cd | appauth
© 2012 Progress Software Corporation. All rights reserved.
Default
| 0
|HomeDepot
HomeDepot | 1
| Lowes
Lowes
| 2
Users Are Granted Access to Tenants by Domains
Suzi HomeDepot
Allen HomeDepot
Cat HomeDepot
Rich HomeDepot
Rich Lowes
John Lowes
Claudio Lowes
Louie Lowes
Domains
name
Data
HomeDepot
tenant
Customers
HomeDepot
HomeDepot
name
tenant
Lowes
Lowes
name
tenant
blank
Default
edward
james
Orders
Tenancy Layer
Users
© 2012 Progress Software Corporation. All rights reserved.
…
Lowes
Customers
Orders
Items
…
Default
deallocated or migrated data
Shared
_file
state
8
Items
_field
…
_tenant
…
Using a CLIENT-PRINCIPAL to get to a Domain
 The CLIENT-PRINCIPAL is an ABL built-in object with methods similar
to SETUSERID
 Getting a domain using SETUSERID (obsolete):
IF NOT SETUSERID(“rich@homedepot”,encoded_password)
THEN error…
 Getting the same domain using CLIENT-PRINCIPAL:
CREATE CLIENT-PRINCIPAL hCP.
hCP:INITIALIZE(“rich@homedepot”,?,?,encoded_password).
IF NOT SET-DB-CLIENT(hCP,dbname)
THEN error…
 SEAL the CLIENT-PRINCIPAL or NOT??
9
© 2012 Progress Software Corporation. All rights reserved.
11.x uses CLIENT-PRINCIPALs to Manage Users
and their access to Tenant data
 BEST security
• Use SET-DB-CLIENT() or SECURITY-POLICY:SET-CLIENT()
• 11.1+ : configurable server-side ABL plug-in which results in a sealed
CLIENT-PRINCIPAL with no domain access code outside database
• 11.0+:configurable _USER and _OSlocal plug-in
 BETTER security
• Use SET-DB-CLIENT() or SECURITY-POLICY:SET-CLIENT()
• Client ABL creates/seals CLIENT-PRINCIPAL and SSO to database
• Requires secure r-code with domain access code outside database
 OK security
• Use SETUSERID()
• Not extensible – no more enhancements
• Continue to use in data servers (for now )
• Do replace for OpenEdge 11.x RDBMS
10
© 2012 Progress Software Corporation. All rights reserved.
Agenda
 Introduction
 Regular Tenant Programming Model
 Super-Tenant Programming Model
 AppServer and Client-Principal
 Questions
11
© 2012 Progress Software Corporation. All rights reserved.
The same pcode and rcode can serve for all
Regular tenants
 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
12
© 2012 Progress Software Corporation. All rights reserved.
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.
Customer
Home
Depot
13
1 Albert Hall
2 Candace Jones
3 Carrie Abrahm
© 2012 Progress Software Corporation. All rights reserved.
Customer
Lowes 1 Fred Smith
2 Joan Adlon
3 George Holmes
Regular tenant ABL
FIND FIRST Customer. /*automatically gets the right tenant*/
DISPLAY CustNum Name.
Home 1 Albert Hall
Depot
Lowes 1 Fred Smith
CREATE Customer.
/*automatically goes to the right tenant*/
Name = “New Cust”
DISPLAY CustNum Name.
Home 4 New Cust
Depot
14
© 2012 Progress Software Corporation. All rights reserved.
Lowes 4 New Cust
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
Home
Depot
1 Albert Hall
2 Candace Jones
3 Carrie Abrahm
Lowes 1 Fred Smith
2 Joan Adlon
3 George Holmes
15
© 2012 Progress Software Corporation. All rights reserved.
Sequences – shared across tenants
 For the same database, the custNum
from a shared or non-multi-tenant
sequence will number consecutively
across tenants
Customer
 The custNum therefore is unique
across all tenants (only 1 Cust 4)
 Why would you ever want this?
FOR EACH Customer,
EACH Order of Customer.
• If the Order table is shared, then the
Order.CustNum would be non-unique
and useless (e.g. 2 Cust 4’s) unless the
CustNum sequence is shared.
16
© 2012 Progress Software Corporation. All rights reserved.
Home
Depot
2 Albert Hall
5 Candace Jones
6 Carrie Abrahm
Lowes 1 Fred Smith
3 Joan Adlon
4 George Holmes
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.
Home
Depot
17
HomeDepot
Lowes
Customer
Customer
1 Albert Hall
2 Candace Jones
3 Carrie Abrahm
© 2012 Progress Software Corporation. All rights reserved.
Lowes
1 Fred Smith
2 Joan Adlon
3 George Holmes
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”:
18
© 2012 Progress Software Corporation. All rights reserved.
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.
Item
Item
Home
Depot
19
2 Lawn Mower
5 Screw Driver
6 Table
© 2012 Progress Software Corporation. All rights reserved.
LowesBOS
And
LowesNY, as
GROUP
LowesItm
1 Shovel
bos
3 Extension cable bos
4 Hammer
ny
7 Green Paint
bos
8 Faucet
ny
9 Lamp
bos
Data Access for 3 tenants, HomeDepot and
LowesBos, LowesNy and 1 Item table group
HomeDepot
Customers
Orders
Customers
Tenancy Layer
LowesBos
…
Orders
…
…
LowesItm Group
Items for both LowesBos and Ny
Default
Shared
20
Orders
Customers
LowesNy
Items
deallocated, or recently migrated data
_file
_field
State
© 2012 Progress Software Corporation. All rights reserved.
…
_tenant
…
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
 BUFFER-GROUP-ID and BUFFER-GROUP-NAME functions
and buffer methods provide group information for a buffer
 You must use shared sequences for unique indexes with groups
Item
LowesBOS
And
LowesNY, as
GROUP
LowesItm
21
© 2012 Progress Software Corporation. All rights reserved.
1 Shovel
bos
3 Extension cable bos
4 Hammer
ny
7 Green Paint
bos
8 Faucet
ny
9 Lamp
bos
Agenda
 Introduction
 Regular Tenant Programming Model
 Super-tenant Programming Model
 AppServer and Client-Principal
 Questions
22
© 2012 Progress Software Corporation. All rights reserved.
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
• Utilities where simultaneous access to multiple tenant’s data is
required
 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
23
© 2012 Progress Software Corporation. All rights reserved.
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
SET-EFFECTIVE-TENANT(“HomeDepot”).
FIND FIRST Customer.
DISPLAY CustNum Name.
RUN myCustApp.p etc.
Home 1 Albert Hall
Depot
 All FINDs,CREATEs,DELETEs,FOR EACHs, all ABL will use
HomeDepot indexes and access HomeDepot tenant records
24
© 2012 Progress Software Corporation. All rights reserved.
BUFFER-TENANT-ID() and BUFFER-TENANTNAME 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 for the Super-tenant not the
buffer.
 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/methods when applied to a group record:
• Sometimes return an arbitrary member of the group
• Usually return the effective-tenant if it is a member of the group
25
© 2012 Progress Software Corporation. All rights reserved.
Using _tenant schema table to scan across
tenants
FOR EACH _Tenant WHERE _Tenant-Name < “M”:
SET-EFFECTIVE-TENANT(_Tenant._TenantId).
FOR EACH Customer:
DISPLAY BUFFER-TENANT-ID(Cust) CustNum Name.
RUN myCustApplication.p(CustNum).
END.
END.
Home
Depot
Customer
1 1 Albert Hall
1 2 Candace
1 3 Carrie
Lowes 2 1 Fred Smith
26
2 2 Joan Adlon
2 3 George
© 2012 Progress Software Corporation. All rights reserved.
Using TENANT-WHERE to scan across tenants
FOR EACH Customer TENANT-WHERE TENANT-NAME() < “M”:
SET-EFFECTIVE-TENANT(BUFFER-TENANT-ID(Cust)).
DISPLAY BUFFER-TENANT-ID(Cust) CustNum Name.
RUN myCustApplication.p(CustNum).
END.
Home
Depot
Customer
1 1 Albert Hall
1 2 Candace
1 3 Carrie
Lowes 2 1 Fred Smith
27
2 2 Joan Adlon
2 3 George
© 2012 Progress Software Corporation. All rights reserved.
TENANT-WHERE with Sorting may be Slow
 Default order is by _tenant, overrideable by using a BY phrase
FOR EACH Customer TENANT-WHERE TENANT-ID() > 0
BY BUFFER-TENANT-NAME(Customer) BY Customer.Name:
SET-EFFECTIVE-TENANT(BUFFER-TENANT-ID(Customer)).
Etc.
End.
 Sorting will be slow, across all _tenants. It would be better to
use the nested form which can take advantage of indexing.
FOR EACH _tenant WHERE _tenant._tenantId > 0
BY _tenant._tenant-name:
SET-EFFECTIVE-TENANT(_tenant._tenantId).
FOR EACH Customer BY Customer.Name:
Etc.
END.
END.
28
© 2012 Progress Software Corporation. All rights reserved.
TENANT-WHERE with Joins
 Only 1 level of join can have the TENANT-WHERE phrase
 The AVM 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.
29
© 2012 Progress Software Corporation. All rights reserved.
Super-tenants and Migration
Multi-Tenant and Shared Data
 Scenario:
• Log in as a Super-tenant user, with
default effective-tenancy.
HomeDepot
Customers
Orders
Items
 To copy 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.
END.
Tenancy Layer
Lowes
 Afterward, delete default data.
Shared
…
Customers
Orders
…
Items
Default
Customers
Orders
Items
_file
_field
…
_tenant
State
30
© 2012 Progress Software Corporation. All rights reserved.
…
Super-tenants and Migration - note: TRIGGERS
DEFINE BUFFER bCust FOR Cust.
FOR EACH Cust:
CREATE bCust FOR TENANT(Cust.Ten-name). /*CREATE triggers?*/
BUFFER-COPY Cust TO bCust.
DELETE Cust.
/*DELETE TRIGGERS may not work*/
END.
BELOW IS BETTER AND SAFER IF THERE ARE TRIGGERS !!
FOR EACH Cust:
SET-EFFECTIVE-TENANT(Cust.Ten-name).
CREATE bCust.
/* CREATE triggers will work fine*/
BUFFER-COPY Cust TO bCust.
SET-EFFECTIVE-TENANT(0).
DELETE Cust.
/* DELETE triggers will work fine*/
END.
31
© 2012 Progress Software Corporation. All rights reserved.
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-GROUPDUPLICATES
FOR EACH Item TENANT-WHERE
TENANT-ID() > 0
SKIP-GROUP-DUPLICATES:
32
© 2012 Progress Software Corporation. All rights reserved.
HomeDepot
2 Lawn Mower
5 Screw Driver
6 Table
LowesBOS
And
LowesNY, as
GROUP
LowesItm
1 Shovel
bos
3 Extension cable bos
4 Hammer
ny
7 Green Paint
bos
8 Faucet
ny
9 Lamp
bos
LowesBOS
And
LowesNY, as
GROUP
LowesItm
1 Shovel
bos
3 Extension cable bos
4 Hammer
ny
7 Green Paint
bos
8 Faucet
ny
9 Lamp
bos
Agenda
 Introduction
 Regular Tenant Programming Model
 Super-tenant Programming Model
 AppServer and Client-Principal
 Questions
33
© 2012 Progress Software Corporation. All rights reserved.
AppServer and Regular Multi-tenant Programming
 All types of AppServers will need to manage user logins with
CLIENT-PRINCIPALS to get correct user auditing, permissions,
and tenancy
 Stateless, State-free and WebSpeed AppServers in a Multitenant environment will need some form of context management
of CLIENT-PRINCIPALS because
•
multiple appserver instances serve the same client “session”
across multiple requests
 State-reset, State-aware can optionally use context
management to resume user logins that span connections
 Everything else works normally for a regular tenant user
34
© 2012 Progress Software Corporation. All rights reserved.
Anatomy of a Context Manager
 Has physical storage that
• Spans OS processes
• Spans multiple AppServers
• Spans AppServer starts & stops
 Stores raw CLIENT-PRINCIPAL and login session id
• Uses login session id as primary index
 Has basic operations for
•
•
•
35
startUserSession (export CLIENT-PRINCIPAL under the session id)
stopUserSession (import CLIENT-PRINCIPAL using session id, delete it)
restoreUserSession (import CLIENT-PRINCIPAL using session id)
© 2012 Progress Software Corporation. All rights reserved.
Using clientContextId for exporting CLIENTPRINCIPALs
/* new in OpenEdge 11.1 */
DEFINE VARIABLE reqInfo AS
Progress.Lang.OERequestInfo.
reqInfo = CAST(SESSION:CURRENT-REQUEST-INFO,
Progress.Lang.OERequestInfo).
CREATE CLIENT-PRINCIPAL hCP.
hCP:SESSION-ID =
reqInfo:clientContextId.
…
SET-DB-CLIENT(hCP) etc etc
CREATE mycp. /*new ctx record*/
mycp.sessionId = hCP:SESSION-ID.
mycp.cp = hCP:EXPORT-PRINCIPAL().
DELETE OBJECT hCP.
36
© 2012 Progress Software Corporation. All rights reserved.
Context store
mycp table
sessionId
|
ajfrbo9kk … |
lqjdkor71 … |
cp
raw data..
raw data..
Using clientContextId for importing CLIENTPRINCIPALs
/* new in OpenEdge 11.1 */
DEFINE VARIABLE reqInfo AS
Progress.Lang.OERequestInfo.
reqInfo = CAST(SESSION:CURRENT-REQUEST-INFO,
Progress.Lang.OERequestInfo).
FIND mycp WHERE mycp.sessionId =
reqInfo:clientContextId NO-ERROR.
IF NOT AVAILABLE mycp THEN error…
CREATE CLIENT-PRINCIPAL hCP.
hCP:IMPORT-PRINCIPAL(mycp.cp).
Context store
Ok = SET-DB-CLIENT(hCP).
If NOT Ok THEN error…
or
DELETE mycp …
37
© 2012 Progress Software Corporation. All rights reserved.
mycp table
sessionId
|
ajfrbo9kk … |
lqjdkor71 … |
cp
raw data..
raw data..
AppServer context management of CLIENTPRINCIPALS and tenancy
1. AppServer startup.p procedure :
–
Capture/create initial database user (such as “blank”) into a CLIENTPRINCIPAL to be used later to explicitly reset tenancy to default
2. User login: connect.p or equivalent :
–
Create and save the CLIENT-PRINCIPAL identity context
3. User logout: disconnect.p or equivalent :
–
Find and delete the CLIENT-PRINCIPAL identity context
4. Start remote procedure: activate.p (no equivalent ):
–
Find/receive the CLIENT-PRINCIPAL identity context
–
–
Switch user identity contexts from previous to current one
May include saving the context from the previous user
5. End remote procedure: deactivate.p (no equivalent):
–
Do any optional context and identity cleanup such as resetting
tenancy to the default one set up in startup.p
NOTE: State-reset and state-aware servers do usually not need 4 and 5.
Webspeed,/WebServices/AIA do 2 and 3 without clientContextId
38
© 2012 Progress Software Corporation. All rights reserved.
Sample code snippet to create a CLIENTPRINCIPAL in an AppServer login.p/connect.p
encrypted_pswd = “oech1::” +
audit-policy:encrypt-audit-mac-key(pswd).
hServ:CONNECT(“-S nnnn –H hostname”, userid, encrypted_pswd).
 The CONNECT method of the client’s SERVER object allows you to
optionally pass the userid, password, and a character string to the
AppServer. They become the 3 parameters to the connect.p on
the AppServer. This is one of many ways to get your
userid@domain and user password to the connect.p.
DEFINE INPUT PARAMETER user_domain
AS CHAR.
DEFINE INPUT PARAMETER encryptd_pswd AS CHAR.
DEFINE INPUT PARAMETER mychar
AS CHAR.
CREATE CLIENT-PRINCIPAL hCP.
hCP:INITIALIZE(user_domain,?,?,encrypted_pswd).
39
© 2012 Progress Software Corporation. All rights reserved.
AppServer context switching automatically clears
buffers and invalidates query index cursors
 When AppServer (or any AVM) switches tenant context because
its user is changed to be a user of a different tenant , the AVM
• clears out all Multi-tenant buffers (temp-tables are not Multi-tenant)
• marks all Multi-tenant index cursor scans as invalid
 ABL Code to handle a db tenant context switch should proactively
• clear buffers and temp-tables
• close existing queries and index scans
 State-aware and State-reset Appservers switch tenant context
per connection
• State-reset automatically clears out context
 Stateless/State-free Appservers switch tenant context per request
 Dangerous to use a Super-Tenant and SET-EFFECTIVETENANT on an AppServer since no automatic context clearing
happens
40
© 2012 Progress Software Corporation. All rights reserved.
Questions
41
© 2012 Progress Software Corporation. All rights reserved.
?
42
© 2012 Progress Software Corporation. All rights reserved.