store procedure security issue

Question

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

George George · Answer

Thanks Ryan! I am not sure whether your configuration could meet with my requirement. 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? regards, George

George George · Answer

Thanks Sreenivas, 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 ------------------------------ regards, George

Ryan Alford · Answer

You are over complicating this. The user has no way of getting to the database unless they install SQL Server Management Studio on their PC, get the server name, and get the username and password, and know what they are actually doing. Normally, you don't give the user's an account that has access to the database. In my applications, I create a username that I do all the database querying with. The user NEVER sees this. They might have their own login to the application, but this has nothing to do with access to the database. I control the database login and I am the only one that uses it. Therefore, I have no "security" problems.

George George · Answer

Thanks Ryan! I am using SQL Server 2005 on Windows Server 2003. Do you have any advice? regards, George

Ryan Alford · Answer

What database are you using? If the user doesn't have some type of UI component to connect to the database, then they aren't going to be able to write queries.

Sreenivas Chinni · Answer

1. assign the role 'accessadmin' on the database to the user. 2. as u want grant execute permissions on the stored proc. i hope this will help u