Create Script Templates

Describes custom database action script templates for user creation.

The Create script implements the defined function when a user is created. We recommend naming this function create.

This script is optional. If it’s enabled, when a user signs up through Universal Login or is created through the Auth0 Dashboard or Auth0 Management API, Auth0 will run the script to create a corresponding user record in the external database.

When a user is created in Auth0, Auth0 calls a series of scripts:

1. Get User: Verifies that the user does not already exist in Auth0 or the external database.
2. Create: Creates the user in the external database.
3. Login: Verifies that the user was created successfully.

Create function

The create function should:

• Send the user’s profile data to the external database’s API.
• Return an error if the user creation operation failed.

Definition

The create function accept two parameters and returns a callback function:

create(user, callback): function

Parameter	Description
user	Object. Contains user profile data sourced from the user creation process.
callback	Function. Used to pass error data through the pipeline.

Example

This is a pseudo-JavaScript example of how you could implement the create function. For language-specific examples, read Language-specific script examples.

function create(user, callback) {
  // Send user profile data to external database API
  let hashedPassword = hash(user.password);

  let options = {
    url: "https://example.com/api/create",
    body: {
      email: user.email,
      username: user.username,
      password: hashedPassword
    }
  };

  send(options, err => {
    // Return error in callback if user already exists
    if (err && err.id === "USER_ALREADY_EXISTS") {
      return callback(new ValidationError("user_exists", "My custom error message."));
    } else if (err) {
      // Return error in callback if error occurred
      return callback(new Error("My custom error message."));
    }

    // Return `null` value in callback if user creation operation succeeded
    return callback(null);
  });
}

Encryption

Avoid logging, storing, or transporting the password credential anywhere in its unencrypted form.

Encrypt the password value using a cryptographic hash encryption library such as bcrypt to prevent any potential data leak.

Example

bcrypt.hash(password, 10, function (err, hash) {
    if (err) {
        return callback(err);
    } else {
        // Return hashed password
    }
});

Callback function

The callback function accepts one parameter and returns a function.

Definition

callback(error): function

Parameter	Type	Required	Description
error	Object	Required	Contains error data.

Return a success

If the user creation operation succeeded, return the callback function, and pass a null value as the error parameter.

Example

return callback(null);

Return an error

If an error occurs, return the callback function, and pass relevant error information to the the error parameter.

ValidationError type object

The ValidationError custom error type object allows you to pass data that will be displayed in your Tenant Logs.

Constructor

The ValidationError constructor accepts up to two parameters:

new ValidationError(errorCode[, message]): ValidationError

Parameter	Description
errorCode	(Required) String. Specifies the type of error.
message	(Optional) String. Contains information about the error.

Return error that user already exists

If you return an error with the value of user_exists for the errorCode parameter, Auth0 will record an fs tenant log event.

Example

return callback(new ValidationError("user_exists", "My custom error message."));

Tenant Log Event Field	Value
Code	fs
Event	Failed Signup
Description	My custom error message.

User object parameter

The user object parameter contains a predefined set of properties sourced from the user creation process:

Property	Description
client_id	The Auth0 application’s client ID if the user signed up through Universal Login, or the API key if the user was created through the Auth0 Dashboard or Management API.
tenant	The Auth0 tenant name.
email	The user’s email address.
password	The user’s password in plain text.
username	The user’s username. Required only if the custom database connection has the Requires Username setting enabled.
connection	The Auth0 connection name.
user_metadata	Contains the properties of the user_metadata object on the user’s Auth0 profile, if the object exists.
app_metadata	Contains the properties of the app_metadata object on the user’s Auth0 profile, if the object exists.

Username property

If your custom database connection has the Requires Username setting enabled, then the Login and Get User scripts must support the username property, so you should store it in your external database.

User and app metadata

The user_metadata and app_metadata properties do not need to be stored in your external database. Auth0 automatically stores these values as part of the user profile record created internally.

Custom sign up fields

If you create and use custom fields during the sign up process, they will be included in the user object.

Example

{
    client_id: "8tkMo6n1QkKOazqPcSQd8wU7LzXYibgK",
    tenant: "{yourAuth0Tenant}",
    email: "[e-mail]",
    password: "mySuperSecretPassword123",
    username: "username456",
    user_metadata: {
        "language": "en"
    },
    app_metadata: {
        "plan": "full"
    }
}

Language-specific script examples

Auth0 provides sample scripts for use with the following languages/technologies:

• JavaScript
• ASP.NET Membership Provider (MVC3 - Universal Providers)
• ASP.NET Membership Provider (MVC4 - Simple Membership)
• MongoDB
• MySQL
• PostgreSQL
• SQL Server
• Windows Azure SQL Database
• Request with Basic Auth

JavaScript

function create(user, callback) {
  // This script should create a user entry in your existing database. It will
  // be executed when a user attempts to sign up, or when a user is created
  // through the Auth0 Dashboard or Management API.
  // When this script has finished executing, the Login script will be
  // executed immediately afterwards, to verify that the user was created
  // successfully.
  //
  // The user object will always contain the following properties:
  // * email: the user's email
  // * password: the password entered by the user, in plain text
  // * tenant: the name of this Auth0 account
  // * client_id: the client ID of the application where the user signed up, or
  //              API key if created through the Management API or Auth0 Dashboard
  // * connection: the name of this database connection
  //
  // There are three ways this script can finish:
  // 1. A user was successfully created
  //     callback(null);
  // 2. This user already exists in your database
  //     callback(new ValidationError("user_exists", "my error message"));
  // 3. Something went wrong while trying to reach your database
  //     callback(new Error("my error message"));
  const msg = 'Please implement the Create script for this database connection ' +
    'at https://manage.auth0.com/#/connections/database';
  return callback(new Error(msg));
}

ASP.NET Membership Provider (MVC3 - Universal Providers)

function create(user, callback) {
  const crypto = require('crypto');
  const sqlserver = require('tedious@1.11.0');
  const Connection = sqlserver.Connection;
  const Request = sqlserver.Request;
  const TYPES = sqlserver.TYPES;
  const connection = new Connection({
    userName: 'the username',
    password: 'the password',
    server: 'the server',
    options: {
      database: 'the db name',
      encrypt: true,
      // Required to retrieve userId needed for Membership entity creation
      rowCollectionOnRequestCompletion: true
    }
  });
  const applicationId = 'your-application-id-goes-here';
  /**
   * hashPassword
   *
   * This function creates a hashed version of the password to store in the database.
   *
   * @password  {[string]}      the password entered by the user
   * @return    {[string]}      the hashed password
   */
  function hashPassword(password, salt) {
    // the default implementation uses HMACSHA256 and since Key length is 64
    // and default salt is 16 bytes, Membership will fill the buffer repeating the salt
    const key = Buffer.concat([salt, salt, salt, salt]);
    const hmac = crypto.createHmac('sha256', key);
    hmac.update(Buffer.from(password, 'ucs2'));
    return hmac.digest('base64');
  }
  connection.on('debug', function(text) {
    // if you have connection issues, uncomment this to get more detailed info
    // console.log(text);
  }).on('errorMessage', function(text) {
    // this will show any errors when connecting to the SQL database or with the SQL statements
    console.log(JSON.stringify(text));
  });
  connection.on('connect', function(err) {
    if (err) {
      return callback(err);
    }
    createMembershipUser(user, function(err, user) {
      if (err) return callback(err); // this will return a 500
      if (!user) return callback(); // this will return a 401
      callback(null, user);
    });
  });
  function createMembershipUser(user, callback) {
    const userData = {
      UserName: user.email,
      ApplicationId: applicationId
    };
    const createUser =
      'INSERT INTO Users (UserName, LastActivityDate, ApplicationId, UserId, IsAnonymous) ' +
      'OUTPUT Inserted.UserId ' +
      'VALUES (@UserName, GETDATE(), @ApplicationId, NEWID(), \'false\')';
    const createUserQuery = new Request(createUser, function(err, rowCount, rows) {
      if (err) return callback(err);
      // No records added
      if (rowCount === 0) return callback(null);
      const userId = rows[0][0].value;
      const salt = crypto.randomBytes(16);
      const membershipData = {
        ApplicationId: applicationId,
        Email: user.email,
        Password: hashPassword(user.password, salt),
        PasswordSalt: salt.toString('base64'),
        UserId: userId
      };
      const createMembership =
        'INSERT INTO Memberships (ApplicationId, UserId, Password, PasswordFormat, ' +
        'PasswordSalt, Email, isApproved, isLockedOut, CreateDate, LastLoginDate, ' +
        'LastPasswordChangedDate, LastLockoutDate, FailedPasswordAttemptCount, ' +
        'FailedPasswordAttemptWindowStart, FailedPasswordAnswerAttemptCount, ' +
        'FailedPasswordAnswerAttemptWindowsStart) ' +
        'VALUES ' +
        '(@ApplicationId, @UserId, @Password, 1, @PasswordSalt, ' +
        '@Email, \'false\', \'false\', GETDATE(), GETDATE(), GETDATE(), GETDATE(), 0, 0, 0, 0)';
      const createMembershipQuery = new Request(createMembership, function(err, rowCount) {
        if (err) return callback(err);
        if (rowCount === 0) return callback(null);
        callback(null, rowCount > 0);
      });
      createMembershipQuery.addParameter('ApplicationId', TYPES.VarChar, membershipData.ApplicationId);
      createMembershipQuery.addParameter('Email', TYPES.VarChar, membershipData.Email);
      createMembershipQuery.addParameter('Password', TYPES.VarChar, membershipData.Password);
      createMembershipQuery.addParameter('PasswordSalt', TYPES.VarChar, membershipData.PasswordSalt);
      createMembershipQuery.addParameter('UserId', TYPES.VarChar, membershipData.UserId);
      connection.execSql(createMembershipQuery);
    });
    createUserQuery.addParameter('UserName', TYPES.VarChar, userData.UserName);
    createUserQuery.addParameter('ApplicationId', TYPES.VarChar, userData.ApplicationId);
    connection.execSql(createUserQuery);
  }
}

ASP.NET Membership Provider (MVC4 - Simple Membership)

function create(user, callback) {
  const crypto = require('crypto');
  const sqlserver = require('tedious@1.11.0');
  const Connection = sqlserver.Connection;
  const Request = sqlserver.Request;
  const TYPES = sqlserver.TYPES;
  const connection = new Connection({
    userName: 'the username',
    password: 'the password',
    server: 'the server',
    options: {
      database: 'the db name',
      encrypt: true,
      // Required to retrieve userId needed for Membership entity creation
      rowCollectionOnRequestCompletion: true
    }
  });
  /**
   * hashPassword
   *
   * This function hashes a password using HMAC SHA256 algorithm.
   *
   * @password    {[string]}    password to be hased
   * @salt        {[string]}    salt to be used in the hashing process
   * @callback    {[function]}  callback to be called after hashing the password
   */
  function hashPassword(password, salt, callback) {
    const iterations = 1000;
    const passwordHashLength = 32;
    crypto.pbkdf2(password, salt, iterations, passwordHashLength, 'sha1', function (err, hashed) {
      if (err) return callback(err);
      const result = Buffer.concat([Buffer.from([0], 1), salt, Buffer.from(hashed, 'binary')]);
      const resultBase64 = result.toString('base64');
      callback(null, resultBase64);
    });
  }
  connection.on('debug', function (text) {
    // if you have connection issues, uncomment this to get more detailed info
    // console.log(text);
  }).on('errorMessage', function (text) {
    // this will show any errors when connecting to the SQL database or with the SQL statements
    console.log(JSON.stringify(text));
  });
  connection.on('connect', function (err) {
    if (err) return callback(err);
    const createUser =
      'INSERT INTO UserProfile (UserName) ' +
      'OUTPUT Inserted.UserId ' +
      'VALUES (@UserName)';
    const createUserQuery = new Request(createUser, function (err, rowCount, rows) {
      if (err || rowCount === 0) return callback(err);
      const userId = rows[0][0].value;
      const salt = crypto.randomBytes(16);
      const createMembership =
        'INSERT INTO webpages_Membership ' +
        '(UserId, CreateDate, IsConfirmed, PasswordFailuresSinceLastSuccess, Password, PasswordSalt) ' +
        'VALUES ' +
        '(@UserId, GETDATE(), \'false\', 0, @Password, \'\')';
      const createMembershipQuery = new Request(createMembership, function (err, rowCount) {
        if (err || rowCount === 0) return callback(err);
        callback(null, rowCount > 0);
      });
      hashPassword(user.password, salt, function (err, hashedPassword) {
        if (err) return callback(err);
        createMembershipQuery.addParameter('Password', TYPES.VarChar, hashedPassword);
        createMembershipQuery.addParameter('PasswordSalt', TYPES.VarChar, salt.toString('base64'));
        createMembershipQuery.addParameter('UserId', TYPES.VarChar, userId);
        connection.execSql(createMembershipQuery);
      });
    });
    createUserQuery.addParameter('UserName', TYPES.VarChar, user.email);
    connection.execSql(createUserQuery);
  });
}

MongoDB

function create(user, callback) {
  const bcrypt = require('bcrypt');
  const MongoClient = require('mongodb@3.1.4').MongoClient;
  const client = new MongoClient('mongodb://user:[e-mail]');
  client.connect(function (err) {
    if (err) return callback(err);
    const db = client.db('db-name');
    const users = db.collection('users');
    users.findOne({ email: user.email }, function (err, withSameMail) {
      if (err || withSameMail) {
        client.close();
        return callback(err || new Error('the user already exists'));
      }
      bcrypt.hash(user.password, 10, function (err, hash) {
        if (err) {
          client.close();
          return callback(err);
        }
        user.password = hash;
        users.insert(user, function (err, inserted) {
          client.close();
          if (err) return callback(err);
          callback(null);
        });
      });
    });
  });
}

MySQL

function create(user, callback) {
  const mysql = require('mysql');
  const bcrypt = require('bcrypt');
  const connection = mysql({
    host: 'localhost',
    user: 'me',
    password: 'secret',
    database: 'mydb'
  });
  connection.connect();
  const query = 'INSERT INTO users SET ?';
  bcrypt.hash(user.password, 10, function(err, hash) {
    if (err) return callback(err);
    const insert = {
      password: hash,
      email: user.email
    };
    connection.query(query, insert, function(err, results) {
      if (err) return callback(err);
      if (results.length === 0) return callback();
      callback(null);
    });
  });
}

PostgreSQL

function create(user, callback) {
  //this example uses the "pg" library
  //more info here: https://github.com/brianc/node-postgres
  const bcrypt = require('bcrypt');
  const postgres = require('pg');
  const conString = 'postgres://user:pass@localhost/mydb';
  postgres.connect(conString, function (err, client, done) {
    if (err) return callback(err);
    bcrypt.hash(user.password, 10, function (err, hashedPassword) {
      if (err) return callback(err);
      const query = 'INSERT INTO users(email, password) VALUES ($1, $2)';
      client.query(query, [user.email, hashedPassword], function (err, result) {
        // NOTE: always call `done()` here to close
        // the connection to the database
        done();
        return callback(err);
      });
    });
  });
}

SQL Server

function create(user, callback) {
  //this example uses the "tedious" library
  //more info here: http://pekim.github.io/tedious/index.html
  const bcrypt = require('bcrypt');
  const sqlserver = require('tedious@1.11.0');
  const Connection = sqlserver.Connection;
  const Request = sqlserver.Request;
  const TYPES = sqlserver.TYPES;
  const connection = new Connection({
    userName:  'test',
    password:  'test',
    server:    'localhost',
    options:  {
      database: 'mydb'
    }
  });
  const query = 'INSERT INTO dbo.Users SET Email = @Email, Password = @Password';
  connection.on('debug', function(text) {
    console.log(text);
  }).on('errorMessage', function(text) {
    console.log(JSON.stringify(text, null, 2));
  }).on('infoMessage', function(text) {
    console.log(JSON.stringify(text, null, 2));
  });
  connection.on('connect', function (err) {
    if (err) return callback(err);
    const request = new Request(query, function (err, rows) {
      if (err) return callback(err);
      callback(null);
    });
    bcrypt.hash(user.password, 10, function(err, hash) {
      if (err) return callback(err);
      request.addParameter('Email', TYPES.VarChar, user.email);
      request.addParameter('Password', TYPES.VarChar, hash);
      connection.execSql(request);
    });
  });
}

Windows Azure SQL Database

function create (user, callback) {
  //this example uses the "tedious" library
  //more info here: http://pekim.github.io/tedious/index.html
  var Connection = require('tedious@1.11.0').Connection;
  var Request = require('tedious@1.11.0').Request;
  var TYPES = require('tedious@1.11.0').TYPES;
  var bcrypt = require('bcrypt');
  var connection = new Connection({
    userName:  '[e-mail]',
    password:  'the-password',
    server:    'your-server-id.database.windows.net',
    options:  {
      database: 'mydb',
      encrypt:  true
    }
  });
  var query = "INSERT INTO users (Email, Password) VALUES (@Email, @Password)";
  connection.on('debug', function(text) {
    // Uncomment next line in order to enable debugging messages
    // console.log(text);
  }).on('errorMessage', function(text) {
    console.log(JSON.stringify(text, null, 2));
  }).on('infoMessage', function(text) {
    // Uncomment next line in order to enable information messages
    // console.log(JSON.stringify(text, null, 2));
  });
  connection.on('connect', function (err) {
    if (err) { return callback(err); }
    var request = new Request(query, function (err, rows) {
      if (err) { return callback(err); }
      console.log('rows: ' + rows);
      callback(null);
    });
    bcrypt.hash(user.password, 10, function (err, hashedPassword) {
      if (err) { return callback(err); }
      request.addParameter('Email', TYPES.VarChar, user.email);
      request.addParameter('Password', TYPES.VarChar, hashedPassword);
      connection.execSql(request);
    });
  });
}

Request with Basic Auth

function create(user, callback) {
  const request = require('request');
  request.post({
    url: 'https://myserviceurl.com/users',
    json: user
    //for more options check:
    //https://github.com/mikeal/request#requestoptions-callback
  }, function(err, response, body) {
    if (err) return callback(err);
    callback(null);
  });
}

Troubleshoot

If you are unable to create a user in either your legacy database or Auth0:

1. Check the console.log() statements with Actions Real-Time Logs.
2. Find the user in your legacy database and delete accordingly. If the partial user state is in Auth0, use the Management API’s Delete a User endpoint or Delete a Connection User endpoint.
3. Make sure Import Mode is disabled, then configure the create script.

Learn more

• Login Script Templates
• Get User Script Templates
• Delete Script Templates
• Verify Script Templates
• Change Password Script Templates
• Change Email Script Template