It’s been about 3 months now since my last blog; I have been busy on couple of projects with very high severity.
The issue I am going to discuss now has very significance which most people ignore for low to medium security level organizations. But of organizations where Security polices take higher preference, we need to identify the minimum database rights required for set up and work properly.
In normal implementations, what I and most of my colleagues around the world end up doing is, creating a user with sysadmin (Members of the sysadmin fixed server role can perform any activity in the server: Courtesy MSDN) rights, and doing the all the implementation using that.
One of my company’s clients, obviously with very high security policies in place, wanted me to setup environment where I sit with their Database administrator in order to identify the minimum requirement of rights.
I had told him my hunch is that you need to give dbcreator and ability to change / set up new users.
So we started our research, here are some important points form it
During installation
A user (HP QC/ALM DB Admin) with rights DB Creator (dbcreator) and Security Admin (securityadmin) is required for HP ALM
Reasons:
- A new database is to be created for Site Administration purposes
- A USER is to be created for record manipulation and connections establishing
One observation was made, for the created project, the user HP ALM DB Admin, was also given DB Owner permission for the database (Site Administration).
During ALM Usage
Now when ALM was being used, we observed that we need two types of permission sets
Normal Processes
The user TD (with maximum privileges of ddladmin) created during installation is enough for normal processes which include
- Record Manipulation
- Reports generation
- Setting up and working with Configurations (Site Admin)
- Working with Project Customization, where no Database Field is to be change
Administration Process
The user HP ALM DB Admin with elevated privileges, which being dbowner, dbcreator and securityadmin is required for administrative processes
- Project Management (Creating, deleting, restore, export, import, verify, maintain & Upgrade)
- Working with Project Customization, where Database Fields are going to be changed (new fields, change field types, delete fields)
- Domains
- DB SERVES
Precaution
But we need to understand one thing, the user HP ALM DB Admin needs to not be shared with everybody, as this user according to MSDN should be treated with similar care as of sysadmin (Members of the securityadmin fixed server role manage logins and their properties. They can GRANT, DENY, and REVOKE server-level permissions. They can also GRANT, DENY, and REVOKE database-level permissions if they have access to a database. Additionally, they can reset passwords for SQL Server logins.)