Expansion Country Permission Creation

[amunn33]

Goal

Create tables for new permission settings that will be used for country expansion.

Details

• Permissions should be able to connect a user with a country/station and specific permission (i.e. IRF edit or Investigations Alert)
• Will be edited through the system set up on the client side
• Create views for the following:
  • GetCountries - Input: (none) Output: List of countries
  • GetStations - Input: (country_id) Output: List of stations within country
  • GetPermissions - Input (none) Output: List of all permissions
  • GetUserPermissions - Input: (user_id) Output: List of all permissions for given user
  • UpdateUserPermissions - Input: (user_id, country_id, station_id, permission_group?, [permission,..]) Output: Update user permissions

Questions

• When update is clicked do we send all permissions from that group along with a true/false value or just the permissions set to true along with the permission_group_id?

Table Examples

Permission

id	Group	Action
1	IRF	View
2	IRF	Add
4	IRF	Delete
5	IRF	Approve
10	Budget	Add
14	Alert	Investigations
20	Account	View
21	Account	Manage

Country

id	Name
2	Nepal
3	South Africa
4	Bangladesh
5	Thailand

Station

id	country_id	Name
1	2	Bhadrapur
27	3	Johannesburg

User

id	Name
1	Austin (Super User)
2	Ashish (Nepal Data Entry)
14	Phil (Accountant)
25	Bhadrapur Station
30	Anil (Investigator)
33	Kirk (Asia Transit Monitoring Director)
37	Bob (South Africa techie)

User Location Permission

user_id	country_id	station_id	permission_id	description
1	null	null	1	Austin can view all IRFs
1	null	null	4	Austin can delete all IRFs
2	2	null	5	Ashish can approve all Nepali IRFs
2	3	null	1	Ashish can view all SA IRFs
14	null	null	10	Phil can add budgets for all countries
25	null	1	2	Bhadrapur staff account can add Bhadrapur IRFs
25	2	null	1	Bhadrapur staff account can view all Nepal IRFs
30	2	null	14	Anil receives investigation alerts for Nepal
33	2	null	1	Kirk can view Nepal IRFs
33	4	null	1	Kirk can view Bangladesh IRFs
33	5	null	1	Kirk can view Thailand IRFs
33	2	null	20	Kirk can view Nepal accounts
33	4	null	20	Kirk can view Bangladesh accounts
33	5	null	20	Kirk can view Thailand accounts
37	3	null	21	Bob can manage South Africa accounts

[amunn33]

@scrishel How does this look?

[scrishel]

Expansion Permission Server.pdf

[amunn33]

Thanks for writing this up.

• I like the idea of permissions having attributes for group and action
• The value in parentheses for accounts is not important in terms of database design. I just wanted to make a somewhat real-life example so when I was creating users I wanted them to be real people and including their role/title was so that anyone reading it would have background for why that user would be granted those permissions.
• I like the inclusion of both country_id and station_id as a way to enforce referential integrity and it also clears up the global permission.
• Good catch on the duplication for Bhadrapur view permissions, that was an error on my part.
• Description column is also not necessary it was just a way to translate what the permission is doing for readability.