Thanks everyone so much for your feedback. We’ve decided to not allow write access to the MS SQL db, although based on our understanding of the risks it looks like we will allow read only access for developers. My understanding of the reasons not to allow read only access are
- Security is bypassed (anyone with MS SQL access has access to all tables)
- Data may be stale (cached data may not have been flushed to the DB)
Please note we have a good number of system level software engineers(C and Assembly devs). Some of these developers would like access to the DB so they can generate custom reports with tools they are familiar with and prefer. Ownership trusts these engineers with access to all data with in Epicor and feels the benefits of giving them the access they desire so they can build reports to help the business is the best path forward. I also understand there are other benefits to getting direct access to the DB such as having the ability to create views which I understand can have a pretty big performance improvement. I get where these developers are coming from as I personally think it would be nice to be able to run a quick SQL query via Sequel Pro.
Assuming we’re not missing any other reasons to not give read only DB access, we’re curious to better understand how caching works in Epicor. Is anyone familiar or is there any documentation on this topic? As an example I can’t imagine data would remain in an object in RAM and not flushed to disk/db for more than a couple seconds.
In giving direct DB read only access are there any recommendations on how to set this up. That is should we create a MS SQL user with read only access and give the credentials to all developers, create one user per developer, or something else? Are there any other best practices we should be following given that we plan to give developers read only access to MS SQL?