LightSwitch for Games Part 2: OData Tutorial and User Account Management on the Server

NOTE: Although this series is aimed at small game developers, it is equally applicable to anyone wishing to learn how to use LightSwitch.

In part 1 of this series we looked at the rationale for using LightSwitch as a game data server and how to setup and publish a LightSwitch project on Windows Azure (optional). In this part, we’ll look at the server-side work needed to enable users to create their own accounts on the game network. We shall take a look at several client-side samples for registering and logging on in part 3 to complete the end-to-end process.

In this article, you will learn:

• What OData is, how it works and the underlying HTTP messages it uses
• How to use OData to retrieve and insert rows in your LightSwitch application database
• How users, permissions and roles are organized in a LightSwitch application
• How to create a desktop application to edit the users and roles in your application
• How to create a WCF RIA Service which provides an updateable view of two tables
• How to create an anonymous guest user with limited permissions (in this case, permission to add a new, real user)
• How to limit a user to accessing only their own data in a particular table
• How to create a user profiles table which can store additional information and automatically generates a new user in LightSwitch’s internal user authentication database when a new row (user) is inserted
• How to automatically assign new users to roles

This article assumes some familiarity with:

• HTTP (GET and POST methods, headers and request and response bodies)
• a basic understanding of either XML or JSON
• a basic understanding simple database structures (tables and rows) and basic SQL queries (SELECT and INSERT)
• a moderate understanding of C# (but you can just copy and paste the code if you aren’t too familiar with C#)

NOTE: You need Visual Studio Professional 2012 Update 2 or Update 3, or Visual Studio 2013 (Preview) to complete the tutorials in this article. Note that in Visual Studio 2013 (Preview), the organization of items in Solution Explorer has changed so some items may be in different places to those indicated below.

Querying your LightSwitch database over the web with OData

As we mentioned briefly in part 1, LightSwitch projects store their data in an SQL database server which is published when you deploy the project to a web site or the cloud. LightSwitch provides an industry standard mechanism to access and modify this data called the OData protocol. I have set up a simple project at http://gamenetwork.azurewebsites.net which you are welcome to use as a guinea pig! (please note that the database contents are subject to change)

Each table in your database is accessible via OData using an HTTP service endpoint, which is really nothing more than a web page that accepts and returns data in an OData-compliant format. Before you can access these endpoints, you need to be authenticated; if you are using Forms authentication in your LightSwitch project, you can either include the username and password in the URL like http://username:password@yourwebsite.com/endpoint-page or log in using the login page at yourwebsite.com/LogIn.aspx, then your session key will be stored in a cookie (meaning you won’t need to re-send your username and password every single time you access an endpoint). Any attempts to access an endpoint without being logged in are denied (to logout, use yourwebsite.com/LogOut.aspx). Note that when you use the login page, a successful login will present you with an error as it tries to re-direct to a page that doesn’t currently exist in our project. This is nothing to worry about; if you login with an incorrect username or password, this will be shown on the login page itself.

The starting point for everything is the ApplicationData.svc endpoint, which summarizes all the tables in your database. If you have a single table called UserProfiles, then visiting yourwebsite.com/ApplicationData.svc will yield something like this:

<service xmlns="http://www.w3.org/2007/app" xmlns:atom="http://www.w3.org/2005/Atom" xml:base="http://gamenetwork.azurewebsites.net/ApplicationData.svc/">
 <workspace>
  <atom:title>Default</atom:title>
  <collection href="UserProfiles">
   <atom:title>UserProfiles</atom:title>
  </collection>
 </workspace>
</service>

(for the interested, you can see above that OData is built on top of the Atom protocol)

OData endpoints return XML by default, but if you want JSON (the JavaScript notation) instead, you can add $format=json to the URL query string, for example yourwebsite.com/ApplicationData.svc/?$format=json will show the same data as above like this:

{"odata.metadata":"http://gamenetwork.azurewebsites.net/ApplicationData.svc/$metadata","value":[{"name":"UserProfiles","url":"UserProfiles"}]}

The format to use depends on whether your client can deal with XML or JSON more easily, and makes no difference to the actual data itself. You can also submit data using either XML or JSON as you prefer.

Each table itself can be accessed by appending its name after the endpoint, so to read the contents of the UserProfiles table, we visit yourwebsite.com/ApplicationData.svc/UserProfiles. If the table consists of Id (integer), FirstName (string) and LastName (string) fields, and has one entry with ID 1, FirstName Katy and LastName Coe (that’s me!), fetching ApplicationData.svc/UserProfiles will look like this:

XML

<?xml version="1.0" encoding="utf-8"?>
 <feed xml:base="http://gamenetwork.azurewebsites.net/ApplicationData.svc/" xmlns="http://www.w3.org/2005/Atom" xmlns:d="http://schemas.microsoft.com/ado/2007/08/dataservices" xmlns:m="http://schemas.microsoft.com/ado/2007/08/dataservices/metadata">
  <id>http://gamenetwork.azurewebsites.net/ApplicationData.svc/UserProfiles</id>
  <title type="text">UserProfiles</title>
  <updated>2013-08-26T00:26:49Z</updated>
  <link rel="self" title="UserProfiles" href="UserProfiles" />

  <entry m:etag="W/&quot;X'00000000000007D1'&quot;">
   <id>http://gamenetwork.azurewebsites.net/ApplicationData.svc/UserProfiles(1)</id>
   <category term="LightSwitchApplication.UserProfile" scheme="http://schemas.microsoft.com/ado/2007/08/dataservices/scheme" />
   <link rel="edit" title="UserProfile" href="UserProfiles(1)" />
   <title />
   <updated>2013-08-26T00:26:49Z</updated>
   <author>
    <name />
   </author>
   <content type="application/xml">
    <m:properties>
     <d:Id m:type="Edm.Int32">1</d:Id>
     <d:RowVersion m:type="Edm.Binary">AAAAAAAAB9E=</d:RowVersion>
     <d:FirstName>Katy</d:FirstName>
     <d:LastName>Coe</d:LastName>
    </m:properties>
   </content>
  </entry>
 </feed>

JSON

{"odata.metadata":"http://gamenetwork.azurewebsites.net/ApplicationData.svc/$metadata#UserProfiles","value":[{"Id":1,"RowVersion":"AAAAAAAAB9E=","FirstName":"Katy","LastName":"Coe"}]}

Using the XPath notation, the actual contents of each row in the XML version can be found in /feed/entry[rowNumber]/content/m:properties/*.

Notice the link tag associated with the first row. It specifies that you can fetch the individual row by using the endpoint ApplicationData.svc/UserProfiles(1). Sure enough, visiting this endpoint in the browser gives us:

XML

<?xml version="1.0" encoding="utf-8"?>
<entry xml:base="http://gamenetwork.azurewebsites.net/ApplicationData.svc/" xmlns="http://www.w3.org/2005/Atom" xmlns:d="http://schemas.microsoft.com/ado/2007/08/dataservices" xmlns:m="http://schemas.microsoft.com/ado/2007/08/dataservices/metadata" m:etag="W/&quot;X'00000000000007D1'&quot;">
 <id>http://gamenetwork.azurewebsites.net/ApplicationData.svc/UserProfiles(1)</id>
 <category term="LightSwitchApplication.UserProfile" scheme="http://schemas.microsoft.com/ado/2007/08/dataservices/scheme" />
 <link rel="edit" title="UserProfile" href="UserProfiles(1)" />
 <title />
 <updated>2013-08-26T00:37:00Z</updated>
 <author>
  <name />
 </author>
 <content type="application/xml">
  <m:properties>
   <d:Id m:type="Edm.Int32">1</d:Id>
   <d:RowVersion m:type="Edm.Binary">AAAAAAAAB9E=</d:RowVersion>
   <d:FirstName>Katy</d:FirstName>
   <d:LastName>Coe</d:LastName>
  </m:properties>
 </content>
</entry>

JSON

{"odata.metadata":"http://gamenetwork.azurewebsites.net/ApplicationData.svc/$metadata#UserProfiles/@Element","Id":1,"RowVersion":"AAAAAAAAB9E=","FirstName":"Katy","LastName":"Coe"}

As a final example, if you want to fetch the profile (row) for a particular user, you can use the equivalent of the SQL WHERE clause by using an endpoint like ApplicationData.svc/UserProfiles(Id=1234) or ApplicationData.svc/UserProfiles(FirstName=”Katy”, LastName=”Coe”). Notice that for this to work, the field names you use must be defined as keys in the LightSwitch project.

All of the general querying (you can think of it as SQL SELECT statements) of your LightSwitch database works like this – HTTP GET queries using OData. There are many, many other things you can append to the endpoints to simulate things like WHERE clauses and so on, and we will explore these in later articles. You can also check out the articles in the References section at the end for the full notation.

Adding rows to your LightSwitch tables

Much of the point of LightSwitch is to create fast forms-over-data applications; that is to say, you create a table, generate a web page in Visual Studio’s LightSwitch project editor which allows you to enter rows into that table and everything is done for you (these pages are known as Screens in LightSwitch terminology). However, we will be wanting our users to be able to create accounts from within a game’s interface without having to exit to a web browser to do so, so we need to know how to add rows programmatically instead (without a web interface, as we will provide our own in-game interface for registering a new account). Additionally, since we would like to use our LightSwitch application to store online leaderboards and perhaps game state, it would not make any sense to have web pages to edit these tables – they need to be done from the game code in the background, transparently to the user.

Figure 1. Using Hurl to add a row to a LightSwitch database table with JSON

To add rows (like SQL INSERT statements), you need to use HTTP’s form-posting mechanism, the HTTP POST method. Since browsers always fetch URLs you type into the address bar with the GET method, and since we haven’t yet written a client to insert rows for us, we need to use a tool to try this out. There are many downloadable tools such as WFetch which can do this for you, as well as many online web pages that will submit an HTTP request of your choice and show you the request and response body and headers. Personally I find hurl.it (and the updated version hurl.eu) to be an excellent online tool for this and is what I shall be using in this series.

Figure 1 shows how to add a row to our example UserProfiles table. Obviously, the XML version contains a lot of unnecessary information for just adding a table row, and JSON is more human-readable, so I prefer using JSON for this kind of work when we are typing in stuff by hand. The important points are:

1. set the URL to that of the endpoint of the table to which you wish to add a row
2. set the HTTP method to POST
3. enter a JSON-formatted row into the post body (in the format {FirstFieldName:”FirstFieldValue”,”SecondFieldName”:”SecondFieldValue” …}
4. set authentication to HTTP Basic when using LightSwitch Forms authentication, and enter the account details of a user with permission to add rows to the table. I have just used the default administrator account in the example
5. add a Content-Type header and set it to application/json (or application/xml if you are sending XML data)
6. add an Accept header and set it to application/json to get the response in JSON format (or application/xml if you want the response in XML)

The raw HTTP request looks like this:

POST /ApplicationData.svc/UserProfiles HTTP/1.1
Host: gamenetwork.azurewebsites.net
Authorization: Basic *********************
Accept: application/json
Content-Type: application/json
Content-Length: 40

{Id:"1",FirstName:"Katy",LastName:"Coe"}

(I have starred out the Authorization header – this will contain a Base64-encoded string consisting of your username and password – it is not secure unless you use SSL where the HTTP header will be encrypted, and even then there are problems – see the article about Basic Auth HTTPS security in the References section for more details)

And if successful, you will get an HTTP response like this:

HTTP/1.1 201 Created
Cache-Control: no-cache
Content-Length: 180
Content-Type: application/json;odata=minimalmetadata;streaming=true;charset=utf-8
ETag: W/"X'00000000000007D1'"
Location: http://gamenetwork.azurewebsites.net/ApplicationData.svc/UserProfiles(1)
Server: Microsoft-IIS/8.0
Set-Cookie: ARRAffinity=54d4ad8399187bcf9b8dcac2e09e083a23c703c38053d721e1ec6c9e153057db;Path=/;Domain=gamenetwork.azurewebsites.net
X-Content-Type-Options: nosniff
DataServiceVersion: 3.0;
X-AspNet-Version: 4.0.30319
X-Powered-By: ASP.NET
X-Powered-By: ARR/2.5
X-Powered-By: ASP.NET
Set-Cookie: WAWebSiteSID=50c0bae9aa7140a1869dcad515f1750f; Path=/; HttpOnly
Date: Mon, 26 Aug 2013 00:26:37 GMT

{
  "odata.metadata": "http://gamenetwork.azurewebsites.net/ApplicationData.svc/$metadata#UserProfiles/@Element",
  "Id": 1,
  "RowVersion": "AAAAAAAAB9E=",
  "FirstName": "Katy",
  "LastName": "Coe"
}

The important part here is the HTTP status code highlighted. Here are the main possibilities:

• 201 – the row was successfully added
• 500 – there was a problem, and the HTTP body will contain an XML-formatted error message (or a JSON object cdata.error -> message -> value containing the same XML-formatted error message if you are using JSON).
• 403 – the username and password supplied were incorrect
• 404 – the table doesn’t exist (you have used a non-existent endpoint URL)
• 400 – the request was malformed (you made a mistake in the JSON or XML notation or you used a field name that doesn’t exist)

Of course, we will use APIs when creating our client code that deal with all the business of HTTP, XML and JSON for us, so we don’t need to worry about most of these internal details, and we won’t be constructing the requests and responses by hand. All of this is just to show you how it works under the hood.

In the examples below, think of Hurl (or whatever other tool you use) as a simulation of the client code we will write later; we don’t have a client (game) at the moment, so we just use these HTTP tools instead for test purposes, but if you imagine the tool as the game itself, it may help you to visualize and understand what we are trying to accomplish better.

User management in a LightSwitch application

So far we have talked about how to query (read) and add rows to an existing table in our application, and doing this has always required us to be logged in as an authenticated LightSwitch user. If you followed part 1, you will have a deployed project with a single user probably called admin. We will want our players to be able to log in to the LightSwitch application as themselves and have read/write access to their own profiles, read access to the leaderboards and a certain amount of write access to the leaderboards and certain other tables depending on the game. We therefore need a way to access the actual LightSwitch user database and add new users. But we first need to understand how users are organized in a LightSwitch application.

The LightSwitch user database contains five tables named UserRegistrations, Roles, RoleAssignments, Permissions and RolePermissions. Here is what they are for:

• the UserRegistrations table defines each user’s username and password (login credentials)
• the Permissions table is a read-only table which defines names for each specific allowed action you have defined in the LightSwitch project (can write to table X, can read from table Y etc.)
• the Roles table defines the names of roles, which are groups of permissions
• the RolePermissions table defines which permissions are in which roles
• the RoleAssignments table defines which users are members of which roles

This sounds more complicated than it is, so let’s illustrate with an example:

You want two types of users: Players who can read and write their own user profile, read anyone else’s user profile, read the leaderboards and write a new row to the leaderboard table, but nothing else. The other type of user will be a GameManager who is an employee at your company who manages the game server. These users can do everything Players can, but they can also read and write anyone’s profiles (to remove offensive content), delete profiles (ban users), and edit anything on the leaderboards (to remove cheaters). This would be organized as follows:

1. Create two roles called GameManager and Player. These go into the Roles table. This is most easily done from the user management interface (see next section below)
2. Create permissions called CanReadLeaderboards, CanEditLeaderboards, CanAddNewScore, CanReadAllProfiles, CanWriteOwnProfile, CanWriteAllProfiles and CanDeleteProfiles. These go into the Permissions table. You do this from within the LightSwitch IDE in Visual Studio itself. Note that to make the permissions actually do something (take effect), code has to be added to each table to enforce them; this is discussed below
3. Assign permissions to roles. This is done in the RolePermissions table and is again most easily achieved via the user management interface. For the Player role, we will add CanReadLeaderboards, CanAddNewScore, CanReadAllProfiles and CanWriteOwnProfile as the permissions assigned to this role. For the GameManager role, we will add CanEditLeaderboards, CanWriteAllProfiles and CanDeleteProfiles.
4. When a user is created, their username and password will go into UserRegistrations. By adding server-side code to the LightSwitch project in Visual Studio, we will ensure that new accounts automatically have an entry for them added into RoleAssignments to assign them to the Player role. It is very important to do this only with server-side code. If you allow the client-side code to set the user’s role, they could hack the code, change the role to GameManager, create a new user and essentially accomplish a privilege escalation hack which will allow them to delete everyone’s profiles and mess up the leaderboards.

If you’re starting to sweat, don’t worry! We will go over how to do all of these things with step-by-step examples in the course of this series!

Creating a user management interface for your LightSwitch application

The LightSwitch IDE doesn’t let us edit the user database during development or deployment, and the HTML client also doesn’t provide any interface for administrators. It would certainly be rather inconvenient to have to use a tool like Hurl to configure all the default users, permissions and roles but fortunately there is a solution. By creating a desktop client for our LightSwitch application, we can access the administrator interface which lets us edit the user database.

Figure 2. Setting the desktop client properties to create a Windows desktop application

To add this to your project from part 1 of this series, follow these steps:

Figure 3. LightSwitch desktop client user database administration window

1. Right-click the GameNetwork project in Solution Explorer and choose Add Client… from the menu
2. Click Desktop Client, leave the default name (DesktopClient) and click OK
3. Right-click on DesktopClient in Solution Explorer and choose Properties from the menu
4. Select the Client Type tab and change the client option from Web to Desktop. This will cause a downloadable EXE to be produced which is a Windows application you can use to connect to and administer the LightSwitch database from your desktop (see Figure 2). Close the Properties window
5. Choose Build -> Publish GameNetwork from the main menu bar
6. You will now need to navigate to the Digital Signature tab in the Security Settings section of the publish settings dialog (it will be highlighted with an exclamation mark) and select a certificate. Simply choose your Windows Azure publish credentials file here – which should already be in the drop-down list.
7. Click Next and Publish then wait a couple of minutes
8. Navigate to yourwebsite.com/DesktopClient/ and once Silverlight loads you will be prompted to download and install the application. Click the button and use the More Options drop-down to decide where you want the application shortcuts to be installed (Desktop and/or Start Menu).
9. Once the application has installed, start it up and you will be presented with a standard login screen. Log in as an administrator.
10. You should now be in the administration window. If you click on the Administration menu in the bar at the top, you can choose Roles and Users and you should see screens similar to Figure 3. You can now edit the user database however you wish.

Remember that although you can add permissions here, you can and should in preference do that in the LightSwitch IDE in Visual Studio so that the permission names can be referenced in your server-side code.

We’ll come back to this interface and add things as and when we need them, but now we shall move on and look at three ways to add users programmatically (and assign them to roles), each with their own pros and cons.

1. Accessing the user database directly as an administrator

LightSwitch provides an endpoint that allows us to query and modify the user database called Microsoft.LightSwitch.SecurityData.svc. It works as follows:

• If you are logged in as an administrator, you can read and write all user data.
• If you are logged in as a user without administrator privileges, you can only see (read) your own user data and you can’t write any data. That is, if you try to query the user table to fetch all of the usernames and passwords, you will only retrieve your own. This is for obvious security reasons.

Using Hurl or another HTTP tool we can add a user by POSTing some data to Microsoft.LightSwitch.SecurityData.svc/UserRegistrations as follows:

Target URL: yourwebsite.com/Microsoft.LightSwitch.SecurityData.svc/UserRegistrations
HTTP method: POST

HTTP headers:

Accept: application/json
Content-Type: application/json

HTTP body:

{UserName:"katycoe",FullName:"Katy Coe",Password:"SomePassword123"}

As always, you need to remember to supply your administrator username and password using HTTP Basic auth as well, in the same way as we did earlier. Also recall that the supplied password must meet the minimum password complexity requirements (discussed in part 1, along with how to change them) otherwise the server will return an HTTP status code 500 error.

If you want to also assign a user to an existing role, that is easy too. For example, we assign the user katycoe to the Player role as follows:

Target URL: yourwebsite.com/Microsoft.LightSwitch.SecurityData.svc/RoleAssignments
HTTP method: POST

HTTP headers:

Accept: application/json
Content-Type: application/json

HTTP body:

{UserName:"katycoe",RoleName:"Player",SourceAccountName:"admin"}

The SourceAccountName field is just for logging purposes and specifies which account granted the role to the user. You can set this to anything you want.

Problems

This is all fine for testing and debugging purposes, but would be horrible if we actually used it in production game code, because we would have to ship the administrator password in every copy of the game, leading to inevitable fast destruction of our server by unscrupulous users. So the problem becomes, how do we add ourselves as a new user when we aren’t authenticated as an administrator, or in fact any user at all, if the application requires HTTP Basic authentication?

The answer is to create some kind of limited ‘guest’ user which has a freely available password that we can distribute with the application (and it doesn’t matter if people discover it), but which is only used for adding real new users and has no other permissions. Both of the methods below use this technique to allow users to be added without needing knowledge of the administrator account password.

Another problem is that while the user database can only store user names, full names and passwords, any self-respecting game network will doubtless want to store lots of other information about each user, such as their email address, perhaps a location, biography, avatar and so on. Networks like PSN and Xbox Live store information like the user’s total gamerscore. To store this information, we will use a standard table in our LightSwitch application, which solves the problem but leads to another: when we create the user, we may want them to submit their email address and other details in one step, and we have to make sure that both the internal user database and our user profiles table get updated at the same time, with only one OData request. This is for a couple of reasons: first, if we do it in two steps, updating one table at a time, something can go wrong (the user could lose their internet connection for example, or data for one table could be valid but the other invalid) and leave the database in an inconsistent state. Second, we don’t want the game to have to be patched when we change the database design or structure; we just want the game to be able to call some kind of ‘add user’ function and for our LightSwitch server to update whichever tables need to be updated to accomplish this. Our first stab at a solution follows below.

2. Using a WCF RIA Service with a guest user to add a new user and profile into two tables simultaneously

(this portion of the article is a re-formulation of Michael Washington’s excellent article WCF RIA Service: Combining Two Tables, updated for current versions of Visual Studio 2012, with source code for SELECT-style statements, security configuration and limiting the table read to the currently logged in user added by me)

WCF RIA is short for Windows Communication Framework Rich Internet Application. Alright, don’t get nervous 🙂 A WCF RIA Service is nothing more than a fancy way of saying a function that you can call on a remote server using the OData protocol. We will create a service with its own endpoint that appears to the client like a regular database table through OData, but in fact merges together the internal UserRegistrations table and another table we will create in our LightSwitch application to store our own customized user profile data (such as email address). You can think of it like creating an updateable view in SQL if you are familiar with that, except since LightSwitch doesnt support these directly, we will have to write some code to accomplish it.

NOTE: Take a backup of your LightSwitch project if you intend to try method 3 below, as the steps here will create unnecessary clutter in your project.

Create data table

Figure 4. Creating the UserProfile table

Start by creating a table in your project to hold the user profiles. Right-click the ApplicationData node in the Server folder in Solution Explorer and select Add New Table.. Create a table that looks like the one in Figure 4. When you create the UserName field, select Include In Unique Index in the field’s properties pane.

Create WCF RIA Service project

Add a new project to your Solution called UserProfileManager. Using the project type pane, browse to Visual C# and choose Class Library. Ensure that you target .NET Framework 4.0 when you create the project (there is a drop-down at the top of the Add New Project window where you can change this), otherwise your LightSwitch application won’t be able to access the WCF RIA Service. This will create a new C# class library project that will contain the WCF RIA Service. Delete the default source code file (Class1.cs).

Right-click the project and choose Add New Item… Select a Domain Service Class from the item window. Call it UserProfileService and click OK. The Add New Domain Service Class window will appear. Uncheck the Enable client access box and click OK. The domain service class will now be added to your project. This is where the source code for our WCF RIA Service goes.

Allow RCF RIA Service to access LightSwitch

We need to be able to access the LightSwitch .NET classes, so right-click the project again, choose Add Reference…, choose Browse from the left-hand pane, click the Browse… button and navigate to the folder on your system containing the Microsoft.LightSwitch assemblies. For a default install Visual Studio 2012 on 64-bit Windows, this can be found in C:\Program Files (x86)\Microsoft Visual Studio 11.0\Common7\IDE\LightSwitch\Client for example. Double-click the Microsoft.LightSwitch.dll file when you find it and choose OK in the browse dialog to add the reference.

At the namespace level in your source file (UserProfileService.cs), add the following using statements:

using Microsoft.LightSwitch;
using Microsoft.LightSwitch.Framework.Base;

Allow WCF RIA Service to access LightSwitch project data sources

In order to work with tables in your LightSwitch application, we must enable the WCF RIA service to access this data.

Right-click the WCF RIA service project and choose Add Existing Item… Browse to the folder in your solution containing the automatically generated source code for LightSwitch data sources; in the example project, this will be GameNetwork\GameNetwork\GameNetwork.Server\GeneratedArtifacts. Generally, it will be the folder GeneratedArtifacts inside the Server project folder. Click once on ApplicationDataObjectContext.cs and choose Add As Link from the Add drop-down. This causes the source code to be added to the WCF RIA Service but for the file not to be copied, so that when LightSwitch re-generates it when the available data changes, this is automatically updated in the WCF RIA Service project.

In your main source code file (UserProfileService.cs in this example), you need to add some references and namespaces. Right-click the project, choose Add Reference…, and from the Assemblies -> Framework section of the tree, select System.Configuration, System.Data.Entity and System.Web. In your source code, add the following lines at the namespace level:

using System.Data.EntityClient;              // for EntityConnectionStringBuilder
using System.Web.Configuration;              // for WebConfigurationManager
using LightSwitchApplication.Implementation; // for ApplicationData

• System.Data.EntityClient is needed to build the connection string to the LightSwitch project database
• System.Web.Configuration is needed to build the connection string to the LightSwitch server
• LightSwitchApplication.Implementation is needed to allow named access to data sources from your LightSwitch project in the WCF RIA Service source code. This namespace is defined in the ApplicationDataObjectContext.cs file we imported above.

Now we have all the boilerplate code out of the way, we can focus on actually creating the service itself.

Write code to implement the service

First we need to build a class which represents the view of the combined tables we want to show. In this case we are merging our own UserProfiles table from the LightSwitch project, and SecurityData.UserRegistrations which is the internal user database. The class looks like this:

public class UserRegistrationView
{
    [Key]
    public string UserName { get; set; }
    public string FullName { get; set; }
    public string Password { get; set; }
    public string Email { get; set; }
}

The UserName field has the [Key] attribute which marks it as a unique key (and index) field.

Next, we have to create a connection to the LightSwitch project. In the main class (UserProfileService), we create a property which does this for us (credit for this code goes to Michael Washington):

#region Database connection
private ApplicationData m_context;
public ApplicationData Context
{
    get
    {
        if (this.m_context == null)
        {
            string connString =
                System.Web.Configuration.WebConfigurationManager
                .ConnectionStrings["_IntrinsicData"].ConnectionString;
            EntityConnectionStringBuilder builder = new EntityConnectionStringBuilder();
            builder.Metadata =
                "res://*/ApplicationData.csdl|res://*/ApplicationData.ssdl|res://*/ApplicationData.msl";
            builder.Provider =
                "System.Data.SqlClient";
            builder.ProviderConnectionString = connString;
            this.m_context = new ApplicationData(builder.ConnectionString);
        }
        return this.m_context;
    }
}
#endregion

We create a public property called Context. This first time this is used, a connection string is built and used to connect to the LightSwitch database. On subsequent calls, the existing connection is used. LightSwitch uses the _IntrinsicData connection string to refer to the database holding the data tables you created in the LightSwitch project.

Inserting new users

The following code does the trick:

public void AddUserRegistration(UserRegistrationView NewUser)
{
    IDataWorkspace workspace = ApplicationProvider.Current.CreateDataWorkspace();

    Microsoft.LightSwitch.Security.UserRegistration user = workspace.SecurityData.UserRegistrations.AddNew();
    user.UserName = NewUser.UserName;
    user.FullName = NewUser.FullName;
    user.Password = NewUser.Password;
    workspace.SecurityData.SaveChanges();

    UserProfile profile = new UserProfile();
    profile.UserName = NewUser.UserName;
    profile.Email = NewUser.Email;
    this.Context.AddToUserProfiles(profile);
    this.Context.SaveChanges();
}

First to note is that LightSwitch will use reflection to find the method with the correct signature for inserting rows, so you don’t need to worry too much about the method name.

In the first line, we establish access to the LightSwitch database. We then write new rows to two tables; SecurityData.UserRegistration and our own UserProfiles table. In both cases, this is done by creating a new object of the row type (with SecurityData.UserRegistrations.AddNew() and new UserProfile()), populating them with the desired data and calling SecurityData.SaveChanges() to update the internal user database, and Context.SaveChanges() to update our own UserProfiles table. In this way, we pass in one UserRegistrationView via OData containing the user name, full name, password and email address; the user name, full name and password get written to the internal user database, and the user name and email address get added to our UserProfiles table in a single atomic step, as far as the client calling the OData service is concerned.

Reading existing user profiles

First the code:

[Query(IsDefault = true)]
public IEnumerable<UserRegistrationView> GetUsers()
{
    IDataWorkspace workspace = ApplicationProvider.Current.CreateDataWorkspace();
    IEnumerable<Microsoft.LightSwitch.Security.UserRegistration> users =
        workspace.SecurityData.UserRegistrations.GetQuery().Execute();

    return (from user in users
            select new UserRegistrationView
            {
                UserName = user.UserName,
                Password = user.Password,
                FullName = user.FullName,
                Email = (from profile in this.Context.UserProfiles
                    where profile.UserName == user.UserName
                    select profile.Email).FirstOrDefault()
            });
}

The attribute [Query(IsDefault = true)] tells LightSwitch that the function to follow is the default SQL SELECT-style query (what OData returns when you call the endpoint with HTTP GET). The function returns IEnumerable<UserRegistrationView> which is nothing more than a simple list of UserRegistrationView rows.

The first two lines of the function retrieve the full list of users from the internal user database (SecurityData). Note that this doesn’t scale very well so if you have many thousands or millions of users you will definitely need to re-write this. We now use LINQ to match the user names we just fetched from SecurityData.UserRegistrations with the UserName field in our UserProfiles table and return a mixture of data from both tables – UserName, Password and FullName from SecurityData.Registrations and Email from UserProfiles. When fetching the email field, the LINQ select returns an IEnumerable<string> list so we call FirstOrDefault() on this returned list to just get the first match (string) – there should only be one match anyway. If no match is found, a default value is placed in the Email field (blank in this case).

The complete finished source code for UserProfileService.cs looks like this:

namespace UserProfileManager
{
    using System;
    using System.Collections.Generic;
    using System.ComponentModel;
    using System.ComponentModel.DataAnnotations;
    using System.Linq;
    using System.ServiceModel.DomainServices.Hosting;
    using System.ServiceModel.DomainServices.Server;

    using Microsoft.LightSwitch;                 // for IDataWorkspace
    using Microsoft.LightSwitch.Framework.Base;  // for ApplicationProvider

    using System.Data.EntityClient;              // for EntityConnectionStringBuilder
    using System.Web.Configuration;              // for WebConfigurationManager
    using LightSwitchApplication.Implementation; // for ApplicationData

    public class UserRegistrationView
    {
        [Key]
        public string UserName { get; set; }
        public string FullName { get; set; }
        public string Password { get; set; }
        public string Email { get; set; }
    }

    // TODO: Create methods containing your application logic.
    // TODO: add the EnableClientAccessAttribute to this class to expose this DomainService to clients.
    public class UserProfileService : DomainService
    {
        #region Database connection
        private ApplicationData m_context;
        public ApplicationData Context
        {
            get
            {
                if (this.m_context == null)
                {
                    string connString =
                        System.Web.Configuration.WebConfigurationManager
                        .ConnectionStrings["_IntrinsicData"].ConnectionString;
                    EntityConnectionStringBuilder builder = new EntityConnectionStringBuilder();
                    builder.Metadata =
                        "res://*/ApplicationData.csdl|res://*/ApplicationData.ssdl|res://*/ApplicationData.msl";
                    builder.Provider =
                        "System.Data.SqlClient";
                    builder.ProviderConnectionString = connString;
                    this.m_context = new ApplicationData(builder.ConnectionString);
                }
                return this.m_context;
            }
        }
        #endregion

        [Query(IsDefault = true)]
        public IEnumerable<UserRegistrationView> GetUsers()
        {
            IDataWorkspace workspace = ApplicationProvider.Current.CreateDataWorkspace();
            IEnumerable<Microsoft.LightSwitch.Security.UserRegistration> users =
                workspace.SecurityData.UserRegistrations.GetQuery().Execute();

            return (from user in users
                    select new UserRegistrationView
                    {
                        UserName = user.UserName,
                        Password = user.Password,
                        FullName = user.FullName,
                        Email = (from profile in this.Context.UserProfiles
                         where profile.UserName == user.UserName
                         select profile.Email).FirstOrDefault()
                    });
        }

        public void AddUserRegistration(UserRegistrationView NewUser)
        {
            IDataWorkspace workspace = ApplicationProvider.Current.CreateDataWorkspace();

            Microsoft.LightSwitch.Security.UserRegistration user = workspace.SecurityData.UserRegistrations.AddNew();
            user.UserName = NewUser.UserName;
            user.FullName = NewUser.FullName;
            user.Password = NewUser.Password;
            workspace.SecurityData.SaveChanges();

            UserProfile profile = new UserProfile();
            profile.UserName = NewUser.UserName;
            profile.Email = NewUser.Email;
            this.Context.AddToUserProfiles(profile);
            this.Context.SaveChanges();
        }
    }
}

Allow LightSwitch project to access WCF RIA Service

In order to expose the service as an OData service endpoint, it needs to be published along with the LightSwitch project, so we need to import it as a data source.

First, rebuild the solution so that the WCF RIA Service project’s compiled files are visible to the LightSwitch project. You will see many warnings which you can simply ignore.

Once the re-build is complete, we configure the LightSwitch project to consume (access) the WCF RIA Service. Right-click the Server folder in Solution Explorer and choose Add Data Source… Select WCF RIA Service from the window that appears and click Next. Click Add Reference and in the Solution -> Projects tree section, choose the service (UserProfileManager) and click OK. After a few moments, the service should appear in the Available WCF RIA Service classes box. Select it and click Next.

On the next page, you will be prompted to select which data sources to import. Don’t be alarmed if you get an error at this point, as the list doesn’t always appear correctly first time; if this happens to you, just click Previous and Next and it should appear correctly. Check the Entities box to import everything (you should see UserRegistrationView with all its fields underneath if you expand the tree). Set the name of the data source to UserRegistrationService and click Finish. You will now see the UserRegistrationService appear in the Server folder in Solution Explorer, with the UserRegistrationViews table (view) underneath. The service will now be deployed whenever you publish the LightSwitch project.

Test the service

If everything has gone well, your solution should now look something like this (with the WCF RIA Service proxy table shown):

Figure 5. How the LightSwitch solution looks after adding the WCF RIA Service

You can now go ahead and publish the project, and try to create a user account using hurl or a tool of your choice. Something like Figure 6 will do the trick:

Figure 6. Example OData request to create a new user and user profile via the WCF RIA Service

Notice the URL endpoint /UserRegistrationService.svc/UserRegistrationViews/ and the JSON request body:

{UserName:"sometestuser",FullName:"Some Test User",Password:"SomeTestUser1234",Email:"testuser@testuser.com"}

If everything works well and the user is created, the request will return a HTTP/1.1 201 Created status code.

At this point, it is still only the administrator who can add accounts, because although any user can currently add rows to UserProfiles, only the administrator can write to SecurityData.UserRegistrations. In the next section, we restrict access to UserProfiles so that only a special public guest account can add new users, and show how to also allow this user to update SecurityData.UserRegistrations.

Set up guest user which can add new user accounts

To accomplish this, we need to:

1. Create a permission which we will call CanAddUsers that will be required to insert data via the WCF RIA Service
2. Create a user which we will call __userRegistrant. This will be a public guest user that has extremely limited access. They will be able to add user accounts via the WCF RIA Service and nothing else. We can safely include the username and password for this user into our client code without fear of it being misused to access or change other data in the database (unlike the admin account which gives total access).
3. Since users cannot be assigned permissions directly, but must instead be assigned to roles, create a role which will only be used by __userRegistrant and add the CanAddUsers permission to this role. We will call the role UserRegistrant.
4. Assign the role to __userRegistrant.
5. Write code which checks that the current (acting) user has the CanAddUsers permission when trying to insert data via the WCF RIA Service. Since __userRegistrant will be the only user with this permission, it will be the only user who can insert data (add new users).
6. Write code which temporarily allows __userRegistrant to write to the internal user database in SecurityData.UserRegistrations.

Start by right-clicking the GameNetwork project and choosing Properties. In the Access Control tab, add a new permission CanAddUsers with a display name and description of your choice (see Figure 7):

Figure 7. Adding the CanAddUsers permission to the LightSwitch project

Now re-publish the project so that the permission is added to the database.

For editing users and roles, we use the desktop client discussed earlier. Open the desktop client (GameNetwork in the Windows Start menu). Open the Administration -> Roles menu and create a new role called UserRegistrant. Add the CanAddUsers permission to the role (it will be shown in the Permissions pane using the display name you selected in the project properties when creating the permission). This sets up the role.

Now open the Administration -> Users menu and create a new user called __userRegistrant, with the password __userRegistrant, click the + icon in the Roles section and add the UserRegistrant role. Finally, click Save.

The Users and Roles tabs should now look as shown in Figures 8 and 9:

Figure 8. Adding the __userRegistrant user and assigning it to the UserRegistrant role

Figure 9. Configuring the UserRegistrant role

Enforce permissions

Now we just need to enforce the CanAddUsers permission. In your LightSwitch project, double-click the Server -> UserRegistrationService -> UserRegistrationViews table in Solution Explorer (the WCF RIA Service data source table). Select the arrow next to Write Code from the Designer toolbar and choose the UserRegistrationViews_CanInsert method in the Access Control Methods section. Visual Studio will create a new function in which you can assign a boolean value to the variable result, which signals to LightSwitch whether the acting user will be allowed to perform an insert operation or not. This method is called when a user tries to insert something in the UserRegistrationViews table, before the insert is performed. If result is set to false, the attempt to insert data will be denied.

The code is very simple and looks like this:

partial void UserRegistrationViews_CanInsert(ref bool result)
{
    result = Application.Current.User.HasPermission(Permissions.CanAddUsers);
}

The Application.Current.User.HasPermission function returns true if the current user has the specified permission, so this check will ensure only users with CanAddUsers (ie. only __userRegistrant) will be allowed to insert data.

We also need to allow __userRegistrant to write to SecurityData.UserRegistrations. Since only administrator accounts are allowed to do this, we use a technique called privilege elevation to temporarily give __userRegistrant administrator permissions for the duration of the call to the WCF RIA Service. Once the call ends, the elevated permissions are revoked.

Choose the Write Code drop-down again and this time select UserRegistrationViews_Inserting. This function is called after UserRegistrationViews_CanInsert, after the insert has been allowed, but before it actually takes place. Here we can temporarily give __userRegistrant administrator privileges as follows:

partial void UserRegistrationViews_Inserting(UserRegistrationView entity)
{
    Application.Current.User.AddPermissions(Permissions.SecurityAdministration);
}

Once again, as soon as the insert is completed, any permissions added here are revoked, so this is not a permanent change.

Now we should be in business! Re-publish your project and repeat the HTTP test shown in Figure 6. You should find that the account __userRegistrant with password __userRegistrant can add new accounts, but no other account including ones you create and admin itself can add users (if you want administrator accounts to be able to add users, add CanAddUsers to the Administrator role in the desktop client).

Limit who can read user profiles

At the moment, any logged in user can read the entire UserRegistrationViews table (except for passwords). This may sometimes be what you want, but not usually. We especially don’t people to be able to read other users’ email addresses or other personal, contact or payment details. Fortunately, it is easy to restrict UserRegistrationViews such that the logged in user can only access their own profile.

Simply add one line of code in the GetUsers() method of your WCF RIA Service and re-publish the project:

return (from user in users
        where user.UserName == ApplicationProvider.Current.User.Name
        select new UserRegistrationView
        {
            UserName = user.UserName,
            Password = user.Password,
            FullName = user.FullName,
            Email = (from profile in this.Context.UserProfiles
                where profile.UserName == user.UserName
                select profile.Email).FirstOrDefault()
        });

The where clause modifies the query result such that only rows matching the name of the currently logged in user are returned, in essence meaning that only one row – the current user’s row – will be shown.

If you now visit UserRegistrationService.svc/UserRegistrationViews/ in your browser as a logged in user, only that user’s profile data will be shown.

Summary

At this point we have:

• a service which allows us to insert and select data to/from both our UserProfiles table and SecurityData.UserRegistrations in one step
• restricted access such that only the guest user can add new accounts via this service
• restricted access such that users can only access their own profiles
• removed the need for the client to know the administrator password or have administrator privileges
• no unnecessary duplication of data between tables

and it all works pretty well. Using the WCF RIA Service spares us from the devastating problem of needing to supply the administrator password to users that method 1 suffers and solves the issue of atomicity of using UserProfiles and SecurityData.UserRegistrations at the same time, allowing us to store more data for each user than the default fields provided by SecurityData.UserRegistrations.

IMPORTANT: One security issue to note is that we have not secured the UserProfiles table from being read or written directly, so malicious users can still use this as a backdoor to steal everybody’s email addresses and whatever other data you store there, or modify it. This must be addressed on a production server.

There are a few downsides, however:

• Lots of boilerplate code
• Every time the fields in one of your tables change, you have to change the proxy class you defined in your WCF RIA project source code
• Data sources from WCF RIA Services cannot be edited in the LightSwitch Designer (except for a few properties)
• Ensuring everything is secure can be cumbersome

Wouldn’t it be nice if we could just do away with WCF RIA Services altogether, and automatically generate new users in SecurityData.UserRegistrations when a new row is added to UserProfiles? We are also yet to address the issue of automatically assigning new users to roles when they are created, so we need to look at that too.

3. Generating new users in the LightSwitch user database automatically when a new user profile is created

Cleaning up and setting up the guest user

Since this method is an entirely different solution to exactly the same problem, I recommend that you restore the project backup made at the end of part 1 so you have a clean slate before you proceed. You also need to follow these steps:

1. Use the desktop client to delete any test users you created.
2. Re-add the CanAddUsers permission in the LightSwitch project properties Access Control tab as it will have been removed when you restored the backup.
3. Delete the UserProfiles table. We will make a new one below.
4. Re-publish the project.
5. Use the desktop client to delete the (now blank) permission from the UserRegistrant role and re-add the new CanAddUsers permission to it.
6. Use your SQL server interface to drop the UserProfiles table.
7. If you want to tidy up the server, login via FTP and delete the UserRegistrationService.svc file which is left over from the old WCF RIA Service.

Leave the admin and __userRegistrant accounts and the UserRegistrant role intact as we will use the same solution for allowing new users to create accounts via the guest account as in part 2. If you skipped part 2, follow the instructions in the Set up a guest user which can add new user accounts section up to the part where you enter source code (it will be different for this solution).

In this solution, we are going to set up the user creation to work like this:

• Create a UserProfiles table which stores all of the user’s profile data
• When creating a user, we will write to UserProfiles directly (instead of calling the WCF RIA Service)
• When a new row is added to UserProfiles, automatically create a new user in SecurityData.UserRegistrations

Create data table

Right click the Server folder and choose Add Table. Create a table called UserProfiles with UserName, FullName, Password and Email fields as shown in figure 10:

Figure 10. The UserProfiles table

Inserting new users

As with the WCF RIA Service, we want to ensure that only __userRegistrant can add users to the table, so select Write Code from the toolbar and choose the UserProfiles_CanInsert method. Enter the following code to enforce the CanAddUsers permission on the table:

partial void UserProfiles_CanInsert(ref bool result)
{
    // Only allow people with the CanAddUsers permission to add new users
    result = Application.Current.User.HasPermission(Permissions.CanAddUsers);
}

Now we write the code to cause a new user to be auto-generated in SecurityData.UserRegistrations when a row is inserted into UserProfiles. Choose Write Code and select the UserProfiles_Inserting method. This method will run after UserProfiles_CanInsert but before the row is actually inserted. Here is the code:

partial void UserProfiles_Inserting(UserProfile entity)
{
    // Add new user to SecurityData database
    var NewUser = this.DataWorkspace.SecurityData.UserRegistrations.AddNew();

    NewUser.UserName = entity.UserName;
    NewUser.FullName = entity.FullName;
    NewUser.Password = entity.Password;

    // Save changes
    this.DataWorkspace.SecurityData.SaveChanges();
}

When this code executes, a new user will be added to SecurityData.Registrations using the same user name, full name and password supplied when we try to insert a row into UserProfiles.

We need administrator privileges to do this, so we need to elevate the permissions of __userRegistrant temporarily while SecurityData.UserRegistrations is updated. Choose Write Code and select the SaveChanges_Executing method, and add the privilege elevation code:

partial void SaveChanges_Executing()
{
    // Temporarily elevate user's permissions so a new user can be added
    // The privilege escalation is removed once the new user has been added
    // (when the save pipeline ends)
    Application.Current.User.AddPermissions(Permissions.SecurityAdministration);
}

Limiting who can read user profiles

In the WCF RIA Service, we modified the default query to only return the profile for the currently logged in user. This time, we can use the UserProfiles_Filter method to filter out unwanted query results in a similar fashion. Choose Write Code and select the UserProfiles_Filter method, and add the following code:

partial void UserProfiles_Filter(ref Expression<Func<UserProfile, bool>> filter)
{
    // Only allow user to see their own profile
    filter = e => e.UserName == Application.Current.User.Name;
}

What happens here is that each row of the query results is iterated over. The row is placed in e and then a filter is applied to e after the => operator. If the filter returns true, the row is retained in the query results, otherwise it is discarded. Here we compare the row’s username to that of the logged in user, and only return true if they match. The net effect is that all rows are discarded except the row (profile) of the current user.

At this point, we have replicated all the functionality of the WCF RIA Service, but without any extra projects, code or proxy tables/classes. If you re-publish the project you can test it out on the ApplicationData.svc/UserProfiles endpoint.

Assigning new users to a role

Use the desktop client to add a new role called Player. This will be the default role that new users are assigned to.

In your UserProfiles_Inserting code, add the following lines just before SaveChanges() is called:

// Assign user to Player role by default
var NewRoleAssignment = NewUser.RoleAssignments.AddNew();
NewRoleAssignment.Role = this.DataWorkspace.SecurityData.Roles_Single("Player");

This assigns the new user to the Player role before the changes are written to SecurityData. Don’t forget to re-publish the project to make the changes take effect.

Password storage

There is a problem with this solution which is that the FullName and Password data gets stored un-encrypted in the UserProfiles table as well as in SecurityData.UserRegistrations. We really don’t want this as it is a security risk. Fortunately a one-line addition to UserProfiles_Inserting will somewhat mitigate the problem. First, edit the UserProfiles table so that Password is no longer a required field (uncheck the Required box for it in the table designer),the add the following code to UserProfiles_Inserting after SecurityData.SaveChanges():

// Don't save password into UserProfiles
entity.Password = "";

The password will be saved into SecurityData.UserRegistrations when SecurityData.SaveChanges() is called, but after that we change the password field to an empty string, such that when it is written into UserProfiles when UserProfiles_Inserting ends, only a blank field will be stored, and no password.

The complete source code looks like this:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.LightSwitch;
using Microsoft.LightSwitch.Security.Server;
using System.Linq.Expressions;

namespace LightSwitchApplication
{
    public partial class ApplicationDataService
    {
        partial void UserProfiles_Filter(ref Expression<Func<UserProfile, bool>> filter)
        {
            // Only allow user to see their own profile
            filter = e => e.UserName == Application.Current.User.Name;
        }

        partial void UserProfiles_Inserting(UserProfile entity)
        {
            // Add new user to SecurityData database
            var NewUser = this.DataWorkspace.SecurityData.UserRegistrations.AddNew();

            NewUser.UserName = entity.UserName;
            NewUser.FullName = entity.FullName;
            NewUser.Password = entity.Password;

            // Assign user to Player role by default
            var NewRoleAssignment = NewUser.RoleAssignments.AddNew();
            NewRoleAssignment.Role = this.DataWorkspace.SecurityData.Roles_Single("Player");

            // Save changes
            this.DataWorkspace.SecurityData.SaveChanges();

            // Don't save password into UserProfiles
            entity.Password = "";
        }

        partial void UserProfiles_CanInsert(ref bool result)
        {
            // Only allow people with the CanAddUsers permission to add new users
            result = Application.Current.User.HasPermission(Permissions.CanAddUsers);
        }

        partial void SaveChanges_Executing()
        {
            // Temporarily elevate user's permissions so a new user can be added
            // The privilege escalation is removed once the new user has been added
            // (when the save pipeline ends)
            Application.Current.User.AddPermissions(Permissions.SecurityAdministration);
        }
    }
}

Summary

At this point we have:

• a coded UserProfiles table which automatically generates a user in SecurityData.Registrations when a new row is added
• restricted access such that only the guest user can add new accounts (insert rows) in UserProfiles
• restricted access such that users can only access their own profiles
• removed the need for the client to know the administrator password or have administrator privileges
• solved the issue of UserProfiles being used as a backdoor to get profile information that we had with the WCF RIA Service
• eliminated a vast amount of boilerplate code and complexity
• eliminated the need to update code when the fields in the UserProfiles table change
• the table can now be edited in the LightSwitch Designer, unlike with the WCF RIA Service
• simplified enforcing permissions security compared to the WCF RIA Service where both the tables in the LightSwitch project and the WCF RIA Service endpoint must be secured

So what’s the downside? In a nutshell, we have two unnecessary duplicated fields – FullName and Password – in UserProfiles. The reason for this is that when you insert a row into UserProfiles, LightSwitch of course expects a value for each field, so having the duplicate fields is unavoidable. As we showed above, the fields can be blanked out with code if desired, but it is still a waste of storage space in the database.

Conclusion

We have now engineered three different ways to add users:

• Use the administrator account directly. This is the simplest solution but is a security disaster since the administrator username and password must be embedded into your client software (game). It also does not allow the storing of additional user profile data.
• Use a WCF RIA Service. This provides the ability to merge two or more tables into an updateable view – allowing extra user profile information to be stored with no duplication of data, but involves a lot of technical coding and increases the maintenance and security burden on your application.
• Use a normal table and write to SecurityData.Registrations automatically when new rows are inserted. This is much simpler to maintain and secure than the WCF RIA Service and also allows extra user profile information to be stored, but leads to duplicated FullName and Password fields in our user profile table.

The first solution should be avoided except for development and debugging purposes. Of the last two options, there is no right or wrong solution to use; it just depends on how you prefer to engineer your application.

At this point, this may all seem like a lot of extra work compared to just whipping up some PHP scripts that read and write a database for you, however when we add more features to the project later, you will see how the work pays off.

In part 3 we shall look at creating a web-based user interface to let the user view and edit their profile, change their password etc. In part 4 we shall look at various ways to integrate the registration and login process to (game) clients written in C++. I hope you found the article useful, please leave your feedback below.

Until next time!

Useful Links

Is HTTP Basic Auth secure if done over HTTPS? @ StackOverflow – important reading if you plan to use LightSwitch Forms authentication with SSL

MSDN – OData By Example – a comprehensive guide to the various queries you can use with OData to select and modify data in your database

OData Operations at the official odata.org site – a low-level guide to the HTTP requests and responses for various OData operations

OData Services @ CodeProject – a guide to the OData protocol and how to create an OData service using WCF

Security Considerations for LightSwitch @ MSDN – basic information about Forms authentication, permissions, users, roles and using SSL

Using the SecurityData service in LightSwitch – details about all of the tables in the Microsoft.LightSwitch.SecurityData.svc endpoint

How to reference security entities in LightSwitch @ MSDN Blog – using a WCF RIA service to access the LightSwitch user database

How to Assign Users, Roles and Permissions to a LightSwitch HTML Mobile Client by Beth Massi – deploying a desktop client to manage users, roles and permissions via an administrative user interface

WCF RIA Services: Combining Two Tables, Add, Delete, Edit data using WCF RIA Services in LightSwitch (original article) – shows how to combine items from two tables in a WCF RIA service such that when you query and write data to the service, it appears as one table (relevant for method 2 of creating users above)

WCF RIA Services: Combining Two Tables, Add, Delete, Edit data using WCF RIA Services in LightSwitch (copy) – this is a copy of the above article with additional notes about compiling with Visual Studio 2012, and how to add methods to simulate SQL INSERT, UPDATE and DELETE statements on the merged table

Creating a WCF RIA Service for Visual Studio 2012 (Update 2 and higher) – contains updates for the instructions and code in the above two articles for Visual Studio 2012 Update 2

How to Elevate Permissions in Server Code by Ravi Eda @ MSDN Blog – temporarily elevating the permissions of one user for an operation (relevant for method 3 of creating users above)

Allowing Users To Self Register In Your LightSwitch Website – this is an alternative approach where a web form is created to allow users to register their accounts via the web browser instead of in-game

LightSwitch Authentication and Authorization @ MSDN – shows how to write code in your LightSwitch application in Visual Studio to enforce permissions

Advertisement