Sql server - executing a stored procedure with read-only permission

Question

First, I’ll say that I don’t have much experience with sql server security. Any mistake I may have, let me know.

I have an application in which a user can enter a sql statement to execute in a SQL Server database. Operators must return data and not perform any DML or DDL operations. Currently, input is only checked for writing SELECT statements. But now this requirement allows you to execute stored procedures, but only those that only read data. It would also be useful to list these SPs to show some kind of intellisense.

I was thinking of creating a user with only db_datareader permission and executing the statement as this user. But in order to execute the stored procedure, I must grant the user the right to execute, and because the "Chain of Ownership Rights" permissions may not be checked. I did not understand how to solve this problem.

I also thought that only the creation of stored procedures is created in a certain database schema, which has only read permission. Also in this way I can easily list SP. I do not know if this is possible.

Answer 1

After some digging and using @TheGameiswar's answer, I came up with this solution

1- Create a read-only user

Create user user_reader without login

2- , user_reader

Create schema reader

ALTER AUTHORIZATION ON Schema::reader TO user_reader

3 - user_reader

GRANT SELECT TO user_reader

4- user_reader, . , , user_reader , .

GRANT EXECUTE ON Schema::reader TO user_reader

5 sql, user_reader. " 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
}

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

:

insert into TestTable VALUES (2)

INSERT "TestTable", 'Test', 'dbo'.

select * from TestTable

OK

exec usp_read

EXECUTE 'USP_READ', 'Test', 'dbo'.

exec usp_insert 1

EXECUTE 'USP_INSERT', 'Test', 'dbo'.

exec reader.usp_insert 1

INSERT "TestTable", 'Test', 'dbo'.

exec reader.usp_read

OK

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

, , , .

Answer 2

.

, . EXECUTE . EXECUTE , user1, ,

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, 14, 5, 28 INSERT "test", "master" , "dbo".