Home > Articles > Data

  • Print
  • + Share This
Like this article? We recommend

Administrative Privileges on Development or QA Servers

Data security isn't a joking matter. The DBA's main responsibility is to provide database server uptime, but keeping data secure from wrong eyes is the next most-crucial responsibility. DBAs typically try to grant the least number of privileges that allow a particular individual to perform her job. This means that developers usually won't have administrative privileges on any database servers.

The SQL Server security model is very flexible and granular, but it does have a few nuances that can create heated discussions. For example, who should be able to restore databases on development servers? Developers argue, "This is a development server—hello, anybody there? Of course we're supposed to restore databases." On the other hand, DBAs posit that if a developer restored databases he could easily get access to sensitive data, such as employee salaries or social security numbers.

One viable solution is to scrub sensitive data after restoring the database. Then the DBA could create a login for developers and make it a member of the db_owner built-in database role. Developers can write queries and modify data using this account only after sensitive data is scrubbed. This solution still doesn't allow developers to restore their own databases, however. A better alternative is to place backups only on development servers that already have scrubbed data. This way, developers can safely create and restore as many databases as they want.

You can allow system administrator privileges to the developers without much harm, so long as DBAs control the backups available on development servers. Some DBAs will suggest that a privileged group should be administering all database servers, including development servers. Otherwise, developers could drop databases used by their peers or step on each other's toes in some other way. This argument can be avoided by instituting strict disciplinary measures. Developers who intentionally damage databases used by their peers can be terminated, or simply not considered for the next raise!

QA servers are somewhat of a different beast. QA often involves checking the actual customer names or account numbers; therefore, scrubbing data might not be an option. The same holds true for IAT/PPT servers. Backup and restore in all test and production environments should be managed solely by DBAs.

  • + Share This
  • 🔖 Save To Your Account