Get Column Names Dynamically! - SQL Server Forums

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.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Get Column Names Dynamically!

Author

Topic

arpan_de
Starting Member

2 Posts

Posted - 2005-09-08 : 16:53:31

A SQL Server DB table has, say, 12 columns which only 3 users can access. Now I want that if the first user has logged in, he should be allowed to access/edit the records under the columns Col1, Col4, Col9 & Col12. If the second user has logged in, he should be allowed to access/edit the records under the columns Col2, Col6, Col8 & Col11. If the third user has logged in, he should be allowed to access/edit the records under the columns Col3, Col5, Col7 & Col10. Please note that users will be accessing the records in an ASP page.

I implemented this by first making this table as the base table & created 3 views with each of the views containing 4 columns.

In the ASP application, the users have to enter a userid & password after which records will be retrieved from the views & displayed depending upon the userid & password. This is how it can be done:

<%
     Dim strUserID,strPwd
     strUserID=Request.Form("userid")
     strPwd=Request.Form("pwd")

     Dim strSQL
     If(strUserID="uid1" And strPwd="pwd1") Then
          strSQL="SELECT * FROM View1"
     ElseIf(strUserID="uid2" And strPwd="pwd2") Then
          strSQL="SELECT * FROM View2"
     ElseIf(strUserID="uid3" And strPwd="pwd3") Then
          strSQL="SELECT * FROM View3"
     End If
%>

Till this point, everything's fine. The problem comes up if instead of 3 users, there are, say, 100 users. Under such circumstances, this means that 100 views have to be created in SQL Server - one for each user. Not only this, even the ASP page should have 100 If.....Else statements so that users can access only those records which they are permitted to access.

This will be a highly tedious & monotonous approach. Please suggest a more efficient way to implement this?

Thanks,

Arpan

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-09-08 : 17:21:23

Why don't you have use a single view, and based on permissions only show the user what is relevant to them. That should be done client side.

Corey

Co-worker on The Wizard of Oz "...those three midgets that came out and danced, the freaked me out when I was little. But they are ok now."

arpan_de
Starting Member

2 Posts

Posted - 2005-09-08 : 17:51:17

You mean creating a single view & then assigning permissions column-wise to each & every user? If so, then why create a view? Why not use the table only & assign permissions on a per-column basis, something like

GRANT ALL ON MyTable(Col1,Col4,Col9,Col12) TO User1
GRANT ALL ON MyTable(Col2,Col6,Col8,Col11) TO User2
GRANT ALL ON MyTable(Col3,Col5,Col7,Col10) TO User3
...............
...............
...............
GRANT ALL ON MyTable(Col1,Col3,Col4,Col11) TO User100

Please do correct me if I have misinterpreted your suggestion.

Thanks,

Regards,

Arpan

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-09-08 : 22:33:16

oh yeah.. you missed it

I meant that you should keep track of who needs what column in some kind of table format, and use that to direct your asp on what to show each user.

Like

if RsUser("showCol01") then Response.write RsView("Col01")

Corey

Co-worker on The Wizard of Oz "...those three midgets that came out and danced, the freaked me out when I was little. But they are ok now."

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources