1NAME 2 3 Dancer2::Plugin::Auth::Extensible::Provider::Database - authenticate 4 via a database 5 6DESCRIPTION 7 8 This class is an authentication provider designed to authenticate users 9 against a database, using Dancer2::Plugin::Database to access a 10 database. 11 12 Crypt::SaltedHash is used to handle hashed passwords securely; you 13 wouldn't want to store plain text passwords now, would you? (If your 14 answer to that is yes, please reconsider; you really don't want to do 15 that, when it's so easy to do things right!) 16 17 See Dancer2::Plugin::Database for how to configure a database 18 connection appropriately; see the "CONFIGURATION" section below for how 19 to configure this authentication provider with database details. 20 21 See Dancer2::Plugin::Auth::Extensible for details on how to use the 22 authentication framework, including how to pick a more useful 23 authentication provider. 24 25CONFIGURATION 26 27 This provider tries to use sensible defaults, so you may not need to 28 provide much configuration if your database tables look similar to 29 those in the "SUGGESTED SCHEMA" section below. 30 31 The most basic configuration, assuming defaults for all options, and 32 defining a single authentication realm named 'users': 33 34 plugins: 35 Auth::Extensible: 36 realms: 37 users: 38 provider: 'Database' 39 40 You would still need to have provided suitable database connection 41 details to Dancer2::Plugin::Database, of course; see the docs for that 42 plugin for full details, but it could be as simple as, e.g.: 43 44 plugins: 45 Auth::Extensible: 46 realms: 47 users: 48 provider: 'Database' 49 Database: 50 driver: 'SQLite' 51 database: 'test.sqlite' 52 on_connect_do: ['PRAGMA foreign_keys = ON'] 53 dbi_params: 54 PrintError: 0 55 RaiseError: 1 56 57 A full example showing all options: 58 59 plugins: 60 Auth::Extensible: 61 realms: 62 users: 63 provider: 'Database' 64 # optionally set DB connection name to use (see named 65 # connections in Dancer2::Plugin::Database docs) 66 db_connection_name: 'foo' 67 68 # Optionally disable roles support, if you only want to check 69 # for successful logins but don't need to use role-based access: 70 disable_roles: 1 71 72 # optionally specify names of tables if they're not the defaults 73 # (defaults are 'users', 'roles' and 'user_roles') 74 users_table: 'users' 75 roles_table: 'roles' 76 user_roles_table: 'user_roles' 77 78 # optionally set the column names (see the SUGGESTED SCHEMA 79 # section below for the default names; if you use them, they'll 80 # Just Work) 81 users_id_column: 'id' 82 users_username_column: 'username' 83 users_password_column: 'password' 84 roles_id_column: 'id' 85 roles_role_column: 'role' 86 user_roles_user_id_column: 'user_id' 87 user_roles_role_id_column: 'roles_id' 88 89 See the main Dancer2::Plugin::Auth::Extensible documentation for how to 90 configure multiple authentication realms. 91 92SUGGESTED SCHEMA 93 94 If you use a schema similar to the examples provided here, you should 95 need minimal configuration to get this authentication provider to work 96 for you. 97 98 The examples given here should be MySQL-compatible; minimal changes 99 should be required to use them with other database engines. 100 101 users table 102 103 You'll need a table to store user accounts in, of course. A suggestion 104 is something like: 105 106 CREATE TABLE users ( 107 id INTEGER AUTO_INCREMENT PRIMARY KEY, 108 username VARCHAR(32) NOT NULL UNIQUE KEY, 109 password VARCHAR(40) NOT NULL 110 ); 111 112 You will quite likely want other fields to store e.g. the user's name, 113 email address, etc; all columns from the users table will be returned 114 by the logged_in_user keyword for your convenience. 115 116 roles table 117 118 You'll need a table to store a list of available roles in (unless 119 you're not using roles - in which case, disable role support (see the 120 "CONFIGURATION" section). 121 122 CREATE TABLE roles ( 123 id INTEGER AUTO_INCREMENT PRIMARY KEY, 124 role VARCHAR(32) NOT NULL 125 ); 126 127 user_roles table 128 129 Finally, (unless you've disabled role support) you'll need a table to 130 store user <-> role mappings (i.e. one row for every role a user has; 131 so adding extra roles to a user consists of adding a new role to this 132 table). It's entirely up to you whether you use an "id" column in this 133 table; you probably shouldn't need it. 134 135 CREATE TABLE user_roles ( 136 user_id INTEGER NOT NULL, 137 role_id INTEGER NOT NULL, 138 UNIQUE KEY user_role (user_id, role_id) 139 ); 140 141 If you're using InnoDB tables rather than the default MyISAM, you could 142 add a foreign key constraint for better data integrity; see the MySQL 143 documentation for details, but a table definition using foreign keys 144 could look like: 145 146 CREATE TABLE user_roles ( 147 user_id INTEGER, FOREIGN KEY (user_id) REFERENCES users (id), 148 role_id INTEGER, FOREIGN KEY (role_id) REFERENCES roles (id), 149 UNIQUE KEY user_role (user_id, role_id) 150 ) ENGINE=InnoDB; 151 152ATTRIBUTES 153 154 dancer2_plugin_database 155 156 Lazy-loads the correct instance of Dancer2::Plugin::Database which 157 handles the following methods: 158 159 * plugin_database 160 161 This corresponds to the database keyword from 162 Dancer2::Plugin::Database. 163 164 database 165 166 The connected "plugin_database" using "db_connection_name". 167 168 db_connection_name 169 170 Optional. 171 172 users_table 173 174 Defaults to 'users'. 175 176 users_id_column 177 178 Defaults to 'id'. 179 180 users_username_column 181 182 Defaults to 'username'. 183 184 users_password_column 185 186 Defaults to 'password'. 187 188 roles_table 189 190 Defaults to 'roles'. 191 192 roles_id_column 193 194 Defaults to 'id'. 195 196 roles_role_column 197 198 Defaults to 'role'. 199 200 user_roles_table 201 202 Defaults to 'user_roles'. 203 204 user_roles_user_id_column 205 206 Defaults to 'user_id'. 207 208 user_roles_role_id_column 209 210 Defaults to 'role_id'. 211 212METHODS 213 214 authenticate_user $username, $password 215 216 create_user 217 218 get_user_details $username 219 220 get_user_roles $username 221 222 set_user_details 223 224 set_user_password 225 226COOKBOOK 227 228 Handle locked or disabled user accounts 229 230 (contributed by PerlDuck, Borodin and simbabque via Stack Overflow 231 <https://stackoverflow.com/questions/46746864>) 232 233 It's a good practice to not delete certain data, like user accounts. 234 But what do you do when you want to get rid of a user? Maybe an 235 employee left or was temporary suspended, or a user did not pay their 236 subscription fee. In those cases you would want the user data to stay 237 around, but they should not be able to log in any more. 238 239 Let's say there is a column disabled in an already existing user table. 240 It might hold a timestamp for when the user was disabled, and be NULL 241 if the user is active. By default, Dancer2::Plugin::Auth::Extensible 242 will give you this information as part of the user data, but to check 243 if the user is allowed to proceed would happen after the password has 244 been checked and they have already been logged in. 245 246 The following sections will describe two different ways of implementing 247 this. The first one is easier to implement, but only allows read 248 operations on the user table, while the second one requires a little 249 more effort, but will allow almost all operations to work. If you need 250 even more flexibility you will have to subclass and add a bit more 251 logic. 252 253 ... without changing any code 254 255 An easy way to achieve this is by adding a new view to your database 256 that only shows active users. Let's look at the following example 257 database. 258 259 -- user table 260 CREATE TABLE users ( 261 id INTEGER PRIMARY KEY AUTOINCREMENT, 262 username VARCHAR(32) NOT NULL UNIQUE, 263 password VARCHAR(40) NOT NULL, 264 disabled TIMESTAMP NULL 265 ); 266 267 -- active user view 268 CREATE VIEW active_users (id, username, password) AS 269 SELECT id, username, password FROM users WHERE disabled IS NULL; 270 271 -- some data 272 INSERT INTO users ( username, password, disabled ) 273 VALUES ( 'Alice', 'test', null), 274 ( 'Bob', 'test', '2017-10-01 10:10:10'); 275 276 Now all you need to do is change the "users_table" setting to point to 277 active_users instead of users. 278 279 # config.yml 280 plugins: 281 Auth::Extensible: 282 realms: 283 users: 284 provider: 'Database' 285 users_table: 'active_users' 286 287 That's it. Your application will now only let active users log in, 288 because it has no way of knowing about the others. Only Alice will be 289 able to log in, but Bob has been disabled and the application will not 290 allow him to log in. 291 292 But be aware that this comes with a few drawbacks. If you want to use 293 Dancer2::Plugin::Auth::Extensible to also update user information, this 294 is now no longer possible because in most database engines you cannot 295 write data into a view. 296 297 ... by creating a subclass of this database provider 298 299 The alternative is to subclass this provider to add a little bit of 300 logic. You can add code to exclude users directly when the user data is 301 fetched, even before Dancer2::Plugin::Auth::Extensible verifies the 302 password. This way, inactive users can easily be discarded. 303 304 The following code is an example implementation specifically for the 305 user table outlined in the alternative solution above. 306 307 package Provider::Database::ActiveOnly; 308 309 use Moo; 310 extends 'Dancer2::Plugin::Auth::Extensible::Provider::Database'; 311 312 around 'get_user_details' => sub { 313 my $orig = shift; 314 my $self = shift; 315 316 # do nothing if we there was no user 317 my $user = $self->$orig(@_) or return; 318 319 # do nothing if the user is disabled 320 return if $user->{disabled}; 321 322 return $user; 323 }; 324 325 1; 326 327 The code uses an around modifier from Moo to influence the 328 get_user_details method, so users that are disabled are never found. 329 330 To enable this new provider, you need to change the provider setting in 331 your configuration. 332 333 # config.yml 334 plugins: 335 Auth::Extensible: 336 realms: 337 users: 338 provider: 'Provider::Database::ActiveOnly' 339 users_table: 'users' # this is the default 340 341 With this custom subclass your application will be able to perform 342 write operations on active users, including making them inactive. 343 However, inactive users will be invisible to 344 Dancer2::Plugin::Auth::Extensible, so you cannot use this to turn 345 inactive users back on. 346 347 If you want that functionality, you will have to add a bit more logic 348 to your subclass. A possible approach could be to replace the 349 "authenticate_user" method. 350 351AUTHOR 352 353 David Precious, <davidp at preshweb.co.uk> 354 355 Dancer2 port of Dancer::Plugin::Auth::Extensible by: 356 357 Stefan Hornburg (Racke), <racke at linuxia.de> 358 359 Conversion to Dancer2's new plugin system in 2016 by: 360 361 Peter Mottram (SysPete), <peter at sysnix.com> 362 363BUGS / FEATURE REQUESTS 364 365 This is an early version; there may still be bugs present or features 366 missing. 367 368 This is developed on GitHub - please feel free to raise issues or pull 369 requests against the repo at: 370 https://github.com/PerlDancer/Dancer2-Plugin-Auth-Extensible-Provider-Database 371 372ACKNOWLEDGEMENTS 373 374 From Dancer2::Plugin::Auth::Extensible: 375 376 Valuable feedback on the early design of this module came from many 377 people, including Matt S Trout (mst), David Golden (xdg), Damien 378 Krotkine (dams), Daniel Perrett, and others. 379 380 Configurable login/logout URLs added by Rene (hertell) 381 382 Regex support for require_role by chenryn 383 384 Support for user_roles looking in other realms by Colin Ewen (casao) 385 386 LDAP provider added by Mark Meyer (ofosos) 387 388 Documentation fix by Vince Willems. 389 390 Henk van Oers (GH #8, #13). 391 392 Andrew Beverly (GH #6, #7, #10, #17, #22, #24, #25, #26). This includes 393 support for creating and editing users and manage user passwords. 394 395 Gabor Szabo (GH #11, #16, #18). 396 397 Evan Brown (GH #20, #32). 398 399 Jason Lewis (Unix provider problem, typo fix). 400 401 Yanick Champoux (typo fix). 402 403LICENSE AND COPYRIGHT 404 405 Copyright 2012-16 David Precious. Copyright 2017-19 Stefan Hornburg 406 (Racke). 407 408 This program is free software; you can redistribute it and/or modify it 409 under the terms of either: the GNU General Public License as published 410 by the Free Software Foundation; or the Artistic License. 411 412 See http://dev.perl.org/licenses/ for more information. 413 414