How to allow multi user work on same table without any issue of interact data?

Transact-SQL
, ,
1

I work on sql server 2017 I have issue

How to allow mutli user working on same table without using temp table

and without interact two user with same data

as example

user a :

exec sp_workingmultiuser 5

user b :

exec sp_workingmultiuser 10

create proc sp_workingmultiuser

@productid int

as

begin

select * into dbo.testtrade from parts.tradecodes

where productid=@productid

end

so my question if

user a

using product id 5

and

user b

using product id 10 on same time

data for user b will display for user a

so how to prevent that please ?

3

what you mean flag please
i don't know what you mean by flag

4

what issue are you trying to solve?

5

Sorry, my solution was using tempdb so it didn't answer your question. I don't understand all what you are doing so @yosiasz question is a better one.

I don't get the part of creating a table each time a query is running so maybe we should take a look at the issue your trying to solve first before we can go on.

6

@ahmedbarbary ,

You posted the same question on at least one other forum. I'm going to answer on this forum first and then on the other so others may benefit, as well.

From the code you posted, there will be no mixing of data between user A and B. But it turns out that's now what you're concerned about. It turns out that you're really asking for a way where if user A OWNS product id 5 and user B OWNS product 10, is it possible to prevent user A from being able to see product 10 and also prevent use B from being able to see product 5 no matter what query they use?

The answer is "Yes" and it's called "Row Level Security". It requires a bit of complexity but it's not difficult unless you have thousands of separate users that cannot be managed as groups of users.

Here's the official MS documentation on the subject. They have a pretty good example of how to do it.

Possibly an easier to read and understand example can be found here.
https://www.sqlshack.com/introduction-to-row-level-security-in-sql-server/

If you need more info, do a Google Search for "Row Level Security in SQL Server".

7 
GRANT SELECT ON dbo.Sales TO CEO;
GRANT SELECT ON dbo.Sales TO Fred;

this does not seem to be granular enough, nor scalable and maintainable.

It depends on what context we are talking about for a system to sort out filter based on user level.

Is this an application that sql server as it's backend? It is his SSRS reporting? Some CRM or what?

For our applications, web apps (angular/react), we use role based permissions.

we have 2 table to manage that

  1. Roles

  2. UserRoles

  3. Roles
    Admin
    Viewer
    Editor

  4. UserRoles
    userid, roleid

userid could be based on users table or in an ADirectory environment, it could be their SAMAccountName

Then on application later we call sprocs using the currently logged in user name

exec assets_sp @username

assets_sp

Select *
from assets
where username = @username

8

Of course it's not... it's just an example. Your tip on "role based permissions is awesome, though, especially compared to the whacko stuff they did a decade ago on a couple of our apps that we still live with.

9

Amazing it still works. Don't fix it?

10

Cooooorrrreeeeeccccttttt! :smiley: There's no sign of it breaking either. I did discover a weakness in it about 7 years ago when we started grow pretty well as a company and me an a front-end developer fixed that by caching a stored procedure lookup and refreshing it just once an hour instead for the nearlly 80,000 times per hour it previously needed to run.

1 Like