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 (914847)


The information in this article 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

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 "Symptoms" 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

REFERENCES

For more information about ownership chains, see the "Ownership chains" topic in SQL Server 2005 Books Online.
Modification Type:MajorLast Reviewed:3/3/2006
Keywords:kbtshoot kbExpertiseAdvanced kbsql2005engine kbprb KB914847 kbAudITPRO kbAudDeveloper
©2004 Microsoft Corporation. All rights reserved.