Click here to Skip to main content
15,885,905 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more: , +
Hello,
I am trying to develop a solution based on a sql server database, entity framework and wcf (OData). I secure my data seen so how do use the sql server security levels (access rights and roles) or use wcf authentication, I'm really perturbed.

Thank you in advance.
Posted
Comments
FranzBe 28-Jun-15 10:42am    
I don't understand your question. Your WCF solution will be composed of a service and a proxy/client part. You can setup you SQL Server so that the WCF-Service part that accesses the database sees only the tables required.
A starting point for this would be something like this:

IF NOT EXISTS (SELECT * FROM MASTER.DBO.SYSLOGINS WHERE LOGINNAME = N'yourwcfserviceuser')
BEGIN
CREATE LOGIN [yourwcfserviceuser] WITH PASSWORD=N'yourwcfservicepassword'
,DEFAULT_DATABASE=yourdatabase
,CHECK_EXPIRATION=OFF
,CHECK_POLICY=OFF
END

EXEC sp_grantdbaccess N'yourwcfserviceuser', N'yourwcfserviceuser'

SELECT 'GRANT SELECT ON '+ a.table_name + ' TO yourwcfserviceuser' FROM
(SELECT DISTINCT TABLE_NAME FROM information_schema.columns
WHERE TABLE_NAME LIKE 'yourtablesearchpattern%' ) a

I don't think SQL server roles will make too much sense as there will be only one user.

Between the WCF Client and the WCF Service part that's where the WCF authentication comes into play. Here you specify How the WCF Client may or may not access the WCF Service (or parts of it). This has nothing to do with SQL-Server security (and it is a big topic).

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900