Authentication Tables

In order to use revIgniter's authentication library you need 4 database tables, or 5 in case you enable two-factor authentication (see One-Time Password Authentication).

Note: The default admin user has the following credentials. Identity: admin@admin.com Password: revigniter. This password works as long as you use the default authentication encryption settings.

Note: The authentication library automatically connects to your database using the connection group specified in your database config file. So, you don’t need to auto-load your database, nor do you need to connect to it manually.

The MySQL version:

# Table structure for groups
# ------------------------------------------------------------

CREATE TABLE `groups` (
  `id` mediumint(8) unsigned NOT NULL auto_increment,
  `name` varchar(20) NOT NULL,
  `description` varchar(100) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;



# Records of groups
# ------------------------------------------------------------

LOCK TABLES `groups` WRITE;

INSERT INTO `groups` (`id`, `name`, `description`)
VALUES
  (1,'admin','Administrator'),
  (2,'members','General User'),
  (3,'other','Other User');

UNLOCK TABLES;



# Table structure for login_attempts
# ------------------------------------------------------------

CREATE TABLE `login_attempts` (
  `id` mediumint(8) unsigned NOT NULL auto_increment,
  `ipAddress` varchar(16) NOT NULL,
  `login` varchar(100) NOT NULL,
  `time` int(11) unsigned default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;



# Table structure for users
# ------------------------------------------------------------

CREATE TABLE `users` (
  `id` mediumint(8) unsigned NOT NULL auto_increment,
  `ipAddress` varchar(16) character set utf8 collate utf8_unicode_ci NOT NULL,
  `username` varchar(100) character set utf8 collate utf8_unicode_ci NOT NULL,
  `password` tinytext character set utf8 collate utf8_unicode_ci NOT NULL,
  `email` varchar(100) character set utf8 collate utf8_unicode_ci NOT NULL,
  `activationCode` varchar(70) character set utf8 collate utf8_unicode_ci default NULL,
  `forgottenPasswordCode` varchar(70) character set utf8 collate utf8_unicode_ci default NULL,
  `forgottenPasswordTime` int(11) unsigned NOT NULL default '0',
  `rememberCode` varchar(50) character set utf8 collate utf8_unicode_ci default NULL,
  `createdOn` int(11) unsigned NOT NULL,
  `lastLogin` int(11) unsigned default NULL,
  `active` tinyint(1) unsigned default NULL,
  `firstName` varchar(50) character set utf8 collate utf8_unicode_ci default NULL,
  `lastName` varchar(50) character set utf8 collate utf8_unicode_ci default NULL,
  `company` varchar(100) character set utf8 collate utf8_unicode_ci default NULL,
  `phone` varchar(20) character set utf8 collate utf8_unicode_ci default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;



# Records of users
# ------------------------------------------------------------

LOCK TABLES `users` WRITE;

INSERT INTO `users` (`id`, `ipAddress`, `username`, `password`, `email`, 
`activationCode`, `forgottenPasswordCode`, `forgottenPasswordTime`, 
`rememberCode`, `createdOn`, `lastLogin`, `active`, `firstName`, 
`lastName`, `company`, `phone`)
VALUES (1,'127.0.0.1','admin','U2FsdGVkX1+F3hUUeIgHdj0Qx2XGvMlrkQeuDS2PwofAXf9KhPznBw==',
'admin@admin.com','',NULL,0,NULL,1361712014,1361732014,1,'Admin','Istrator','ADMIN','0');

UNLOCK TABLES;


# Table structure for users_groups
# ------------------------------------------------------------

DROP TABLE IF EXISTS `users_groups`;

CREATE TABLE `users_groups` (
  `id` mediumint(8) unsigned NOT NULL auto_increment,
  `userId` mediumint(8) unsigned NOT NULL,
  `groupId` mediumint(8) unsigned NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;



# Records of users_groups
# ------------------------------------------------------------

LOCK TABLES `users_groups` WRITE;

INSERT INTO `users_groups` (`id`, `userId`, `groupId`)
VALUES
  (1,1,1),
  (2,1,2);

UNLOCK TABLES;

The PostgreSQL version:

# Table structure for users
# ------------------------------------------------------------

CREATE TABLE "users" (
    "id" SERIAL NOT NULL,
    "ipAddress" varchar(16) NOT NULL,
    "username" varchar(100) NOT NULL,
    "password" text NOT NULL,
    "email" varchar(100) NOT NULL,
    "activationCode" varchar(70),
    "forgottenPasswordCode" varchar(70),
    "forgottenPasswordTime" int,
    "rememberCode" varchar(50),
    "createdOn" int NOT NULL,
    "lastLogin" int,
    "active" int4,
    "firstName" varchar(50),
    "lastName" varchar(50),
    "company" varchar(100),
    "phone" varchar(20),
  PRIMARY KEY("id"),
  CONSTRAINT "check_id" CHECK(id >= 0),
  CONSTRAINT "check_active" CHECK(active >= 0)
);



# Table structure for groups
# ------------------------------------------------------------

CREATE TABLE "groups" (
    "id" SERIAL NOT NULL,
    "name" varchar(20) NOT NULL,
    "description" varchar(100) NOT NULL,
  PRIMARY KEY("id"),
  CONSTRAINT "check_id" CHECK(id >= 0)
);



# Table structure for users_groups
# ------------------------------------------------------------

CREATE TABLE "users_groups" (
    "id" SERIAL NOT NULL,
    "userId" integer NOT NULL REFERENCES "users" ON UPDATE CASCADE ON DELETE CASCADE,
    "groupId" integer NOT NULL REFERENCES "groups" ON UPDATE CASCADE ON DELETE RESTRICT,
  PRIMARY KEY("id"),
  CONSTRAINT "users_groups_check_id" CHECK(id >= 0),
  CONSTRAINT "users_groups_check_userId" CHECK("userId" >= 0),
  CONSTRAINT "users_groups_check_groupId" CHECK("groupId" >= 0)
);



# Records of groups
# ------------------------------------------------------------

INSERT INTO "groups" (id, name, description)
SELECT 1,'admin','Administrator'
UNION SELECT
2,'members','General User'
UNION SELECT
3,'other','Other User';



# Records of users
# ------------------------------------------------------------

INSERT INTO "users" ("ipAddress", username, password, email, 
"activationCode", "forgottenPasswordCode", "forgottenPasswordTime", 
"rememberCode", "createdOn", "lastLogin", active, "firstName", 
"lastName", company, phone) VALUES 
('127.0.0.1','admin','U2FsdGVkX1+F3hUUeIgHdj0Qx2XGvMlrkQeuDS2PwofAXf9KhPznBw==',
'admin@admin.com','',NULL,'0',NULL,'1361712014','1361732014','1',
'Admin','Istrator','ADMIN','0');



# Records of users_groups
# ------------------------------------------------------------

INSERT INTO "users_groups" ("userId", "groupId")
SELECT 1, 1
UNION SELECT
1, 2;



# Table structure for login_attempts
# ------------------------------------------------------------

CREATE TABLE "login_attempts" (
    "id" SERIAL NOT NULL,
    "ipAddress" varchar(16) NOT NULL,
    "login" varchar(100) NOT NULL,
    "time" int,
  PRIMARY KEY("id"),
  CONSTRAINT "check_id" CHECK(id >= 0)
);

The SQLite version:

# Table structure for users
# ------------------------------------------------------------

CREATE TABLE "users" (
   "id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
   "ipAddress" TEXT(16,0) NOT NULL,
   "username" TEXT(100,0) NOT NULL,
   "password" TEXT NOT NULL,
   "email" TEXT(100,0) NOT NULL,
   "activationCode" TEXT(70,0) DEFAULT NULL,
   "forgottenPasswordCode" TEXT(70,0) DEFAULT NULL,
   "forgottenPasswordTime" INTEGER(11,0) NOT NULL DEFAULT 0,
   "rememberCode" TEXT(50,0) DEFAULT NULL,
   "createdOn" INTEGER(11,0) NOT NULL,
   "lastLogin" INTEGER(11,0) DEFAULT NULL,
   "active" INTEGER(1,0) DEFAULT NULL,
   "firstName" TEXT(50,0) DEFAULT NULL,
   "lastName" TEXT(50,0) DEFAULT NULL,
   "company" TEXT(100,0) DEFAULT NULL,
   "phone" TEXT(20,0) DEFAULT NULL
);



# Table structure for groups
# ------------------------------------------------------------

CREATE TABLE "groups" (
   "id" INTEGER NOT NULL DEFAULT 0 PRIMARY KEY AUTOINCREMENT,
   "name" TEXT(20,0) NOT NULL,
   "description" TEXT(100,0) NOT NULL
);



# Table structure for users_groups
# ------------------------------------------------------------

CREATE TABLE "users_groups" (
   "id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
   "userId" INTEGER(8,0) NOT NULL,
   "groupId" INTEGER(8,0) NOT NULL,
  CONSTRAINT "Foreign_UsersID" FOREIGN KEY ("userId") REFERENCES "users" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT "Foreign_GroupsID" FOREIGN KEY ("groupId") REFERENCES "groups" ("id") ON DELETE RESTRICT ON UPDATE CASCADE
);



# Records of groups
# ------------------------------------------------------------

BEGIN;
INSERT INTO "groups" VALUES (1, 'admin', 'Administrator');
INSERT INTO "groups" VALUES (2, 'members', 'General User');
INSERT INTO "groups" VALUES (3, 'other', 'Other User');
COMMIT;



# Records of users
# ------------------------------------------------------------

BEGIN;
INSERT INTO "users" VALUES 
(1, '127.0.0.1', 'admin', 'U2FsdGVkX1+F3hUUeIgHdj0Qx2XGvMlrkQeuDS2PwofAXf9KhPznBw==', 
'admin@admin.com', '', null, 0, null, 1361712014, 1361712014, 1, 
'Admin', 'Istrator', 'ADMIN', 0);
COMMIT;



# Records of users_groups
# ------------------------------------------------------------

BEGIN;
INSERT INTO "users_groups" VALUES (1, 1, 1);
INSERT INTO "users_groups" VALUES (2, 1, 2);
COMMIT;



# Table structure for login_attempts
# ------------------------------------------------------------

CREATE TABLE "login_attempts" (
   "id" INTEGER NOT NULL DEFAULT 0 PRIMARY KEY AUTOINCREMENT,
   "ipAddress" TEXT(16,0) NOT NULL,
   "login" TEXT(100,0) NOT NULL,
   "time" INTEGER(11,0) DEFAULT NULL
);

Note: If, for security reasons, you need to validate session IDs please follow the instructions in the User Guide regarding Saving Session Data to a Database and create an additional table to store sessions.