DD Logon Project
- Create the tables necessary for user authentication
- Logon example script
- 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#