Config 3.4: Issuer db

hugo Configure

 

Simple SQL

A popular authentication is to use an user-account table to logins. For example,  assuming the table polls_admin and the credential columns are admin_name and admin_pass, you can setup the following issuer:

"Issuers : {
  "db" : {
    "Default": true,
    "Credential" : ["login", "passwd"],
    "Sql": "select admin_name, admin_id FROM polls_admin WHERE admin_name=? and admin_pass=?", 
  }
}

where the first two variables login and passwd, passed in the login form, will be used in the Sql for search. A zero return will mean a failed login.

 

Issuer db with Stored Procedure

In a real project, the above database issuer should setup a few security rules to stop spams. For example, you may want to detect how many failed attempts for a login name or from an IP during the past hour or past day. If they exceed certain thresholds, you may block the account or IP for 24 hours.

In all those sophisticated cases, Genelet asks you to build one stored procedure for authentication. For the db issuer, following these steps to build an login procedure:

  1. The first two inputs to the procedure are always the credential pair defined in Credential.
  2. There are 4 more optional input variables depending on Screen. If it’s Logic-And 1, then the URL will be added to inputs; 2 for IP (as a 32-bit unsigned integer), 3 for User Agent and 4 for Referer.
  3. Your outputs are values of variables defined in Attributes of the gate.
  4. The first item in outputs should always be the value of Id_name.
  5. For failed login, just return an empty value for Id_name.

Here is an example to use db issuer of MySQL to issue a member ticket.

"Roles" : {
  "m": {
    "Attributes": ["member_id", "email", "first_name", "last_name", "status_id"],
    "Type_id": 1,
    "Id_name": "member_id",
    "Is_admin": false,
    "Surface": "mc",
    "Domain": ".example.com",
    "Duration": 360000,
    "Max_age": 360000,
    "Secret": "11223344556677889900aabbccddeeffgg",
    "Coding": "11223344556677889900mmnnbbvvccxxzz",
    "Logout': "/",
    "Issuers": {
      "db" : {
        "Default": true,
        "Screen": 2,
        "Sql": "proc_member",
        "Credential": ["email", "passwd"]
      }
    }
  }
}

Here is the member table, the login history table and the stored procedure proc_member that authenticates login and blocks rough attempts. If an email or IP had 5 failed attempts during the past hour or 20 during the past 24 hours, it will be blocked for 24 hours.

DROP TABLE IF EXISTS `cus_member`;
CREATE TABLE `cus_member` (
  `member_id` int(10) unsigned NOT NULL,
  `email` varchar(48) NOT NULL,
  `passwd` char(40) NOT NULL,
  `first_name` varchar(64) NOT NULL,
  `last_name` varchar(32) NOT NULL,
  `state_id` tinyint unsigned DEFAULT NULL,
  PRIMARY KEY (`member_id`),
  UNIQUE KEY `email` (`email`),
  KEY `status_id` (`status_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `cus_ip`;
CREATE TABLE `cus_ip` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `ip` int(10) unsigned NOT NULL,
  `email` varchar(48) NOT NULL,
  `updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `ret` enum('fail','success') NOT NULL DEFAULT 'fail',
  PRIMARY KEY (`id`),
  KEY `updated` (`updated`),
  KEY `ip` (`ip`,`email`(16))
) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=utf8;

DELIMITER ~
DROP PROCEDURE IF EXISTS proc_member;
CREATE PROCEDURE proc_member(IN i_email VARCHAR(48), IN i_passwd VARCHAR(24), IN i_ip INT unsigned, OUT o_member_id INT unsigned, OUT o_email VARCHAR(48), OUT o_first_name VARCHAR(64), OUT o_last_name VARCHAR(32), OUT o_status_id tinyint unsigned)
BEGIN
DECLARE c1 INT;
DECLARE c2 INT;
SELECT COUNT(*) INTO c1 FROM cus_ip WHERE ret='fail' AND ip=i_ip AND email=i_email AND (UNIX_TIMESTAMP(updated) >= (UNIX_TIMESTAMP(NOW())-3600));
SELECT COUNT(*) INTO c2 FROM cus_ip WHERE ret='fail' AND ip=i_ip AND (UNIX_TIMESTAMP(updated) >= (UNIX_TIMESTAMP(NOW())-24*3600));
IF (c1<=5 AND c2<=20) THEN
  SELECT member_id, email, first_name, last_name, status_id
  INTO o_member_id, o_email, o_first_name, o_last_name, o_status_id
  FROM cus_member
  WHERE email=i_email and passwd=SHA1(i_passwd) and status_id IN (1);

  IF ISNULL(o_member_id) THEN
    INSERT INTO cus_ip (ip, email, ret) VALUES (i_ip, i_email, 'fail');
  ELSE
    DELETE FROM cus_ip WHERE ret='fail' AND ip=i_ip AND (UNIX_TIMESTAMP(updated) >= (UNIX_TIMESTAMP(NOW())-24*3600));
    INSERT INTO cus_ip (ip, email, ret) VALUES (i_ip, i_email, 'success');
  END IF;
ELSE
  SELECT '1030' INTO o_email;
END IF;
END~
DELIMITER ;

Comments are closed.