store procedure security issue

[George2]

Hello everyone,

I am not sure whether it is possible to setup a security policy like this with store procedure. The user account foo could only access database through store procedure, not possible to do other operations, like select/update on the tables of the database directly without using the store procedure.

My store procedure is doing some select/update job. My confusion is, as the user needs to use the store procedure and the store procedure is doing select/update job, then I have to grant the user to have select/update privilege of the database tables? Then the user could skip using the store procedure and select/update database tables directly which causes a security hole. My purpose is to let user use store procedure all the time, no walk around to access database without using store procedure.

Any advice to solve my probleme?

thanks in advance,
George

[marceln]

See this article (user impersonation):
http://www.mssqltips.com/tip.asp?tip=1579
Something like:

...
EXECUTE AS 'UserWithSelectPermission"
--do select statement(s) here
REVERT
...


Your user must have only the EXECUTE permission on the database.

[TheCPUWizard]

Hello everyone,

I am not sure whether it is possible to setup a security policy like this with store procedure. The user account foo could only access database through store procedure, not possible to do other operations, like select/update on the tables of the database directly without using the store procedure.

My store procedure is doing some select/update job. My confusion is, as the user needs to use the store procedure and the store procedure is doing select/update job, then I have to grant the user to have select/update privilege of the database tables? Then the user could skip using the store procedure and select/update database tables directly which causes a security hole. My purpose is to let user use store procedure all the time, no walk around to access database without using store procedure.

Any advice to solve my probleme?

thanks in advance,
George

IF the user has EXECUTE permission on the stored procedure..AND the stored procedure does NOT have any dynamic SQL; then the user should NOT need rights directly to the table (The OWNER of the stored procedure does...)

[George2]

Thanks TheCPUWizard,

I have tested your idea. But met with a issue.

I created a new user which is using SQL Server authentication type. The user default database is AdventureWorks. I grant the user rights of execute one store procedure of AdventureWorks (the store procedure is used to query some specific table) and denied the select rights of the user to select the specific table directly (using table, permission, deny select permission in management sonsole).

But when I tried to use the user to login the management console (in order to test with execution of the store procedure and the select statement), I met with the following error when click login from management console. Do you have any ideas why?


TITLE: Connect to Server
------------------------------

Cannot connect to ..

------------------------------
ADDITIONAL INFORMATION:

A connection was successfully established with the server, but then an error occurred during the login process. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.) (Microsoft SQL Server, Error: 233)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=233&LinkId=20476

------------------------------
BUTTONS:

OK
------------------------------

IF the user has EXECUTE permission on the stored procedure..AND the stored procedure does NOT have any dynamic SQL; then the user should NOT need rights directly to the table (The OWNER of the stored procedure does...)

regards,
George

[George2]

Thanks marceln,

I read your recommended document, but I think it is not what I am asking for, actually my requirement is, I want to have a low privilege user and open the user to some 3rd party and then the 3rd party could only use the specific user to do some low privilege operations, like execute a specific store procedure and no access to direct write statement in ADO.Net to connect to my database and query the table. Any comments or ideas?

See this article (user impersonation):
http://www.mssqltips.com/tip.asp?tip=1579
Something like:

...
EXECUTE AS 'UserWithSelectPermission"
--do select statement(s) here
REVERT
...


Your user must have only the EXECUTE permission on the database.

regards,
George

[dglienna]

ADDITIONAL INFORMATION:

A connection was successfully established with the server, but then an error occurred during the login process. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.) (Microsoft SQL Server, Error: 233)


User cannot log in to Management Studio. That is correct.

You can't test a SP that way.

[George2]

Thanks dglienna,

User cannot log in to Management Studio. That is correct.

You can't test a SP that way.

Two more questions,

1. who is able to log into management studio? what types of roles?

2. what is your recommended way to test store procedure?

regards,
George