0

I have a user table and I need to create a VIEW so a user can only see their own data/row.

How could I achieve this? I have been searching online but I can't find anything useful

User with user_id, name, address

0
1

You need to know who the user is and that information needs to be accessible through the view.

CREATE VIEW dbo.tPatientForUser
AS
SELECT patient_id, name, address, gender, loginame
FROM tPatient 
WHERE loginame = SUSER_SNAME();

Or, depending on your application you might need:

WHERE loginame = ORIGINAL_LOGIN(); 

This can help you filter the rows to only those with the same user.

EDIT: Per your latest comment:

To create a login for someone, and to make them a user of your database, you can read the KB article at: https://msdn.microsoft.com/en-us/library/aa337562.aspx (Look at the examples toward the bottom of the page.)

Examples: -- Create a login for SQL Server by specifying a server name and a Windows domain account name.

CREATE LOGIN [<domainName>\<loginName>] FROM WINDOWS;
GO

-- Creates the user "shcooper" for SQL Server using the security credential "RestrictedFaculty" -- The user login starts with the password "Baz1nga," but that password must be changed after the first login.

CREATE LOGIN shcooper 
   WITH PASSWORD = 'Baz1nga' MUST_CHANGE,
   CREDENTIAL = RestrictedFaculty;
GO

Remember though that the View or the Stored Procedure must implement your security plans, which you have not really explained. This tip is just showing you how to make use of a users identity, not how to create your system.

3
  • It could be either, depending on your environment. Either way, you get a login. If the users are in your domain CREATE USER... FOR LOGIN. If they are external then likely you need to give them a way to login and set up their password.
    – RLF
    May 9 '15 at 23:28
  • 1
    Would it better to have the view as a stored procedure?
    – whwuifhwui
    May 9 '15 at 23:42
  • In general, I agree that a well-written stored procedure can be more effective. But again, it depends on your architecture, security plan, and so forth. In any case, you do still need to know who is logging in and adjust your results accordingly.
    – RLF
    May 9 '15 at 23:52
1

A view and individual SQL logins would be appropriate if each patient accessed the database directly using an ad-hoc query tool, like Access. However, in the case of a front-end application, I suggest performing patient authentication in the app code (perhaps with individual user credentials stored securely in a database) and then just use the patient identifier in queries involving patient data. The application can use direct table access, views, or stored procedures.

Not the answer you're looking for? Browse other questions tagged or ask your own question.