Separation of users and schemas is another cool SQL Server 2005 feature, but it has some interesting behaviors that folks may have to get used to. One is database object resolution, another is ownership chains. Say I have a user FRED.
FRED is the owner of a schema named FRED
FRED is a memeber of the role PAYROLL
FRED's default schema is PAYROLL
Even though FRED is the owner of a schema named after him, resolving a one-part object name like some_table uses a simple_algorithm: look in default schema first, then look in dbo schema. Even if you own another schema, only your default schema is used to resolve a 1-part name. BTW, the "sys" metadata schema complicates this a little bit, but I'm ignoring that for now. So for FRED, if the following tables exist:
fred.some_table
dbo.some_table
payroll.some_table
the statement "select * from some_table" executed by FRED, selects payroll.some_table. If payroll.some_table is dropped, it selects dbo.some_table. If FRED leaves the payroll department (and is removed from the role), it still selects dbo.some_table. Only when you do:
ALTER USER FRED WITH DEFAULT_SCHEMA = FRED
will it even attempt to resolve the 1-part name to fred.some_table.
I'd always wondered about how this affected ownership chains, too. A simplistic explanation of these is: authorization of a database object is only checked when an ownership chain is broken. So if procedure A uses table B, authorization is only checked if the owner of procedure A is different from the owner of table B.
So does user-schema separation change this? Is "ownership" defined as the user who owns the object or as the schema the object lives in? This is an easy one also…owner is still not object's owner, NOT the schema the object lives in.
This can have some interesting twists because you can GRANT other users the right to create objects in a schema you own:
GRANT CREATE TABLE TO ALICE
GRANT ALTER ON SCHEMA::FRED to ALICE
means ALICE can create tables in the FRED schema. But that's a subject for another day….
One Response to Users, Schemas, Objects, and Owners
In SQL 2005, the name resolution algorithm searches schemas in this order (sys, default schema, dbo). We always search "sys" first to avoid spoofing system code.
Also – the schema owner does not necessarily own all the objects in a schema. YOU can own a table in MY schema. This security team adding this feature so you can break ownership chaining WITHIN a schema. This is illustrated, below.
use tempdb
go
create login X with password = ‘fekjfhHG9870()(‘
create login Y with password = ‘fekjfhHG9870()(‘
go
create user X with default_schema = X
create user Y with default_schema = Y
go
create schema X authorization X
go
create schema Y authorization Y
go
grant create table to X, Y
go
execute as user = ‘X’
go
create table xtable(c int)
go
grant take ownership on object :: xtable to Y
go
revert
go
execute as user = ‘Y’
go
alter authorization on object :: X.xtable to Y
go
revert
go
select user_name(s.principal_id) as schema_owner
, schema_name(s.schema_id) as schema_name
, user_name(t.principal_id) as table_owner
, t.*
from sys.tables t join sys.schemas s
on t.schema_id = s.schema_id
where t.name = ‘xtable’
go
— Clean
drop table X.xtable
go
drop schema X
drop schema Y
go
drop user X
drop user Y
go
drop login X
drop login Y
go