Lösung: SimpleAuthService mit Datenbank

 1  
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41

-- phpMyAdmin SQL init testdb
-- CAUTION: all existing data will be lost

CREATE USER IF NOT EXISTS 'testUser'@'localhost' IDENTIFIED BY 'testPwd';
GRANT ALL PRIVILEGES ON *.* TO 'testUser'@'localhost';

DROP DATABASE IF EXISTS testDb;
CREATE DATABASE testDb; 

USE testDb;

CREATE TABLE tblRole (
  roleID tinyint(4) NOT NULL AUTO_INCREMENT,
  rolename varchar(20) NOT NULL,
  PRIMARY KEY(roleID)
);

INSERT INTO tblRole (roleID, rolename) VALUES
(1, 'Administrator'),
(2, 'Viewer');

CREATE TABLE tblUser (
  userID int(11) NOT NULL AUTO_INCREMENT,
  username varchar(50) DEFAULT NULL,
  pwd varchar(128) DEFAULT NULL,
  token bigint(64) NOT NULL,
  tokenExpiry timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY(userID)
);

-- password 'testPwd' hashed with argon2
INSERT INTO tblUser (userID, username, pwd, token) VALUES
(1, 'testUser', '$argon2id$v=19$m=65536,t=3,p=4$AO8XsjZEt2aaGL7Xh/4DeQ$vTplDqP6zL3Kk8uCUu9rPV1+dT3hSmo8Si8DDH3nhQU', 123456);                 
CREATE TABLE tblRoleUser (
  userID int(11) NOT NULL,
  roleID int(11) NOT NULL
);

INSERT INTO tblRoleUser (userID, roleID) VALUES
(1, 1);

1  
2
3
4
5
6
7

from argon2 import PasswordHasher

ph = PasswordHasher()
hash = ph.hash("testPwd")
print(hash) 

print(ph.verify(hash, "testPwd"))

1  
2
3
4
5
6
7
8
9

# database connection parameters
# CAUTION: exclude settings.py from versioning in .gitignore

HOST = "localhost"
USER = "testUser"
PASSWD = "testPwd"
DB = "testDb"

DEBUG_MODE = True     # disable DEBUG_MODE in productive environment

 1  
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30

import mysql.connector as mc
import sys
import settings


# Wrapper class for database
class Db:
    def __init__(self):
        try:
            self.__connection = mc.connect(host=settings.HOST,
                                            user=settings.USER,
                                            password=settings.PASSWD,
                                            db=settings.DB)
        except mc.Error as e:
            print("Error %d: %s" % (e.args[0], e.args[1]))
            sys.exit(1)

        self.__cursor = self.__connection.cursor()

    def execute(self, sql):
        self.__cursor.execute(sql)
        return self.__cursor.fetchall()

    def commit(self):
        self.__connection.commit()              # actually executes SQL command 
                                                # (required after INSERT, UPDATE etc.)

    def __del__(self):                          # destructor closes db connection
        self.__cursor.close()
        self.__connection.close()

 1  
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70

from flask import Flask, request
import json
from argon2 import PasswordHasher
import secrets
import db
import settings
# make cross-origin AJAX possible because of using swagger editor
# https://flask-cors.readthedocs.io/en/latest/#using-json-with-cors
from flask_cors import CORS, cross_origin  


def generateToken():
    if settings.DEBUG_MODE:
        return "123456"                     # testing environment
    else:
        return secrets.token_urlsafe(64)    # production

app = Flask(__name__)

if settings.DEBUG_MODE:
    CORS(app)           # make cross-origin AJAX possible 

@app.route('/auth/user/login', methods=['POST'])
def loginUser():
    content_type = request.headers.get('Content-Type')
    if content_type == 'application/json':
        print(request)
        username = request.json['username']
        password = request.json['password']
    elif (content_type == 'application/x-www-form-urlencoded'):     # regular html form data
        username = request.form['username']
        password = request.form['password']
    else:
        return 'Content-Type not supported: ' + content_type, 400   # Bad request
    
    db1 = db.Db()
    ph = PasswordHasher()
    hashedPW = ph.hash(str(password))
    query = "SELECT userId, pwd FROM tblUser WHERE username='%s'" %(username)
    result = db1.execute(query)
    if(result):
        for row in result:                                          # more than one user with this username possible
            try:                                                    # verify hashed password fail -> throws exception
                if ph.verify(row[1], password) == True:             # check hashed password
                    userId = row[0]                                 
                    token = generateToken()
                    query = "UPDATE tblUser SET token = '%s' WHERE userID=%d" %(token, userId)
                    result = db1.execute(query)
                    db1.commit()                                    # actually execute
                    return json.dumps({ "token": token })           # 200 OK
            except:
                pass
        return json.dumps({ "token": "-1" }), 403                   # 403 forbidden - wrong password
    else:
        return json.dumps({ "token": "-1" }), 403                   # 403 forbidden - no user with this username
    del db1                                                         # close db connection
                                                    
                                                    
@app.route('/auth/user/<token>', methods=['DELETE'])
def logoutUser(token):
    db1 = db.Db()
    query = "UPDATE tblUser SET token = '-1' WHERE token=%s" %(token)
    result = db1.execute(query)
    db1.commit()      
    del db1       
    return "{ \"token\": \"-1\" }"                                  # 200 logout sucessful


if __name__ == '__main__':
    app.run()