Scrubbing UserId in Windows Azure Mobile Services

First, many thanks to Chris Risner for the assistance on this solution!   Chris is part of the corp DPE team and has does an extensive amount of work with Windows Azure Mobile Services (WAMS) – including this session at //build, which was a great resource for getting started. If you go through the demo of getting started with WAMS building a TodoList, the idea is that the data in the todo list is locked down to each user.   One of the nice things about WAMS is that it’s easy to enforce this via server side javascript … for example, to ensure only the current user’s rows are returned, the following read script can be used that enforces the rows returned only belong to the current user: function read(query, user, request) { query.where({ userId: user.userId }); request.execute(); } If we crack open the database, we’ll see that the userId is an identifier, like the below for a Microsoft Account: MicrosoftAccount:0123456789abcd When the app connects to WAMS, the data returned includes the userId … for example, if we look at the JSON in fiddler: The app never displays this information, and it is requested over SSL, but it’s an important consideration and here’s why.   What if we have semi-public data?   In the next version of Dark Skies, I allow users to pin favorite spots on the map.  The user has the option to make those points public or keep them private … for example, maybe they pin a great location for stargazing and want to share it with the world: … Or, maybe the user pins their home locations or a private farm they have permission to use, where it might be inappropriate to show publically. Now here comes the issue:  if a location is shared publically, that userId is included in the JSON results.  Let’s say I launch the app and see 10 public pins.  If I view the JSON in fiddler, I’ll see the userId for each one of those public pins – for example: Now, the userId contains no personally identifiable information.   Is this a big deal, then?   It’s not like it is the user’s name or address, and it would only be included in spots the user is sharing publically anyway. But, if a hacker ever finds a way to map a userId back to a specific person, this is a security issue.  Even my app doesn’t know who the users really are, it just knows the identifier.  Still, I think from a best practice/threat modeling perspective, if we can scrub that data, we should.  Note: this issue doesn’t exist with the todo list example, because the user only, and ever, sees their own data. Ideally, what we’d like to do is return the userId if it’s the current user’s userId.  If the point belongs to another user, we should scrub that from the result set.   To do this via a read script in WAMS, we could do something like: function read(query, user, request) { request.execute( { success: function(results) { //scrub user token if (results.length > 0) { for (var i=0; i< results.length; i++) { if (results[i].UserId != user.userId) { results[i].UserId = 'scrubbeduser'; } } } request.respond(); } }); } .csharpcode, .csharpcode pre { font-size: small; color: black; font-family: consolas, "Courier New", courier, monospace; background-color: #ffffff; /*white-space: pre;*/ } .csharpcode pre { margin: 0em; } .csharpcode .rem { color: #008000; } .csharpcode .kwrd { color: #0000ff; } .csharpcode .str { color: #006080; } .csharpcode .op { color: #0000c0; } .csharpcode .preproc { color: #cc6633; } .csharpcode .asp { background-color: #ffff00; } .csharpcode .html { color: #800000; } .csharpcode .attr { color: #ff0000; } .csharpcode .alt { background-color: #f4f4f4; width: 100%; margin: 0em; } .csharpcode .lnum { color: #606060; }If we look at the results in fiddler, we’ll see that I’ll get my userId for any of my points, but the userId is scrubbed if it’s another user’s points that are shared publically: [Note: these locations are random spots on the map for testing.] Doing this is a good practice.  The database of course has the correct info, but the data for public points is guaranteed to be anonymous should a vulnerability ever present itself.   The downside of this approach is the extra overhead as we’re iterating the results – but, this is fairly minor given the relatively small amounts of data. Technical point:  In my database and classes, I use Pascal case (as a matter of preference), as you can see in the above fiddler captures, such as UserId.   In the todo example and in the javascript variables, objects are conventionally camel case.   So, if you’re using any code here, just be aware that case does matter in situations like this: if (results[i].UserId != user.userId) // watch casing! .csharpcode, .csharpcode pre { font-size: small; color: black; font-family: consolas, "Courier New", courier, monospace; background-color: #ffffff; /*white-space: pre;*/ } .csharpcode pre { margin: 0em; } .csharpcode .rem { color: #008000; } .csharpcode .kwrd { color: #0000ff; } .csharpcode .str { color: #006080; } .csharpcode .op { color: #0000c0; } .csharpcode .preproc { color: #cc6633; } .csharpcode .asp { background-color: #ffff00; } .csharpcode .html { color: #800000; } .csharpcode .attr { color: #ff0000; } .csharpcode .alt { background-color: #f4f4f4; width: 100%; margin: 0em; } .csharpcode .lnum { color: #606060; } Be sure they match your convention.   Since Pascal case is the standard for properties in C#, and camel case is the standard in javascript, properties in .NET can be decorated with the datamember attribute to make them consistent in both locations – something I, just as a matter of preference, prefer not to do: [DataMember(Name = "userId")] public string UserId { get; set; }

Windows Azure Trust Center

The Windows Azure team recently posted about the Windows Azure Trust Center.   One of the most frequent conversations that comes up when discussing moving applications to the cloud revolves around security and compliance, and it’s also one of the most challenging conversations to have.  What makes it particularly challenging is the fact that the responsibility of compliance is typically shared between the hardware, platform, and software. The site has a few sections that in particular drill down into security, privacy, and compliance related information.  Definitely good information to refer to when evaluating a move into the cloud!

SQL Azure Logins

SQL Azure currently has fairly limited management capabilities.  When you create a database, you receive an administrator account that is tied to your login (you can change the SQL Azure password, though).  Because there is no GUI for user management, there’s a temptation to use this account in all your applications, but I highly recommend you create users for your application that have limited access.  If you limit access to only stored procedures, you need to specify execute permissions.  Assuming you want your connection to have execute permissions on all stored procedures, I recommend a new role that has execute permissions.  That way, you can simply add users to this role and as you add more stored procedures, it simply works.  To create this role, you can do something like this: CREATE ROLE db_executor GRANT EXECUTE TO db_executor Now in the master database (currently, you need to do this in a separate connection – just saying ‘use master’ won’t work) you can create your login for the database: CREATE LOGIN MyUserName WITH PASSWORD = 'Password'; In your application database, you need to create a user – in this case, we’ll just create a user with the same name as the login: CREATE USER MyUserName FOR LOGIN MyUserName; Next, we’ll specify the appropriate roles.  Depending on your needs, you may need only datareader.  I recommend db_owner only if necessary. -- read/write/execute permissions EXEC sp_addrolemember N'db_datareader', N'MyUserName' EXEC sp_addrolemember N'db_datawriter', N'MyUserName' EXEC sp_addrolemember N'db_executor', N'MyUserName' -- only if you need dbo access: EXEC sp_addrolemember N'db_owner', N'MyUserName' You can continue to customize as necessary, as long as you are familiar with the appropriate T-SQL. 

My Apps

Dark Skies Astrophotography Journal Vol 1 Explore The Moon
Mars Explorer Moons of Jupiter Messier Object Explorer
Brew Finder Earthquake Explorer Venus Explorer  

My Worldmap

Month List