Storing data for users

I am working on a project connecting to a MySQL database. I want the users to be allowed to only access records that are connected to them via SSN. To do this, I would need to store each user’s SSN when they register, preferrably as a lookup key.

I know how to force the user to provide an SSN when they call the user/register method, but how can I store it?

What about calling a stored procedure and then limiting a role to stored procs.

See Sql wiki stored procs

So just make a get stored proc gUserSsn where username is a parameter that pulls the ssn from your lookup table and restricts your data

Same for add cUserSsn which creates a new ssn user relationship in your lookup table

The use the scripts tab to fire the stored procedures during registration
Or maybe use the scripts tab for everything.

You might try scripts tab then process events/users/register

You could also call a custom stored procedure that gets fired when they register adding the username/ssn to a lookup table and then a get stored proc in you api that pulls the snn out of the lookup table so you can restrict data on further api calls

Limiting the role to stored procedures sounds like it kinda defeats the purpose of using dreamfactory. The main reason for using it is to get the REST API.

So far I’ve tried to create a table, mapping to SSN, and populating the table on I managed to write the SSN to the table, but not the

Another thing that I tried, which would make things a lot simpler if I got it to work, was to create a lookup key for the user on I tried doing it according to this, but it didn’t work.

Both approaches would work if I could get/set lookup keys for users via scripting, but I still haven’t found a way to do that.

I figured out a way to do this. I added a script to, and stored the SSN using user/custom, like so:

var ssn = event.request.payload.ssn;

var emailString = “email%3D’” + + “’”;
var pathString = “system/user?fields=id&filter=” + emailString;
var result = platform.api.get(pathString, {});

if (result.content.resource && result.content.resource.length === 1) {
var user_id = result.content.resource[0].id;
var user_data = {
“resource”: [
“user_id”: user_id,
“name”: “ssn”,
“value”: ssn
};"user/custom", user_data);


And then I can retrieve this value for the logged-in user by making a GET request to ‘user/custom’


Hey @Gudmundur_Stefansson,

Thanks for posting your answer and updating the thread! Glad to hear that you figured it out.


I figured out a better way to do this.

var ssn = event.request.payload.ssn;
var user_id =;

platform.api.patch("system/user?related%3Duser_lookup_by_user_id", {
    "resource": [
            "id": user_id,
            "user_lookup_by_user_id": [
                    "name": "ssn",
                    "value": ssn,
                    "private": false,
                    "description": "The SSN of the user."

The above code sets a lookup key for the newly registered user.

1 Like

This is great @Gudmundur_Stefansson.


1 Like