Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
vijay1234
Starting Member
48 Posts |
 Posted - 2014-08-06 : 10:22:52
|
| Hi All,I have a requirement in my project.1.We have a set of Tables (12).2. These tables are being used by various users.3. Planning to create User Groups based on their role.( Ex: GroupA having set of users, GroupB having set of users )4.Here when a user logged into the server the entry details will be stored in all the above set of tables along with time.So now the requirement is 1. When User 1 logged into the server the data will be inserted into all the above tables.2. Similarly for User 2,.......User 1003. When User 2 query the tables, he can able to see the User1 information as well. Similarly for all the users....Example our Facebook, we can access only our data but not others.In the same way here, the user should see only his data but not all the others data.What would be the best approach to get this done Team.Thanks & Regards,Vijay |
|
|
Bustaz Kool
Master Smack Fu Yak Hacker
1834 Posts |
Posted - 2014-08-06 : 11:33:51
|
I am assuming that there is a way to identify which data in the table belongs to a particular user. An approach to consider:1) Data is accessed through a set of stored procedures2) There is some metadata which defines each user's scope (Only my data vs All data vs Other). This could be a table you maintain or membership in a user defined database role (User vs SuperUser vs Other).3) The stored procedure knows who the user is. This could be a parameter or a system function (USER_NAME()).4) The stored procedure limits the results based on who is calling it and what rights they have defined in the metadata.
Too often we enjoy the comfort of opinion without the discomfort of thought. - John F. Kennedy |
 |
|
|
|
|
|
User Specific Data Access to a Set of Tables