Home Dashboard Directory Help
Search

SQL Server PDW - cross db ownership chaining by rrchitturi


Status: 

Active


1
0
Sign in
to vote
Type: Suggestion
ID: 766154
Opened: 10/4/2012 11:47:11 AM
Access Restriction: Public
0
Workaround(s)
view

Description

We have a requirement to provide access through views in one database referring to tables in another database without providing direct access to query the tables. "cross db ownership chaining" feature in SQL Server enables this. Requesting for same capability in SQL server PDW


Testing in SQL Server PDW:

use master;

DROP database OPS_DB01;
CREATE DATABASE OPS_DB01 WITH ( AUTOGROW = OFF, REPLICATED_SIZE = 0.25 GB, DISTRIBUTED_SIZE = 16 GB, LOG_SIZE = 16 GB);

DROP database OPS_DB02;
CREATE DATABASE OPS_DB02 WITH ( AUTOGROW = OFF, REPLICATED_SIZE = 0.25 GB, DISTRIBUTED_SIZE = 16 GB, LOG_SIZE = 16 GB);

DROP LOGIN test2;
CREATE LOGIN [test2] WITH PASSWORD = 'Temp_1111', CHECK_POLICY = ON, CHECK_EXPIRATION = OFF;


use OPS_DB01;
CREATE TABLE OPS_DB01.dbo.TableUsage
(    DB_Name VARCHAR(50),
    Table_Name VARCHAR(50),
    Rows BIGINT NULL
) WITH (DISTRIBUTION = HASH (Table_Name));


USE OPS_DB02;
CREATE VIEW dbo.TableUsage AS Select * FROM OPS_DB01.dbo.TableUsage;

CREATE TABLE OPS_DB02.dbo.Tables
(    DB_Name VARCHAR(50),
    Table_Name VARCHAR(50),
    Rows BIGINT NULL
) WITH (DISTRIBUTION = HASH (Table_Name));


USE OPS_DB02;
CREATE user test2 for login test2;
EXEC sp_addrolemember [db_datareader], [test2];


--Check with login as test2
select * from OPS_DB02.dbo.TableUsage;
--ERROR [08004] [Microsoft][SQL Server Native Client 10.0][SQL Server]The server principal "test2" is not able to access the database "OPS_DB01" under the current security context.
select * from OPS_DB02.dbo.Tables;
--success


--Login as SA
USE OPS_DB01;
CREATE user test2 for login test2;


--Check with login as test2
select * from OPS_DB02.dbo.TableUsage;
--ERROR [42000] [Microsoft][SQL Server Native Client 10.0][SQL Server]The SELECT permission was denied on the object 'TableUsage', database 'OPS_DB01', schema 'dbo'.
select * from OPS_DB02.dbo.Tables;
--success


--Login as SA and cleanup
use master;
DROP database OPS_DB01;
DROP database OPS_DB02;
DROP LOGIN test2;
What system and version are you using when the problem occurs? SQL Server PDW AU3 RTM
Are there any online articles that match the problem, even if the solution is not working? (Include links to the articles.) http://technet.microsoft.com/en-us/library/ms188694.aspx



Testing in SQL Server SMP:
EXEC sp_dboption 'OPS_DB01', 'db chaining', 'ON'
EXEC sp_dboption 'OPS_DB02', 'db chaining', 'ON'

CREATE TABLE OPS_DB01.dbo.TableUsage
(    DB_Name VARCHAR(50),
    Table_Name VARCHAR(50),
    Rows BIGINT NULL );

USE [OPS_DB02]
CREATE VIEW [dbo].[TableUsage] AS Select * FROM OPS_DB01.dbo.TableUsage


select * from [OPS_DB02].[dbo].[TableUsage]
--works with ops_test1 account

drop database OPS_DB01;
drop database OPS_DB02;
drop login ops_test1;
Details
Sign in to post a comment.
Posted by Microsoft on 10/15/2012 at 9:26 PM
Thanks Raja. While we made significant investments with PDW V1 AU3 to align the PDW security model with SQL Server, we are aware that they are still gaps. Currently, cross db ownership chaining is not in scope for V2. However, we will track this feature request as a DCR on (our) engineering side and evaluate its priority for coming AU's of V2. Also, I'll synch-up with you in our next meeting to understand the piority on your end as well the associated use cases.

Thanks again for your feedback.
Regards,
- Artin
Sign in to post a workaround.