Name resolution, default schema, implicit schema Part III

[SQL-Server-Team]

First published on MSDN on Apr 03, 2006

It is time to cover name resolution algorithm in SQL 2005 for objects.

[Algorithm]

In a nutshell, objects name resolution follows the same rule as types and XML schema collections. However, SQL Server name resolution and execution context used to have special behaviors in SQL2000 as outlined in Section I, not to mention the fact that system objects are now migrated to resource database, and user-schema separation feature is introduced. Clearly name resolution algorithm needs to be more complex, with the following goals (more details later):

• Avoiding luring attack


• Minimizing user’s name space hijacking


• Backward compatibility


• Unifying algorithm with types and XML schema collections


• Acceptable performance

For DDL access, including CREATE/DROP/ALTER object, the algorithm is the same as types and XML collections. Attached flow chart covers other scenarios for 1-part or 2-part names.

If server name is specified, the object reference is resolved by the specified remote/linked server.

If database name is specified, the object is resolved in the specified database context. (For system objects which are visible in every database, it means compile/execution database context.) It is worth to mention the case when schema name is omitted though. Under this circumstance, the active default schema is defined as following:

• For object references outside SQL modules, or DDL, dynamic SQL inside SQL modules, current user’s default schema in the specified database is used.


• Otherwise, the module schema owner’s default schema in the specified database is used.

E.g. suppose user bar owns schema foo in database nwind, when pubs..tab1 is resolved, default schema of the user in pubs that maps to the same login as bar (or guest) is the active default schema.

use nwind

go

create procedure foo.proc1

as

select * from pubs..tab1

go

[Avoiding Luring Attack]

Consider following scenario system admin uses an unqualified name reference to a system procedure from an arbitrary database/schema context.

use database1

go

exec sp_addlogin 'somelogin', 'somepassword'

go

We recommend users to qualify with schema “sys” when referencing system procedures in SQL2005. However, existing applications may well rely on SQL 2000 name resolution special behavior to bind system procedure (with sp_ prefix) tighter than user procedures. If we did not force a “sys first” strategy, the unqualified name reference shown above would bind to dbo.sp_addlogin in database1. A “malicious dbo” could then get a system admin to execute arbitrary code.

[Minimizing User’s Name Space Hijacking]

If schema not specified or dbo is specified, look in sys schema, any of –

1) prefix ‘sp_’;

2) prefix ‘fn_’, ‘xp_’ and master context if dbo schema specified;

3) SQL 2K db-scoped catalog name;

4) SQL 2K server-scoped catalog name and db-context is master.

As illustrated in the algorithm logic, system objects are resolved first to avoid luring attack. Unfortunately, it is hijacking user’s namespace, i.e. if Microsoft SQL Server ships system stored procedure sp_foo in SQL2005 or future releases, user stored procedure with same name, which is contained in dbo schema, can no longer be used.

The strategy is for system objects to use prefix naming standards, and requiring users to qualify with schema otherwise, such as catalog views, Information-Schema views and dynamic management views. End users are discouraged to use MS-reserved name prefixes “sp_/fn_/xp_”.

We may introduce SQL-path or similar feature in next release to improve this, as well as address user’s desire to choose different name resolution search order in general.

[Backward Compatibility]

As we can tell from the algorithm, the special behavior of SQL 2000 name resolution with respect to system objects is preserved. There is subtle difference between SQL2005 and SQL 2000 with respect to collation used to match names though.

In SQL2005, system object names are matched using collation of current context database. This means if you are using case sensitive collation database, system object name must be exactly matched. E.g. “exec SP_help” will fail to resolve as system procedure sp_help in database with Latin1_General_BIN collation.

The reason is that system objects logically exist in every database, ideally they should be treated same way as user objects, which are always resolved using context database collation.

This behavior change from SQL 2000 is summarized in the following table. This makes difference on SQL Server instances where user databases use different collation with server. (System databases use server collation.)

System object collation difference between SQL 2000 and SQL2005

	SQL 2000	SQL2005
Schemas	Database collation	Database collation
Stored procedures, functions	Server collation (shipped in master-db and contained in schema dbo)	Database collation (shipped in resource-db contained in schema sys)
Functions	Keyword collation, i.e. Latin1_General_CI_AS_KS_WS (shipped in master-db and contained in schema system_function_schema)	Database collation (shipped in resource-db contained in schema sys)
Information-Schema views	Server collation (shipped in master-db and contained in schema INFORMATION_SCHEMA)	Database collation (shipped in resource-db contained in schema INFORMATION_SCHEMA)
Tables	Server collation (shipped in master-db and contained in schema dbo)	Database collation (changed to back-compatible views, shipped in resource-db and contained in schema sys)
Tables	Database collation (shipped in all databases and contained in schema dbo)	Database collation (changed to back-compatible views, shipped in resource-db and contained in schema sys)

Name Resolution.doc