Just want to know everyone's experience and take on managing users/authentication on a multi-node db2 cluster with users groups. I have 17 apps in production (project based company, only 2 online apps), and some 30 users with 7 groups.
- prodsel - group that has select privilege on all tables
- produpdt - update group on selective tables (as required by the apps)
- proddel - delete
- prodins - insert permissions for the group
Now what my company does is when an app uses certain user (called app1user), and needs select and insert privilege on a table, they 1. grant select and insert for prodsel, prodins respectively 2. add the user under those two groups... now this creates one to many relationship between user and privileges, and this app1user also gets select on other tables granted for the prodsel group. I know this is wrong. Before I explain, I need to know how this is done elsewhere. Please share your experiences, even if you use other Databases that uses OS level authentication.
The clean way would be to have the users only per application. You can share a user between application if both apps are essentially one system. E.g. You can share the users if all apps are part of a marketing systems. If some apps are part of the marketing solution and others are part of the human resource system, than definitely don't share the user accounts/groups.
The less permission a user has, the less side effects and security breaches are possible. Lots (if not most) of security breaches are originated internally.
For me it sounds strange to create groups named after rights.
I would create groups that represent either the application or a typical role profile.
Say the database is called DB and the application is called APP, further we call one role EDITOR and the other READER. In this case I would add the groups
with all necessary rights needed to fulfill the tasks.