users table

    CREATE TABLE users (
        id       INTEGER     AUTO_INCREMENT PRIMARY KEY,
        username VARCHAR(32) NOT NULL       UNIQUE KEY,
        password VARCHAR(40) NOT NULL
    );

You will quite likely want other fields to store e.g. the user's name, email address, etc; all columns from the users table will be returned by the "logged_in_user" keyword for your convenience.

roles table

    CREATE TABLE roles (
        id    INTEGER     AUTO_INCREMENT PRIMARY KEY,
        role  VARCHAR(32) NOT NULL
    );

user_roles table

    CREATE TABLE user_roles (
        user_id  INTEGER  NOT NULL,
        role_id  INTEGER  NOT NULL,
        UNIQUE KEY user_role (user_id, role_id)
    );

If you're using InnoDB tables rather than the default MyISAM, you could add a foreign key constraint for better data integrity; see the MySQL documentation for details, but a table definition using foreign keys could look like:

    CREATE TABLE user_roles (
        user_id  INTEGER, FOREIGN KEY (user_id) REFERENCES users (id),
        role_id  INTEGER, FOREIGN KEY (role_id) REFERENCES roles (id),
        UNIQUE KEY user_role (user_id, role_id)
    ) ENGINE=InnoDB;

dancer2_plugin_database

•

plugin_database

This corresponds to the "database" keyword from Dancer2::Plugin::Database.

database

db_connection_name

users_table

users_id_column

users_username_column

users_password_column

roles_table

roles_id_column

roles_role_column

user_roles_table

user_roles_user_id_column

user_roles_role_id_column

authenticate_user $username, $password

create_user

get_user_details $username

get_user_roles $username

set_user_details

set_user_password

Handle locked or disabled user accounts

It's a good practice to not delete certain data, like user accounts. But what do you do when you want to get rid of a user? Maybe an employee left or was temporary suspended, or a user did not pay their subscription fee. In those cases you would want the user data to stay around, but they should not be able to log in any more.

Let's say there is a column "disabled" in an already existing user table. It might hold a timestamp for when the user was disabled, and be "NULL" if the user is active. By default, Dancer2::Plugin::Auth::Extensible will give you this information as part of the user data, but to check if the user is allowed to proceed would happen after the password has been checked and they have already been logged in.

The following sections will describe two different ways of implementing this. The first one is easier to implement, but only allows read operations on the user table, while the second one requires a little more effort, but will allow almost all operations to work. If you need even more flexibility you will have to subclass and add a bit more logic.

... without changing any code

An easy way to achieve this is by adding a new view to your database that only shows active users. Let's look at the following example database.

    -- user table
    CREATE TABLE users (
        id       INTEGER     PRIMARY KEY AUTOINCREMENT,
        username VARCHAR(32) NOT NULL UNIQUE,
        password VARCHAR(40) NOT NULL,
        disabled TIMESTAMP   NULL
    );

    -- active user view
    CREATE VIEW active_users (id, username, password) AS
        SELECT id, username, password FROM users WHERE disabled IS NULL;

    -- some data
    INSERT INTO users ( username, password, disabled )
    VALUES  ( 'Alice', 'test', null),
            ( 'Bob', 'test', '2017-10-01 10:10:10');

Now all you need to do is change the "users_table" setting to point to "active_users" instead of "users".

    # config.yml
    plugins:
        Auth::Extensible:
            realms:
                users:
                    provider: 'Database'
                    users_table: 'active_users'

That's it. Your application will now only let active users log in, because it has no way of knowing about the others. Only Alice will be able to log in, but Bob has been disabled and the application will not allow him to log in.

But be aware that this comes with a few drawbacks. If you want to use Dancer2::Plugin::Auth::Extensible to also update user information, this is now no longer possible because in most database engines you cannot write data into a view.

... by creating a subclass of this database provider

The alternative is to subclass this provider to add a little bit of logic. You can add code to exclude users directly when the user data is fetched, even before Dancer2::Plugin::Auth::Extensible verifies the password. This way, inactive users can easily be discarded.

The following code is an example implementation specifically for the user table outlined in the alternative solution above.

    package Provider::Database::ActiveOnly;

    use Moo;
    extends 'Dancer2::Plugin::Auth::Extensible::Provider::Database';

    around 'get_user_details' => sub {
        my $orig = shift;
        my $self = shift;

        # do nothing if we there was no user
        my $user = $self->$orig(@_) or return;

        # do nothing if the user is disabled
        return if $user->{disabled};

        return $user;
    };

    1;

The code uses an "around" modifier from Moo to influence the get_user_details method, so users that are disabled are never found.

To enable this new provider, you need to change the "provider" setting in your configuration.

    # config.yml
    plugins:
        Auth::Extensible:
            realms:
                users:
                    provider: 'Provider::Database::ActiveOnly'
                    users_table: 'users'  # this is the default

With this custom subclass your application will be able to perform write operations on active users, including making them inactive. However, inactive users will be invisible to Dancer2::Plugin::Auth::Extensible, so you cannot use this to turn inactive users back on.

If you want that functionality, you will have to add a bit more logic to your subclass. A possible approach could be to replace the "authenticate_user" method.