Add database connection.

21 files changed, 820 insertions, 111 deletions

diff --git a/Timeline.Tests/Helpers/WebApplicationFactoryExtensions.cs b/Timeline.Tests/Helpers/WebApplicationFactoryExtensions.cs
index bb8fc71b..4a7f87fb 100644
--- a/Timeline.Tests/Helpers/WebApplicationFactoryExtensions.cs
+++ b/Timeline.Tests/Helpers/WebApplicationFactoryExtensions.cs
@@ -1,6 +1,10 @@
 using Microsoft.AspNetCore.Hosting;
 using Microsoft.AspNetCore.Mvc.Testing;
+using Microsoft.EntityFrameworkCore;
+using Microsoft.Extensions.DependencyInjection;
 using Microsoft.Extensions.Logging;
+using Timeline.Models;
+using Timeline.Services;
 using Xunit.Abstractions;
 
 namespace Timeline.Tests.Helpers
@@ -16,6 +20,52 @@ namespace Timeline.Tests.Helpers
                     .ConfigureLogging(logging =>
                     {
                         logging.AddXunit(outputHelper);
+                    })
+                    .ConfigureServices(services =>
+                    {
+                        var serviceProvider = new ServiceCollection()
+                            .AddEntityFrameworkInMemoryDatabase()
+                            .BuildServiceProvider();
+
+                        services.AddDbContext<DatabaseContext>(options =>
+                        {
+                            options.UseInMemoryDatabase("timeline");
+                            options.UseInternalServiceProvider(serviceProvider);
+                        });
+
+                        var sp = services.BuildServiceProvider();
+
+                        // Create a scope to obtain a reference to the database
+                        // context (ApplicationDbContext).
+                        using (var scope = sp.CreateScope())
+                        {
+                            var scopedServices = scope.ServiceProvider;
+                            var db = scopedServices.GetRequiredService<DatabaseContext>();
+
+                            var passwordService = new PasswordService(null);
+
+                            // Ensure the database is created.
+                            db.Database.EnsureCreated();
+
+                            db.Users.AddRange(new User[] {
+                                new User
+                                {
+                                    Id = 0,
+                                    Name = "user",
+                                    EncryptedPassword = passwordService.HashPassword("user"),
+                                    RoleString = "user"
+                                },
+                                new User
+                                {
+                                    Id = 0,
+                                    Name = "admin",
+                                    EncryptedPassword = passwordService.HashPassword("admin"),
+                                    RoleString = "user,admin"
+                                }
+                            });
+
+                            db.SaveChanges();
+                        }
                     });
             });
         }
diff --git a/Timeline/Configs/DatabaseConfig.cs b/Timeline/Configs/DatabaseConfig.cs
new file mode 100644
index 00000000..34e5e65f
--- /dev/null
+++ b/Timeline/Configs/DatabaseConfig.cs
@@ -0,0 +1,12 @@
+using System;
+using System.Collections.Generic;
+using System.Linq;
+using System.Threading.Tasks;
+
+namespace Timeline.Configs
+{
+    public class DatabaseConfig
+    {
+        public string ConnectionString { get; set; }
+    }
+}
diff --git a/Timeline/Controllers/UserController.cs b/Timeline/Controllers/UserController.cs
index eb1b8513..b9d760ec 100644
--- a/Timeline/Controllers/UserController.cs
+++ b/Timeline/Controllers/UserController.cs
@@ -1,6 +1,8 @@
 using Microsoft.AspNetCore.Authorization;
 using Microsoft.AspNetCore.Mvc;
 using Microsoft.Extensions.Logging;
+using System;
+using System.Threading.Tasks;
 using Timeline.Entities;
 using Timeline.Services;
 
@@ -16,23 +18,22 @@ namespace Timeline.Controllers
         }
 
         private readonly IUserService _userService;
-        private readonly IJwtService _jwtService;
         private readonly ILogger<UserController> _logger;
 
-        public UserController(IUserService userService, IJwtService jwtService, ILogger<UserController> logger)
+        public UserController(IUserService userService, ILogger<UserController> logger)
         {
             _userService = userService;
-            _jwtService = jwtService;
             _logger = logger;
         }
 
         [HttpPost("[action]")]
         [AllowAnonymous]
-        public ActionResult<CreateTokenResponse> CreateToken([FromBody] CreateTokenRequest request)
+        public async Task<ActionResult<CreateTokenResponse>> CreateToken([FromBody] CreateTokenRequest request)
         {
-            var user = _userService.Authenticate(request.Username, request.Password);
+            var result = await _userService.CreateToken(request.Username, request.Password);
 
-            if (user == null) {
+            if (result == null)
+            {
                 _logger.LogInformation(LoggingEventIds.LogInFailed, "Attemp to login with username: {} and password: {} failed.", request.Username, request.Password);
                 return Ok(new CreateTokenResponse
                 {
@@ -45,17 +46,46 @@ namespace Timeline.Controllers
             return Ok(new CreateTokenResponse
             {
                 Success = true,
-                Token = _jwtService.GenerateJwtToken(user),
-                UserInfo = user.GetUserInfo()
+                Token = result.Token,
+                UserInfo = result.UserInfo
             });
         }
 
         [HttpPost("[action]")]
         [AllowAnonymous]
-        public ActionResult<TokenValidationResponse> ValidateToken([FromBody] TokenValidationRequest request)
+        public async Task<ActionResult<TokenValidationResponse>> ValidateToken([FromBody] TokenValidationRequest request)
         {
-            var result = _jwtService.ValidateJwtToken(request.Token);
-            return Ok(result);
+            var result = await _userService.VerifyToken(request.Token);
+
+            if (result == null)
+            {
+                return Ok(new TokenValidationResponse
+                {
+                    IsValid = false,
+                });
+            }
+
+            return Ok(new TokenValidationResponse
+            {
+                IsValid = true,
+                UserInfo = result
+            });
+        }
+
+        [HttpPost("[action]")]
+        [Authorize(Roles = "admin")]
+        public async Task<ActionResult<CreateUserResponse>> CreateUser([FromBody] CreateUserRequest request)
+        {
+            var result = await _userService.CreateUser(request.Username, request.Password, request.Roles);
+            switch (result)
+            {
+                case CreateUserResult.Success:
+                    return Ok(new CreateUserResponse { ReturnCode = CreateUserResponse.SuccessCode });
+                case CreateUserResult.AlreadyExists:
+                    return Ok(new CreateUserResponse { ReturnCode = CreateUserResponse.AlreadyExistsCode });
+                default:
+                    throw new Exception("Unreachable code.");
+            }
         }
     }
 }
diff --git a/Timeline/Controllers/UserTestController.cs b/Timeline/Controllers/UserTestController.cs
index cf5cf074..de8058f2 100644
--- a/Timeline/Controllers/UserTestController.cs
+++ b/Timeline/Controllers/UserTestController.cs
@@ -14,14 +14,14 @@ namespace Timeline.Controllers
         }
 
         [HttpGet("[action]")]
-        [Authorize(Roles = "User,Admin")]
+        [Authorize(Roles = "user,admin")]
         public ActionResult BothUserAndAdmin()
         {
             return Ok();
         }
 
         [HttpGet("[action]")]
-        [Authorize(Roles = "Admin")]
+        [Authorize(Roles = "admin")]
         public ActionResult OnlyAdmin()
         {
             return Ok();
diff --git a/Timeline/Entities/Token.cs b/Timeline/Entities/Token.cs
deleted file mode 100644
index ce5b92ff..00000000
--- a/Timeline/Entities/Token.cs
+++ /dev/null
@@ -1,26 +0,0 @@
-namespace Timeline.Entities
-{
-    public class CreateTokenRequest
-    {
-        public string Username { get; set; }
-        public string Password { get; set; }
-    }
-
-    public class CreateTokenResponse
-    {
-        public bool Success { get; set; }
-        public string Token { get; set; }
-        public UserInfo UserInfo { get; set; }
-    }
-
-    public class TokenValidationRequest
-    {
-        public string Token { get; set; }
-    }
-
-    public class TokenValidationResponse
-    {
-        public bool IsValid { get; set; }
-        public UserInfo UserInfo { get; set; }
-    }
-}
diff --git a/Timeline/Entities/User.cs b/Timeline/Entities/User.cs
index c77e895d..1cb5a894 100644
--- a/Timeline/Entities/User.cs
+++ b/Timeline/Entities/User.cs
@@ -1,25 +1,41 @@
-namespace Timeline.Entities
+namespace Timeline.Entities
 {
-    public class User
+    public class CreateTokenRequest
     {
-        public int Id { get; set; }
         public string Username { get; set; }
         public string Password { get; set; }
-        public string[] Roles { get; set; }
+    }
 
-        public UserInfo GetUserInfo()
-        {
-            return new UserInfo
-            {
-                Username = Username,
-                Roles = Roles
-            };
-        }
+    public class CreateTokenResponse
+    {
+        public bool Success { get; set; }
+        public string Token { get; set; }
+        public UserInfo UserInfo { get; set; }
     }
 
-    public class UserInfo
+    public class TokenValidationRequest
+    {
+        public string Token { get; set; }
+    }
+
+    public class TokenValidationResponse
+    {
+        public bool IsValid { get; set; }
+        public UserInfo UserInfo { get; set; }
+    }
+
+    public class CreateUserRequest
     {
         public string Username { get; set; }
-        public string[] Roles { get; set; }
+        public string Password { get; set; }
+        public string[] Roles { get; set; } 
+    }
+
+    public class CreateUserResponse
+    {
+        public const int SuccessCode = 0;
+        public const int AlreadyExistsCode = 1;
+
+        public int ReturnCode { get; set; }
     }
 }
diff --git a/Timeline/Entities/UserInfo.cs b/Timeline/Entities/UserInfo.cs
new file mode 100644
index 00000000..d9c5acad
--- /dev/null
+++ b/Timeline/Entities/UserInfo.cs
@@ -0,0 +1,26 @@
+using System;
+using System.Linq;
+using Timeline.Models;
+
+namespace Timeline.Entities
+{
+    public class UserInfo
+    {
+        public UserInfo()
+        {
+
+        }
+
+        public UserInfo(User user)
+        {
+            if (user == null)
+                throw new ArgumentNullException(nameof(user));
+
+            Username = user.Name;
+            Roles = user.RoleString.Split(',').Select(s => s.Trim()).ToArray();
+        }
+
+        public string Username { get; set; }
+        public string[] Roles { get; set; }
+    }
+}
diff --git a/Timeline/Migrations/20190412102517_InitCreate.Designer.cs b/Timeline/Migrations/20190412102517_InitCreate.Designer.cs
new file mode 100644
index 00000000..c68183de
--- /dev/null
+++ b/Timeline/Migrations/20190412102517_InitCreate.Designer.cs
@@ -0,0 +1,43 @@
+// <auto-generated />
+using Microsoft.EntityFrameworkCore;
+using Microsoft.EntityFrameworkCore.Infrastructure;
+using Microsoft.EntityFrameworkCore.Migrations;
+using Microsoft.EntityFrameworkCore.Storage.ValueConversion;
+using Timeline.Models;
+
+namespace Timeline.Migrations
+{
+    [DbContext(typeof(DatabaseContext))]
+    [Migration("20190412102517_InitCreate")]
+    partial class InitCreate
+    {
+        protected override void BuildTargetModel(ModelBuilder modelBuilder)
+        {
+#pragma warning disable 612, 618
+            modelBuilder
+                .HasAnnotation("ProductVersion", "2.2.3-servicing-35854")
+                .HasAnnotation("Relational:MaxIdentifierLength", 64);
+
+            modelBuilder.Entity("Timeline.Models.User", b =>
+                {
+                    b.Property<long>("Id")
+                        .ValueGeneratedOnAdd()
+                        .HasColumnName("id");
+
+                    b.Property<string>("EncryptedPassword")
+                        .HasColumnName("password");
+
+                    b.Property<string>("Name")
+                        .HasColumnName("name");
+
+                    b.Property<string>("RoleString")
+                        .HasColumnName("roles");
+
+                    b.HasKey("Id");
+
+                    b.ToTable("user");
+                });
+#pragma warning restore 612, 618
+        }
+    }
+}
diff --git a/Timeline/Migrations/20190412102517_InitCreate.cs b/Timeline/Migrations/20190412102517_InitCreate.cs
new file mode 100644
index 00000000..c8f3b0ac
--- /dev/null
+++ b/Timeline/Migrations/20190412102517_InitCreate.cs
@@ -0,0 +1,32 @@
+using Microsoft.EntityFrameworkCore.Metadata;
+using Microsoft.EntityFrameworkCore.Migrations;
+
+namespace Timeline.Migrations
+{
+    public partial class InitCreate : Migration
+    {
+        protected override void Up(MigrationBuilder migrationBuilder)
+        {
+            migrationBuilder.CreateTable(
+                name: "user",
+                columns: table => new
+                {
+                    id = table.Column<long>(nullable: false)
+                        .Annotation("MySql:ValueGenerationStrategy", MySqlValueGenerationStrategy.IdentityColumn),
+                    name = table.Column<string>(nullable: true),
+                    password = table.Column<string>(nullable: true),
+                    roles = table.Column<string>(nullable: true)
+                },
+                constraints: table =>
+                {
+                    table.PrimaryKey("PK_user", x => x.id);
+                });
+        }
+
+        protected override void Down(MigrationBuilder migrationBuilder)
+        {
+            migrationBuilder.DropTable(
+                name: "user");
+        }
+    }
+}
diff --git a/Timeline/Migrations/20190412144150_AddAdminUser.Designer.cs b/Timeline/Migrations/20190412144150_AddAdminUser.Designer.cs
new file mode 100644
index 00000000..319c646a
--- /dev/null
+++ b/Timeline/Migrations/20190412144150_AddAdminUser.Designer.cs
@@ -0,0 +1,43 @@
+// <auto-generated />
+using Microsoft.EntityFrameworkCore;
+using Microsoft.EntityFrameworkCore.Infrastructure;
+using Microsoft.EntityFrameworkCore.Migrations;
+using Microsoft.EntityFrameworkCore.Storage.ValueConversion;
+using Timeline.Models;
+
+namespace Timeline.Migrations
+{
+    [DbContext(typeof(DatabaseContext))]
+    [Migration("20190412144150_AddAdminUser")]
+    partial class AddAdminUser
+    {
+        protected override void BuildTargetModel(ModelBuilder modelBuilder)
+        {
+#pragma warning disable 612, 618
+            modelBuilder
+                .HasAnnotation("ProductVersion", "2.2.3-servicing-35854")
+                .HasAnnotation("Relational:MaxIdentifierLength", 64);
+
+            modelBuilder.Entity("Timeline.Models.User", b =>
+                {
+                    b.Property<long>("Id")
+                        .ValueGeneratedOnAdd()
+                        .HasColumnName("id");
+
+                    b.Property<string>("EncryptedPassword")
+                        .HasColumnName("password");
+
+                    b.Property<string>("Name")
+                        .HasColumnName("name");
+
+                    b.Property<string>("RoleString")
+                        .HasColumnName("roles");
+
+                    b.HasKey("Id");
+
+                    b.ToTable("user");
+                });
+#pragma warning restore 612, 618
+        }
+    }
+}
diff --git a/Timeline/Migrations/20190412144150_AddAdminUser.cs b/Timeline/Migrations/20190412144150_AddAdminUser.cs
new file mode 100644
index 00000000..9fac05ff
--- /dev/null
+++ b/Timeline/Migrations/20190412144150_AddAdminUser.cs
@@ -0,0 +1,19 @@
+using Microsoft.EntityFrameworkCore.Migrations;
+using Timeline.Services;
+
+namespace Timeline.Migrations
+{
+    public partial class AddAdminUser : Migration
+    {
+        protected override void Up(MigrationBuilder migrationBuilder)
+        {
+            migrationBuilder.InsertData("user", new string[] { "name", "password", "roles" },
+                new string[] { "crupest", new PasswordService(null).HashPassword("yang0101"), "user,admin" });
+        }
+
+        protected override void Down(MigrationBuilder migrationBuilder)
+        {
+            migrationBuilder.DeleteData("user", "name", "crupest");
+        }
+    }
+}
diff --git a/Timeline/Migrations/20190412153003_MakeColumnsInUserNotNull.Designer.cs b/Timeline/Migrations/20190412153003_MakeColumnsInUserNotNull.Designer.cs
new file mode 100644
index 00000000..c1d1565f
--- /dev/null
+++ b/Timeline/Migrations/20190412153003_MakeColumnsInUserNotNull.Designer.cs
@@ -0,0 +1,46 @@
+// <auto-generated />
+using Microsoft.EntityFrameworkCore;
+using Microsoft.EntityFrameworkCore.Infrastructure;
+using Microsoft.EntityFrameworkCore.Migrations;
+using Microsoft.EntityFrameworkCore.Storage.ValueConversion;
+using Timeline.Models;
+
+namespace Timeline.Migrations
+{
+    [DbContext(typeof(DatabaseContext))]
+    [Migration("20190412153003_MakeColumnsInUserNotNull")]
+    partial class MakeColumnsInUserNotNull
+    {
+        protected override void BuildTargetModel(ModelBuilder modelBuilder)
+        {
+#pragma warning disable 612, 618
+            modelBuilder
+                .HasAnnotation("ProductVersion", "2.2.3-servicing-35854")
+                .HasAnnotation("Relational:MaxIdentifierLength", 64);
+
+            modelBuilder.Entity("Timeline.Models.User", b =>
+                {
+                    b.Property<long>("Id")
+                        .ValueGeneratedOnAdd()
+                        .HasColumnName("id");
+
+                    b.Property<string>("EncryptedPassword")
+                        .IsRequired()
+                        .HasColumnName("password");
+
+                    b.Property<string>("Name")
+                        .IsRequired()
+                        .HasColumnName("name");
+
+                    b.Property<string>("RoleString")
+                        .IsRequired()
+                        .HasColumnName("roles");
+
+                    b.HasKey("Id");
+
+                    b.ToTable("user");
+                });
+#pragma warning restore 612, 618
+        }
+    }
+}
diff --git a/Timeline/Migrations/20190412153003_MakeColumnsInUserNotNull.cs b/Timeline/Migrations/20190412153003_MakeColumnsInUserNotNull.cs
new file mode 100644
index 00000000..0b7b5f08
--- /dev/null
+++ b/Timeline/Migrations/20190412153003_MakeColumnsInUserNotNull.cs
@@ -0,0 +1,52 @@
+using Microsoft.EntityFrameworkCore.Migrations;
+
+namespace Timeline.Migrations
+{
+    public partial class MakeColumnsInUserNotNull : Migration
+    {
+        protected override void Up(MigrationBuilder migrationBuilder)
+        {
+            migrationBuilder.AlterColumn<string>(
+                name: "roles",
+                table: "user",
+                nullable: false,
+                oldClrType: typeof(string),
+                oldNullable: true);
+
+            migrationBuilder.AlterColumn<string>(
+                name: "name",
+                table: "user",
+                nullable: false,
+                oldClrType: typeof(string),
+                oldNullable: true);
+
+            migrationBuilder.AlterColumn<string>(
+                name: "password",
+                table: "user",
+                nullable: false,
+                oldClrType: typeof(string),
+                oldNullable: true);
+        }
+
+        protected override void Down(MigrationBuilder migrationBuilder)
+        {
+            migrationBuilder.AlterColumn<string>(
+                name: "roles",
+                table: "user",
+                nullable: true,
+                oldClrType: typeof(string));
+
+            migrationBuilder.AlterColumn<string>(
+                name: "name",
+                table: "user",
+                nullable: true,
+                oldClrType: typeof(string));
+
+            migrationBuilder.AlterColumn<string>(
+                name: "password",
+                table: "user",
+                nullable: true,
+                oldClrType: typeof(string));
+        }
+    }
+}
diff --git a/Timeline/Migrations/DatabaseContextModelSnapshot.cs b/Timeline/Migrations/DatabaseContextModelSnapshot.cs
new file mode 100644
index 00000000..a833d2dc
--- /dev/null
+++ b/Timeline/Migrations/DatabaseContextModelSnapshot.cs
@@ -0,0 +1,44 @@
+// <auto-generated />
+using Microsoft.EntityFrameworkCore;
+using Microsoft.EntityFrameworkCore.Infrastructure;
+using Microsoft.EntityFrameworkCore.Storage.ValueConversion;
+using Timeline.Models;
+
+namespace Timeline.Migrations
+{
+    [DbContext(typeof(DatabaseContext))]
+    partial class DatabaseContextModelSnapshot : ModelSnapshot
+    {
+        protected override void BuildModel(ModelBuilder modelBuilder)
+        {
+#pragma warning disable 612, 618
+            modelBuilder
+                .HasAnnotation("ProductVersion", "2.2.3-servicing-35854")
+                .HasAnnotation("Relational:MaxIdentifierLength", 64);
+
+            modelBuilder.Entity("Timeline.Models.User", b =>
+                {
+                    b.Property<long>("Id")
+                        .ValueGeneratedOnAdd()
+                        .HasColumnName("id");
+
+                    b.Property<string>("EncryptedPassword")
+                        .IsRequired()
+                        .HasColumnName("password");
+
+                    b.Property<string>("Name")
+                        .IsRequired()
+                        .HasColumnName("name");
+
+                    b.Property<string>("RoleString")
+                        .IsRequired()
+                        .HasColumnName("roles");
+
+                    b.HasKey("Id");
+
+                    b.ToTable("user");
+                });
+#pragma warning restore 612, 618
+        }
+    }
+}
diff --git a/Timeline/Models/DatabaseContext.cs b/Timeline/Models/DatabaseContext.cs
new file mode 100644
index 00000000..1e89ea82
--- /dev/null
+++ b/Timeline/Models/DatabaseContext.cs
@@ -0,0 +1,33 @@
+using Microsoft.EntityFrameworkCore;
+using System.ComponentModel.DataAnnotations;
+using System.ComponentModel.DataAnnotations.Schema;
+
+namespace Timeline.Models
+{
+    [Table("user")]
+    public class User
+    {
+        [Column("id"), Key, DatabaseGenerated(DatabaseGeneratedOption.Identity)]
+        public long Id { get; set; }
+
+        [Column("name"), Required]
+        public string Name { get; set; }
+
+        [Column("password"), Required]
+        public string EncryptedPassword { get; set; }
+
+        [Column("roles"), Required]
+        public string RoleString { get; set; }
+    }
+
+    public class DatabaseContext : DbContext
+    {
+        public DatabaseContext(DbContextOptions<DatabaseContext> options)
+            : base(options)
+        {
+
+        }
+
+        public DbSet<User> Users { get; set; }
+    }
+}
diff --git a/Timeline/Services/JwtService.cs b/Timeline/Services/JwtService.cs
index abdde908..91e7f879 100644
--- a/Timeline/Services/JwtService.cs
+++ b/Timeline/Services/JwtService.cs
@@ -7,34 +7,25 @@ using System.Linq;
 using System.Security.Claims;
 using System.Text;
 using Timeline.Configs;
-using Timeline.Entities;
 
 namespace Timeline.Services
 {
     public interface IJwtService
     {
         /// <summary>
-        /// Create a JWT token for a given user.
-        /// Return null if <paramref name="user"/> is null.
+        /// Create a JWT token for a given user id.
         /// </summary>
-        /// <param name="user">The user to generate token.</param>
-        /// <returns>The generated token or null if <paramref name="user"/> is null.</returns>
-        string GenerateJwtToken(User user);
+        /// <param name="userId">The user id used to generate token.</param>
+        /// <returns>Return the generated token.</returns>
+        string GenerateJwtToken(long userId, string[] roles);
 
         /// <summary>
-        /// Validate a JWT token.
+        /// Verify a JWT token.
         /// Return null is <paramref name="token"/> is null.
-        /// If token is invalid, return a <see cref="TokenValidationResponse"/> with
-        /// <see cref="TokenValidationResponse.IsValid"/> set to false and
-        /// <see cref="TokenValidationResponse.UserInfo"/> set to null.
-        /// If token is valid, return a <see cref="TokenValidationResponse"/> with
-        /// <see cref="TokenValidationResponse.IsValid"/> set to true and
-        /// <see cref="TokenValidationResponse.UserInfo"/> filled with the user info
-        /// in the token.
         /// </summary>
-        /// <param name="token">The token string to validate.</param>
-        /// <returns>Null if <paramref name="token"/> is null. Or the result.</returns>
-        TokenValidationResponse ValidateJwtToken(string token);
+        /// <param name="token">The token string to verify.</param>
+        /// <returns>Return null if <paramref name="token"/> is null or token is invalid. Return the saved user id otherwise.</returns>
+        long? VerifyJwtToken(string token);
 
     }
 
@@ -50,17 +41,13 @@ namespace Timeline.Services
             _logger = logger;
         }
 
-        public string GenerateJwtToken(User user)
+        public string GenerateJwtToken(long id, string[] roles)
         {
-            if (user == null)
-                return null;
-
             var jwtConfig = _jwtConfig.CurrentValue;
 
             var identity = new ClaimsIdentity();
-            identity.AddClaim(new Claim(ClaimTypes.NameIdentifier, user.Id.ToString()));
-            identity.AddClaim(new Claim(identity.NameClaimType, user.Username));
-            identity.AddClaims(user.Roles.Select(role => new Claim(identity.RoleClaimType, role)));
+            identity.AddClaim(new Claim(ClaimTypes.NameIdentifier, id.ToString()));
+            identity.AddClaims(roles.Select(role => new Claim(identity.RoleClaimType, role)));
 
             var tokenDescriptor = new SecurityTokenDescriptor()
             {
@@ -80,7 +67,7 @@ namespace Timeline.Services
         }
 
 
-        public TokenValidationResponse ValidateJwtToken(string token)
+        public long? VerifyJwtToken(string token)
         {
             if (token == null)
                 return null;
@@ -100,24 +87,12 @@ namespace Timeline.Services
                     IssuerSigningKey = new SymmetricSecurityKey(Encoding.ASCII.GetBytes(config.SigningKey))
                 }, out SecurityToken validatedToken);
 
-                var identity = principal.Identity as ClaimsIdentity;
-
-                var userInfo = new UserInfo
-                {
-                    Username = identity.FindAll(identity.NameClaimType).Select(claim => claim.Value).Single(),
-                    Roles = identity.FindAll(identity.RoleClaimType).Select(claim => claim.Value).ToArray()
-                };
-
-                return new TokenValidationResponse
-                {
-                    IsValid = true,
-                    UserInfo = userInfo
-                };
+                return long.Parse(principal.FindAll(ClaimTypes.NameIdentifier).Single().Value);
             }
             catch (Exception e)
             {
                 _logger.LogInformation(e, "Token validation failed! Token is {} .", token);
-                return new TokenValidationResponse { IsValid = false };
+                return null;
             }
         }
     }
diff --git a/Timeline/Services/PasswordService.cs b/Timeline/Services/PasswordService.cs
new file mode 100644
index 00000000..8eab526e
--- /dev/null
+++ b/Timeline/Services/PasswordService.cs
@@ -0,0 +1,205 @@
+using Microsoft.AspNetCore.Cryptography.KeyDerivation;
+using Microsoft.Extensions.Logging;
+using System;
+using System.Runtime.CompilerServices;
+using System.Security.Cryptography;
+
+namespace Timeline.Services
+{
+    public interface IPasswordService
+    {
+        /// <summary>
+        /// Returns a hashed representation of the supplied <paramref name="password"/>.
+        /// </summary>
+        /// <param name="password">The password to hash.</param>
+        /// <returns>A hashed representation of the supplied <paramref name="password"/>.</returns>
+        string HashPassword(string password);
+
+        /// <summary>
+        /// Returns a boolean indicating the result of a password hash comparison.
+        /// </summary>
+        /// <param name="hashedPassword">The hash value for a user's stored password.</param>
+        /// <param name="providedPassword">The password supplied for comparison.</param>
+        /// <returns>True indicating success. Otherwise false.</returns>
+        bool VerifyPassword(string hashedPassword, string providedPassword);
+    }
+
+    /// <summary>
+    /// Copied from https://github.com/aspnet/AspNetCore/blob/master/src/Identity/Extensions.Core/src/PasswordHasher.cs
+    /// Remove V2 format and unnecessary format version check.
+    /// Remove configuration options.
+    /// Remove user related parts.
+    /// Add log for wrong format.
+    /// </summary>
+    public class PasswordService : IPasswordService
+    {
+        /* =======================
+        * HASHED PASSWORD FORMATS
+        * =======================
+        * 
+        * Version 3:
+        * PBKDF2 with HMAC-SHA256, 128-bit salt, 256-bit subkey, 10000 iterations.
+        * Format: { 0x01, prf (UInt32), iter count (UInt32), salt length (UInt32), salt, subkey }
+        * (All UInt32s are stored big-endian.)
+        */
+
+        private static EventId BadFormatEventId { get; } = new EventId(4000, "BadFormatPassword");
+
+        private readonly RandomNumberGenerator _rng = RandomNumberGenerator.Create();
+        private readonly ILogger<PasswordService> _logger;
+
+        public PasswordService(ILogger<PasswordService> logger)
+        {
+            _logger = logger;
+        }
+
+
+        // Compares two byte arrays for equality. The method is specifically written so that the loop is not optimized.
+        [MethodImpl(MethodImplOptions.NoInlining | MethodImplOptions.NoOptimization)]
+        private static bool ByteArraysEqual(byte[] a, byte[] b)
+        {
+            if (a == null && b == null)
+            {
+                return true;
+            }
+            if (a == null || b == null || a.Length != b.Length)
+            {
+                return false;
+            }
+            var areSame = true;
+            for (var i = 0; i < a.Length; i++)
+            {
+                areSame &= (a[i] == b[i]);
+            }
+            return areSame;
+        }
+
+        public string HashPassword(string password)
+        {
+            if (password == null)
+                throw new ArgumentNullException(nameof(password));
+            return Convert.ToBase64String(HashPasswordV3(password, _rng));
+        }
+
+        private byte[] HashPasswordV3(string password, RandomNumberGenerator rng)
+        {
+            return HashPasswordV3(password, rng,
+                prf: KeyDerivationPrf.HMACSHA256,
+                iterCount: 10000,
+                saltSize: 128 / 8,
+                numBytesRequested: 256 / 8);
+        }
+
+        private static byte[] HashPasswordV3(string password, RandomNumberGenerator rng, KeyDerivationPrf prf, int iterCount, int saltSize, int numBytesRequested)
+        {
+            // Produce a version 3 (see comment above) text hash.
+            byte[] salt = new byte[saltSize];
+            rng.GetBytes(salt);
+            byte[] subkey = KeyDerivation.Pbkdf2(password, salt, prf, iterCount, numBytesRequested);
+
+            var outputBytes = new byte[13 + salt.Length + subkey.Length];
+            outputBytes[0] = 0x01; // format marker
+            WriteNetworkByteOrder(outputBytes, 1, (uint)prf);
+            WriteNetworkByteOrder(outputBytes, 5, (uint)iterCount);
+            WriteNetworkByteOrder(outputBytes, 9, (uint)saltSize);
+            Buffer.BlockCopy(salt, 0, outputBytes, 13, salt.Length);
+            Buffer.BlockCopy(subkey, 0, outputBytes, 13 + saltSize, subkey.Length);
+            return outputBytes;
+        }
+
+        private void LogBadFormatError(string hashedPassword, string message, Exception exception = null)
+        {
+            if (_logger == null)
+                return;
+
+            if (exception != null)
+                _logger.LogError(BadFormatEventId, exception, $"{message} Hashed password is {hashedPassword} .");
+            else
+                _logger.LogError(BadFormatEventId, $"{message} Hashed password is {hashedPassword} .");
+        }
+
+        public virtual bool VerifyPassword(string hashedPassword, string providedPassword)
+        {
+            if (hashedPassword == null)
+                throw new ArgumentNullException(nameof(hashedPassword));
+            if (providedPassword == null)
+                throw new ArgumentNullException(nameof(providedPassword));
+
+            byte[] decodedHashedPassword = Convert.FromBase64String(hashedPassword);
+
+            // read the format marker from the hashed password
+            if (decodedHashedPassword.Length == 0)
+            {
+                LogBadFormatError(hashedPassword, "Decoded hashed password is of length 0.");
+                return false;
+            }
+            switch (decodedHashedPassword[0])
+            {
+                case 0x01:
+                    return VerifyHashedPasswordV3(decodedHashedPassword, providedPassword, hashedPassword);
+
+                default:
+                    LogBadFormatError(hashedPassword, "Unknown format marker.");
+                    return false; // unknown format marker
+            }
+        }
+
+        private bool VerifyHashedPasswordV3(byte[] hashedPassword, string password, string hashedPasswordString)
+        {
+            try
+            {
+                // Read header information
+                KeyDerivationPrf prf = (KeyDerivationPrf)ReadNetworkByteOrder(hashedPassword, 1);
+                int iterCount = (int)ReadNetworkByteOrder(hashedPassword, 5);
+                int saltLength = (int)ReadNetworkByteOrder(hashedPassword, 9);
+
+                // Read the salt: must be >= 128 bits
+                if (saltLength < 128 / 8)
+                {
+                    LogBadFormatError(hashedPasswordString, "Salt length < 128 bits.");
+                    return false;
+                }
+                byte[] salt = new byte[saltLength];
+                Buffer.BlockCopy(hashedPassword, 13, salt, 0, salt.Length);
+
+                // Read the subkey (the rest of the payload): must be >= 128 bits
+                int subkeyLength = hashedPassword.Length - 13 - salt.Length;
+                if (subkeyLength < 128 / 8)
+                {
+                    LogBadFormatError(hashedPasswordString, "Subkey length < 128 bits.");
+                    return false;
+                }
+                byte[] expectedSubkey = new byte[subkeyLength];
+                Buffer.BlockCopy(hashedPassword, 13 + salt.Length, expectedSubkey, 0, expectedSubkey.Length);
+
+                // Hash the incoming password and verify it
+                byte[] actualSubkey = KeyDerivation.Pbkdf2(password, salt, prf, iterCount, subkeyLength);
+                return ByteArraysEqual(actualSubkey, expectedSubkey);
+            }
+            catch (Exception e)
+            {
+                // This should never occur except in the case of a malformed payload, where
+                // we might go off the end of the array. Regardless, a malformed payload
+                // implies verification failed.
+                LogBadFormatError(hashedPasswordString, "See exception.", e);
+                return false;
+            }
+        }
+
+        private static uint ReadNetworkByteOrder(byte[] buffer, int offset)
+        {
+            return ((uint)(buffer[offset + 0]) << 24)
+                | ((uint)(buffer[offset + 1]) << 16)
+                | ((uint)(buffer[offset + 2]) << 8)
+                | ((uint)(buffer[offset + 3]));
+        }
+
+        private static void WriteNetworkByteOrder(byte[] buffer, int offset, uint value)
+        {
+            buffer[offset + 0] = (byte)(value >> 24);
+            buffer[offset + 1] = (byte)(value >> 16);
+            buffer[offset + 2] = (byte)(value >> 8);
+            buffer[offset + 3] = (byte)(value >> 0);
+        }
+    }
+}
diff --git a/Timeline/Services/UserService.cs b/Timeline/Services/UserService.cs
index 1da6922d..ad36c37b 100644
--- a/Timeline/Services/UserService.cs
+++ b/Timeline/Services/UserService.cs
@@ -1,31 +1,126 @@
-using System.Collections.Generic;
+using Microsoft.EntityFrameworkCore;
+using Microsoft.Extensions.Logging;
 using System.Linq;
+using System.Threading.Tasks;
 using Timeline.Entities;
+using Timeline.Models;
 
 namespace Timeline.Services
 {
+    public class CreateTokenResult
+    {
+        public string Token { get; set; }
+        public UserInfo UserInfo { get; set; }
+    }
+
+    public enum CreateUserResult
+    {
+        Success,
+        AlreadyExists
+    }
+
     public interface IUserService
     {
         /// <summary>
         /// Try to anthenticate with the given username and password.
+        /// If success, create a token and return the user info.
         /// </summary>
         /// <param name="username">The username of the user to be anthenticated.</param>
         /// <param name="password">The password of the user to be anthenticated.</param>
-        /// <returns><c>null</c> if anthentication failed.
-        /// An instance of <see cref="User"/> if anthentication succeeded.</returns>
-        User Authenticate(string username, string password);
+        /// <returns>Return null if anthentication failed. An <see cref="CreateTokenResult"/> containing the created token and user info if anthentication succeeded.</returns>
+        Task<CreateTokenResult> CreateToken(string username, string password);
+
+        /// <summary>
+        /// Verify the given token.
+        /// If success, return the user info.
+        /// </summary>
+        /// <param name="token">The token to verify.</param>
+        /// <returns>Return null if verification failed. The user info if verification succeeded.</returns>
+        Task<UserInfo> VerifyToken(string token);
+
+        Task<CreateUserResult> CreateUser(string username, string password, string[] roles);
     }
 
     public class UserService : IUserService
     {
-        private readonly IList<User> _users = new List<User>{
-            new User { Id = 0, Username = "admin", Password = "admin", Roles = new string[] { "User", "Admin" } },
-            new User { Id = 1, Username = "user", Password = "user", Roles = new string[] { "User"} }
-        };
+        private readonly ILogger<UserService> _logger;
+        private readonly DatabaseContext _databaseContext;
+        private readonly IJwtService _jwtService;
+        private readonly IPasswordService _passwordService;
+
+        public UserService(ILogger<UserService> logger, DatabaseContext databaseContext, IJwtService jwtService, IPasswordService passwordService)
+        {
+            _logger = logger;
+            _databaseContext = databaseContext;
+            _jwtService = jwtService;
+            _passwordService = passwordService;
+        }
 
-        public User Authenticate(string username, string password)
+        public async Task<CreateTokenResult> CreateToken(string username, string password)
         {
-            return _users.FirstOrDefault(user => user.Username == username && user.Password == password);
+            var users = _databaseContext.Users.ToList();
+
+            var user = await _databaseContext.Users.Where(u => u.Name == username).SingleOrDefaultAsync();
+
+            if (user == null)
+            {
+                _logger.LogInformation($"Create token failed with invalid username. Username = {username} Password = {password} .");
+                return null;
+            }
+
+            var verifyResult = _passwordService.VerifyPassword(user.EncryptedPassword, password);
+
+            if (verifyResult)
+            {
+                var userInfo = new UserInfo(user);
+
+                return new CreateTokenResult
+                {
+                    Token = _jwtService.GenerateJwtToken(user.Id, userInfo.Roles),
+                    UserInfo = userInfo
+                };
+            }
+            else
+            {
+                _logger.LogInformation($"Create token failed with invalid password. Username = {username} Password = {password} .");
+                return null;
+            }
+        }
+
+        public async Task<UserInfo> VerifyToken(string token)
+        {
+            var userId = _jwtService.VerifyJwtToken(token);
+
+            if (userId == null)
+            {
+                _logger.LogInformation($"Verify token falied. Reason: invalid token. Token: {token} .");
+                return null;
+            }
+
+            var user = await _databaseContext.Users.Where(u => u.Id == userId.Value).SingleOrDefaultAsync();
+
+            if (user == null)
+            {
+                _logger.LogInformation($"Verify token falied. Reason: invalid user id. UserId: {userId} Token: {token} .");
+                return null;
+            }
+
+            return new UserInfo(user);
+        }
+
+        public async Task<CreateUserResult> CreateUser(string username, string password, string[] roles)
+        {
+            var exists = (await _databaseContext.Users.Where(u => u.Name == username).ToListAsync()).Count != 0;
+
+            if (exists)
+            {
+                return CreateUserResult.AlreadyExists;
+            }
+
+            await _databaseContext.Users.AddAsync(new User { Name = username, EncryptedPassword = _passwordService.HashPassword(password), RoleString = string.Join(',', roles) });
+            await _databaseContext.SaveChangesAsync();
+
+            return CreateUserResult.Success;
         }
     }
 }
diff --git a/Timeline/Startup.cs b/Timeline/Startup.cs
index 88348892..87de7501 100644
--- a/Timeline/Startup.cs
+++ b/Timeline/Startup.cs
@@ -1,16 +1,18 @@
+using Microsoft.AspNetCore.Authentication.JwtBearer;
 using Microsoft.AspNetCore.Builder;
 using Microsoft.AspNetCore.Hosting;
+using Microsoft.AspNetCore.HttpOverrides;
 using Microsoft.AspNetCore.Mvc;
 using Microsoft.AspNetCore.SpaServices.AngularCli;
+using Microsoft.EntityFrameworkCore;
 using Microsoft.Extensions.Configuration;
 using Microsoft.Extensions.DependencyInjection;
-using Microsoft.AspNetCore.Authentication.JwtBearer;
 using Microsoft.IdentityModel.Tokens;
 using System.Text;
 using Timeline.Configs;
-using Timeline.Services;
-using Microsoft.AspNetCore.HttpOverrides;
 using Timeline.Formatters;
+using Timeline.Models;
+using Timeline.Services;
 
 namespace Timeline
 {
@@ -37,8 +39,8 @@ namespace Timeline
                 configuration.RootPath = "ClientApp/dist";
             });
 
-            services.Configure<JwtConfig>(Configuration.GetSection("JwtConfig"));
-            var jwtConfig = Configuration.GetSection("JwtConfig").Get<JwtConfig>();
+            services.Configure<JwtConfig>(Configuration.GetSection(nameof(JwtConfig)));
+            var jwtConfig = Configuration.GetSection(nameof(JwtConfig)).Get<JwtConfig>();
 
             services.AddAuthentication(JwtBearerDefaults.AuthenticationScheme)
                 .AddJwtBearer(o =>
@@ -52,8 +54,16 @@ namespace Timeline
                     o.TokenValidationParameters.IssuerSigningKey = new SymmetricSecurityKey(Encoding.ASCII.GetBytes(jwtConfig.SigningKey));
                 });
 
-            services.AddSingleton<IUserService, UserService>();
-            services.AddSingleton<IJwtService, JwtService>();
+            services.AddScoped<IUserService, UserService>();
+            services.AddScoped<IJwtService, JwtService>();
+            services.AddTransient<IPasswordService, PasswordService>();
+
+            var databaseConfig = Configuration.GetSection(nameof(DatabaseConfig)).Get<DatabaseConfig>();
+
+            services.AddDbContext<DatabaseContext>(options =>
+            {
+                options.UseMySql(databaseConfig.ConnectionString);
+            });
         }
 
         // This method gets called by the runtime. Use this method to configure the HTTP request pipeline.
diff --git a/Timeline/Timeline.csproj b/Timeline/Timeline.csproj
index e55eb90d..2958dd38 100644
--- a/Timeline/Timeline.csproj
+++ b/Timeline/Timeline.csproj
@@ -17,7 +17,9 @@
   <ItemGroup>
     <PackageReference Include="Microsoft.AspNetCore.App" />
     <PackageReference Include="Microsoft.AspNetCore.Razor.Design" Version="2.2.0" PrivateAssets="All" />
-    <PackageReference Include="Microsoft.VisualStudio.Web.CodeGeneration.Design" Version="2.2.1" />
+    <PackageReference Include="Microsoft.VisualStudio.Web.CodeGeneration.Design" Version="2.2.3" />
+    <PackageReference Include="Pomelo.EntityFrameworkCore.MySql" Version="2.2.0" />
+    <PackageReference Include="Pomelo.EntityFrameworkCore.MySql.Design" Version="1.1.2" />
   </ItemGroup>
 
   <ItemGroup>
diff --git a/Timeline/appsettings.Test.json b/Timeline/appsettings.Test.json
index b1cd5a3b..ea32348b 100644
--- a/Timeline/appsettings.Test.json
+++ b/Timeline/appsettings.Test.json
@@ -3,7 +3,9 @@
     "LogLevel": {
       "Default": "Debug",
       "System": "Information",
-      "Microsoft": "Information"
+      "Microsoft": "Information",
+      "Microsoft.AspNetCore.Authentication": "Debug",
+      "Microsoft.AspNetCore.Authorization": "Debug"
     }
   },
   "JwtConfig": {