What’s the first thing you should do when you create a new database for a corporate environment? Remove all accounts from the DBA Schema, and create an Application account pointing to another schema. There may be other databases other than those hosted in a corporate environment, depending on the required security and authentication mechanism. That’s a pretty strong statement. So what do I base this against?
Using this practice requires you to address security concerns from the very beginning of your project. If you don’t, I’ve seen many times that we attempt to shoe horn security in, and don’t have time to do a good job. If you start out with a schema and service accounts, then your application is organically implemented to implement security, and you don’t have to go back and fit it in.
So, how much extra work is it to do this? It depends on the engine. For SQL Server, you first create (if you don’t already have one) an account for the SQL Service to authenticate against. It can be a server user or an active directory user. Then the SQL Server is modified to use this service account credentials. Now you can lock all directory access as well as behaviors. Even the SQL Agent service can have it’s own account, and directory and resource access be modified accordingly.
But, at the schema level, you either use actual user logins, such as active directory accounts or groups, and assign them to the appropriate schema, not dbo, restricting their capabilities within a specific database. If you use a single authenticating account for all database users, such as a web service that talks to the database with a single login, then you still need to create a windows or Active Directory account to represent any application accessing the database. Then your web service needs to be altered to run using the authentication of this new account. Again, the account you create here should not be associated with DBO.
By doing one ore more schemas up front, you can simply associate the Windows account you create to the necessary schema, and then each object participating in that schema automatically has the default permissions. So, if you define your schemas based on roles, you can limit capabilities appropriately. For example, if you had a reporting application, you could have a reporting schema, and associate AD Users or Groups to that read only schema. The read only schema can point to objects in other schemas, but be granted only read type permissions. A table could belong to the application schema, but grant select permissions to the readonly schema. A stored procedure that only selects data may belong to the application schema, but be granted execute permissions to the readonly schema.
You get the point. It’s easier to create all of these security things up front, while you thinking about it, rather than trying to go through all of your objects after the fact, and grant all the appropriate permission.
So, let me encourage you to introduce security sooner than later when you are designing or extending the capabililties of a database.
Cheers,
Ben