Sql – Is a stored procedure with EXECUTE AS OWNER a valid replacement for a view selecting from third-schema tables

ownershippermissionssqlsql serverstored-procedures

A database user A should only have access to specific data.
This data is currently provided by a view B.VIEW1 selecting from tables owned by schema B and C.

CREATE VIEW [B].[VIEW1] AS SELECT * FROM [B].[VIEW2], [C].[VIEW1]

Since C.VIEW1 is not owned by B, Ownership Chains apply.

That means although A is granted SELECT permission ON B.VIEW1, it can't select from.

SELECT permission denied on object 'C.VIEW1', database '...', schema '...'.

Is a stored procedure B.PROC1 with EXECUTE AS OWNER Clause a valid replacement for B.VIEW1 in terms of security?

CREATE PROC [B.PROC1] WITH EXECUTE AS OWNER AS BEGIN SELECT * FROM [B.VIEW2], [C].[VIEW1] END

Or are there any negative side-effects which will possibly lead to any security problems?

Best Answer

In terms of security, this seems to be a good way to prevent access to underlying tables.

A negative side-effect is that you can't filter the resultset generated by the stored procedure by a WHERE, GROUP BY clause or similar.

But this is not that tragic if defining static constraints in an underlying view or defining "dynamic" constraints via stored proc's input parameters.

1) Static constraints in underlying view

CREATE VIEW [B].[VIEW3] AS SELECT * FROM [B].[VIEW2], [C].[VIEW1] WHERE [X]='Something' AND [Y] = GETDATE()
CREATE PROC [B].[PROC1] WITH EXECUTE AS OWNER AS BEGIN SELECT * FROM [B].[VIEW3] END

2) Dynamic constraints via input parameters

CREATE PROC [B].[PROC1] (@X varchar(30), @Y DATETIME) WITH EXECUTE AS OWNER AS BEGIN SELECT * FROM [B].[VIEW2], [C].[VIEW1] WHERE [X]=@X AND [Y]=@Y AND

Related Solutions

Sql – How to execute a stored procedure once for each row returned by query

use a cursor

ADDENDUM: [MS SQL cursor example]

declare @field1 int
declare @field2 int
declare cur CURSOR LOCAL for
    select field1, field2 from sometable where someotherfield is null

open cur

fetch next from cur into @field1, @field2

while @@FETCH_STATUS = 0 BEGIN

    --execute your sproc on each row
    exec uspYourSproc @field1, @field2

    fetch next from cur into @field1, @field2
END

close cur
deallocate cur

in MS SQL, here's an example article

note that cursors are slower than set-based operations, but faster than manual while-loops; more details in this SO question

ADDENDUM 2: if you will be processing more than just a few records, pull them into a temp table first and run the cursor over the temp table; this will prevent SQL from escalating into table-locks and speed up operation

ADDENDUM 3: and of course, if you can inline whatever your stored procedure is doing to each user ID and run the whole thing as a single SQL update statement, that would be optimal

Sql – The SELECT permission was denied on the object ‘sysobjects’, database ‘mssqlsystemresource’, schema ‘sys’

This was a problem with the user having deny privileges as well; in my haste to grant permissions I basically gave the user everything. And deny was killing it. So as soon as I removed those permissions it worked.

Best Answer

Related Solutions

Sql – How to execute a stored procedure once for each row returned by query

Sql – The SELECT permission was denied on the object ‘sysobjects’, database ‘mssqlsystemresource’, schema ‘sys’

Related Topic