Tenant membership and delegated authorization

erDiagram
TENANT ||--o{ MEMBERSHIP : contains
USER ||--o{ MEMBERSHIP : joins
MEMBERSHIP ||--o{ ROLE_ASSIGNMENT : holds
ROLE ||--o{ ROLE_ASSIGNMENT : grants
ROLE ||--o{ ROLE_PERMISSION : includes
PERMISSION ||--o{ ROLE_PERMISSION : enables
TENANT ||--o{ RESOURCE : owns
RESOURCE ||--o{ ACCESS_AUDIT : records
MEMBERSHIP ||--o{ ACCESS_AUDIT : performs
TENANT {
 string tenant_id PK
 string name
}
USER {
 string user_id PK
 string email
}
MEMBERSHIP {
 string membership_id PK
 string tenant_id FK
 string user_id FK
 string status
}
ROLE {
 string role_id PK
 string scope
}
ROLE_ASSIGNMENT {
 string assignment_id PK
 string membership_id FK
 string role_id FK
}
PERMISSION {
 string permission_id PK
 string action
}
ROLE_PERMISSION {
 string role_id FK
 string permission_id FK
}
RESOURCE {
 string resource_id PK
 string tenant_id FK
}
ACCESS_AUDIT {
 string audit_id PK
 string resource_id FK
 string membership_id FK
}