When using a Microsoft SQL Database with Enterprise Analyzer, is it possible to restrict a user’s role to only ‘db_datareader’?
Is it possible to assign only the db_datareader privilege to a database user id that is going to be used with Enterprise Analyzer (EA)?
The db_datareader role grants a user select access rights to the user tables (ie the EA tables) within a database. The other roles and privileges that EA users require are:
- db_datawriter – this grants a user insert/update and delete access to the user tables within the database.
- db_owner – this grants a user the ability to alter, create and delete the user tables within a database. By having the db_owner role assigned, it also implies that user has db_datareader and db_datawriter.
- The user must be granted permission to connect to the server.
- The login of the user must be enabled.
- User mapping to all of the EA database’s. The user must be mapped to the EA database(s) that are to be used and the default schema must be set to dbo.
Within EA there are two levels of user access the master user and the normal user. The tasks these users would perform include:
- Create a Workspace, Delete a Workspace, Configure a Workspace
- Add and remove files from a Workspace
- Verify source files
Because of the above list of tasks, the master user will need to create, drop and alter tables. The master user will also need insert / delete / update and select data from the tables as well. This means that the master user requires db_owner role for the database.
A normal user will do tasks like:
- Run the various reports available within EA, such as reference reports and CRUD reports.
- Manage tags
- Manage their own projects
- Run queries against the workspace using Interactive Analysis
- Create Business rules
- Perform decision resolution
- Perform Effort Estimation
- Run Change Analyzer
If EA is started so that it connects to the MS SQL database with a user id that has the following privileges assigned to them:
- The public and db_dataread roles for a database that is used by EA
- The user is mapped to the database so they can connect to the database
- The user’s login is enabled and they have been granted connect permission to the server.
Then this will lead to the error shown below.
The reason for this error is that EA will create an activity log for every session that is attached to the workspace. To be able to create the activity log, the user must have insert privilege to the relevant table. The db_dataread only grants select privileges to the user tables.
Also, most operations that a normal user performs involves storing data into the database behind the workspace. All the lists that hold the results of the queries that users perform against the source modules in the workspace are held within the database.
Assigning tags to modules requires updates to the tables in the workspace. Changing any of the options within EA means that the database is updated to point to the correct options files. Changing the projects that modules are in requires updates to the database tables.
This means that even normal users require the db_datawriter role as this grants a user the privileges to insert, delete and update data within the user tables.
Below is an example of the roles that a normal user should be setup with to use an EA database.
The user must have read and write access to all of the workspace directories, especially if the workspace is accessed via a network share.
When granting access to the database for a workspace, the user needs to be assigned the default schema of dbo. When EA creates the tables in the database, it creates them under the dbo schema. By assigning the default schema of dbo, this means that EA will be able to access the tables.
For more information on the different roles that can be used within a MS SQL database see this link: http://msdn.microsoft.com/en-us/library/ms189121.aspx
Incident # 2788322