view release on metacpan or  search on metacpan

lib/Dancer2/Plugin/Auth/Extensible/Provider/Database.pm  view on Meta::CPAN

    # Yes, there's SQL interpolation here; yes, it makes me throw up a little.
    # However, all the variables used have been quoted appropriately above, so
    # although it might look like a camel's arsehole, at least it's safe.
    my $sql = <<QUERY;
SELECT $roles_table.$roles_role_column
FROM $user_roles_table
JOIN $roles_table 
  ON $roles_table.$roles_role_id_column 
   = $user_roles_table.$user_roles_role_id_column
WHERE $user_roles_table.$user_roles_user_id_column = ?
QUERY

    my $sth = $database->prepare($sql)
        or croak "Failed to prepare query - error: " . $database->err_str;

    $sth->execute($user->{$self->users_id_column});

    my @roles;
    while (my($role) = $sth->fetchrow_array) {
        push @roles, $role;
    }

    return \@roles;

    # If you read through this, I'm truly, truly sorry.  This mess was the price
    # of making things so configurable.  Send me your address, and I'll send you
    # a complementary fork to remove your eyeballs with as way of apology.
    # If I can bear to look at this code again, I think I might seriously
    # refactor it and use Template::Tiny or something on it.  Or Acme::Bleach.
}

=head2 set_user_details

=cut

sub set_user_details {
    my ($self, $username, %update) = @_;

    croak "Username to update needs to be specified" unless $username;

    my $user = $self->get_user_details($username) or return;

    my $ret = $self->database->quick_update( $self->users_table,
        { $self->users_username_column => $username }, \%update );
    return $ret ? $self->get_user_details($username) : undef;
}

=head2 set_user_password

=cut

sub set_user_password {
    my ( $self, $username, $password ) = @_;
    my $encrypted = $self->encrypt_password($password);
    my %update = ( $self->users_password_column => $encrypted );
    $self->set_user_details( $username, %update );
};

=head1 COOKBOOK

=head2 Handle locked or disabled user accounts

I<(contributed by PerlDuck, Borodin and simbabque
L<via Stack Overflow|https://stackoverflow.com/questions/46746864>)>

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 C<disabled> in an already existing user table.
It might hold a timestamp for when the user was disabled, and be C<NULL> if the
user is active. By default, L<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.

=head3 ... 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 L</users_table> setting to point
to C<active_users> instead of C<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 I<Alice> will be able to log in,
but I<Bob> has been disabled and the application will not allow him to log in.