DD Logon Project

esse quam videri
Jump to: navigation, search
  • 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


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

Logon vw.png

<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, 

FROM dbo.people INNER JOIN

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


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


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


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, 

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#