Create Windows Authentication

[Tellie]

Hi All

I am creating an ASP page where I am trying to display controls on the page based on the roles of the user. I have a database wherein I have 5 tables to store the following:

Table 1: Application details having the app id as the primary key.
Table 2: Securtiy Roles, having the roles for particular WIndows Login id
Table 3: Security Users, having the details of the users with NT Login as the primary key
Table 4: Role Master table, having the roles available for the application with role id as the primary key
Table 5: Permissions table, having permission details for a role and for particular users for a particular application.

I want to know as to how I could create a role check and display the controls on a page as per the role.

Please help ASAP

[DVHost]

Like any code, there are probably a thousand ways to go about this. What I have here is the most fundamental method.

If you are using sessions or polling the database before the page is built you can probably build the page with the functions based on the users privlidges. I presume that when you say 'role' you are referring to their access rights such as superuser, admin, user, or guest.

The complexity of your code will also be defined by how your roles are labeled (numerically or alphanumerically) such as Superuser = 4 or Superuser = superuser. This is an example using ASP and VBScript and you could probably reduce this code dramatically.

For Sessions:

<%
' Key references
'
' Superuser = 4
' Admin = 3
' User = 2
' Guest = 1
%>
<% If Session("role") = 4 Then %>
<table width="100%"  border="0" cellspacing="0" cellpadding="0">
  <tr>
	<td><input name="button1" type="button" value="button1"></td>
	<td><input name="button2" type="button" value="button2"></td>
	<td><input name="button3" type="button" value="button3"></td>
	<td><input name="button4" type="button" value="button4"></td>
  </tr>
</table>
<% ElseIf Session("role") = 3 Then %>
<table width="100%"  border="0" cellspacing="0" cellpadding="0">
  <tr>
	<td><input name="button5" type="button" value="button5"></td>
	<td><input name="button6" type="button" value="button6"></td>
	<td><input name="button7" type="button" value="button7"></td>
	<td><input name="button8" type="button" value="button8"></td>
  </tr>
</table>
<% End If %>

For DB, just change your If and ElseIf statements to reflect the fields in question:

<% If (rsGET.Fields.Item("role").Value) = 4 Then %>
<% ElseIf (rsGET.Fields.Item("role").Value) = 3 Then %>

The code above will display a table with functions based on privlidges (role) of the user. Its not limited to buttons, in fact you can use anything you want. What the code actually does is display a TABLE based on a value.

Since it is ASP (server side) the page will only be built with the allowed table/functions and not reveal any others or how they work. Again, this code could be dumbed down even further but you should get the general idea.

I hope I am on the right track and this helps.

[Tellie]

Yeah you are on the right track, thanks.

I am using MS Access as my backend and this is the code that I am writing for accessing the database:

Dim connectionString As String

connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\Documents and Settings\\rkrishn3\\Desktop\\authentication.mdb"

Dim myConnection As New OleDbConnection(connectionString)

Dim mySelectQuery As String = "'Select NT_LOGIN from ALC_SEC_USERS where NT_LOGIN = '" + username

Dim myAdapter As OleDbDataAdapter = New OleDbDataAdapter(mySelectQuery, myConnection)

Dim employeeData As DataSet = New DataSet

myAdapter.Fill(employeeData)

Dim myCommand As New OleDbCommand(mySelectQuery)

myCommand.Connection = myConnection

myConnection.Open()

myCommand.ExecuteNonQuery()

End Sub

I am not able to get as to how I can compare the values in my database.
As per your solution I am trying to comapre the ROLE_ID given in the database which contains all the NT id of the users, which I get through this code:

Dim user As WindowsPrincipal = New WindowsPrincipal(WindowsIdentity.GetCurrent())

Dim username As String

If (user Is Nothing) Then

Response.Write("No User")

Else

username = user.Identity.Name

End If

I want to check from the database value if "username" exits and if it is the same as logged in and then display the particular page.

Could you help me

[DVHost]

In all logic, what you are trying to do sounds simple. But for clarification let me state what it is I think you are trying to do.

You have a logged in user and their username is stored in a variable. You want to compare that variable against a possible match in the database which will then determine what they will see.

If I am correct, you are halfway there. What I see missing is that your database function does not appear to iterate through all of the data. Here is a quick and painless way to access db records and iterate through them until you find the one you want. Depending on the results of the search, you can take one of two actions.

<%
Dim rsGET
Set rsGET = Server.CreateObject("ADODB.Recordset")
rsGET.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\Documents and Settings\\rkrishn3\\Desktop\\authentication.mdb"
rsGET.Source = "Select NT_LOGIN from ALC_SEC_USERS where NT_LOGIN = " + username
rsGET.CursorType = 0
rsGET.CursorLocation = 2
rsGET.LockType = 1
rsGET.Open()
 
If rsGET.BOF OR rsGET.EOF Then 
 
' NO MATCHING RECORDS FOUND CODE
 
Else
 
' FOUND MATCHING RECORDS CODE
 
End If
%> 
 
' Other html code here
 
<%
' Destroy and close the db connection
 
rsGET.Close()
Set rsGET = Nothing
%>

Your select statement is right on the money, you are preserving resources by looking for an explicit record. Now, rsGET.BOF and rsGET.EOF stand for Beginning of File and End of File respectively.

To the server, if it reaches either end of the DB and has not found anything then it will fire the 'NO MATCHING RECORDS code. If it does find the record in question it will fire the FOUND MATCHING RECORD code after the Else statement.

Whatever code you use in there is up to you. You could easily set a few session variables or do just about anything based on the query results.

Again, I hope I am answering the question. I am kind of new to helping in forums so please bare with me if I have lost site of your goals.

[Tellie]

O Yes you did answer my question, thanks a lot.....

Apart from this I just wanted to know one more thing,

I am trying to display the controls in a page as per the user logged in. Now I was thinking the right way is to check the user logged in and the Role to which the user belongs and then in the front end itself hide or activate the controls as per the rights of the user. One of the controls is the tree structured items on the left pane shown as links. What I was suggested is that the unique id's of the items on the tree or the controls on the page would be populated in a database and then retrieved from the database and displayed on the page as per the roles which is populated in another table.

Is this a right way of doing it? Any suggestions as to what would be the consequences with respect to the performance and other things.

[DVHost]

Sure! That is yet another way of doing it. My third choice in fact which I did not want to get into fearing over complicating the issue. But yes you can do it that way.

The only thing you need to consider is that many folks will tell you that an Access database is not the most robust db in terms of speed. Enough transactions to that puppy will result in performance issues. But that is what they will tell you, so I am doing both of us the great favor of letting you know this. Typically, whenever I discuss development with Access DB's, some code cowboy usually chimes in with "no, no, mySQL, pgSQL, SQL SQL... is better better!" So for the record, let it be known that I informed you. :)

On the other hand, I use Access db's in everything I develop and I have yet to see any evidence of performance issues but I try to keep the transactions to the db at a minimal. When they login, one transaction to the db to take care of permissions and what have you. Afterwards, I don't make calls to the db unless absolutely necessary like pulling customer records or updating them. So this could be what keeps me from seeing those alleged performance issues.

Although in your situation, it might be ideal to use the db for populating the tree because it would be easier to deal with changes to its structure and user access rights rather than coding it in the web page itself. Although you could use file includes to simplify the task and make the code easier to read. But the choice is up to you. Either way will work.

[Tellie]

Okie...

Please help me out with this error:

here is the code

Dim connectionString AsString

connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\Documents and Settings\\rkrishn3\\Desktop\\authentication.mdb"

Dim myConnection AsNew OleDbConnection(connectionString)

myConnection.Open()

Dim mySelectQuery AsString = "Select NT_LOGIN, ROLE_ID, PERMISSION_BUTTON_ID from ALC_SECURITY_PERMISSION where NT_LOGIN = '" + username + "' and ROLE_ID = 1"

Dim myAdapter As OleDbDataAdapter = New OleDbDataAdapter(mySelectQuery, myConnection)

Dim employeeData As DataSet = New DataSet

myAdapter.Fill(employeeData, "ALC_SECURITY_PERMISSION")

If (employeeData.Tables("ALC_SECURITY_PERMISSION").Rows.Count > 0) Then

Dim myQuery AsString = "Select NT_LOGIN, ROLE_ID, APP_ID from ALC_SEC_ROLES where NT_LOGIN = '" + username + "' and ROLE_ID = 1 and APP_ID = 1"

Dim myAdap As OleDbDataAdapter = New OleDbDataAdapter(myQuery, myConnection)

Dim empData1 As DataSet = New DataSet

myAdap.Fill(empData1, "ALC_SEC_ROLES")

If (empData1.Tables("ALC_SEC_ROLES").Rows.Count > 0) Then

Response.Write("HI")

EndIf

EndIf

And here is the error:

Data type mismatch in criteria expression.

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.OleDb.OleDbException: Data type mismatch in criteria expression.

Source Error:

Line 62: Dim myAdap As OleDbDataAdapter = New OleDbDataAdapter(myQuery, myConnection)
Line 63: Dim empData1 As DataSet = New DataSet
Line 64: myAdap.Fill(empData1, "ALC_SEC_ROLES")
Line 65:
Line 66: If (empData1.Tables("ALC_SEC_ROLES").Rows.Count > 0) Then

[DVHost]

Usually this error is generated when a numeric is expected and a string is supplied or vice versa. I presume that username is indeed a string and not a numeric. If it is numeric; remove the '.

If it is not numeric, make sure your DB column is specified as NOT numeric as well.

This error can also occur if you are querying the DB with empty values. I.E. username is empty.

Also, make sure that the coulumns in your DB for ROLE_ID and APP_ID are specified as numeric. If not change them to a numeric type or add 's around the 1's:

From:

and ROLE_ID = 1 and APP_ID = 1

To:

and ROLE_ID = '1' and APP_ID = '1'

See if any of that helps.

[Tellie]

Ok I could get that, the error was because a field being used in the select queries had different data types in the two tables. They were supposed to be the same

[Tellie]

Do you have an idea about retrieving a value from a select statement.

ok lemme put it up like this,

I have a table which has a column that contains the ids of the controls on my page. Now depending upon the user roles I would have to display the controls on the page. Now my problem is in the Select statement:

I am firing the following select statement:

Select PERMISSION_BUTTON_ID from ALC_SECURITY_PERMISSION where NT_LOGIN = '" + username + "' and ROLE_ID = 1

I want to capture the values of PERMISSION_BUTTON_ID so that I could display only those controls whose id's have been retrieved from the above select statement.

Please Help

[DVHost]

It sounds like we are back to the second and fourth post in this thread. Now its time to bring them together

Your select statement is fine, what you need to do is use the results of the query:

<%
Dim rsGET
Set rsGET = Server.CreateObject("ADODB.Recordset")
rsGET.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\Documents and Settings\\rkrishn3\\Desktop\\authentication.mdb"
rsGET.Source = "Select PERMISSION_BUTTON_ID from ALC_SECURITY_PERMISSION where NT_LOGIN = '" + username + "' and ROLE_ID = 1"
rsGET.CursorType = 0
rsGET.CursorLocation = 2
rsGET.LockType = 1
rsGET.Open()
 
If rsGET.BOF OR rsGET.EOF Then 
 
' NO MATCHING RECORDS FOUND CODE - DON'T DISPLAY ANYTHING
 
Else
 
%>
<table width="100%"  border="0" cellspacing="0" cellpadding="0">
  <tr>
 <td><%=(rsGET.Fields.Item("PERMISSION_BUTTON_ID").Value)%></td>
  </tr>
</table>
<% End If %>
 
 
' Other html code here
 
<%
' Destroy and close the db connection
 
rsGET.Close()
Set rsGET = Nothing
%>

I am not 100% sure how you go about displaying your controls via there ID's but the code: <%=(rsGET.Fields.Item("PERMISSION_BUTTON_ID").Value)%> will implement the contents of the data in the DB that matches the query.

[Tellie]

I have not used the recordset as there was some error that I was facing. Here is the code that I am working on:

Dim user As WindowsPrincipal = New WindowsPrincipal(WindowsIdentity.GetCurrent())

Dim username As String

If (user Is Nothing) Then

Response.Write("No User")

Else

username = user.Identity.Name

Response.Write("Username " + username)

End If

Dim connectionString As String

connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\Documents and Settings\\rkrishn3\\Desktop\\authentication.mdb"

Dim myConnection As New OleDbConnection(connectionString)

myConnection.Open()

Dim mySelectQuery As String = "Select NT_LOGIN, ROLE_ID, APP_ID from ALC_SEC_ROLES where NT_LOGIN = '" + username + "' and ROLE_ID = 1 and APP_ID = 1"

Dim myAdapter As OleDbDataAdapter = New OleDbDataAdapter(mySelectQuery, myConnection)

Dim employeeData As DataSet = New DataSet

myAdapter.Fill(employeeData, "ALC_SEC_ROLES")

If (employeeData.Tables("ALC_SEC_ROLES").Rows.Count > 0) Then

Dim myQuery As String = "Select PERMISSION_BUTTON_ID from ALC_SECURITY_PERMISSION where NT_LOGIN = '" + username + "' and ROLE_ID = 1"

Dim myAdap As OleDbDataAdapter = New OleDbDataAdapter(myQuery, myConnection)

Dim empData1 As DataSet = New DataSet

myAdap.Fill(empData1, "ALC_SECURITY_PERMISSION")

If (empData1.Tables("ALC_SECURITY_PERMISSION").Rows.Count > 0) Then

Button1.Visible = False

Button2.Visible = True

Button3.Visible = False

End If

End If

myConnection.Close()

myConnection = Nothing

End Sub

The error that I was getting while using the code:

Dim rsGET
Set rsGET = Server.CreateObject("ADODB.Recordset")
rsGET.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\Documents and Settings\\rkrishn3\\Desktop\\authentication.mdb"
rsGET.Source = "Select PERMISSION_BUTTON_ID from ALC_SECURITY_PERMISSION where NT_LOGIN = '" + username + "' and ROLE_ID = 1"
rsGET.CursorType = 0
rsGET.CursorLocation = 2
rsGET.LockType = 1
rsGET.Open()

was for rsGET.CursorType = 0 stating that the value is out of scope or so, I do not remember the exact message