January 7, 2011 at 3:37 am
Section 4.11 of the "CIS Benchmark for SQL Server 2005" has the following requirement:
"Ensure dbo owns all user-created database schemas. Having dbo own all user-created database schemas prevents issues raised when users need to be deleted."
Are there any security implications with having a user-created database schema not owned by dbo? Or is this just an administration issue?
If a user owns a schema and that user needs to be deleted, I guess that the user cannot be deleted until the ownership of the schema is changed. Please correct me if I'm wrong.
Thanks in advance.
Andy
January 7, 2011 at 6:47 pm
One thing I noticed when changing a schema owner was that a database login that had permissions on the schema lost their permissions.
Not sure if you see the same thing. (SQL build 10.0.2531)
January 17, 2011 at 3:59 am
Hi
Every database you create has a predefined sys XML schema collection in the sys relational schema. It reserves these predefined schemas, and they can be accessed from any other user-created XML schema collection. The prefixes used in these predefined schemas are meaningful in XQuery. Only xml is a reserved prefix.
Copy
xml = http://www.w3.org/XML/1998/namespace
xs = http://www.w3.org/2001/XMLSchema
xsi = http://www.w3.org/2001/XMLSchema-instance
fn = http://www.w3.org/2004/07/xpath-functions
sqltypes = http://schemas.microsoft.com/sqlserver/2004/sqltypes
xdt = http://www.w3.org/2004/07/xpath-datatypes
(no prefix) = urn:schemas-microsoft-com:xml-sql
(no prefix) = http://schemas.microsoft.com/sqlserver/2004/SOAP
Thanks
January 20, 2011 at 9:43 am
andrew.gordon (1/7/2011)
Are there any security implications with having a user-created database schema not owned by dbo? Or is this just an administration issue?
I do not know of any operational security implications although it can present some administrative challenges as it is cumbersome to remove users when they own schemas, especially when those schemas contain objects. There are many considerations when removing a schema when it comes to permissions so they are trying to save you the trouble by making sure everything is owned by dbo from the outset.
As an aside, when the environment's security requirements allow for it, I prefer to have all schemas, not just user-specific schemas, authorized by (i.e. owned by) dbo. This allows all schemas to function solely as a categorization tool and ignores the granular security options schemas offer. This can be just what the doctor ordered however because it preserves ownership chaining across all schemas and greatly simplifies object-level security.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply