Database tables + procedures + schemas protection

Question

Hello,

I'm using a secure tsql connection to tsql database whith a tsql user (db owner of the database). In the Web.config and the App.config we can see the username and the login in the connectionstring.

So, if anyone have access to these files could connect to sql server throu this user and have acess to the database.

Is there a way to protect the access to the database ?

Regards,

Answer 1

Perhaps  you can grant only EXECUTION permission to that user on specific stored procedure and not being a member of db_owner role?

---

Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

Answer 2

The best way would be to use Windows authentication as this is more secure than a SQL Login unless you are also using something like IPsec at the same time.
It's not ideal having tha passwords viewable in what are essentially text files, so in this scenario you need to really secure the OS folders that these files reside on. Would it be possible for you to implement EFS? http://technet.microsoft.com/en-us/library/cc700811.aspx
I wouldn't recommend it if the files reside on the same box as the SQL Server as this will really slow down SQL, it makes all file access serializable if I remember correctly.

Uri has made a good shout in that from a security point of view all logins no matter if they are windows or SQL should always adhere to the policy of least privileges, in other words only provide the essentials that the user needs to do their job. Your processes should be designed so that they only require execution privileges on stored procedures and should one of these stored procedures need high level privileges to complete a task you should run them under the context of another user: http://msdn.microsoft.com/en-us/library/ms188354.aspx

 

---

Richard Douglas Blog: Http://SQL.RichardDouglas.co.uk Twitter: @SQLRich

Answer 3

Hello, thanks for your post.

If you are using the same account to work on the program, then if you control permissions to that account when the application start you won't have the permission you want to.

You may control the access to your code or may use Windows Authentication to protect your database, so to access to SQL Server you need an Account from the Local Computer or member of AD.

Also you can try creating an script in your application to create the credentials and permissions (add to any role in SQL Server, like db_owner) automatically when application starts, then the user won't have the permissions before starting the app, but when starts you will set the permissions. This is an option, not the recommended one. Please turn to Windows Authentication Mode or Trusted Connection as called, will be better.

Hope this helps.

---

Willy Taveras.-

http://itlearn.net

Answer 4

Thanks all,

I'll check your suggestions and let you know.

 

Regards,

Answer 5

GRANT EXEC ON some_sp TO nisse -- Grant permission on a single procedure.
GRANT EXEC ON SCHEMA::dbo TO kalle -- Grant perpmission on all procedures in
                                      the dbo schema
GRANT EXEC TO putte -- Grant EXEC permission all procedures in the database.

To grant all "normal" permissions on a table

   GRANT SELECT, UPDATE, DELETE, INSERT on pec TO freddie

To grant freddie access on tables in a schema:

   GRANT SELECT, UPDATE, DELETE, INSERT on SCHEMA::dbo TO freddie

To grant him access on all tables:

   GRANT SELECT, UPDATE, DELETE, INSERT TO freddie

---

Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/