Based on the applications or projects I developed for the company. I am responsible for developing and implementing securities at the application level. It is Active Directory-integrated but at the application level, we need to grab the groups/roles attribute for user & roles tables.
Another option is to always query the AD whenever the user uses the applications and its different aspects. To give access to a user, the application must determine if the user belongs to the correct group i.e Admin, regular user and etc. But in my case, I decided to save the user’s group/roles in the table and sync up to AD in the first initialization.
Don’t be confused with the Entity Relationships Diagram (ERD) above. As you go along with the projects you are developing the diagram will make more sense based on what you are trying to accomplish.
Let’s say we have the given Windows Form below but you want to control all objects in the application level in more granular way.
In the first time, it seems like it is hard to make it sense, but don’t worry, I will explain why we need to accomplish this and how I did it step by step.
So, Why do we need to normalize our table? Well, without normalization it is not because of database bad design but also it’s hard for the database engine to figure out which to point base on what query to be executed.
The common reasons for normalization:
- Avoid duplicate records i.e descriptions
- Make reusable data (Data Manipulation)
- A compliment to indexing for faster executions (Performance)
- Visualization to table/objects relationships (Integrity)
- Avoid updates/insertions anomalies
- Prevent search and sorting issues
More about database normalization visit WiKipedia.
STEP 1 – The User’s Profile Table
We can see that the user profile table met the 1NF and 2NF. By doing this we can reuse the UserProfileRoleID value attribute and both the other 2 columns dependent on the primary ID.
The User’s profile table relates to who is the user and what is the user’s role. Imagine if we will not split the user’s role table. In that case, we need to keep using the role’s description over and over again whenever we decide to add a new user. Did you get it?
Step 2 – The User’s Configuration Table
The Configuration table composed of a user’s profile and additionally, we can inject the database will be used for that profile. In this case, you can see that David “Admin” have both access to both database i.e Test & Production. The Configuration table satisfies the 1NF & 2NF.
It is always good to have a separate what Database connection the user can access. For example, if the user is new then you probably want to point to the test environment first.
Step 3 – Objects Collections Table
We can see here that all forms, groups of forms and the object’s IDs belong to groups/forms. Having all these in a separate table we are able to reuse the obj_Access_Id.
The three other columns depend on obj_Access_Id and this will be used to map what users can access and its privilege.
You noticed that Obj_Id & Group_ID can be reuse for Authorization & Referrals forms. The Object’s Collections table satisfies the 3NF & 4NF.
Step 4 – User’s Object Access Table
We can see here that the User’s Object Access table has the info of what obj_Access_Id, obj_Access_Level, and what User_Config_Id it points to.
If you double-check the Objects Collections table, We can also create special permission and assign it to a user. The User’s Access table satisfies the 3NF.
Sample User Interface
As you can see, we have a Role’s management where we can use to create an Access Level template/privileges for a Role, Ofcource “Developer” & “Admin” can be omitted. You can also create User’s management and give special access to users that associated with the Roles. i.e A & B have the same Roles but you want to give access to B for certain Forms, Groups or Fields.
The main point of this normalization is to save space to the database and make forms, groups/frames, and objects names reusable (Avoid duplications) for a lot of users :-).
If you like it, please share, comment and don’t forget to signup to get updates.