Microsoft KB Archive/914847

= A user may be able to access objects in other schemas when you grant the ALTER permission on a schema to the user in SQL Server 2005 =

Article ID: 914847

Article Last Modified on 3/3/2006

-

APPLIES TO


 * Microsoft SQL Server 2005 Standard Edition
 * Microsoft SQL Server 2005 Developer Edition
 * Microsoft SQL Server 2005 Enterprise Edition
 * Microsoft SQL Server 2005 Express Edition
 * Microsoft SQL Server 2005 Workgroup Edition

-





SYMPTOMS
When you grant the ALTER permission on a schema to a user in Microsoft SQL Server 2005, the user may be able to access objects in other schemas. This problem may occur even if access to the schemas is explicitly denied to that user.

For example, this problem may occur in the following scenarios.

Note These scenarios assume that a user, referred to as U1, has the ALTER permission on the S1 schema. U1 is denied access to a table object, referred to as T1, in the S2 schema. The S1 schema and the S2 schema are owned by the same owner.
 * U1 has the CREATE PROCEDURE permission on the database and the EXECUTE permission on the S1 schema. Therefore, U1 can create a stored procedure, and then access T1 in the stored procedure.
 * U1 has the CREATE SYNONYM permission on the database and the SELECT permission on the S1 schema. Therefore, U1 can create a synonym in the S1 schema for T1, and then access T1 by using the synonym.
 * U1 has the CREATE VIEW permission on the database and the SELECT permission on the S1 schema. Therefore, U1 can create a view in the S1 schema to query data from T1, and then access T1 by using the view.



CAUSE
This problem occurs because ownership chains bypass permissions on the referenced objects when the objects have the same owner. The ALTER permission lets the user create objects that will be owned by the schema owner. Therefore, when you create an object in another user's schema, the newly created object can extend the permissions of the user who created it.



WORKAROUND
We recommend that you consider these scenarios when you grant the ALTER permission on a schema whose owner also owns other schemas. Avoid granting the ALTER permission on a schema unless it is necessary. If you must grant the ALTER permission, consider changing the schema owner to a specific principal that does not own other schemas.



STATUS
This behavior is by design.



MORE INFORMATION
The following Transact-SQL statements demonstrate the three scenarios that are mentioned in the &quot;Symptoms&quot; section. To use this example, run the following statements in SQL Server Management Studio. -- Create the test environment. USE master GO CREATE DATABASE test GO USE test GO CREATE LOGIN TestUser WITH PASSWORD = 'Password'; GO CREATE USER TestUser GO CREATE SCHEMA secret GO CREATE SCHEMA visible GO CREATE TABLE secret.t (c INT); Go INSERT INTO secret.t VALUES (42); GO DENY SELECT ON secret.t TO TestUser; GRANT ALTER ON SCHEMA::visible TO TestUser; GO --######## -- Scenario 1 -- Grant permissions. GRANT EXECUTE ON SCHEMA::visible TO TestUser; GRANT CREATE PROCEDURE TO TestUser; GO -- Access the denied object. EXECUTE AS USER = 'TestUser'; SELECT USER_NAME AS CURRENT_USER_NAME; GO CREATE PROCEDURE visible.sptest AS BEGIN SELECT * FROM secret.t END GO SELECT 'Scenario 1: Executing procedure' EXEC visible.sptest GO

-- Scenario 2 -- Clear the state. REVERT GO DROP PROCEDURE visible.sptest REVOKE EXECUTE ON SCHEMA::visible TO TestUser; REVOKE CREATE PROCEDURE TO TestUser; GO -- Grant permissions. GRANT SELECT ON SCHEMA::visible TO TestUser; GRANT CREATE SYNONYM TO TestUser; GO -- Access the denied object. EXECUTE AS USER = 'TestUser'; SELECT USER_NAME AS CURRENT_USER_NAME; GO CREATE SYNONYM visible.mytest FOR secret.t; GO SELECT 'Scenario 2: Querying from the synonym' SELECT * FROM visible.mytest GO -- Scenario 3 -- Clear the state. REVERT GO DROP SYNONYM visible.mytest REVOKE SELECT ON SCHEMA::visible TO TestUser; REVOKE CREATE SYNONYM TO TestUser; GO -- Grant permissions. GRANT SELECT ON SCHEMA::visible TO TestUser; GRANT CREATE VIEW TO TestUser; GO -- Access the denied object. EXECUTE AS USER = 'TestUser'; SELECT USER_NAME AS CURRENT_USER_NAME; GO CREATE VIEW visible.myview AS SELECT * FROM secret.t GO

SELECT 'Scenario 3: Querying from the newly created view' SELECT * FROM visible.myview

-- Remove the test database. REVERT GO USE master GO DROP LOGIN TestUser GO DROP DATABASE test GO

