My company is has a database that is used to track engineering change requests (ECR) for our products. If something needs to be fixed on a product, an ECR is submitted. The database is junk and they want to update how it works.
If an ECR is submitted, they would like the security of the new ECR to change, depending on if it has been accepted, is in progress, is rejected, or is completed.
Is there a way (with ACLs, Roles, etc.) to set it up so that based on the progress of the ECR, an individual can or cannot edit certain fields? For example, if an ECR is pending, User #1 can edit it, but once it has been accepted and is in progress, he can no longer touch it.
I should add that I’m new at database design, so I’m still learning.