Menu
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?
511
Stored procedures are typically executed with an EXEC statement. However, you can execute a stored procedure implicitly from within a SELECT statement, provided that the stored procedure returns a result set. The OPENROWSET function is key to this technique, which involves three steps.
There could be scenarios where you want to use a Stored Procedure in view and Join the result set returned by a Stored procedure with some other tables/views. Step 2: Create Stored Procedure that will return all results.
With the EXECUTE AS clause, the stored procedure is run under the context of the object owner and therefore when this code executes the creation of table dbo. table_2 is created and we are able to insert the rows successfully.
This means that CTE is valid only to the scope of the query. However, you can write a CTE inside a stored procedure or User Defined Functions (UDFs) or triggers or views. However, you cannot implement CTEs inside indexed views.
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With