I mentioned in my previous post, the token should capture additional information for the token such as whether it's a bearer token or not. Today I want to discuss the other attributes of the token such as scope and state that may also need to be applied during policy resolution and a way to look them up. We mentioned that the scope and the state are not used for validating the client so they need not be part of what makes the token. However, applying scope to users protected resources is also relevant to security policy implementation. By default the scope may not be specified or set and the presence of a valid token could imply access to all of user's protected resources. In such cases, there is not scope resolution required. In the cases where scopes are specified and enforced, we need a mechanism that facilitates APIs to be called due to the presence of a token but fails when the required resources are outside of the scope of the token. we will discuss the scope storage/lookup and scope enforcement separately.
The security mechanism we discussed earlier is a layer above the API because it filters the calls to the API without the API knowing about it. During the calls, the user's resources are protected with row level security. This is done with labels on users protected resources. Labels correspond directly to the scope.
Scope storage and lookup is facilitated by token table. This doesn't mean scope labels cannot be included in the token itself.

In my OAuth implementation, I talked about token provisioning for user and client by the OAuth server. I also talked about varying the token over time for calls by the same client on behalf of the same user. I would now like to describe the OAuth bearer token and its provisioning in my proposed OAuth implementation.
Bearer tokens are not the 'default' tokens. These are special tokens issued so that any source can access any protected resource. It is merely used to encapsulate user authorization. As long as they are transmitted securely and are tamper proof and with some other considerations discussed earlier, they can supposedly be used by any client to request user's protected resources. This is merely a step to make it easy for user authorizations to be passed around for more reachability. In all the usages of the bearer token, the client must authenticate the server before sending the token over. The tokens are supposed to be handled and passed around with care since presumably anybody can use it. I consider the bearer tokens as a simple relaxation of client validation and using the user authorization only to access protected resources.
Therefore in my proposed OAuth provider implementation, I add the check to bypass client validation when the token is a bearer token.
This implies that we need to add additional information to our initial userId and ClientId information that we encrypt to call it a bearer token. The way we store this information in the token is entirely up to us. We could choose to set a bit or a digit to indicate a bearer token, or add a column to our token table among others such that we can lookup the token to see if its a bearer token. Based on my previous post, I want to add the bearer token information to the original string before I encrypt it. This way I have that information handy when I decrypt it.
Note that I do not consider other token attributes such as redirect uri, scope, state etc in the original information because the only thing I'm validating is the client. Should the client validation be bypassed, I would like that information in the decrypted string.
Next let us consider how to specify the bearer token in the original string before encryption. One way to use this could be to use a special delimiter on the highest place order in the clientId.

Another way would be to specify even number for the rotation of the userId and clientId

Yet another way could be to add a bearer token as a separate byte between user Id and client Id.
Yet another way could be to zero out the client Id to say that anyone can use it or special case the client Id.

Thus there are many ways in which the bearer token can be specified in the original string.

No matter how we choose to capture the bearer information, we have to add a check to the client validation to be bypassed when its a bearer token.

In this post, I want to talk about Application Object in Microsoft Excel, that lets you work with Excel sheets programmatically.
You could write code such as follows:
Application.Windows("book1.xls").Activate
or
Set x1 = CreateObject("Excel.sheet")
x1.Application.Workbooks.Open "newbook.xls"

You also have a PivotTable and PivotChart object in the Excel Object Model.
They facilitate pivot transformation of data. You could use the PivotTableWizard to generate the PivotTable.
ActiveSheet.PivotTableWizard(xlDatabase, Range("A1:C100")

In the previous post, we discussed generating different tokens for the same user and client over time. We could do this based on a variant we add to the userId and clientId before encryption.
This was made possible with a hhmmss integer that we append to the UserId and ClientId.
This had the benefit that we used fixed length string to encyrpt and decrypt. However all of the original string may be known to the user. So if we want to keep some part of the original string unknown, we could add a magic number.
All of this could be avoided if we used an offset to rotate the userId+clientId string based on say hhmmss. The logic to translate the hhmmss to an offset thats within the bounds of 0 to the length of the original fixed length string is entirely up to the server.
We also mentioned that with the OAuth server, we preferred to keep a token table that gets populated for all OAuth token grants. At the same time, we said that the APIs that validate the tokens need not rely on the token table and can directly decrypt the tokens to identify the userId and the clientId. The table is only for auditing purposes at this point.
The token validation does not need to happen within the API implementation although APIs could choose to do so. That validation could be done with ActionFilterAttributes we discussed earlier. or even via HTTP handlers. The URI and query string could still be passed to the API implementation.
The id conversion may need to occur if the APIs would like to get the userId and clientId from the token itself so that the API resources do not require a user or client in the resource qualifiers. This is because the id is integer based. If the earlier implementation was based on GUIDs, the id and GUID for the same user or client may need to be looked up.
APIs are not expected to do away with user or client id since the token is a security artifact and not a functional artifact. To the user it could be rendundant or additional task to provide user and client information alongwith a token. To the API implementation, irrespective of the caller, the userId and clientId could be parameters so that callers can look up the information when the parameter values change.
That said, most of the resources are based on user profiles and has nothing to do with client profiles. If the users are already being aliased so that they don't have to enter their userId, then the resource qualifiers for the API implementations can certainly choose to not require userId and clientId. This will benefit the users who call the API.
I mentioned earlier that on mobile devices where text entry and input is generally difficult at this time, it is better to require less when the users have to specify the API directly.
Lastly, the database schema may need to be modified if ID parameter is not already what is currently being proposed. 

In today's post I want to continue the discussion on generating tokens by encryption. We mentioned encrypting the UserId and ClientId and circulate the base64 encoded string as tokens. We rely on the strength of the encryption to pass the information around. However we missed mentioning a magic number to add to the UserId and ClientId before we encrypt. This is important for several reasons. One because we want to be able to vary the tokens for the same UserId and ClientId. And we want to make it hard for a hacker to guess how we vary it. One way to do this for example would be to use the current time such as in hhmmss format along with an undisclosed constant increment.
Another way to generate the tokens without adding another number to the input is rotate the userId and ClientId. So the string constituting the UserId and the ClientId will be split and the two substrings exchanged in their positions. After decryption, we can swap it again to get the original string. Since the client Ids are not expected to grow as large as the integer max, we can use the leftmost padding of the client Ids as the delimiter.
Another way to do this would be to use the Luhn's algorithm that is used for generating credit card numbers. Here every alternate number in the original sequence is doubled and then their sum is multiplied by 9 and taken with modulus 10. This gives the check number to add to the end.
No matter how we vary the tokens from being generated, we can create a base64 encoded token.
The OAuth spec does not restrict the tokens to be a hash. The tokens could be anything. If they store information about the user and the client for validations during API calls, that is not restricted. The OAuth goes to mention such possibilities in the spec.
When considering the tradeoffs between a hash and an encrypted content for a token, the main caveat is whether the tokens are interpretable. If a malicious user can decrypt the tokens, then its a severe security vulnerability. Tokens can then be faked and the OAuth server will compromise the protected resources. Since the token is the primary means to gain access to the protected resources of the user, there's no telling when the token will be faked and misused and by whom. Tokens therefore need to be as tamper proof as the user's password.
If the tokens were not encrypted but merely a hash that has no significance in terms of contents, and the relevance is private to the server, then we are relying on  a simpler security model. This means we don't have to keep upgrading the token generating technologies as more and more ways are discovered to break them. This however adds a persistence to the OAuth server such that these hashes can be tied back to the user and the client.
Even though I have been advocating a token database, and a hash is a convenient simpler security model, I firmly believe that we need both a token table and tokens that have user and client information in place with them in a way that only the server can generate them.
Simpler security model does not buy us the improvement in terms of scale and performance in the API implementations where token validation is desirable. All API calls should have token validation. Some could have user validation but all should have client validation.

In today's post I want to cover encryption and decryption of data we have talked about in the previous posts. For example, we wanted to generate an OAuth token based on information about the user and the client. So we wanted to do something like

Encrypt(UserID  + ClientID) = Token 

where UserID is a large integer and and Client ID is a regular integer. The original text can therefore be 16 and 8 characters in length which gives us 24 characters. We used fixed length for both UserID and ClientID and pad left. If we want to keep the size of the encrypted text to be the same as the original string, we could choose AES stream encryption. If we were to use stronger algorithms the size would likely bloat. And when you hex or base64 encode, the text could double in size.
In the database, there is an easy way to encrypt and decrypt the string using the ENCRYPTBYKEY (key_GUID)

CREATE CERTIFICATE Certificate01
   ENCRYPTION BY PASSWORD = 'adasdefjasdsad7fafd98s0'
   WITH SUBJECT = 'OAuth tokens for user and client',
   EXPIRY_DATE = '20201212';
GO

OPEN SYMMETRIC KEY Token_Key_01
   DECRYPTION BY CERTIFICATE Certificate01;

UPDATE Token
SET Token = EncryptByKey(Key_GUID('Token_Key_01'),
             Convert(Varchar(16), UserID) + Convert(Varchar(8), ClientID));
GO

I want to mention the choice of the encryption algorithms. This could be AES, DES, 3DES, SHA1 etc. The encryption could be both block based and stream based.
For our purposes, we want to keep the size of the tokens to be a reasonable length. Since access tokens are likely passed around in the URI as query parameter, this should not be very long.
Moreover, the decryption should also work for quick and reasonable check on the tokens.
This way the storage of the tokens can be separated from the validation of user and client against a token. The storage of the token comes useful for auditing etc. The data is always pushed by the token granting endpoint/ There is no pull required from the database if the API implementations can merely decrypt the token.

In today's post I want to cover encryption and decryption of data we have talked about in the previous posts. For example, we wanted to generate a token based on information about the user and the client. So we wanted to do something like Encrypt(UserID + ClientID) = Hash where UserID is a large integer and and Client ID is a regular integer. The original text can therefore be 16 and 8 hexadecimal characters in length which gives us 24 characters. If we want to keep the size of the encrypted text to be the same as the original string, we could choose AES stream encryption. If we were to use stronger algorithms the size would likely bloat.