HEJP's Blog

After some contemplation I’ve created what I hope is a suitable solution. A few tables have been added, the ‘user group’ table and the ‘user group member’ table. The ‘follower’ table has been swallowed up by the ‘user group’ table. When a user is created two default user groups are create the ‘Followers’ group and the ‘Followees’ group.

The ‘Followers’ and ‘Followees’ groups have the special property that they can’t be renamed or deleted, I’d imagine that people would probably rename their followers to bellends or something. The ‘Followers’ group also has the special property that anyone can add themselves to it, since anyone can follower anybody if they wish.

To automatically create the ‘Followers’ and ‘Followees’ groups I’ve added a trigger to the ‘user’ table that inserts the groups when a new user is insert. I’ve actually used numerous triggers mainly for updating the modified field of the rows when updated. Detailed information about triggers can be found here. Here is the trigger to insert the ‘Followers’ and ‘Followees’ groups:

	DELIMITER $$
	
	CREATE TRIGGER `INSERT_ADD_FOLLOWERS` AFTER INSERT
	ON `USER` FOR EACH ROW
	BEGIN
	INSERT INTO `USER_GROUP`(`user_id`, `name`, `type`) VALUES (NEW.`user_id`, 'Followers', 1);
	INSERT INTO `USER_GROUP`(`user_id`, `name`, `type`) VALUES (NEW.`user_id`, 'Followees', 2);
	END$$

	DELIMITER ;

The new solution amends the problems we were having earlier and adds a new feature ‘groups’. The user can now create a group and grant them access permissions with ease. Also they can remove a single user from the group without affecting the other members.

Authorisation

Since I wish for it to be possible for others to use the Scriblet users data we need a way to allow clients, such as website or apps, access to a users data once the user has allowed the client access. It is not preferrable for each and every client to know the user’s credientals (username and password). The current method used for authorising access to private resources is OAuth 2.0, used by Facebook, Twitter and Google. The idea behind OAuth 2.0 is to essentially ask the user to authorise a client then hand out access tokens that the client can use to access the user’s resources.

OAuth 2.0 Flow

The OAuth 2.0 flow is described in this video here. Here is a summary:

1. Developer registers a client id, secret and redirect url for Sriblet.
2. The user authorises the client.
3. Scriblet sends an authorisation code, which is short lived, to the redirect url.
4. The client then returns the authorisation code and the client secret.
5. Scriblet returns an access token and a refresh token.
6. The client then begins to access the user’s resources using the access token; requesting new ones when needed.

This is great the user can use the client and access their data as well as remove access whenever they wish. However, this assumes a particular type of client a web app, since the authorisation code is sent to the reditect url. What if the client is an Android app with no web interface. It might be easiest to allow clients that can securely store the client secret to request an access token using their id and secret.

The Database Now

A few more tables have been added to implement the features and additions described above. The database is becoming rather large and I’m thinking of introducing a commenting system and some crude analytics.