1

I'm developing a database that would eventually live on a shared SQL Server 2008 database on the host machine (at hosting provider). I noticed that all the tables and queries are owned by the dbo. I would like to know if this is a security issue when on a shared host. and what is the best practice for assigning ownership of database objects. Should I transfer the ownership of the db objects to the db's admin user in a shared hosting environment?

Thanks

Sivakanesh
  • 817
  • 4
  • 13
  • 36

2 Answers2

0

Best practice when using dbo

When creating objects/tables with dbo it means that the login which aliases to those objects must have the db_owner role and in turn means it can "do anything" within that database. The user accessing that database would normally require CRUD mostly. I.e. data within tables and executing SP's should be all that account should be able to do. Though when db_owner it can do anything which in my opinion is a security flaw.

There should be a login for the application access (svcact_app1) which is a service account (not interactive) and Windows Logins for the the DDL etc which are db_owner's - and therefore defaults to dbo. Each object can be owned by dbo though the grants should grant back to the associated user for the svcact_app1 login.

This gives separation whereby the app connection can only modify the data and execute SP's which are granted to it and nothing else. If you do not do this, and an attacker can successfully launch a SQL Injection, the attack could drop tales, modify SP's, or anything.

Rob Sumsion
  • 41
  • 3
0

The dbo schema is the admin, or DB owner, schema. It is also the default schema when creating tables unless steps are taken to change it.

You can control the security within the database, even in a hosted environment. You must focus on your security strategy and grant, revoke or deny rights to the objects in the database based on that strategy. Avoiding the dbo schema alone will not improve security.

bobs
  • 21,844
  • 12
  • 67
  • 78
  • Understand this and I should have additional users with restrictive permission etc, but I wasn't asking that. I need to know if keeping the owner as dbo considered a security issue in a shred environment and if it is better practice to shift the ownership to another user. – Sivakanesh Nov 19 '10 at 07:50
  • It is not possible to change dbo from being the owner of a database. In other words, you cannot remove dbo from db_owner role membership. Therefore, it is best practice to leave dbo as the owner. – bobs Nov 19 '10 at 17:20