Design Question

I have a scenario that I want to implement and i'm not quiet sure how to get
about this using SQL Server and I would like to share some of your
experiences and expertise.
I need to retreive data on the basis of different roles for example there
are two different sites and there are two different types of users site user
and admin user. site user can only view the site related data and the admin
user can view data from both the sites.
As in the real system there will be lots of sites and there are couple of
more roles defined into the database.
I have created a 3 tables. User, Role and UserRole and i'm kinda stuck
thinking what is the best way to proceed further.
I would appreciate if you could provide me some of your views.
Thanks in advance.
Regards,
Sajid Malik
Sajid
There is not enough info to suggest an accurate solutuion
Can you provide DDL+ sample data + expected result ?
"Sajid S. Malik" <sajid_malick@.yahoo.com> wrote in message
news:%23OEGfS9BHHA.4680@.TK2MSFTNGP04.phx.gbl...
>I have a scenario that I want to implement and i'm not quiet sure how to
>get about this using SQL Server and I would like to share some of your
>experiences and expertise.
> I need to retreive data on the basis of different roles for example there
> are two different sites and there are two different types of users site
> user and admin user. site user can only view the site related data and the
> admin user can view data from both the sites.
> As in the real system there will be lots of sites and there are couple of
> more roles defined into the database.
> I have created a 3 tables. User, Role and UserRole and i'm kinda stuck
> thinking what is the best way to proceed further.
> I would appreciate if you could provide me some of your views.
> Thanks in advance.
> Regards,
> Sajid Malik
>
|||Hi Uri,
User Table
UserID
Username
Password
Role Table
RoleID
Role
User Role Table
UserID
RoleID
I have three tables for users and roles. Now for example I have 1 table
that have some records and I want to retreive only those records if
user that have permissions to view all the records and there could be
some other permissions for other user role.
I hope this information is what you need if there is something else you
need to know please do not hesitate to contact me.
Thank you in advance.
Regards,
Sajid Malik
Uri Dimant wrote:[vbcol=seagreen]
> Sajid
> There is not enough info to suggest an accurate solutuion
> Can you provide DDL+ sample data + expected result ?
>
> "Sajid S. Malik" <sajid_malick@.yahoo.com> wrote in message
> news:%23OEGfS9BHHA.4680@.TK2MSFTNGP04.phx.gbl...
|||Hi
> I have three tables for users and roles. Now for example I have 1 table
> that have some records and I want to retreive only those records if
> user that have permissions to view all the records and there could be
> some other permissions for other user role.
What do you mean "I have 1 table"? Can you isnert some sample data and show
us an expected result?
"Sajid MALIK" <sajid.malik@.gmail.com> wrote in message
news:1163530384.096202.126770@.b28g2000cwb.googlegr oups.com...
> Hi Uri,
> User Table
> --
> UserID
> Username
> Password
> Role Table
> --
> RoleID
> Role
> User Role Table
> --
> UserID
> RoleID
> I have three tables for users and roles. Now for example I have 1 table
> that have some records and I want to retreive only those records if
> user that have permissions to view all the records and there could be
> some other permissions for other user role.
> I hope this information is what you need if there is something else you
> need to know please do not hesitate to contact me.
> Thank you in advance.
> Regards,
> Sajid Malik
>
> Uri Dimant wrote:
>
|||Sajid S. Malik wrote:
> I have a scenario that I want to implement and i'm not quiet sure how to get
> about this using SQL Server and I would like to share some of your
> experiences and expertise.
> I need to retreive data on the basis of different roles for example there
> are two different sites and there are two different types of users site user
> and admin user. site user can only view the site related data and the admin
> user can view data from both the sites.
> As in the real system there will be lots of sites and there are couple of
> more roles defined into the database.
> I have created a 3 tables. User, Role and UserRole and i'm kinda stuck
> thinking what is the best way to proceed further.
> I would appreciate if you could provide me some of your views.
It seems what you are after is row level security. Oracle has a feature
called Virtual Private Databases (VPD), which let you make each user see
only those rows he is allowed to based on his role. You might like to
check Oracle's webpages to check if this is what you are after. If it
is, check Microsoft's page
http://www.microsoft.com/technet/prodtechnol/sql/2005/multisec.mspx .
Arto Viitanen, CSC Ltd.
Espoo, Finland
|||Hi Uri,
Well it doesn't matter what the data is it could be only one column in that
table I want to only select the rows based on the user roles.
Lets take another example of stores So the table would be
Site Table
SiteID
Site
1 | USA
2 | London
3 | France
4 | Germany
Now for example if I'm a site user I should only view the records for the
local division like USA, London etc but if I'm an admin user I can view
records for all the divisions.
I hope this clears the question.
Please do not hesitate to ask further question.
Thanks,
sajid malik
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:Op7ww6HCHHA.3380@.TK2MSFTNGP04.phx.gbl...
> Hi
> What do you mean "I have 1 table"? Can you isnert some sample data and
> show us an expected result?
>
> "Sajid MALIK" <sajid.malik@.gmail.com> wrote in message
> news:1163530384.096202.126770@.b28g2000cwb.googlegr oups.com...
>
|||Hi
http://vyaskn.tripod.com/row_level_security_in_sql_server_databases.htm
"Sajid S. Malik" <sajid_malick@.yahoo.com> wrote in message
news:u4ru3uKCHHA.3448@.TK2MSFTNGP03.phx.gbl...
> Hi Uri,
> Well it doesn't matter what the data is it could be only one column in
> that table I want to only select the rows based on the user roles.
> Lets take another example of stores So the table would be
> Site Table
> --
> SiteID
> Site
> --
> 1 | USA
> 2 | London
> 3 | France
> 4 | Germany
> --
> Now for example if I'm a site user I should only view the records for the
> local division like USA, London etc but if I'm an admin user I can view
> records for all the divisions.
> I hope this clears the question.
> Please do not hesitate to ask further question.
> Thanks,
> sajid malik
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:Op7ww6HCHHA.3380@.TK2MSFTNGP04.phx.gbl...
>