Sql server - execute stored procedure with readonly permission

Question

I have an application where a user can input a sql statement to be executed in an sql server database. The statements must only return data and not perform any DML or DDL operation. At the moment the input is validated only allowing to write SELECT statements. But now is a requirement allow the execution of stored procedures, but only those that only read data. Also would be helpful list these SP in order to show a kind of intellisense.

I thought in create a user with only db_datareader permission and execute the statement as this user. But to execute an stored procedure I must grant Execute permission to the user and becuase the Ownership Chaining, the permissions may not be checked. I haven't figured it out how to solve this problem.

Also I thought in allow only the execution of stored procedures createds in an specific DB schema that only has read permission. Also this way I can list the SPs easily. I don't know if this is possible.

Answer 1

But to execute an stored procedure I must grant Execute permission to the user

This should work by creating an user without login and granting only select to that login.This uses EXECUTE as clause..You need EXECUTE as permissions to create or call this module,but the execution context will be user1 ,once you are in the module which has only select permissions

create user user1 without login

grant select to user1

alter proc usp_test
@sql nvarchar(max)
with execute as 'user1'

as
begin


exec sp_Executesql @sql

end


create table dbo.test
(
id int
)

insert into test
select 1
go 10

exec usp_test N'select * from test' --works
exec usp_test N'insert into test values(1)' --fails with below error

Msg 229, Level 14, State 5, Line 28 The INSERT permission was denied on the object 'test', database 'master', schema 'dbo'.

Answer 2

After some digging and using @TheGameiswar answer I came with this solution

1- Create a "readonly" user

Create user user_reader without login

2- To break the Ownership Chaining I create a new DB schema with user_reader as owner

Create schema reader

ALTER AUTHORIZATION ON Schema::reader TO user_reader

3- Grant select permission to user_reader

GRANT SELECT TO user_reader

4- Grant execute permission to user_reader, only in reader schema. In other schema the stored procedures may have the same owner than tables and the permissions of user_reader wouldn´t be checked because the ownership chain would be unbroken.

GRANT EXECUTE ON Schema::reader TO user_reader

5- Execute every sql statement written by the user in the context of user_reader. To accomplish this always prepend "execute as user = 'user_reader'"

public override DataTable ExecuteQuery(string query)
{
    SqlConnection connection = new SqlConnection(ConnectionString);

    StringBuilder readOnlyQuery = new StringBuilder();
    readOnlyQuery .AppendLine("execute as user = 'user_reader';");      
    readOnlyQuery .AppendLine(query);
    query = readOnlyQuery .ToString();

    SqlCommand command = new SqlCommand(query, connection);

    ...Execute the command
}

FOR TESTING

CREATE TABLE [dbo].[TestTable](
    [TestField] [int] NOT NULL
)

GO
CREATE PROCEDURE USP_INSERT
    @input int
AS
BEGIN
    INSERT INTO TestTable VALUES (@input)
END

GO

CREATE PROCEDURE reader.USP_READ
AS
BEGIN
    SELECT * FROM TestTable
END
GO

CREATE PROCEDURE reader.USP_INSERT
    @input int
AS
BEGIN
    INSERT INTO TestTable VALUES (@input)
END

TESTING:

insert into TestTable VALUES (2)

The INSERT permission was denied on the object 'TestTable', database 'Test', schema 'dbo'.

select * from TestTable

OK

exec usp_read

The EXECUTE permission was denied on the object 'USP_READ', database 'Test', schema 'dbo'.

exec usp_insert 1

The EXECUTE permission was denied on the object 'USP_INSERT', database 'Test', schema 'dbo'.

exec reader.usp_insert 1

The INSERT permission was denied on the object 'TestTable', database 'Test', schema 'dbo'.

exec reader.usp_read

OK

Also we can list all stored procedures from reader schema

select name
from sys.procedures
where SCHEMA_NAME(schema_id) = 'reader'

This solution meets my requiretments but maybe is not enough for others situations.