Posted by MGFoster on 04/21/06 21:17

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Views are the traditional way to restrict access to rows/columns.
Create a View w/ the Role of the users as the owner of the View. E.g.,
two views with the same name. One, owned by the Officer role, the
other, owned by the Employee role.

CREATE VIEW Officer.EmployeeInfo
AS
SELECT employee_id, start_date, salary
FROM Employees


CREATE VIEW Employee.EmployeeInfo
AS
SELECT employee_id, start_date
FROM Employees

When an employee signs on he is a member of the Employee role;
therefore, when he opens the View EmployeeInfo he doesn't see the salary
information. If an officer signs in and opens the EmployeeInfo View he
sees all information.

To restrict access to rows you can have a table like this:

CREATE TABLE UserDistricts (
role_name VARCHAR(25) NOT NULL ,
district_nbr TINYINT NOT NULL,
CONSTRAINT PKUserDistricts PRIMARY KEY (role_name, district_nbr)
)

A function like this:

CREATE FUNCTION dbo.ufn_user_groups()
RETURNS TABLE
AS
RETURN (

select
case
when (usg.uid is null) then 'public'
else usg.name
end as role_name

from
sysusers usu
left join (sysmembers mem inner join sysusers usg
on mem.groupuid = usg.uid)
on usu.uid = mem.memberuid
left join master.dbo.syslogins lo
on usu.sid = lo.sid
where
(usu.islogin = 1 and usu.isaliased = 0
and usu.hasdbaccess = 1)
and (usg.issqlrole = 1 or usg.uid is null)
and usu.name = CURRENT_USER

)

And a View like this:

CREATE VIEW DistrictSales
AS
SELECT district_name, Sum(sales_revenue) As TotSales
FROM Sales
WHERE district_nbr IN
(SELECT district_nbr FROM UserDistricts
WHERE role_name IN (SELECT role_name FROM dbo.ufn_user_groups())

When a user opens the DistrictSales View she will only see the info for
the distict she belongs to (as assigned in the table UserDistricts).
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBREkhzIechKqOuFEgEQJgbwCdGcXgPTab6xk0h2iswg3iKbzZUecAnRtT
Jl1AL/d1jFa12rlNMo5jh+vp
=3nYe
-----END PGP SIGNATURE-----


Friends wrote:
> Hi
>
> I need to set security for row level but not based on Database user's
> login. It should be based on the user table login. For the particular
> user I need to allow only the particular records to access insert,
> update delete and select.
>
> Let me explain clearly
>
> For example think we are using asp/asp.net website
>
> Eg:
>
> www.test.com
>
> So take this is our website and if you try this URL then you will get a
> window for Login name and password.
> For example the Login name is windows user name (Here windows user
> means server windows user and not client) and windows password. So if
> you have login user id you can able to login in our site and we have
> another check. We have our own usertable this table consist all the
> user login names and user rights. We will check the windows user who
> login in our site has rights in the usertable I mean he is present in
> the usertable if he is not present then we will display a message you
> have no rights to access this site.
> If he has login id in our usertable then he allowed viewing our
> pages. Still if he has the login id we will check the user who login
> has how much right to access to each page and the records of each table
> its all depend on the user rights.
>
> So, here I need the row level security. For each and every table we
> need to check the corresponding user and executing the record produce
> lot of business logic problem for us.
> So after the user login we need automatically to set row level
> security for all the tables. Based on the user who login.
>
> So from there if we try select * from <tablename> then we can only able
> to get the allowed records to select, insert, update, delete.
>
> Please can some one help how to solve this?
>
> Note:
>
> For some help you can refer the below URL (See in that they only given
> about the row level and column level security for each database users
> not for our required concept)
>
> http://www.microsoft.com/technet/prodtechnol/sql/2005/multisec.mspx

[Back to original message]