---
# System prepended metadata

title: 功能權限架構

---

# 功能權限架構
Last Review: 2024.07.16 by Taiwei Ciou
## ER Diagram
```mermaid
---
title: Functional Permission ER Diagram
---
erDiagram
    s_user }o--|| s_usertype : ""
    s_usertype ||--o{ s_usertypefunction : ""
    s_usertypefunction }o--|| s_function : ""

    s_user{
        %% User Information
        varchar(36)     acctId 				PK "Guid"
        varchar(36)     usertypeId          FK
    }
    
    s_usertype{
        varchar(36) usertypeId PK 
        nvarchar(100) usertypeName
        nvarchar(250) description
        
    }
    
    s_function{
        varchar(36) functionId PK
        nvarchar(100) functionName 
        nvarchar(250) description
        int isActive "1:是/2:否; Default:1"
    }
    
    s_usertypefunction{
        varchar(36) usertypefunctionId PK
        varchar(36) usertypeId FK
        varchar(36) functionId FK
    }
   
```
* key值按往例使用varchar(36)
* description 使用nvarchar 以儲存中文；長度按前例設為(250)
* isActive 按往例以int儲存。
* 出於簡潔考量，上圖省略`createdBy`(varchar36, FK), `createdOn`(DateTime), `modifiedBy`(varchar36, FK), `modifiedOn`(DateTime)。
* `s_user`僅標示相關欄位。

## MSSQL DDL

Define `s_usertype`, `s_function`, and `s_usertypefunction`.
```sql=
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[s_usertype](
	[usertypeId] [varchar](36) NOT NULL,
	[usertypeName] [nvarchar](100) NULL,
	[description] [nvarchar](250) NULL,
	[createdOn] [datetime] NULL,
	[createdBy] [varchar](36) NULL,
	[modifiedOn] [datetime] NULL,
	[modifiedBy] [varchar](36) NULL,
	CONSTRAINT PK_s_usertype PRIMARY KEY CLUSTERED (usertypeId)
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[s_function](
	[functionId] [varchar](36) NOT NULL,
	[functionName] [nvarchar](100) NULL,
	[description] [nvarchar](250) NULL,
	[isActive][int] DEFAULT 1,
	[createdOn] [datetime] NULL,
	[createdBy] [varchar](36) NULL,
	[modifiedOn] [datetime] NULL,
	[modifiedBy] [varchar](36) NULL,
	CONSTRAINT PK_s_function PRIMARY KEY CLUSTERED (functionId)
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[s_usertypefunction](
	[usertypefunctionId] [varchar](36) NOT NULL,
	[usertypeId] [varchar](36) NOT NULL,
	[functionId] [varchar](36) NOT NULL,
	[createdOn] [datetime] NULL,
	[createdBy] [varchar](36) NULL,
	[modifiedOn] [datetime] NULL,
	[modifiedBy] [varchar](36) NULL,
	CONSTRAINT PK_s_usertypefunction PRIMARY KEY CLUSTERED (usertpefunctionId),
    CONSTRAINT FK_s_usertypefunction_usertype FOREIGN KEY (usertypeId) REFERENCES [dbo].[s_usertype](usertypeId),
    CONSTRAINT FK_s_usertypefunction_function FOREIGN KEY (functionId) REFERENCES [dbo].[s_function](functionId)
) ON [PRIMARY]
GO
```
Add new columns `usertypeId` to emtity `s_user` for associating the users to function permissions.
```sql=
ALTER TABLE [dbo].[s_user]
ADD [usertypeId] [varchar](36) NULL,
	CONSTRAINT FK_s_user_usertype FOREIGN KEY (usertypeId) REFERENCES [dbo].[s_usertype](usertypeId)
GO
```
## 功能設計

![functionauthorizationtable](https://hackmd.io/_uploads/r1qbWeiVC.png)


| | 平台管理者 | 資料維護者 - 全部 | 資料維護者 - 活動數據 | 專案管理者 |
| --- | --- | --- | --- | --- |
| 使用情境 | 管理整個平台包含使用者權限等 | 匯入並維護全部資料，包含活動數據、係數等 | 僅匯入並維護活動數據 | 管理者被視設定目標與分析同家減量方向 |
|使用者管理(User Management)|V|V|||
| 技術支援 (Technical Support) | V | V | V | V |
| 權限設定 (Data Permission & Function Permission) | V | | | |
| 專案列表 (Project List) | V | V|V |V |
| 公司/組織資料維護 (Organizational Profile) | V | V |  | |
| 設定檔維護 (Reference Data) | V | V | | |
| 係數管理 (Emission Factor) | V | V | | |
| 儀表板 (Dashboard) | V | V |  | V |
| 標的產品專案管理 (Project Profile) | V | V | V | V |
| 製程地圖 (Processing Map) | V | V |V | V |
| 數據匯入 (Data Import) | V | V | V | |
| 碳排計算 (Emission Reporting) | V | V |  | V |

## 使用者類型-功能資料

**待更新項目**
- [ ] `s_usertypefunction`添加`isRead`,`isCreate`,`isUpdate`,`isDelete`四個欄位

### s_usertype
```sql=
INSERT INTO [dbo].[s_usertype]
           ([usertypeId]
           ,[usertypeName]
           ,[description]
           ,[createdOn]
           ,[createdBy]
           ,[modifiedOn]
           ,[modifiedBy])
     VALUES
           ('Default-usertype-platformadministrat','Platform_Administrator','平台管理者:管理整個平台，包含使用者權限等。', GETDATE() AT TIME ZONE 'UTC' AT TIME ZONE 'Taipei Standard Time', 'Default', GETDATE() AT TIME ZONE 'UTC' AT TIME ZONE 'Taipei Standard Time', 'Default'),
           ('Default-usertype-datamaintainer-all-','Data_Maintainer_All','資料維護者-全部：匯入並維護全部資料，包含活動數據、係數等', GETDATE() AT TIME ZONE 'UTC' AT TIME ZONE 'Taipei Standard Time', 'Default', GETDATE() AT TIME ZONE 'UTC' AT TIME ZONE 'Taipei Standard Time', 'Default'),
           ('Default-usertype-datamaintainer-acti','Data_Maintainer_Activity_Data','資料維護者-活動數據：僅匯入並維護活動數據。', GETDATE() AT TIME ZONE 'UTC' AT TIME ZONE 'Taipei Standard Time', 'Default', GETDATE() AT TIME ZONE 'UTC' AT TIME ZONE 'Taipei Standard Time', 'Default'),
           ('Default-usertype-projectmanager-0000','Project_Manager','專案管理者:檢視碳足跡與分析洞察減量方向。', GETDATE() AT TIME ZONE 'UTC' AT TIME ZONE 'Taipei Standard Time', 'Default', GETDATE() AT TIME ZONE 'UTC' AT TIME ZONE 'Taipei Standard Time', 'Default')
GO
```

### s_function
```sql=
INSERT INTO [dbo].[s_function]
           ([functionId]
           ,[functionName]
           ,[description]
           ,[isActive]
           ,[createdOn]
           ,[createdBy]
           ,[modifiedOn]
           ,[modifiedBy])
     VALUES
           ('Default-function-technicalsupport-00', 'Technical Support', NULL, 1, GETDATE() AT TIME ZONE 'UTC' AT TIME ZONE 'Taipei Standard Time', 'Default', GETDATE() AT TIME ZONE 'UTC' AT TIME ZONE 'Taipei Standard Time', 'Default'),
           ('Default-function-Datafunctionpermiss', 'Data & Function Permission', NULL, 1, GETDATE() AT TIME ZONE 'UTC' AT TIME ZONE 'Taipei Standard Time', 'Default', GETDATE() AT TIME ZONE 'UTC' AT TIME ZONE 'Taipei Standard Time', 'Default'),
           ('Default-function-projectlist-0000000', 'Project List', NULL, 1, GETDATE() AT TIME ZONE 'UTC' AT TIME ZONE 'Taipei Standard Time', 'Default', GETDATE() AT TIME ZONE 'UTC' AT TIME ZONE 'Taipei Standard Time', 'Default'),
           ('Default-function-organizationprofile', 'Organizational Profile', NULL, 1, GETDATE() AT TIME ZONE 'UTC' AT TIME ZONE 'Taipei Standard Time', 'Default', GETDATE() AT TIME ZONE 'UTC' AT TIME ZONE 'Taipei Standard Time', 'Default'),
		   ('Default-function-referencedata-00000', 'Reference Data', NULL, 1, GETDATE() AT TIME ZONE 'UTC' AT TIME ZONE 'Taipei Standard Time', 'Default', GETDATE() AT TIME ZONE 'UTC' AT TIME ZONE 'Taipei Standard Time', 'Default'),
		   ('Default-function-emissionfactor-0000', 'Emission Factor', NULL, 1, GETDATE() AT TIME ZONE 'UTC' AT TIME ZONE 'Taipei Standard Time', 'Default', GETDATE() AT TIME ZONE 'UTC' AT TIME ZONE 'Taipei Standard Time', 'Default'),
		   ('Default-function-dashboard-000000000', 'Dashboard', NULL, 1, GETDATE() AT TIME ZONE 'UTC' AT TIME ZONE 'Taipei Standard Time', 'Default', GETDATE() AT TIME ZONE 'UTC' AT TIME ZONE 'Taipei Standard Time', 'Default'),
		   ('Default-function-projectprofile-0000', 'Project profile', NULL, 1, GETDATE() AT TIME ZONE 'UTC' AT TIME ZONE 'Taipei Standard Time', 'Default', GETDATE() AT TIME ZONE 'UTC' AT TIME ZONE 'Taipei Standard Time', 'Default'),
		   ('Default-function-processingmap-00000', 'Processing map', NULL, 1, GETDATE() AT TIME ZONE 'UTC' AT TIME ZONE 'Taipei Standard Time', 'Default', GETDATE() AT TIME ZONE 'UTC' AT TIME ZONE 'Taipei Standard Time', 'Default'),
		   ('Default-function-dataimport-00000000', 'Data import', NULL, 1, GETDATE() AT TIME ZONE 'UTC' AT TIME ZONE 'Taipei Standard Time', 'Default', GETDATE() AT TIME ZONE 'UTC' AT TIME ZONE 'Taipei Standard Time', 'Default'),
		   ('Default-function-emissionreporting-0', 'Emission Reporting', NULL, 1, GETDATE() AT TIME ZONE 'UTC' AT TIME ZONE 'Taipei Standard Time', 'Default', GETDATE() AT TIME ZONE 'UTC' AT TIME ZONE 'Taipei Standard Time', 'Default'),
           ('Default-function-usersetting-0000000', 'User Setting', NULL, 1, GETDATE() AT TIME ZONE 'UTC' AT TIME ZONE 'Taipei Standard Time', 'Default', GETDATE() AT TIME ZONE 'UTC' AT TIME ZONE 'Taipei Standard Time', 'Default')

```

### usertypefunction
```sql=
INSERT INTO [dbo].[s_usertypefunction]
           ([usertypefunctionId]
           ,[usertypeId]
           ,[functionId]
           ,[createdOn]
           ,[createdBy]
           ,[modifiedOn]
           ,[modifiedBy])
     VALUES
           -- Platform Administrator's function authorizations
           ('Default-usertypefunction-PA-00000012','Default-usertype-platformadministrat','Default-function-usersetting-0000000',GETDATE() AT TIME ZONE 'UTC' AT TIME ZONE 'Taipei Standard Time', 'Default', GETDATE() AT TIME ZONE 'UTC' AT TIME ZONE 'Taipei Standard Time', 'Default'),
           
           -- Data Maintainer-All's function authorizations
           ('Default-usertypefunction-DM1-0000012','Default-usertype-datamaintainer-all-','Default-function-usersetting-0000000',GETDATE() AT TIME ZONE 'UTC' AT TIME ZONE 'Taipei Standard Time', 'Default', GETDATE() AT TIME ZONE 'UTC' AT TIME ZONE 'Taipei Standard Time', 'Default')
           
```