0

I have an issue about creating database table for user to login in different level access of the system. I have 3 user roles "ENUM(master_admin, admin_country, admin_city )".

If the master_admin logged in will have access to the whole system, but if admin_city logged in will have access to his country only with countryID, and if admin_city logged in will have access to his city data only with cityID

The problem is on creating users table that will save info of different administrators so they can have access to their related part of the system. So later when other admin created to cover other parts of the system it will be easily to set them using the same users table.

I tried this:

users table
+--------+-----------+-------------+
| userID | countryID | user_role|  |
+--------+-----------+-------------+
|  1     |  23       | master      |             
+--------+-----------+-------------+

countries table
+-----------+-------------+
| countryID | countryName | 
+-----------+-------------+
|  23       |  US         |                  
+-----------+-------------+

coutrries table
+-----------+-------------+-------------+
| cityID    | countryID   | cityName    |
+-----------+-------------+-------------+
|  2        |  23         |  New York   |                
+-----------+-------------+-------------+

How can I set my users table for this problem.

Improve this question
7
  • 1
    What is the problem with the database schema you have already described here? Commented Feb 25, 2022 at 22:42
  • you can at al times change the design. It os normal to do that over time as necessity's change over time Commented Feb 25, 2022 at 22:48
  • The problem is when I want to assign user to manage his country with countryID so he can't see other country data the same as for city with cityID, but how can I save them to users table? Commented Feb 25, 2022 at 23:00
  • 1
    Pro tip: avoid ENUMs if you can. They're unreasonably time-consume to extend to new values on large tables. Commented Feb 25, 2022 at 23:23
  • Thanks @O.Jones. What are the best approach do you suggest to me? Commented Feb 25, 2022 at 23:30

2 Answers 2

Reset to default
2

Split the users table

users table
+--------+-----------+-----------------+
| userID | username  | email           |
+--------+-----------+-----------------+
|  1     |  adm      | master@master   |             
+--------+-----------+-----------------+

users_Role table

+--------+-----------+------------+-------------+
| userID | countryID | cityID     |  roleID     |
+--------+-----------+------------+-------------+
|  1     |  23       | NULL       |   1         |             
+--------+-----------+------------+-------------+
|  1     |  22       | NULL       |   2         |             
+--------+-----------+------------+-------------+
|  1     |  NULL     | 2          |  3          |             
+--------+-----------+------------+-------------+
role table
+-----------+-----------------+
|  roleID   | roleName        | 
+-----------+-----------------+
|  1        |  Master         |                  
+-----------+-----------------+
|  2        |  COUNTRYMASTER  |                  
+-----------+-----------------+
|  3        |  CityMaster     |                  
+-----------+-----------------+

OF course you could make a City/Countryid column, as the role defines what type of id is saved.

So can give or remove for every user individual rigst, per country and/or city.

User_role has redundant indormation so another rolentable is necessary for nomalization

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

Thanks @nbk, What happen to user_role if I decided to have column of accessID instead of countryID and cityID so next time i want to extend I would be required to create another column to handle another area, for example if I want to assign the master admin of streets table I will be required to add extra column for streetID to the user_role table?
you can make another column t save an id, or like i already said, i clumn for all id and rth roleid defines its type. So oyu add n a new role and in your app you handle the id depending of role.
Thanks very much @nbk. I will try your solution.
1

You didn't really try all that much, which means your question is reasonable vague. Whenever you design a system with varying levels of access you need to make very sure that access can't be accidentally granted. Even a bug shouldn't make this possible.

A bad way to do this would be to create a cityId in the users table. If there's a number there, say 2, the user has only access to data of New York, if it is 3 only Washington, etc. If the value is zero the user has access to all cities. Choosing zero seems to make sense here, but it is dangerous, because a bug in setting the cityId could set it to zero and give access that shouldn't be granted.

The normal way to do this is to make a separate table which very explicitly grants access. You could call this table permission. Each user can have multiple permissions. You could define a level in it: 'master', 'country' and 'city'. This tells you what kind of access someone has. Other fields could specify exactly which country or city.

Whenever the user accesses a resource you have to check it against the permissions an user has. access is only granted when the answer is positive. You have to write your software in such a way, that forgetting to check the permission, would break the functionality of the software.

I would also log every access, and every change made to the permission table. It might surprise you how often you will have to play detective and find out exactly who did what when.

No matter what you do, this will never be as secure as it can be. There's always a change an user can access something they shouldn't. It could be due to a bug, or a mistake by an administrator. The only way to have real security is to actually put cities and countries in different databases, and let users only exist in the database to which they are allowed to have access. Security and practicality often are enemies.

Improve this answer

1 Comment

Thanks @KIKO, your solution is good, but instead of having different databases, I would fiqure out a way to store either countryID or cityID to permission table, So I can assign user to his part by storing the id(countryID / cityID) when logged in. Any advice please if this seems to be bad idea for any reason.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.