473,226 Members | 1,321 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,226 software developers and data experts.

security for row level but not based on Database user's login

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/pro.../multisec.mspx
Thanks in advance
Rams

Apr 21 '06 #1
1 2646
-----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/AwUBREkhzIechKqOuFEgEQJgbwCdGcXgPTab6xk0h2iswg3iKb zZUecAnRtT
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/pro.../multisec.mspx

Apr 21 '06 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

7
by: PaulThomas | last post by:
I am fighting with XP-Pro and VS.Net trying to allow some of the pages in my application to be accessable by 'all' I am using <authentication mode="Forms" /> and if I Login - everything works...
12
by: Angelos Karantzalis | last post by:
Is there a way to set Permissions based on user roles by using some configuration file for my application ? I'm coming from a Java background, where that could very easily be accomplished but...
116
by: Mike MacSween | last post by:
S**t for brains strikes again! Why did I do that? When I met the clients and at some point they vaguely asked whether eventually would it be possible to have some people who could read the data...
3
by: Br | last post by:
I'm going to go into a fair bit of detail as I'm hoping my methods may be of assistance to anyone else wanting to implement something similar (or totally confusing:) One of systems I've...
4
by: Alexey Smirnov | last post by:
I have intranet application based on ASP.NET on Win2000AS (Framework 1.1). Website uses an Integrated Windows Authentication as Authentication method in IIS and has following security...
0
by: AAJ | last post by:
Hi all I am a relatively experiences Windows/SQL Server database programmes and have just started on my first ever web app. I have managed with most of the simple stuff i.e. datagrids, crystal...
2
by: Nosferatum | last post by:
This script is meant to limit access by sessions, using username and password from mysql db and redirect users after login according to a given value belonging to each user in the db (10,20,30,40)....
7
by: thebarefootnation | last post by:
Hi, I have created an access db that I would like to secure. The database will exist on a shared drive and be used at a number of different locations hence the reason to secure the database. ...
1
by: Matt MacDonald | last post by:
Hi all, I've been debating for a while (basically since asp.net 2.0 came out) on using the built in mebership classes to handle user management in my web apps. I seem to keep coming upon...
0
by: VivesProcSPL | last post by:
Obviously, one of the original purposes of SQL is to make data query processing easy. The language uses many English-like terms and syntax in an effort to make it easy to learn, particularly for...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.