After a fresh infrastructure isntall, why does the WGNUser account persist as having dbcreator permissions in SQL Server? I don't see why it should even have it in the first place. Sure, I have no problem with WGNUser being mapped to dbo, but server wide dbcreator? Leave dbcreator permission to the SA credentials that were entered during the installation wizard used to create the database and users.
What this essentially does, is enable WGNUser to create, alter, and drop as many databases as it wants to. If you point the CMS database to a server farm or shared instance, for example, WGNUser is now a vulnerability. If its credentials are compromised, then the whole server is as good as gone. I hope that the custom SQL searches available in the administration and data management consoles are fully able to sanitise their input...
The only one scenario I could think where this could be assumed to be needed, would be during a service pack or hotfix update in which drastical changes to the schema need to be made. Although those are rare, the most I usually see are modifications to sprocs, functions, and tables, all of this can be accomplished without.
Please confirm why this permission persists, and if it can safely be removed, leaving WGNUser db_owner permission as already configured.
The DBCreator permission is needed to create the database in the first place on a new install and drop on a de-install, additionally providing scope for major schema changes during upgrades. We would not recommend making any changes to the defaulot DB permissions as this could impact the product, however if you feel that the DBCreator permission should be removed for security reasons, I would advise that you raise the matter in a support ticket to formalise a request for testing and review by CA DataMinder Engineering.
This is an issue that should be a public discussion.
I went through a full upgrade path from 14.1 -> 14.5 -> 14.6 with DBCreator disabled, and saw no problems in my testing at all. Additionally I went through all areas of the application where a user or administrator has application access to modify SQL queries. Most were properly sanitized, however one can fool the checks in the data management console by being crafty with DSQL. I'm not going to call this a security flaw, but more of a hats off to users who are interested in the inner functions of the database and know enough about the product and its restrictions, to attempt to try to bypass their own row level security.
Creation of the users and databases should be accomplished by the SA account entered during the installation wizard. It seems like those credentials are cached for unisntallation or installation repair purposes, but I have not ripped open the installer to find out for sure yet. That's on my list.
The earliest version of the software I have is 12.5, if I have enough time I will test the upgrade path with dbcreator off, but even though the schema changes transitioning to 14 are all capable within the db_owner permissions of WgnUser (or OrchUser.) I seriously think that the dbcreator privledge should be revoked. I know of the schema changes during this upgrade and none should involve dbcreator status.
Being that very few schema changes have resulted from many years of development, I do not project any "major" schema changes requiring this privledge for at least 5 years, if the product survives. Every database can be tuned, as well as reports and queries, but I don't suspect schema changes any time soon. More performance can be attained by upgrading to SQL Server 2008 (or better yet 2014 for columnar datastores) which I am working on tweaking.
Regarding the ability to nuke the database while uninstalling, I feel that, although insecure, that option is best left configured by the registry, in which it is. If during uninstallation, and the device is a CMS, the user should be prompted again for sufficient credentials to nuke the database. The opposite of "shoot first, ask questions later"
Community, if you are there, please reply with your thoughts and comments :)