Thread: 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/prodtechnol/sql/2005/multisec.mspx


Thanks in advance
Rams

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.

Well, the data access stuff is all manageable via views, which is the
standard way to do this.

You don't say which version of PostgreSQL you are using, but I'd be
tempted just to switch to a different user after connecting and use the
session_user system function to control what is visible in the view.

For example:
CREATE VIEW my_contacts AS SELECT * FROM contacts WHERE owner =
session_user;

If that's not practical then you'll need to write some functions to
simulate your own session_user (say application_user()). This is easiest
to write in plperl/pltcl or some other interpreted language - check the
list archvies for plenty of discussion.

--
   Richard Huxton
   Archonet Ltd

Ramasamy wrote:
> Hi Richard ,
>  Very good Day. Great information that you given to me.

Great glad you think it's useful. Oh, don't forget to cc: the
mailing-list - that's the convention around here.

> I will try in your idea. Here I am using SQL server 2000(Even I can use SQL
> Sever 2005 too if needed.) We are dealing with lot of databases with lot of
> business logic. I think your information will great for me.

Ah - you're not using PostgreSQL? Well, the principle is the same but
you should be aware that this is a PostgreSQL list so the details will
be different.

> Let I try and I will back with you.
>
> Thanks and hands of too you.
>
> But i don't have any idea of getting Session from databases (I think you are
> meaning that user can be handle with session). If you have some more idea
> then it will be great full to me.

With MS-SQL you'll probably want to look at T-SQL variables, although I
don't know if they last longer than a single transaction. Failing that,
functions or temporary tables might be a help.

--
   Richard Huxton
   Archonet Ltd