DD Logon Project

• Create the tables necessary for user authentication
• Logon example script
  

  Logon Script Diagram
  • People
  • Roles
  • RoleTypes

• Create a form that queries your tables (you should use a view to join the roles)
  • If logon was successful show user roles
  • If logon was not, do not show user roles

• Think about Ideas for a final project

Logon

Insert into the People Table

<sql> INSERT into People (salutationID, firstName, lastName, logonName, passwd) values (1, 'jeff', 'meyers', 'jmeyers', 'monkey') </sql>

Check the new users PeopleID

<sql> SELECT PeopleID from People WHERE firstName = 'jeff' and lastName='meyers' and logonName='jmeyers' </sql>

results PeopleID = 1

Give the new user a Role by inserting the PeopleID and a roleID into the roles table

<sql> INSERT into roles ( PeopleID, roleTypeID, RoleActive ) values ( 1, 1, 1) </sql>

 Note that we need two INSERT statements to create a working account and role. Since these two statements
depend on each other and we will be left with an Person without a role or a role without a person if one 
statement fails the entrire operation should be wrapped in a transaction. More on this next week...

Now we have a user and that user has a role. We need to be able to read back the userName, passowrd, roleActive and roleTypeName in order to check if a user is valid.

The easiest way to do this is with a view

<sql> SELECT dbo.people.peopleID, dbo.people.firstName, dbo.people.lastName, dbo.people.logonName,

      dbo.people.passwd, dbo.peopleSalutationTypes.salutationID, 
      dbo.peopleSalutationTypes.salutation, dbo.roles.roleActive, dbo.roleTypes.RoleTypeID,
      dbo.roleTypes.RoleTypeName, dbo.roleTypes.RoleTypeHier, 
      dbo.roleTypes.roleTypeActive

FROM dbo.people INNER JOIN

      dbo.peopleSalutationTypes ON dbo.people.salutationID = dbo.peopleSalutationTypes.salutationID 

INNER JOIN

      dbo.roles ON dbo.people.peopleID = dbo.roles.PeopleID 

INNER JOIN

      dbo.roleTypes ON dbo.roles.RoleTypeID = dbo.roleTypes.RoleTypeID

</sql>

Now we can use the view to test a logon SELECT statement <sql> SELECT peopleID, firstName, lastName, logonName, passwd, salutationID,

      salutation, roleActive, RoleTypeID, RoleTypeName, RoleTypeHier, 
      roleTypeActive

FROM logon_vw WHERE logonName = 'jmeyers' and passwd = 'monkey' and

       roleActive = 1 and

roleTypeActive = 1 </sql>

Now that we have the DataBase working correctly we can move on to the c#