@@ -, +, @@ better manage permissions for testing. -Logged in branch regression test for Intra. -OPAC password login/logout -OPAC anonymous search history preservation. => a module which consists of specific Permissions. Typically covers one Module in Koha. => a specific Permission of a Module. Granting a Patron the permission to do something. => A specific Permission a specific Patron has, eg. the permission to edit bibliographic records. -2. Note the permissions a bunch of patrons has. -1. run updatedatabase.pl which didn't have any subpermissions, a generic subpermission is added and the patrons should have it if they had the existing module permission. and login persists on other pages. Save. which doesn't have any subpermissions. --- C4/Auth.pm | 108 +++-- Koha/Auth/BorrowerPermission.pm | 221 +++++++++ Koha/Auth/BorrowerPermissions.pm | 38 ++ Koha/Auth/Permission.pm | 60 +++ Koha/Auth/PermissionManager.pm | 529 +++++++++++++++++++++ Koha/Auth/PermissionModule.pm | 78 +++ Koha/Auth/PermissionModules.pm | 38 ++ Koha/Auth/Permissions.pm | 42 ++ Koha/AuthUtils.pm | 62 ++- Koha/Patron.pm | 26 + Koha/Schema/Result/Borrower.pm | 54 ++- Koha/Schema/Result/BorrowerPermission.pm | 149 ++++++ Koha/Schema/Result/Deletedborrower.pm | 7 - Koha/Schema/Result/Permission.pm | 75 +-- Koha/Schema/Result/PermissionModule.pm | 119 +++++ .../Bug-14540-2-Delete_deletedborrowers-flags.perl | 5 + .../atomicupdate/Bug14540-PermissionManager.perl | 151 ++++++ .../data/mysql/en/optional/sample_patrons.sql | 102 ++-- installer/data/mysql/kohastructure.sql | 78 +-- installer/data/mysql/userflags.sql | 42 +- installer/data/mysql/userpermissions.sql | 170 +++---- .../prog/en/modules/members/member-flags.tt | 14 +- members/member-flags.pl | 194 ++++---- opac/opac-user.pl | 2 +- t/Patron.t | 14 +- t/db_dependent/Auth/haspermission.t | 12 +- t/db_dependent/Koha/Auth.t | 57 ++- t/db_dependent/Koha/Auth/BorrowerPermission.t | 102 ++++ t/db_dependent/Koha/Auth/PermissionManager.t | 221 +++++++++ t/db_dependent/Members/member-flags.t | 100 ++++ t/db_dependent/Opac/opac-search.t | 79 +++ 31 files changed, 2532 insertions(+), 417 deletions(-) create mode 100644 Koha/Auth/BorrowerPermission.pm create mode 100644 Koha/Auth/BorrowerPermissions.pm create mode 100644 Koha/Auth/Permission.pm create mode 100644 Koha/Auth/PermissionManager.pm create mode 100644 Koha/Auth/PermissionModule.pm create mode 100644 Koha/Auth/PermissionModules.pm create mode 100644 Koha/Auth/Permissions.pm create mode 100644 Koha/Schema/Result/BorrowerPermission.pm create mode 100644 Koha/Schema/Result/PermissionModule.pm create mode 100644 installer/data/mysql/atomicupdate/Bug-14540-2-Delete_deletedborrowers-flags.perl create mode 100644 installer/data/mysql/atomicupdate/Bug14540-PermissionManager.perl create mode 100644 t/db_dependent/Koha/Auth/BorrowerPermission.t create mode 100644 t/db_dependent/Koha/Auth/PermissionManager.t create mode 100644 t/db_dependent/Members/member-flags.t create mode 100644 t/db_dependent/Opac/opac-search.t --- a/C4/Auth.pm +++ a/C4/Auth.pm @@ -24,6 +24,8 @@ use File::Spec; use JSON qw/encode_json/; use URI::Escape; use CGI::Session; +use Scalar::Util qw(blessed); +use Try::Tiny; require Exporter; use C4::Context; @@ -195,7 +197,7 @@ sub get_template_and_user { $template->param( loginprompt => 1, - script_name => get_script_name(), + script_name => Koha::AuthUtils::get_script_name(), ); print $in->{query}->header( { type => 'text/html', @@ -265,7 +267,7 @@ sub get_template_and_user { # We are going to use the $flags returned by checkauth # to create the template's parameters that will indicate # which menus the user can access. - if ( $flags && $flags->{superlibrarian} == 1 ) { + if ( $flags && $flags->{superlibrarian} ) { $template->param( CAN_user_circulate => 1 ); $template->param( CAN_user_catalogue => 1 ); $template->param( CAN_user_parameters => 1 ); @@ -294,11 +296,7 @@ sub get_template_and_user { if ($flags) { foreach my $module ( keys %$all_perms ) { - if ( defined($flags->{$module}) && $flags->{$module} == 1 ) { - foreach my $subperm ( keys %{ $all_perms->{$module} } ) { - $template->param( "CAN_user_${module}_${subperm}" => 1 ); - } - } elsif ( ref( $flags->{$module} ) ) { + if ( ref( $flags->{$module} ) ) { foreach my $subperm ( keys %{ $flags->{$module} } ) { $template->param( "CAN_user_${module}_${subperm}" => 1 ); } @@ -1022,6 +1020,9 @@ sub checkauth { # $return: 1 = valid user, 2 = superlibrarian if ($return) { + # Koha-Suomi permission rewrite fix for database user authentication + $userid = $q_userid if $return == 2; + #_session_log(sprintf "%20s from %16s logged in at %30s.\n", $userid,$ENV{'REMOTE_ADDR'},(strftime '%c', localtime)); if ( $flags = haspermission( $userid, $flagsrequired ) ) { $loggedin = 1; @@ -1035,7 +1036,7 @@ sub checkauth { if ( $return == 1 ) { my $select = " - SELECT borrowernumber, firstname, surname, flags, borrowers.branchcode, + SELECT borrowernumber, firstname, surname, borrowers.branchcode, branches.branchname as branchname, branches.branchprinter as branchprinter, email @@ -1058,7 +1059,7 @@ sub checkauth { } } if ( $sth->rows ) { - ( $borrowernumber, $firstname, $surname, $userflags, + ( $borrowernumber, $firstname, $surname, $branchcode, $branchname, $branchprinter, $emailaddress ) = $sth->fetchrow; $debug and print STDERR "AUTH_3 results: " . "$cardnumber,$borrowernumber,$userid,$firstname,$surname,$userflags,$branchcode,$emailaddress\n"; @@ -1218,7 +1219,7 @@ sub checkauth { opaclayoutstylesheet => C4::Context->preference("opaclayoutstylesheet"), login => 1, INPUTS => \@inputs, - script_name => get_script_name(), + script_name => Koha::AuthUtils::get_script_name(), casAuthentication => C4::Context->preference("casAuthentication"), shibbolethAuthentication => $shib, SessionRestrictionByIP => C4::Context->preference("SessionRestrictionByIP"), @@ -1815,35 +1816,35 @@ sub checkpw_internal { my $sth = $dbh->prepare( - "select password,cardnumber,borrowernumber,userid,firstname,surname,borrowers.branchcode,branches.branchname,flags from borrowers join branches on borrowers.branchcode=branches.branchcode where userid=?" + "select password,cardnumber,borrowernumber,userid,firstname,surname,borrowers.branchcode,branches.branchname from borrowers join branches on borrowers.branchcode=branches.branchcode where userid=?" ); $sth->execute($userid); if ( $sth->rows ) { my ( $stored_hash, $cardnumber, $borrowernumber, $userid, $firstname, - $surname, $branchcode, $branchname, $flags ) + $surname, $branchcode, $branchname ) = $sth->fetchrow; if ( checkpw_hash( $password, $stored_hash ) ) { C4::Context->set_userenv( "$borrowernumber", $userid, $cardnumber, - $firstname, $surname, $branchcode, $branchname, $flags ) unless $no_set_userenv; + $firstname, $surname, $branchcode, $branchname ) unless $no_set_userenv; return 1, $cardnumber, $userid; } } $sth = $dbh->prepare( - "select password,cardnumber,borrowernumber,userid,firstname,surname,borrowers.branchcode,branches.branchname,flags from borrowers join branches on borrowers.branchcode=branches.branchcode where cardnumber=?" + "select password,cardnumber,borrowernumber,userid,firstname,surname,borrowers.branchcode,branches.branchname from borrowers join branches on borrowers.branchcode=branches.branchcode where cardnumber=?" ); $sth->execute($userid); if ( $sth->rows ) { my ( $stored_hash, $cardnumber, $borrowernumber, $userid, $firstname, - $surname, $branchcode, $branchname, $flags ) + $surname, $branchcode, $branchname ) = $sth->fetchrow; if ( checkpw_hash( $password, $stored_hash ) ) { C4::Context->set_userenv( $borrowernumber, $userid, $cardnumber, - $firstname, $surname, $branchcode, $branchname, $flags ) unless $no_set_userenv; + $firstname, $surname, $branchcode, $branchname ) unless $no_set_userenv; return 1, $cardnumber, $userid; } } @@ -1867,7 +1868,7 @@ sub checkpw_hash { # check what encryption algorithm was implemented: Bcrypt - if the hash starts with '$2' it is Bcrypt else md5 my $hash; if ( substr( $stored_hash, 0, 2 ) eq '$2' ) { - $hash = hash_password( $password, $stored_hash ); + $hash = Koha::AuthUtils::hash_password( $password, $stored_hash ); } else { $hash = md5_base64($password); } @@ -1875,6 +1876,7 @@ sub checkpw_hash { } =head2 getuserflags +@DEPRECATED, USE THE Koha::Auth::PermissionManager my $authflags = getuserflags($flags, $userid, [$dbh]); @@ -1887,6 +1889,7 @@ C<$authflags> is a hashref of permissions =cut sub getuserflags { + #@DEPRECATED, USE THE Koha::Auth::PermissionManager my $flags = shift; my $userid = shift; my $dbh = @_ ? shift : C4::Context->dbh; @@ -1898,6 +1901,8 @@ sub getuserflags { no warnings 'numeric'; $flags += 0; } + return get_user_subpermissions($userid); + #@DEPRECATED, USE THE Koha::Auth::PermissionManager my $sth = $dbh->prepare("SELECT bit, flag, defaulton FROM userflags"); $sth->execute; @@ -1921,7 +1926,7 @@ sub getuserflags { } =head2 get_user_subpermissions - +@DEPRECATED, USE THE Koha::Auth::PermissionManager $user_perm_hashref = get_user_subpermissions($userid); Given the userid (note, not the borrowernumber) of a staff user, @@ -1946,21 +1951,30 @@ necessary to check borrowers.flags. =cut sub get_user_subpermissions { + #@DEPRECATED, USE THE Koha::Auth::PermissionManager my $userid = shift; - - my $dbh = C4::Context->dbh; - my $sth = $dbh->prepare( "SELECT flag, user_permissions.code - FROM user_permissions - JOIN permissions USING (module_bit, code) - JOIN userflags ON (module_bit = bit) - JOIN borrowers USING (borrowernumber) - WHERE userid = ?" ); - $sth->execute($userid); + return {} unless $userid; my $user_perms = {}; - while ( my $perm = $sth->fetchrow_hashref ) { - $user_perms->{ $perm->{'flag'} }->{ $perm->{'code'} } = 1; - } + try { + use Koha::Auth::PermissionManager; + my $permissionManager = Koha::Auth::PermissionManager->new(); + my $borrowerPermissions = $permissionManager->getBorrowerPermissions($userid); #Prefetch all related tables. + foreach my $perm ( @$borrowerPermissions ) { + $user_perms->{ $perm->getPermissionModule->module }->{ $perm->getPermission->code } = 1; + } + } catch { + if (blessed($_) && $_->isa('Koha::Exception::UnknownObject')) { + return $user_perms; + } + elsif (blessed($_)) { + $_->rethrow(); + } + else { + die $_; + } + }; + return $user_perms; } @@ -1977,6 +1991,20 @@ of the subpermission. =cut sub get_all_subpermissions { + #@DEPRECATED, USE THE Koha::Auth::PermissionManager + use Koha::Auth::PermissionManager; + my $permissionManager = Koha::Auth::PermissionManager->new(); + my $all_permissions = $permissionManager->listKohaPermissionsAsHASH(); + foreach my $module ( keys %$all_permissions ) { + my $permissionModule = $all_permissions->{$module}; + foreach my $code (keys %{$permissionModule->{permissions}}) { + my $permission = $permissionModule->{permissions}->{$code}; + $all_permissions->{$module}->{$code} = $permission->{'description'}; + } + } + return $all_permissions; + + #@DEPRECATED, USE THE Koha::Auth::PermissionManager my $dbh = C4::Context->dbh; my $sth = $dbh->prepare( "SELECT flag, code FROM permissions @@ -1991,7 +2019,7 @@ sub get_all_subpermissions { } =head2 haspermission - +@DEPRECATED, USE THE Koha::Auth::PermissionManager $flags = ($userid, $flagsrequired); C<$userid> the userid of the member @@ -2002,17 +2030,21 @@ Returns member's flags or 0 if a permission is not met. =cut sub haspermission { + #@DEPRECATED, USE THE Koha::Auth::PermissionManager my ( $userid, $flagsrequired ) = @_; - my $sth = C4::Context->dbh->prepare("SELECT flags FROM borrowers WHERE userid=?"); - $sth->execute($userid); - my $row = $sth->fetchrow(); - my $flags = getuserflags( $row, $userid ); - if ( $userid eq C4::Context->config('user') ) { + + my $flags = getuserflags( undef, $userid ); + #Sanitate 1 to * because we no longer have 1's from the koha.borrowers.flags. + foreach my $module (%$flagsrequired) { + $flagsrequired->{$module} = '*' if $flagsrequired->{$module} && $flagsrequired->{$module} eq '1'; + } + + if ( defined $userid && $userid eq C4::Context->config('user') ) { # Super User Account from /etc/koha.conf $flags->{'superlibrarian'} = 1; } - elsif ( $userid eq 'demo' && C4::Context->config('demo') ) { + elsif ( defined $userid && $userid eq 'demo' && C4::Context->config('demo') ) { # Demo user that can do "anything" (demo=1 in /etc/koha.conf) $flags->{'superlibrarian'} = 1; @@ -2023,7 +2055,7 @@ sub haspermission { foreach my $module ( keys %$flagsrequired ) { my $subperm = $flagsrequired->{$module}; if ( $subperm eq '*' ) { - return 0 unless ( $flags->{$module} == 1 or ref( $flags->{$module} ) ); + return 0 unless ( ref( $flags->{$module} ) ); } else { return 0 unless ( ( defined $flags->{$module} and --- a/Koha/Auth/BorrowerPermission.pm +++ a/Koha/Auth/BorrowerPermission.pm @@ -0,0 +1,221 @@ +package Koha::Auth::BorrowerPermission; + +# Copyright 2015 Vaara-kirjastot +# +# This file is part of Koha. +# +# Koha is free software; you can redistribute it and/or modify it +# under the terms of the GNU General Public License as published by +# the Free Software Foundation; either version 3 of the License, or +# (at your option) any later version. +# +# Koha is distributed in the hope that it will be useful, but +# WITHOUT ANY WARRANTY; without even the implied warranty of +# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the +# GNU General Public License for more details. +# +# You should have received a copy of the GNU General Public License +# along with Koha; if not, see . + +use Modern::Perl; +use Scalar::Util qw(blessed); + +use Koha::Auth::BorrowerPermissions; +use Koha::Auth::PermissionModules; +use Koha::Auth::Permissions; +use Koha::Patrons; + +use Koha::Exception::BadParameter; + +use base qw(Koha::Object); + +sub _type { + return 'BorrowerPermission'; +} +sub object_class { + return 'Koha::Auth::BorrowerPermission'; +} + +=head NAME + +Koha::Auth::BorrowerPermission + +=head SYNOPSIS + +Object representation of a Permission given to a Borrower. + +=head new + + my $borrowerPermission = Koha::Auth::BorrowerPermission->new({ + borrowernumber => 12, + permission_module_id => 2, + permission => $Koha::Auth::Permission, + }); + my $borrowerPermission = Koha::Auth::BorrowerPermission->new({ + borrower => $Koha::Patron, + permissionModule => $Koha::Auth::PermissionModule, + permission_id => 22, + }); + +Remember to ->store() the returned object to persist it in the DB. +@PARAM1 HASHRef of constructor parameters: + MANDATORY keys: + borrower or borrowernumber + permissionModule or permission_module_id + permission or permission_id + Values can be either Koha::Object derivatives or their respective DB primary keys +@RETURNS Koha::Auth::BorrowerPermission +=cut + +sub new { + my ($class, $params) = @_; + + _validateParams($params); + + #Check for duplicates, and update existing permission if available. + my $self = Koha::Auth::BorrowerPermissions->find({borrowernumber => $params->{borrower}->borrowernumber, + permission_module_id => $params->{permissionModule}->permission_module_id, + permission_id => $params->{permission}->permission_id, + }); + $self = $class->SUPER::new() unless $self; + $self->{params} = $params; + $self->set({borrowernumber => $self->getBorrower()->borrowernumber, + permission_id => $self->getPermission()->permission_id, + permission_module_id => $self->getPermissionModule()->permission_module_id + }); + return $self; +} + +=head getBorrower + + my $borrower = $borrowerPermission->getBorrower(); + +@RETURNS Koha::Patron +=cut + +sub getBorrower { + my ($self) = @_; + + unless ($self->{params}->{borrower}) { + my $dbix_borrower = $self->_result()->borrower; + my $borrower = Koha::Patron->_new_from_dbic($dbix_borrower); + $self->{params}->{borrower} = $borrower; + } + return $self->{params}->{borrower}; +} + +=head setBorrower + + my $borrowerPermission = $borrowerPermission->setBorrower( $borrower ); + +Set the Borrower. +When setting the DB is automatically updated as well. +@PARAM1 Koha::Patron, set the given Borrower to this BorrowerPermission. +@RETURNS Koha::Auth::BorrowerPermission, +=cut + +sub setBorrower { + my ($self, $borrower) = @_; + + unless (blessed($borrower) && $borrower->isa('Koha::Patron')) { + Koha::Exception::BadParameter->throw(error => __PACKAGE__."->setPermissionModule():> Given parameter '\\$borrower' is not a Koha::Patron-object!"); + } + $self->{params}->{borrower} = $borrower; + $self->set({borrowernumber => $borrower->borrowernumber()}); + $self->store(); +} + +=head getPermissionModule + + my $permissionModule = $borrowerPermission->getPermissionModule(); + +@RETURNS Koha::Auth::PermissionModule +=cut + +sub getPermissionModule { + my ($self) = @_; + + unless ($self->{params}->{permissionModule}) { + my $dbix_object = $self->_result()->permission_module; + my $object = Koha::Auth::PermissionModule->_new_from_dbic($dbix_object); + $self->{params}->{permissionModule} = $object; + } + return $self->{params}->{permissionModule}; +} + +=head setPermissionModule + + my $borrowerPermission = $borrowerPermission->setPermissionModule( $permissionModule ); + +Set the PermissionModule. +When setting the DB is automatically updated as well. +@PARAM1 Koha::Auth::PermissionModule, set the given PermissionModule as + the PermissionModule of this BorrowePermission. +@RETURNS Koha::Auth::BorrowerPermission, +=cut + +sub setPermissionModule { + my ($self, $permissionModule) = @_; + + unless (blessed($permissionModule) && $permissionModule->isa('Koha::Auth::PermissionModule')) { + Koha::Exception::BadParameter->throw(error => __PACKAGE__."->setPermissionModule():> Given parameter '\$permissionModule' is not a Koha::Auth::PermissionModule-object!"); + } + $self->{params}->{permissionModule} = $permissionModule; + $self->set({permission_module_id => $permissionModule->permission_module_id()}); + $self->store(); +} + +=head getPermission + + my $permission = $borrowerPermission->getPermission(); + +@RETURNS Koha::Auth::Permission +=cut + +sub getPermission { + my ($self) = @_; + + unless ($self->{params}->{permission}) { + my $dbix_object = $self->_result()->permission; + my $object = Koha::Auth::Permission->_new_from_dbic($dbix_object); + $self->{params}->{permission} = $object; + } + return $self->{params}->{permission}; +} + +=head setPermission + + my $borrowerPermission = $borrowerPermission->setPermission( $permission ); + +Set the Permission. +When setting the DB is automatically updated as well. +@PARAM1 Koha::Auth::Permission, set the given Permission to this BorrowerPermission. +@RETURNS Koha::Auth::BorrowerPermission, +=cut + +sub setPermission { + my ($self, $permission) = @_; + + unless (blessed($permission) && $permission->isa('Koha::Auth::Permission')) { + Koha::Exception::BadParameter->throw(error => __PACKAGE__."->setPermission():> Given parameter '\$permission' is not a Koha::Auth::Permission-object!"); + } + $self->{params}->{permission} = $permission; + $self->set({permission_id => $permission->permission_id()}); + $self->store(); +} + +=head _validateParams + +Validates the given constructor parameters and fetches the Koha::Objects when needed. + +=cut + +sub _validateParams { + my ($params) = @_; + + $params->{permissionModule} = Koha::Auth::PermissionModules->cast( $params->{permission_module_id} || $params->{permissionModule} ); + $params->{permission} = Koha::Auth::Permissions->cast( $params->{permission_id} || $params->{permission} ); + $params->{borrower} = Koha::Patrons->cast( $params->{borrowernumber} || $params->{borrower} ); +} + +1; --- a/Koha/Auth/BorrowerPermissions.pm +++ a/Koha/Auth/BorrowerPermissions.pm @@ -0,0 +1,38 @@ +package Koha::Auth::BorrowerPermissions; + +# Copyright 2015 Vaara-kirjastot +# +# This file is part of Koha. +# +# Koha is free software; you can redistribute it and/or modify it +# under the terms of the GNU General Public License as published by +# the Free Software Foundation; either version 3 of the License, or +# (at your option) any later version. +# +# Koha is distributed in the hope that it will be useful, but +# WITHOUT ANY WARRANTY; without even the implied warranty of +# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the +# GNU General Public License for more details. +# +# You should have received a copy of the GNU General Public License +# along with Koha; if not, see . + +use Modern::Perl; +use Scalar::Util qw(blessed); + +use Koha::Auth::BorrowerPermission; + +use base qw(Koha::Objects); + +sub _type { + return 'BorrowerPermission'; +} +sub object_class { + return 'Koha::Auth::BorrowerPermission'; +} + +sub _get_castable_unique_columns { + return ['borrower_permission_id']; +} + +1; --- a/Koha/Auth/Permission.pm +++ a/Koha/Auth/Permission.pm @@ -0,0 +1,60 @@ +package Koha::Auth::Permission; + +# Copyright 2015 Vaara-kirjastot +# +# This file is part of Koha. +# +# Koha is free software; you can redistribute it and/or modify it +# under the terms of the GNU General Public License as published by +# the Free Software Foundation; either version 3 of the License, or +# (at your option) any later version. +# +# Koha is distributed in the hope that it will be useful, but +# WITHOUT ANY WARRANTY; without even the implied warranty of +# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the +# GNU General Public License for more details. +# +# You should have received a copy of the GNU General Public License +# along with Koha; if not, see . + +use Modern::Perl; + +use Koha::Auth::Permissions; + +use Koha::Exception::BadParameter; + +use base qw(Koha::Object); + +sub _type { + return 'Permission'; +} +sub object_class { + return 'Koha::Auth::Permission'; +} + +sub new { + my ($class, $params) = @_; + + _validateParams($params); + + my $self = Koha::Auth::Permissions->find({code => $params->{code}, module => $params->{module}}); + $self = $class->SUPER::new() unless $self; + $self->set($params); + return $self; +} + +sub _validateParams { + my ($params) = @_; + + unless ($params->{description} && length $params->{description} > 0) { + Koha::Exception::BadParameter->throw(error => "Koha::Auth::Permission->new():> Parameter 'description' isn't defined or is empty."); + } + unless ($params->{module} && length $params->{module} > 0) { + Koha::Exception::BadParameter->throw(error => "Koha::Auth::Permission->new():> Parameter 'module' isn't defined or is empty."); + } + unless ($params->{code} && length $params->{code} > 0) { + Koha::Exception::BadParameter->throw(error => "Koha::Auth::Permission->new():> Parameter 'code' isn't defined or is empty."); + } +} + +1; --- a/Koha/Auth/PermissionManager.pm +++ a/Koha/Auth/PermissionManager.pm @@ -0,0 +1,529 @@ +package Koha::Auth::PermissionManager; + +# Copyright 2015 Vaara-kirjastot +# +# This file is part of Koha. +# +# Koha is free software; you can redistribute it and/or modify it +# under the terms of the GNU General Public License as published by +# the Free Software Foundation; either version 3 of the License, or +# (at your option) any later version. +# +# Koha is distributed in the hope that it will be useful, but +# WITHOUT ANY WARRANTY; without even the implied warranty of +# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the +# GNU General Public License for more details. +# +# You should have received a copy of the GNU General Public License +# along with Koha; if not, see . + +use Modern::Perl; +use Scalar::Util qw(blessed); +use Try::Tiny; + +use Koha::Database; +use Koha::Auth::Permission; +use Koha::Auth::PermissionModule; +use Koha::Auth::BorrowerPermission; +use Koha::Auth::BorrowerPermissions; + +use Koha::Exception::BadParameter; +use Koha::Exception::NoPermission; +use Koha::Exception::UnknownProgramState; + +=head NAME Koha::Auth::PermissionManager + +=head SYNOPSIS + +PermissionManager is a gateway to all Koha's permission operations. You shouldn't +need to touch individual Koha::Auth::Permission* -objects. + +=head USAGE + +See t::db_dependent::Koha::Auth::PermissionManager.t + +=head new + + my $permissionManager = Koha::Auth::PermissionManager->new(); + +Instantiates a new PemissionManager. + +In the future this Manager can easily be improved with Koha::Cache. + +=cut + +sub new { + my ($class, $self) = @_; + $self = {} unless $self; + bless($self, $class); + return $self; +} + +=head addPermission + + $permissionManager->addPermission({ code => "end_remaining_hostilities", + description => "All your base are belong to us", + }); + +INSERTs or UPDATEs a Koha::Auth::Permission to the Koha DB. +Very handy when introducing new features that need new permissions. + +@PARAM1 Koha::Auth::Permission + or + HASHRef of all the koha.permissions-table columns set. +@THROWS Koha::Exception::BadParameter +=cut + +sub addPermission { + my ($self, $permission) = @_; + if (blessed($permission) && not($permission->isa('Koha::Auth::Permission'))) { + Koha::Exception::BadParameter->throw(error => __PACKAGE__."::addPermission():> Given permission is not a Koha::Auth::Permission-object."); + } + elsif (ref($permission) eq 'HASH') { + $permission = Koha::Auth::Permission->new($permission); + } + unless (blessed($permission)) { + Koha::Exception::BadParameter->throw(error => __PACKAGE__."::addPermission():> Given permission '$permission' is not of a recognized format."); + } + + $permission->store(); +} + +=head getPermission + + my $permission = $permissionManager->getPermission('edit_items'); #koha.permissions.code + my $permission = $permissionManager->getPermission(12); #koha.permissions.permission_id + my $permission = $permissionManager->getPermission($dbix_Permission); #Koha::Schema::Result::Permission + +@RETURNS Koha::Auth::Permission-object +@THROWS Koha::Exception::BadParameter +=cut + +sub getPermission { + my ($self, $permissionId) = @_; + + try { + return Koha::Auth::Permissions->cast($permissionId); + } catch { + if (blessed($_) && $_->isa('Koha::Exception::UnknownObject')) { + #We catch this type of exception, and simply return nothing, since there was no such Permission + } + else { + die $_; + } + }; +} + +=head delPermission + + $permissionManager->delPermission('edit_items'); #koha.permissions.code + $permissionManager->delPermission(12); #koha.permissions.permission_id + $permissionManager->delPermission($dbix_Permission); #Koha::Schema::Result::Permission + $permissionManager->delPermission($permission); #Koha::Auth::Permission + +@THROWS Koha::Exception::UnknownObject if no given object in DB to delete. +=cut + +sub delPermission { + my ($self, $permissionId) = @_; + + my $permission = Koha::Auth::Permissions->cast($permissionId); + $permission->delete(); +} + +=head addPermissionModule + + $permissionManager->addPermissionModule({ module => "scotland", + description => "William Wallace is my hero!", + }); + +INSERTs or UPDATEs a Koha::Auth::PermissionModule to the Koha DB. +Very handy when introducing new features that need new permissions. + +@PARAM1 Koha::Auth::PermissionModule + or + HASHRef of all the koha.permission_modules-table columns set. +@THROWS Koha::Exception::BadParameter +=cut + +sub addPermissionModule { + my ($self, $permissionModule) = @_; + if (blessed($permissionModule) && not($permissionModule->isa('Koha::Auth::PermissionModule'))) { + Koha::Exception::BadParameter->throw(error => __PACKAGE__."::addPermission():> Given permissionModule is not a Koha::Auth::PermissionModule-object."); + } + elsif (ref($permissionModule) eq 'HASH') { + $permissionModule = Koha::Auth::PermissionModule->new($permissionModule); + } + unless (blessed($permissionModule)) { + Koha::Exception::BadParameter->throw(error => __PACKAGE__."::addPermission():> Given permissionModule '$permissionModule' is not of a recognized format."); + } + + $permissionModule->store(); +} + +=head getPermissionModule + + my $permission = $permissionManager->getPermissionModule('cataloguing'); #koha.permission_modules.module + my $permission = $permissionManager->getPermission(12); #koha.permission_modules.permission_module_id + my $permission = $permissionManager->getPermission($dbix_Permission); #Koha::Schema::Result::PermissionModule + +@RETURNS Koha::Auth::PermissionModule-object +@THROWS Koha::Exception::BadParameter +=cut + +sub getPermissionModule { + my ($self, $permissionModuleId) = @_; + + try { + return Koha::Auth::PermissionModules->cast($permissionModuleId); + } catch { + if (blessed($_) && $_->isa('Koha::Exception::UnknownObject')) { + #We catch this type of exception, and simply return nothing, since there was no such PermissionModule + } + else { + die $_; + } + }; +} + +=head delPermissionModule + + $permissionManager->delPermissionModule('cataloguing'); #koha.permission_modules.module + $permissionManager->delPermissionModule(12); #koha.permission_modules.permission_module_id + $permissionManager->delPermissionModule($dbix_Permission); #Koha::Schema::Result::PermissionModule + $permissionManager->delPermissionModule($permissionModule); #Koha::Auth::PermissionModule + +@THROWS Koha::Exception::UnknownObject if no given object in DB to delete. +@THROWS Koha::Exception::BadParameter +=cut + +sub delPermissionModule { + my ($self, $permissionModuleId) = @_; + + my $permissionModule = Koha::Auth::PermissionModules->cast($permissionModuleId); + $permissionModule->delete(); +} + +=head getKohaPermissions + + my $kohaPermissions = $permissionManager->getKohaPermissions(); + +Gets all the PermissionModules and their related Permissions in one huge DB query. +@RETURNS ARRAYRef of Koha::Auth::PermissionModule-objects with related objects prefetched. +=cut + +sub getKohaPermissions { + my ($self) = @_; + + my $schema = Koha::Database->new()->schema(); + my @permissionModules = $schema->resultset('PermissionModule')->search( + {}, + { join => ['permissions'], + prefetch => ['permissions'], + order_by => ['me.module', 'permissions.code'], + } + ); + #Cast DBIx to Koha::Object. + for (my $i=0 ; $icast( $permissionModules[$i] ); + } + return \@permissionModules; +} + +=head listKohaPermissionsAsHASH + +@RETURNS HASHRef, a HASH-representation of all the permissions and permission modules +in Koha. Eg: + { + acquisitions => { + description => "Yada yada", + module => 'acquisitions', + permission_module_id => 21, + permissions => { + budget_add_del => { + description => "More yada yada", + code => 'budget_add_del', + permission_id => 12, + } + budget_manage => { + description => "Yaawn yadayawn", + ... + } + ... + } + }, + borrowers => { + ... + }, + ... + } +=cut + +sub listKohaPermissionsAsHASH { + my ($self) = @_; + my $permissionModules = $self->getKohaPermissions(); + my $hash = {}; + + foreach my $permissionModule (sort {$a->module cmp $b->module} @$permissionModules) { + my $module = $permissionModule->module; + + $hash->{$module} = $permissionModule->_result->{'_column_data'}; + $hash->{$module}->{permissions} = {}; + + my $permissions = $permissionModule->getPermissions; + foreach my $permission (sort {$a->code cmp $b->code} @$permissions) { + my $code = $permission->code; + + $hash->{$module}->{permissions}->{$code} = $permission->_result->{'_column_data'}; + } + } + return $hash; +} + +=head getBorrowerPermissions + + my $borrowerPermissions = $permissionManager->getBorrowerPermissions($borrower); #Koha::Patron + my $borrowerPermissions = $permissionManager->getBorrowerPermissions($dbix_borrower);#Koha::Schema::Resultset::Borrower + my $borrowerPermissions = $permissionManager->getBorrowerPermissions(1012); #koha.borrowers.borrowernumber + my $borrowerPermissions = $permissionManager->getBorrowerPermissions('167A0012311'); #koha.borrowers.cardnumber + my $borrowerPermissions = $permissionManager->getBorrowerPermissions('bill69'); #koha.borrowers.userid + +@RETURNS ARRAYRef of Koha::Auth::BorrowerPermission-objects +@THROWS Koha::Exception::UnknownObject, if the given $borrower cannot be casted to Koha::Patron +@THROWS Koha::Exception::BadParameter +=cut + +sub getBorrowerPermissions { + my ($self, $borrower) = @_; + $borrower = Koha::Patrons->cast($borrower); + + if ($borrower->isSuperuser()) { + return [Koha::Auth::BorrowerPermission->new({borrower => $borrower, permission => 'superlibrarian', permissionModule => 'superlibrarian'})]; + } + + my $schema = Koha::Database->new()->schema(); + + my @borrowerPermissions = $schema->resultset('BorrowerPermission')->search({borrowernumber => $borrower->borrowernumber}, + {join => ['permission','permission_module'], + prefetch => ['permission','permission_module'], + order_by => ['permission_module.module', 'permission.code']}); + for (my $i=0 ; $icast($borrowerPermissions[$i]); + } + return \@borrowerPermissions; +} + +=head grantPermissions + + $permissionManager->grantPermissions($borrower, {borrowers => 'view_borrowers', + reserveforothers => ['place_holds'], + tools => ['edit_news', 'edit_notices'], + acquisition => { + budger_add_del => 1, + budget_modify => 1, + }, + } + ); + +Adds a group of permissions to one user. +@THROWS Koha::Exception::UnknownObject, if the given $borrower cannot be casted to Koha::Patron +@THROWS Koha::Exception::BadParameter +=cut + +sub grantPermissions { + my ($self, $borrower, $permissionsGroup) = @_; + + while (my ($module, $permissions) = each(%$permissionsGroup)) { + if (ref($permissions) eq 'ARRAY') { + foreach my $permission (@$permissions) { + $self->grantPermission($borrower, $module, $permission); + } + } + elsif (ref($permissions) eq 'HASH') { + foreach my $permission (keys(%$permissions)) { + $self->grantPermission($borrower, $module, $permission); + } + } + else { + $self->grantPermission($borrower, $module, $permissions); + } + } +} + +=head grantPermission + + my $borrowerPermission = $permissionManager->grantPermission($borrower, $permissionModule, $permission); + +@PARAM1 Koha::Patron or + Scalar koha.borrowers.borrowernumber or + Scalar koha.borrowers.cardnumber or + Scalar koha.borrowers.userid or +@PARAM2 Koha::Auth::PermissionModule-object + Scalar koha.permission_modules.module or + Scalar koha.permission_modules.permission_module_id +@PARAM3 Koha::Auth::Permission-object or + Scalar koha.permissions.code or + Scalar koha.permissions.permission_id +@RETURNS Koha::Auth::BorrowerPermissions +@THROWS Koha::Exception::UnknownObject, if the given parameters cannot be casted to Koha::Object-subclasses +@THROWS Koha::Exception::BadParameter +=cut + +sub grantPermission { + my ($self, $borrower, $permissionModule, $permission) = @_; + + my $borrowerPermission = Koha::Auth::BorrowerPermission->new({borrower => $borrower, permissionModule => $permissionModule, permission => $permission}); + $borrowerPermission->store(); + return $borrowerPermission; +} + +=head + + $permissionManager->revokePermission($borrower, $permissionModule, $permission); + +Revokes a Permission from a Borrower +same parameters as grantPermission() + +@THROWS Koha::Exception::UnknownObject, if the given parameters cannot be casted to Koha::Object-subclasses +@THROWS Koha::Exception::BadParameter +=cut + +sub revokePermission { + my ($self, $borrower, $permissionModule, $permission) = @_; + + my $borrowerPermission = Koha::Auth::BorrowerPermission->new({borrower => $borrower, permissionModule => $permissionModule, permission => $permission}); + $borrowerPermission->delete(); + return $borrowerPermission; +} + +=head revokeAllPermissions + + $permissionManager->revokeAllPermissions($borrower); + +@THROWS Koha::Exception::UnknownObject, if the given $borrower cannot be casted to Koha::Patron +@THROWS Koha::Exception::BadParameter +=cut + +sub revokeAllPermissions { + my ($self, $borrower) = @_; + $borrower = Koha::Patrons->cast($borrower); + + my $schema = Koha::Database->new()->schema(); + $schema->resultset('BorrowerPermission')->search({borrowernumber => $borrower->borrowernumber})->delete_all(); +} + +=head hasPermissions + +See if the given Borrower has all of the given permissions +@PARAM1 Koha::Patron, or any of the koha.borrowers-table's unique identifiers. +@PARAM2 HASHRef of needed permissions, + { + borrowers => 'view_borrowers', + reserveforothers => ['place_holds'], + tools => ['edit_news', 'edit_notices'], + acquisition => { + budger_add_del => 1, + budget_modify => 1, + }, + coursereserves => '*', #Means any Permission under this PermissionModule + } +@RETURNS see hasPermission() +@THROWS Koha::Exception::NoPermission, from hasPermission() if permission is missing. +=cut + +sub hasPermissions { + my ($self, $borrower, $requiredPermissions) = @_; + + foreach my $module (keys(%$requiredPermissions)) { + my $permissions = $requiredPermissions->{$module}; + if (ref($permissions) eq 'ARRAY') { + foreach my $permission (@$permissions) { + $self->hasPermission($borrower, $module, $permission); + } + } + elsif (ref($permissions) eq 'HASH') { + foreach my $permission (keys(%$permissions)) { + $self->hasPermission($borrower, $module, $permission); + } + } + else { + $self->hasPermission($borrower, $module, $permissions); + } + } + return 1; +} + +=head hasPermission + +See if the given Borrower has the given permission +@PARAM1 Koha::Patron, or any of the koha.borrowers-table's unique identifiers. +@PARAM2 Koha::Auth::PermissionModule or koha.permission_modules.module or koha.permission_modules.permission_module_id +@PARAM3 Koha::Auth::Permission or koha.permissions.code or koha.permissions.permission_id or + '*' if we just need any permission for the given PermissionModule. +@RETURNS Integer, 1 if permission check succeeded. + 2 if user is a superlibrarian. + Catch Exceptions if permission check fails. +@THROWS Koha::Exception::NoPermission, if Borrower is missing the permission. + Exception tells which permission is missing. +@THROWS Koha::Exception::UnknownObject, if the given parameters cannot be casted to Koha::Object-subclasses +@THROWS Koha::Exception::BadParameter +=cut + +sub hasPermission { + my ($self, $borrower, $permissionModule, $permission) = @_; + + $borrower = Koha::Patrons->cast($borrower); + $permissionModule = Koha::Auth::PermissionModules->cast($permissionModule); + $permission = Koha::Auth::Permissions->cast($permission) unless $permission eq '*'; + + my $error; + if ($permission eq '*') { + my $borrowerPermission = Koha::Auth::BorrowerPermissions->search({borrowernumber => $borrower->borrowernumber, + permission_module_id => $permissionModule->permission_module_id, + })->next(); + return 1 if ($borrowerPermission); + $error = "Borrower '".$borrower->borrowernumber."' lacks any permission under permission module '".$permissionModule->module."'."; + } + else { + my $borrowerPermission = Koha::Auth::BorrowerPermissions->search({borrowernumber => $borrower->borrowernumber, + permission_module_id => $permissionModule->permission_module_id, + permission_id => $permission->permission_id, + })->next(); + return 1 if ($borrowerPermission); + $error = "Borrower '".$borrower->borrowernumber."' lacks permission module '".$permissionModule->module."' and permission '".$permission->code."'."; + } + + return 2 if not($permissionModule->module eq 'superlibrarian') && $self->_isSuperuser($borrower); + return 2 if not($permissionModule->module eq 'superlibrarian') && $self->_isSuperlibrarian($borrower); + Koha::Exception::NoPermission->throw(error => $error); +} + +sub _isSuperuser { + my ($self, $borrower) = @_; + $borrower = Koha::Patrons->cast($borrower); + + if ( $borrower->userid && $borrower->userid eq C4::Context->config('user') ) { + return 1; + } + elsif ( $borrower->userid && $borrower->userid eq 'demo' && C4::Context->config('demo') ) { + return 1; + } + return 0; +} + +sub _isSuperlibrarian { + my ($self, $borrower) = @_; + + try { + return $self->hasPermission($borrower, 'superlibrarian', 'superlibrarian'); + } catch { + if (blessed($_) && $_->isa('Koha::Exception::NoPermission')) { + return 0; + } + else { + die $_; + } + }; +} + +1; --- a/Koha/Auth/PermissionModule.pm +++ a/Koha/Auth/PermissionModule.pm @@ -0,0 +1,78 @@ +package Koha::Auth::PermissionModule; + +# Copyright 2015 Vaara-kirjastot +# +# This file is part of Koha. +# +# Koha is free software; you can redistribute it and/or modify it +# under the terms of the GNU General Public License as published by +# the Free Software Foundation; either version 3 of the License, or +# (at your option) any later version. +# +# Koha is distributed in the hope that it will be useful, but +# WITHOUT ANY WARRANTY; without even the implied warranty of +# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the +# GNU General Public License for more details. +# +# You should have received a copy of the GNU General Public License +# along with Koha; if not, see . + +use Modern::Perl; + +use Koha::Auth::PermissionModules; + +use Koha::Exception::BadParameter; + +use base qw(Koha::Object); + +sub _type { + return 'PermissionModule'; +} +sub object_class { + return 'Koha::Auth::PermissionModule'; +} + +sub new { + my ($class, $params) = @_; + + _validateParams($params); + + my $self = Koha::Auth::PermissionModules->find({module => $params->{module}}); + $self = $class->SUPER::new() unless $self; + $self->set($params); + return $self; +} + +sub _validateParams { + my ($params) = @_; + + unless ($params->{description} && length $params->{description} > 0) { + Koha::Exception::BadParameter->throw(error => "Koha::Auth::Permission->new():> Parameter 'description' isn't defined or is empty."); + } + unless ($params->{module} && length $params->{module} > 0) { + Koha::Exception::BadParameter->throw(error => "Koha::Auth::Permission->new():> Parameter 'module' isn't defined or is empty."); + } +} + +=head getPermissions + + my $permissions = $permissionModule->getPermissions(); + +@RETURNS List of Koha::Auth::Permission-objects +=cut + +sub getPermissions { + my ($self) = @_; + + unless ($self->{params}->{permissions}) { + $self->{params}->{permissions} = []; + my @dbix_objects = $self->_result()->permissions; + foreach my $dbix_object (@dbix_objects) { + my $object = Koha::Auth::Permission->_new_from_dbic($dbix_object); + push @{$self->{params}->{permissions}}, $object; + } + } + return $self->{params}->{permissions}; +} + +1; --- a/Koha/Auth/PermissionModules.pm +++ a/Koha/Auth/PermissionModules.pm @@ -0,0 +1,38 @@ +package Koha::Auth::PermissionModules; + +# Copyright 2015 Vaara-kirjastot +# +# This file is part of Koha. +# +# Koha is free software; you can redistribute it and/or modify it +# under the terms of the GNU General Public License as published by +# the Free Software Foundation; either version 3 of the License, or +# (at your option) any later version. +# +# Koha is distributed in the hope that it will be useful, but +# WITHOUT ANY WARRANTY; without even the implied warranty of +# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the +# GNU General Public License for more details. +# +# You should have received a copy of the GNU General Public License +# along with Koha; if not, see . + +use Modern::Perl; +use Scalar::Util qw(blessed); + +use Koha::Auth::PermissionModule; + +use base qw(Koha::Objects); + +sub _type { + return 'PermissionModule'; +} +sub object_class { + return 'Koha::Auth::PermissionModule'; +} + +sub _get_castable_unique_columns { + return ['permission_module_id', 'module']; +} + +1; --- a/Koha/Auth/Permissions.pm +++ a/Koha/Auth/Permissions.pm @@ -0,0 +1,42 @@ +package Koha::Auth::Permissions; + +# Copyright 2015 Vaara-kirjastot +# +# This file is part of Koha. +# +# Koha is free software; you can redistribute it and/or modify it +# under the terms of the GNU General Public License as published by +# the Free Software Foundation; either version 3 of the License, or +# (at your option) any later version. +# +# Koha is distributed in the hope that it will be useful, but +# WITHOUT ANY WARRANTY; without even the implied warranty of +# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the +# GNU General Public License for more details. +# +# You should have received a copy of the GNU General Public License +# along with Koha; if not, see . + +use Modern::Perl; +use Scalar::Util qw(blessed); + +use Koha::Auth::Permission; + +use Koha::Exception::BadParameter; +use Koha::Exception::UnknownObject; + +use base qw(Koha::Objects); + +sub _type { + return 'Permission'; +} + +sub object_class { + return 'Koha::Auth::Permission'; +} + +sub _get_castable_unique_columns { + return ['permission_id', 'code']; +} + +1; --- a/Koha/AuthUtils.pm +++ a/Koha/AuthUtils.pm @@ -154,6 +154,67 @@ sub get_script_name { } } +=head checkHash + + my $passwordOk = Koha::AuthUtils::checkHash($password1, $password2) + +Checks if a clear-text String/password matches the given hash when +MD5 or Bcrypt hashing algorith is applied to it. + +Bcrypt is applied if @PARAM2 starts with '$2' +MD5 otherwise + +@PARAM1 String, clear text passsword or any other String +@PARAM2 String, hashed text password or any other String. +@RETURN Boolean, 1 if given parameters match + , 0 if not +=cut + +sub checkHash { + my ( $password, $stored_hash ) = @_; + + $password = Encode::encode( 'UTF-8', $password ) + if Encode::is_utf8($password); + + return if $stored_hash eq '!'; + + my $hash; + if ( substr( $stored_hash, 0, 2 ) eq '$2' ) { + $hash = hash_password( $password, $stored_hash ); + } else { + #@DEPRECATED Digest::MD5, don't use it or you will get hurt. + require Digest::MD5; + $hash = Digest::MD5::md5_base64($password); + } + return $hash eq $stored_hash; +} + +=head checkKohaSuperuser + + my $patron = Koha::AuthUtils::checkKohaSuperuser($userid, $password); + +Check if the userid and password match the ones in the $KOHA_CONF +@PARAM1 String, user identifier, either the koha.borrowers.userid, or koha.borrowers.cardnumber +@PARAM2 String, clear text password from the authenticating user +@RETURNS Koha::Patron branded as superuser with ->isSuperuser() + or undef if user logging in is not a superuser. +@THROWS Koha::Exception::LoginFailed if user identifier matches, but password doesn't +=cut + +sub checkKohaSuperuser { + my ($userid, $password) = @_; + + if ( $userid && $userid eq C4::Context->config('user') ) { + if ( $password && $password eq C4::Context->config('pass') ) { + return _createTemporarySuperuser(); + } + else { + Koha::Exception::LoginFailed->throw(error => "Password authentication failed"); + } + } +} + + =head checkKohaSuperuserFromUserid See checkKohaSuperuser(), with only the "user identifier"-@PARAM. @THROWS nothing. @@ -184,7 +245,6 @@ sub _createTemporarySuperuser { firstname => $superuserName, surname => $superuserName, branchcode => 'NO_LIBRARY_SET', - flags => 1, email => C4::Context->preference('KohaAdminEmailAddress') }); return $patron; --- a/Koha/Patron.pm +++ a/Koha/Patron.pm @@ -638,6 +638,32 @@ sub _type { return 'Borrower'; } +=head isSuperuser + + $borrower->isSuperuser(1); #Set this borrower to be a superuser + if ($borrower->isSuperuser()) { + #All your base are belong to us + } + +Should be used from the authentication modules to mark this $borrower-object to +have unlimited access to all Koha-features. +This $borrower-object is the Koha DB user. +@PARAM1 Integer, 1 means this borrower is the super/DB user. + "0" disables the previously set superuserness. +=cut + +sub isSuperuser { + my ($self, $Iam) = @_; + + if (defined $Iam && $Iam == 1) { + $self->{superuser} = 1; + } + elsif (defined $Iam && $Iam eq "0") { #Dealing with zero is special in Perl + $self->{superuser} = undef; + } + return (exists($self->{superuser}) && $self->{superuser}) ? 1 : undef; +} + =head1 AUTHOR Kyle M Hall --- a/Koha/Schema/Result/Borrower.pm +++ a/Koha/Schema/Result/Borrower.pm @@ -52,8 +52,9 @@ __PACKAGE__->table("borrowers"); =head2 othernames - data_type: 'mediumtext' + data_type: 'varchar' is_nullable: 1 + size: 50 =head2 initials @@ -297,11 +298,6 @@ __PACKAGE__->table("borrowers"); is_nullable: 1 size: 60 -=head2 flags - - data_type: 'integer' - is_nullable: 1 - =head2 userid data_type: 'varchar' @@ -446,7 +442,7 @@ __PACKAGE__->add_columns( "title", { data_type => "mediumtext", is_nullable => 1 }, "othernames", - { data_type => "mediumtext", is_nullable => 1 }, + { data_type => "varchar", is_nullable => 1, size => 50 }, "initials", { data_type => "text", is_nullable => 1 }, "streetnumber", @@ -563,8 +559,6 @@ __PACKAGE__->add_columns( { data_type => "varchar", is_nullable => 1, size => 1 }, "password", { data_type => "varchar", is_nullable => 1, size => 60 }, - "flags", - { data_type => "integer", is_nullable => 1 }, "userid", { data_type => "varchar", is_nullable => 1, size => 75 }, "opacnote", @@ -651,6 +645,18 @@ __PACKAGE__->set_primary_key("borrowernumber"); __PACKAGE__->add_unique_constraint("cardnumber", ["cardnumber"]); +=head2 C + +=over 4 + +=item * L + +=back + +=cut + +__PACKAGE__->add_unique_constraint("othernames_4", ["othernames"]); + =head2 C =over 4 @@ -830,6 +836,21 @@ __PACKAGE__->has_many( { cascade_copy => 0, cascade_delete => 0 }, ); +=head2 borrower_permissions + +Type: has_many + +Related object: L + +=cut + +__PACKAGE__->has_many( + "borrower_permissions", + "Koha::Schema::Result::BorrowerPermission", + { "foreign.borrowernumber" => "self.borrowernumber" }, + { cascade_copy => 0, cascade_delete => 0 }, +); + =head2 branchcode Type: belongs_to @@ -1255,21 +1276,6 @@ __PACKAGE__->has_many( { cascade_copy => 0, cascade_delete => 0 }, ); -=head2 user_permissions - -Type: has_many - -Related object: L - -=cut - -__PACKAGE__->has_many( - "user_permissions", - "Koha::Schema::Result::UserPermission", - { "foreign.borrowernumber" => "self.borrowernumber" }, - { cascade_copy => 0, cascade_delete => 0 }, -); - =head2 virtualshelfcontents Type: has_many --- a/Koha/Schema/Result/BorrowerPermission.pm +++ a/Koha/Schema/Result/BorrowerPermission.pm @@ -0,0 +1,149 @@ +use utf8; +package Koha::Schema::Result::BorrowerPermission; + +# Created by DBIx::Class::Schema::Loader +# DO NOT MODIFY THE FIRST PART OF THIS FILE + +=head1 NAME + +Koha::Schema::Result::BorrowerPermission + +=cut + +use strict; +use warnings; + +use base 'DBIx::Class::Core'; + +=head1 TABLE: C + +=cut + +__PACKAGE__->table("borrower_permissions"); + +=head1 ACCESSORS + +=head2 borrower_permission_id + + data_type: 'integer' + is_auto_increment: 1 + is_nullable: 0 + +=head2 borrowernumber + + data_type: 'integer' + is_foreign_key: 1 + is_nullable: 0 + +=head2 permission_module_id + + data_type: 'integer' + is_foreign_key: 1 + is_nullable: 0 + +=head2 permission_id + + data_type: 'integer' + is_foreign_key: 1 + is_nullable: 0 + +=cut + +__PACKAGE__->add_columns( + "borrower_permission_id", + { data_type => "integer", is_auto_increment => 1, is_nullable => 0 }, + "borrowernumber", + { data_type => "integer", is_foreign_key => 1, is_nullable => 0 }, + "permission_module_id", + { data_type => "integer", is_foreign_key => 1, is_nullable => 0 }, + "permission_id", + { data_type => "integer", is_foreign_key => 1, is_nullable => 0 }, +); + +=head1 PRIMARY KEY + +=over 4 + +=item * L + +=back + +=cut + +__PACKAGE__->set_primary_key("borrower_permission_id"); + +=head1 UNIQUE CONSTRAINTS + +=head2 C + +=over 4 + +=item * L + +=item * L + +=item * L + +=back + +=cut + +__PACKAGE__->add_unique_constraint( + "borrowernumber", + ["borrowernumber", "permission_module_id", "permission_id"], +); + +=head1 RELATIONS + +=head2 borrowernumber + +Type: belongs_to + +Related object: L + +=cut + +__PACKAGE__->belongs_to( + "borrowernumber", + "Koha::Schema::Result::Borrower", + { borrowernumber => "borrowernumber" }, + { is_deferrable => 1, on_delete => "CASCADE", on_update => "CASCADE" }, +); + +=head2 permission + +Type: belongs_to + +Related object: L + +=cut + +__PACKAGE__->belongs_to( + "permission", + "Koha::Schema::Result::Permission", + { permission_id => "permission_id" }, + { is_deferrable => 1, on_delete => "CASCADE", on_update => "CASCADE" }, +); + +=head2 permission_module + +Type: belongs_to + +Related object: L + +=cut + +__PACKAGE__->belongs_to( + "permission_module", + "Koha::Schema::Result::PermissionModule", + { permission_module_id => "permission_module_id" }, + { is_deferrable => 1, on_delete => "CASCADE", on_update => "CASCADE" }, +); + + +# Created by DBIx::Class::Schema::Loader v0.07039 @ 2015-08-03 18:53:48 +# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:oeu9FDU0R/YXazuSik1yfA + + +# You can replace this text with custom code or comments, and it will be preserved on regeneration +1; --- a/Koha/Schema/Result/Deletedborrower.pm +++ a/Koha/Schema/Result/Deletedborrower.pm @@ -295,11 +295,6 @@ __PACKAGE__->table("deletedborrowers"); is_nullable: 1 size: 60 -=head2 flags - - data_type: 'integer' - is_nullable: 1 - =head2 userid data_type: 'varchar' @@ -548,8 +543,6 @@ __PACKAGE__->add_columns( { data_type => "varchar", is_nullable => 1, size => 1 }, "password", { data_type => "varchar", is_nullable => 1, size => 60 }, - "flags", - { data_type => "integer", is_nullable => 1 }, "userid", { data_type => "varchar", is_nullable => 1, size => 75 }, "opacnote", --- a/Koha/Schema/Result/Permission.pm +++ a/Koha/Schema/Result/Permission.pm @@ -23,17 +23,22 @@ __PACKAGE__->table("permissions"); =head1 ACCESSORS -=head2 module_bit +=head2 permission_id data_type: 'integer' - default_value: 0 + is_auto_increment: 1 + is_nullable: 0 + +=head2 module + + data_type: 'varchar' is_foreign_key: 1 is_nullable: 0 + size: 32 =head2 code data_type: 'varchar' - default_value: (empty string) is_nullable: 0 size: 64 @@ -46,15 +51,12 @@ __PACKAGE__->table("permissions"); =cut __PACKAGE__->add_columns( - "module_bit", - { - data_type => "integer", - default_value => 0, - is_foreign_key => 1, - is_nullable => 0, - }, + "permission_id", + { data_type => "integer", is_auto_increment => 1, is_nullable => 0 }, + "module", + { data_type => "varchar", is_foreign_key => 1, is_nullable => 0, size => 32 }, "code", - { data_type => "varchar", default_value => "", is_nullable => 0, size => 64 }, + { data_type => "varchar", is_nullable => 0, size => 64 }, "description", { data_type => "varchar", is_nullable => 1, size => 255 }, ); @@ -63,7 +65,19 @@ __PACKAGE__->add_columns( =over 4 -=item * L +=item * L + +=back + +=cut + +__PACKAGE__->set_primary_key("permission_id"); + +=head1 UNIQUE CONSTRAINTS + +=head2 C + +=over 4 =item * L @@ -71,41 +85,38 @@ __PACKAGE__->add_columns( =cut -__PACKAGE__->set_primary_key("module_bit", "code"); +__PACKAGE__->add_unique_constraint("code", ["code"]); =head1 RELATIONS -=head2 module_bit +=head2 borrower_permissions -Type: belongs_to +Type: has_many -Related object: L +Related object: L =cut -__PACKAGE__->belongs_to( - "module_bit", - "Koha::Schema::Result::Userflag", - { bit => "module_bit" }, - { is_deferrable => 1, on_delete => "CASCADE", on_update => "CASCADE" }, +__PACKAGE__->has_many( + "borrower_permissions", + "Koha::Schema::Result::BorrowerPermission", + { "foreign.permission_id" => "self.permission_id" }, + { cascade_copy => 0, cascade_delete => 0 }, ); -=head2 user_permissions +=head2 module -Type: has_many +Type: belongs_to -Related object: L +Related object: L =cut -__PACKAGE__->has_many( - "user_permissions", - "Koha::Schema::Result::UserPermission", - { - "foreign.code" => "self.code", - "foreign.module_bit" => "self.module_bit", - }, - { cascade_copy => 0, cascade_delete => 0 }, +__PACKAGE__->belongs_to( + "module", + "Koha::Schema::Result::PermissionModule", + { module => "module" }, + { is_deferrable => 1, on_delete => "CASCADE", on_update => "CASCADE" }, ); --- a/Koha/Schema/Result/PermissionModule.pm +++ a/Koha/Schema/Result/PermissionModule.pm @@ -0,0 +1,119 @@ +use utf8; +package Koha::Schema::Result::PermissionModule; + +# Created by DBIx::Class::Schema::Loader +# DO NOT MODIFY THE FIRST PART OF THIS FILE + +=head1 NAME + +Koha::Schema::Result::PermissionModule + +=cut + +use strict; +use warnings; + +use base 'DBIx::Class::Core'; + +=head1 TABLE: C + +=cut + +__PACKAGE__->table("permission_modules"); + +=head1 ACCESSORS + +=head2 permission_module_id + + data_type: 'integer' + is_auto_increment: 1 + is_nullable: 0 + +=head2 module + + data_type: 'varchar' + is_nullable: 0 + size: 32 + +=head2 description + + data_type: 'varchar' + is_nullable: 1 + size: 255 + +=cut + +__PACKAGE__->add_columns( + "permission_module_id", + { data_type => "integer", is_auto_increment => 1, is_nullable => 0 }, + "module", + { data_type => "varchar", is_nullable => 0, size => 32 }, + "description", + { data_type => "varchar", is_nullable => 1, size => 255 }, +); + +=head1 PRIMARY KEY + +=over 4 + +=item * L + +=back + +=cut + +__PACKAGE__->set_primary_key("permission_module_id"); + +=head1 UNIQUE CONSTRAINTS + +=head2 C + +=over 4 + +=item * L + +=back + +=cut + +__PACKAGE__->add_unique_constraint("module", ["module"]); + +=head1 RELATIONS + +=head2 borrower_permissions + +Type: has_many + +Related object: L + +=cut + +__PACKAGE__->has_many( + "borrower_permissions", + "Koha::Schema::Result::BorrowerPermission", + { "foreign.permission_module_id" => "self.permission_module_id" }, + { cascade_copy => 0, cascade_delete => 0 }, +); + +=head2 permissions + +Type: has_many + +Related object: L + +=cut + +__PACKAGE__->has_many( + "permissions", + "Koha::Schema::Result::Permission", + { "foreign.module" => "self.module" }, + { cascade_copy => 0, cascade_delete => 0 }, +); + + +# Created by DBIx::Class::Schema::Loader v0.07039 @ 2015-08-03 18:53:48 +# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:0p9fkT+XinNSQXJa/egQPw + + +# You can replace this text with custom code or comments, and it will be preserved on regeneration +1; --- a/installer/data/mysql/atomicupdate/Bug-14540-2-Delete_deletedborrowers-flags.perl +++ a/installer/data/mysql/atomicupdate/Bug-14540-2-Delete_deletedborrowers-flags.perl @@ -0,0 +1,5 @@ +use C4::Context; +my $dbh = C4::Context->dbh(); + +$dbh->do("ALTER TABLE deletedborrowers DROP COLUMN flags"); +print "Upgrade done (Bug 14540-2 - Drop deletedborrowers.flags column.)\n"; --- a/installer/data/mysql/atomicupdate/Bug14540-PermissionManager.perl +++ a/installer/data/mysql/atomicupdate/Bug14540-PermissionManager.perl @@ -0,0 +1,151 @@ +$DBversion = 'XXX'; # will be replaced by the RM +if( CheckVersion( $DBversion ) ) { + $dbh->do("CREATE TABLE permission_modules ( + permission_module_id int(11) NOT NULL auto_increment, + module varchar(32) NOT NULL, + description varchar(255) DEFAULT NULL, + PRIMARY KEY (permission_module_id), + UNIQUE KEY (module) + ) ENGINE=InnoDB DEFAULT CHARSET=utf8;"); + $dbh->do("INSERT INTO permission_modules (permission_module_id, module, description) SELECT bit, flag, flagdesc FROM userflags WHERE bit != 0;"); #superlibrarian causes primary key conflict + $dbh->do("INSERT INTO permission_modules (permission_module_id, module, description) SELECT 22, flag, flagdesc FROM userflags WHERE bit = 0;"); #So add him by himself. + + $dbh->do("ALTER TABLE permissions RENAME TO permissions_old"); + $dbh->do("CREATE TABLE permissions ( + permission_id int(11) NOT NULL auto_increment, + module varchar(32) NOT NULL, + code varchar(64) NOT NULL, + description varchar(255) DEFAULT NULL, + PRIMARY KEY (permission_id), + UNIQUE KEY (code), + CONSTRAINT permissions_to_modules_ibfk1 FOREIGN KEY (module) REFERENCES permission_modules (module) + ON DELETE CASCADE ON UPDATE CASCADE + ) ENGINE=InnoDB DEFAULT CHARSET=utf8;"); + $dbh->do("INSERT INTO permissions (module, code, description) + SELECT userflags.flag, code, description FROM permissions_old + LEFT JOIN userflags ON permissions_old.module_bit = userflags.bit;"); + + $dbh->do("CREATE TABLE borrower_permissions ( + borrower_permission_id int(11) NOT NULL auto_increment, + borrowernumber int(11) NOT NULL, + permission_module_id int(11) NOT NULL, + permission_id int(11) NOT NULL, + PRIMARY KEY (borrower_permission_id), + UNIQUE KEY (borrowernumber, permission_module_id, permission_id), + CONSTRAINT borrower_permissions_ibfk_1 FOREIGN KEY (borrowernumber) REFERENCES borrowers (borrowernumber) + ON DELETE CASCADE ON UPDATE CASCADE, + CONSTRAINT borrower_permissions_ibfk_2 FOREIGN KEY (permission_id) REFERENCES permissions (permission_id) + ON DELETE CASCADE ON UPDATE CASCADE, + CONSTRAINT borrower_permissions_ibfk_3 FOREIGN KEY (permission_module_id) REFERENCES permission_modules (permission_module_id) + ON DELETE CASCADE ON UPDATE CASCADE + ) ENGINE=InnoDB DEFAULT CHARSET=utf8;"); + $dbh->do("INSERT INTO borrower_permissions (borrowernumber, permission_module_id, permission_id) + SELECT user_permissions.borrowernumber, user_permissions.module_bit, permissions.permission_id + FROM user_permissions + LEFT JOIN permissions ON user_permissions.code = permissions.code + LEFT JOIN borrowers ON borrowers.borrowernumber = user_permissions.borrowernumber + WHERE borrowers.borrowernumber IS NOT NULL;"); #It is possible to have user_permissions dangling around not pointing to anything, so make sure we dont try to move nonexisting borrower permissions. + + ##Add subpermissions to the stand-alone modules (modules with no subpermissions) + $dbh->do("INSERT INTO permissions (module, code, description) VALUES ('superlibrarian', 'superlibrarian', 'Access to all librarian functions.');"); + $dbh->do("INSERT INTO permissions (module, code, description) VALUES ('catalogue', 'staff_login', 'Allow staff login.');"); + $dbh->do("INSERT INTO permissions (module, code, description) VALUES ('borrowers', 'view_borrowers', 'Show borrower details and search for borrowers.');"); + $dbh->do("INSERT INTO permissions (module, code, description) VALUES ('permissions', 'set_permissions', 'Set user permissions.');"); + $dbh->do("INSERT INTO permissions (module, code, description) VALUES ('management', 'management', 'Set library management parameters (deprecated).');"); + $dbh->do("INSERT INTO permissions (module, code, description) VALUES ('editauthorities', 'edit_authorities', 'Edit authorities.');"); + $dbh->do("INSERT INTO permissions (module, code, description) VALUES ('staffaccess', 'staff_access_permissions', 'Allow staff members to modify permissions for other staff members.');"); + + ##Create borrower_permissions to replace singular userflags from borrowers.flags. + my $sthSelectSubPermissions = $dbh->prepare("SELECT * FROM permissions p LEFT JOIN permission_modules pm ON p.module = pm.module WHERE pm.permission_module_id = ?"); + my $sthSelectBorrowerPermission = $dbh->prepare("SELECT * FROM borrower_permissions WHERE permission_id = ? AND borrowernumber = ?"); + my $sthInsertBorrowerPermission = $dbh->prepare("INSERT INTO borrower_permissions (borrowernumber, permission_module_id, permission_id) VALUES (?,?,?)"); + sub grantAllSubpermissions { + my ($borrowernumber, $module_id, $dbh) = @_; + + $sthSelectSubPermissions->execute($module_id); + my $subPermissions = $sthSelectSubPermissions->fetchall_arrayref({}); + foreach my $sp (@$subPermissions) { + $sthSelectBorrowerPermission->execute($sp->{permission_id}, $borrowernumber); + my $alreadyPermission = $sthSelectBorrowerPermission->fetchrow_hashref(); + unless ($alreadyPermission) { + $sthInsertBorrowerPermission->execute($borrowernumber, $sp->{permission_module_id}, $sp->{permission_id}); + } + } + } + + my $sthSelectAllBorrowers = $dbh->prepare("SELECT * FROM borrowers;"); + $sthSelectAllBorrowers->execute(); + my $borrowers = $sthSelectAllBorrowers->fetchall_arrayref({}); + foreach my $b (@$borrowers) { + next unless $b->{flags}; + if ( ( $b->{flags} & ( 2**0 ) ) ) { + grantAllSubpermissions($b->{borrowernumber}, 21, $dbh); #superlibrarian + } + if ( ( $b->{flags} & ( 2**1 ) ) ) { + grantAllSubpermissions($b->{borrowernumber}, 1, $dbh); #circulate + } + if ( ( $b->{flags} & ( 2**2 ) ) ) { + grantAllSubpermissions($b->{borrowernumber}, 2, $dbh); #catalogue + } + if ( ( $b->{flags} & ( 2**3 ) ) ) { + grantAllSubpermissions($b->{borrowernumber}, 3, $dbh); #parameters, should be deprecated already + } + if ( ( $b->{flags} & ( 2**4 ) ) ) { + grantAllSubpermissions($b->{borrowernumber}, 4, $dbh); #borrowers + } + if ( ( $b->{flags} & ( 2**5 ) ) ) { + grantAllSubpermissions($b->{borrowernumber}, 5, $dbh); #permissions + } + if ( ( $b->{flags} & ( 2**6 ) ) ) { + grantAllSubpermissions($b->{borrowernumber}, 6, $dbh); #reserveforothers + } +# if ( ( $b->{flags} & ( 2**7 ) ) ) { +# grantAllSubpermissions($b->{borrowernumber}, 7, $dbh); #borrow, should be deprecated already +# } + if ( ( $b->{flags} & ( 2**9 ) ) ) { + grantAllSubpermissions($b->{borrowernumber}, 9, $dbh); #editcatalogue + } + if ( ( $b->{flags} & ( 2**10 ) ) ) { + grantAllSubpermissions($b->{borrowernumber}, 10, $dbh); #updatecharges + } + if ( ( $b->{flags} & ( 2**11 ) ) ) { + grantAllSubpermissions($b->{borrowernumber}, 11, $dbh); #acquisition + } + if ( ( $b->{flags} & ( 2**12 ) ) ) { + grantAllSubpermissions($b->{borrowernumber}, 12, $dbh); #management + } + if ( ( $b->{flags} & ( 2**13 ) ) ) { + grantAllSubpermissions($b->{borrowernumber}, 13, $dbh); #tools + } + if ( ( $b->{flags} & ( 2**14 ) ) ) { + grantAllSubpermissions($b->{borrowernumber}, 14, $dbh); #editauthorities + } + if ( ( $b->{flags} & ( 2**15 ) ) ) { + grantAllSubpermissions($b->{borrowernumber}, 15, $dbh); #serials + } + if ( ( $b->{flags} & ( 2**16 ) ) ) { + grantAllSubpermissions($b->{borrowernumber}, 16, $dbh); #reports + } + if ( ( $b->{flags} & ( 2**17 ) ) ) { + grantAllSubpermissions($b->{borrowernumber}, 17, $dbh); #staffaccess + } + if ( ( $b->{flags} & ( 2**18 ) ) ) { + grantAllSubpermissions($b->{borrowernumber}, 18, $dbh); #coursereserves + } + if ( ( $b->{flags} & ( 2**19 ) ) ) { + grantAllSubpermissions($b->{borrowernumber}, 19, $dbh); #plugins + } + } + + ##Cleanup redundant tables. + $dbh->do("DELETE FROM userflags"); #Cascades to other tables. + $dbh->do("DROP TABLE user_permissions"); + $dbh->do("DROP TABLE permissions_old"); + $dbh->do("DROP TABLE userflags"); + $dbh->do("ALTER TABLE borrowers DROP COLUMN flags"); + $dbh->do("DELETE FROM permission_modules WHERE module = 'borrow'"); + + # Always end with this (adjust the bug info) + SetVersion( $DBversion ); + print "Upgrade to $DBversion done (Bug 14540 - Move member-flags.pl to PermissionsManager to better manage permissions for testing.)\n"; +} --- a/installer/data/mysql/en/optional/sample_patrons.sql +++ a/installer/data/mysql/en/optional/sample_patrons.sql @@ -1,51 +1,51 @@ -INSERT INTO `borrowers` (`borrowernumber`, `cardnumber`, `surname`, `firstname`, `title`, `othernames`, `initials`, `streetnumber`, `streettype`, `address`, `address2`, `city`, `zipcode`, `email`, `phone`, `mobile`, `fax`, `emailpro`, `phonepro`, `B_streetnumber`, `B_streettype`, `B_address`, `B_city`, `B_zipcode`, `B_email`, `B_phone`, `dateofbirth`, `branchcode`, `categorycode`, `dateenrolled`, `dateexpiry`, `gonenoaddress`, `lost`, `debarred`, `contactname`, `contactfirstname`, `contacttitle`, `guarantorid`, `borrowernotes`, `relationship`, `sex`, `userid`, `opacnote`, `contactnote`, `password`, `flags`, `sort1`, `sort2`) VALUES -(1,'1','Admin','Koha',NULL,NULL,'',NULL,NULL,'',NULL,'',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'MPL','S',NULL,'2099-12-31',NULL,NULL,NULL,'',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL), -(2,'23529000152273','Welch','Marcus',NULL,NULL,'',NULL,NULL,'5601 Library Rd.',NULL,'Springfield, MA','44224',NULL,'(212) 555-1212',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'FRL','ST','1985-10-24','2020-12-31',NULL,NULL,NULL,'',NULL,NULL,NULL,NULL,NULL,'M','23529000152273',NULL,NULL,'42b29d0771f3b7ef',NULL,'0.43609457503641','0.38613959717205'), -(3,'23529000445172','Daniels','Tanya',NULL,NULL,'',NULL,NULL,'2035 Library Rd.',NULL,'Portland, ME','44236',NULL,'(212) 555-1212',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'1966-10-14','MPL','PT','1990-08-22','2020-12-31',NULL,NULL,NULL,'',NULL,NULL,NULL,NULL,NULL,'F','23529000445172',NULL,NULL,'42b29d0771f3b7ef',NULL,'0.62241429148467','0.95365269664084'), -(4,'23529000105040','Dillon','Eva',NULL,NULL,'',NULL,NULL,'8916 Library Rd.',NULL,'Springfield, MA','44224',NULL,'(212) 555-1212',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'1952-04-03','MPL','PT','1987-07-01','2020-12-31',NULL,NULL,NULL,'',NULL,NULL,NULL,NULL,NULL,'F','23529000105040',NULL,NULL,'42b29d0771f3b7ef',NULL,'0.90102063948384','0.64414513823031'), -(5,'23529001000463','Acosta','Edna',NULL,NULL,'',NULL,NULL,'7896 Library Rd.',NULL,'Portland, OR','44240',NULL,'(212) 555-1212',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'1980-04-24','MPL','PT','1990-11-23','2020-12-31',NULL,NULL,NULL,'',NULL,NULL,NULL,NULL,NULL,'F','23529001000463',NULL,NULL,'42b29d0771f3b7ef',NULL,'0.72642678648832','0.6644830635418'), -(6,'23529000591678','Glass','Virgil',NULL,NULL,'',NULL,NULL,'6250 Library Rd.',NULL,'Santa Cruz, CA','44224',NULL,'(212) 555-1212',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'1974-06-19','SPL','T','1985-02-13','2020-12-31',NULL,NULL,NULL,'',NULL,NULL,102,NULL,NULL,'M','23529000591678',NULL,NULL,'42b29d0771f3b7ef',NULL,'0.1431349889777','0.72222578499673'), -(7,'23529000080862','Middleton','Bobbie',NULL,NULL,'',NULL,NULL,'4619 Library Rd.',NULL,'Springfield, MA','44224',NULL,'(212) 555-1212',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'1953-07-24','SPL','J','1991-03-13','2020-12-31',NULL,NULL,NULL,'',NULL,NULL,104,NULL,NULL,'M','23529000080862',NULL,NULL,'42b29d0771f3b7ef',NULL,'0.18172398878422','0.74194261966454'), -(8,'23529000358268','Rivera','Olga',NULL,NULL,'',NULL,NULL,'6058 Library Rd.',NULL,'Mayfield Heights, OH','21234',NULL,'(212) 555-1212',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'1979-12-22','PVL','T','1986-04-17','2020-12-31',NULL,NULL,NULL,'',NULL,NULL,112,NULL,NULL,'F','23529000358268',NULL,NULL,'42b29d0771f3b7ef',NULL,'0.16454116270369','0.59687798525847'), -(9,'23529000429697','Waters','Annie',NULL,NULL,'',NULL,NULL,'5444 Library Rd.',NULL,'Springfield, MA','44224',NULL,'(212) 555-1212',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'1932-10-27','FRL','ST','1984-05-07','2020-12-31',NULL,NULL,NULL,'',NULL,NULL,NULL,NULL,NULL,'F','23529000429697',NULL,NULL,'42b29d0771f3b7ef',NULL,'0.49076646891494','0.66319841953292'), -(10,'23529000068917','Herring','Frances',NULL,NULL,'',NULL,NULL,'4024 Library Rd.',NULL,'Springfield, MA','44224',NULL,'(212) 555-1212',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'1953-02-13','SPL','PT','1993-03-12','2020-12-31',NULL,NULL,NULL,'',NULL,NULL,NULL,NULL,NULL,'F','23529000068917',NULL,NULL,'42b29d0771f3b7ef',NULL,'0.84369272165344','0.22886838040209'), -(11,'23529000065863','Hawkins','Sam',NULL,NULL,'',NULL,NULL,'665 Library Rd.',NULL,'Springfield, MA','44224',NULL,'(212) 555-1212',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'1924-03-26','SPL','PT','1984-05-09','2020-12-31',NULL,NULL,NULL,'',NULL,NULL,NULL,NULL,NULL,'F','23529000065863',NULL,NULL,'42b29d0771f3b7ef',NULL,'0.61326376778378','0.37971372844625'), -(12,'23529000878885','Mcknight','Marie',NULL,NULL,'',NULL,NULL,'4241 Library Rd.',NULL,'Centerville, CA','44262',NULL,'(212) 555-1212',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'SPL','YA','1990-08-06','2020-12-31',NULL,NULL,NULL,'',NULL,NULL,NULL,NULL,NULL,'F','23529000878885',NULL,NULL,'42b29d0771f3b7ef',NULL,'0.058777369613552','0.15474569346266'), -(13,'23529000132291','Edwards','Roy',NULL,NULL,'',NULL,NULL,'9929 Library Rd.',NULL,'Portland, OR','44240',NULL,'(212) 555-1212',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'1928-09-09','MPL','PT','1992-03-10','2020-12-31',NULL,NULL,NULL,'',NULL,NULL,NULL,NULL,NULL,'F','23529000132291',NULL,NULL,'42b29d0771f3b7ef',NULL,'0.59739638920631','0.5227448963772'), -(14,'23529000035726','Mccullough','Phillip',NULL,NULL,'',NULL,NULL,'410 Library Rd.',NULL,'Portland, ME','44236',NULL,'(212) 555-1212',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'1943-11-23','SPL','PT','1986-01-23','2020-12-31',NULL,NULL,NULL,'',NULL,NULL,NULL,NULL,NULL,'F','23529000035726',NULL,NULL,'42b29d0771f3b7ef',NULL,'0.82153534500071','0.53944206660561'), -(15,'23529001203323','Peterson','Gordon',NULL,NULL,'',NULL,NULL,'6285 Library Rd.',NULL,'Springfield, MA','44224',NULL,'(212) 555-1212',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'1963-10-01','SPL','J','1984-09-11','2020-12-31',NULL,NULL,NULL,'',NULL,NULL,131,NULL,NULL,'F','23529001203323',NULL,NULL,'42b29d0771f3b7ef',NULL,'0.23260432875958','0.54469547471469'), -(16,'23529000991266','Walker','Andrea',NULL,NULL,'',NULL,NULL,'4992 Library Rd.',NULL,'Portland, OR','44240',NULL,'(212) 555-1212',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'1956-08-05','FRL','IL','1993-06-23','2020-12-31',NULL,NULL,NULL,'',NULL,NULL,NULL,NULL,NULL,'F','23529000991266',NULL,NULL,'42b29d0771f3b7ef',NULL,'0.025664418028355','0.49423569673135'), -(17,'23529000711078','King','Casey',NULL,NULL,'',NULL,NULL,'3438 Library Rd.',NULL,'Springfield, MA','44224',NULL,'(212) 555-1212',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'1953-09-23','SPL','S','1992-05-18','2020-12-31',NULL,NULL,NULL,'',NULL,NULL,NULL,NULL,NULL,'F','23529000711078',NULL,NULL,'42b29d0771f3b7ef',NULL,'0.39418526030535','0.48821924206635'), -(18,'23529000809559','Delgado','Floyd',NULL,NULL,'',NULL,NULL,'6661 Library Rd.',NULL,'Springfield, MA','44224',NULL,'(212) 555-1212',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'1968-02-01','MPL','K','1988-08-05','2020-12-31',NULL,NULL,NULL,'',NULL,NULL,160,NULL,NULL,'M','23529000809559',NULL,NULL,'42b29d0771f3b7ef',NULL,'0.25854046014933','0.82804460528125'), -(19,'23529000035676','Acevedo','Henry',NULL,NULL,'',NULL,NULL,'4345 Library Rd.',NULL,'Springfield, MA','44224',NULL,'(212) 555-1212',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'1958-05-30','MPL','S','1990-09-23','2020-12-31',NULL,NULL,NULL,'',NULL,NULL,NULL,NULL,NULL,'F','23529000035676',NULL,NULL,'42b29d0771f3b7ef',NULL,'0.36460167669188','0.33887459834933'), -(20,'23529000033739','Frazier','Sonia',NULL,NULL,'',NULL,NULL,'8165 Library Rd.',NULL,'Springfield, MA','44224',NULL,'(212) 555-1212',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'1945-09-16','MPL','PT','1984-06-21','2020-12-31',NULL,NULL,NULL,'',NULL,NULL,NULL,NULL,NULL,'F','23529000033739',NULL,NULL,'42b29d0771f3b7ef',NULL,'0.60056799240463','0.98621619770882'), -(21,'23529000139858','Ballard','Ronnie',NULL,NULL,'',NULL,NULL,'5730 Library Rd.',NULL,'Centerville, CA','44262',NULL,'(212) 555-1212',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'1956-02-23','MPL','T','1989-06-01','2020-12-31',NULL,NULL,NULL,'',NULL,NULL,177,NULL,NULL,'F','23529000139858',NULL,NULL,'42b29d0771f3b7ef',NULL,'0.12937704206386','0.68823664792649'), -(22,'23529000224510','Gaines','Joyce',NULL,NULL,'',NULL,NULL,'6012 Library Rd.',NULL,'Springfield, MA','44224',NULL,'(212) 555-1212',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'1957-08-01','SPL','YA','1992-09-16','2020-12-31',NULL,NULL,NULL,'',NULL,NULL,NULL,NULL,NULL,'F','23529000224510',NULL,NULL,'42b29d0771f3b7ef',NULL,'0.053052144174513','0.20055080782673'), -(23,'23529000120056','Bryan','Johnny',NULL,NULL,'',NULL,NULL,'8180 Library Rd.',NULL,'Springfield, MA','44224',NULL,'(212) 555-1212',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'1961-10-24','MPL','PT','1990-08-22','2020-12-31',NULL,NULL,NULL,'',NULL,NULL,NULL,NULL,NULL,'F','23529000120056',NULL,NULL,'42b29d0771f3b7ef',NULL,'0.84359763734378','0.61633579397233'), -(24,'23529000218553','Duffy','Joann',NULL,NULL,'',NULL,NULL,'1555 Library Rd.',NULL,'Portland, OR','44240',NULL,'(212) 555-1212',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'1942-02-17','MPL','ST','1984-08-02','2020-12-31',NULL,NULL,NULL,'',NULL,NULL,NULL,NULL,NULL,'F','23529000218553',NULL,NULL,'42b29d0771f3b7ef',NULL,'0.55088608856395','0.90542309163643'), -(25,'23529000662701','Mejia','Armando',NULL,NULL,'',NULL,NULL,'8209 Library Rd.',NULL,'Portland, OR','44240',NULL,'(212) 555-1212',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'1939-07-22','SPL','PT','1990-09-26','2020-12-31',NULL,NULL,NULL,'',NULL,NULL,NULL,NULL,NULL,'M','23529000662701',NULL,NULL,'42b29d0771f3b7ef',NULL,'0.87445722322395','0.65601687194409'), -(26,'23529000092040','Hendrix','Keith',NULL,NULL,'',NULL,NULL,'6546 Library Rd.',NULL,'Springfield, MA','44224',NULL,'(212) 555-1212',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'1944-04-05','SPL','PT','1987-12-12','2020-12-31',NULL,NULL,NULL,'',NULL,NULL,NULL,NULL,NULL,'M','23529000092040',NULL,NULL,'42b29d0771f3b7ef',NULL,'0.65671272078223','0.31551301881253'), -(27,'23529000058017','Turner','Sherri',NULL,NULL,'',NULL,NULL,'1731 Library Rd.',NULL,'Springfield, MA','44224',NULL,'(212) 555-1212',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'1964-11-21','FRL','PT','1987-09-14','2020-12-31',NULL,NULL,NULL,'',NULL,NULL,NULL,NULL,NULL,'F','23529000058017',NULL,NULL,'42b29d0771f3b7ef',NULL,'0.60742696244961','0.090595786544118'), -(28,'23529000299603','Cherry','Lillie',NULL,NULL,'',NULL,NULL,'263 Library Rd.',NULL,'Springfield, MA','44224',NULL,'(212) 555-1212',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'1931-01-19','MPL','PT','1993-03-19','2020-12-31',NULL,NULL,NULL,'',NULL,NULL,NULL,NULL,NULL,'F','23529000299603',NULL,NULL,'42b29d0771f3b7ef',NULL,'0.6306980761054','0.88170305162827'), -(29,'23529000121682','Schneider','Nicole',NULL,NULL,'',NULL,NULL,'1008 Library Rd.',NULL,'Centerville, CA','44306',NULL,'(212) 555-1212',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'1954-01-12','FRL','ST','1985-09-09','2020-12-31',NULL,NULL,NULL,'',NULL,NULL,NULL,NULL,NULL,'F','23529000121682',NULL,NULL,'42b29d0771f3b7ef',NULL,'0.5164210605588','0.93699617864284'), -(30,'23529000240482','White','Jamie',NULL,NULL,'',NULL,NULL,'937 Library Rd.',NULL,'Springfield, MA','44224',NULL,'(212) 555-1212',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'1950-04-22','FRL','T','1990-02-03','2020-12-31',NULL,NULL,NULL,'',NULL,NULL,254,NULL,NULL,'F','23529000240482',NULL,NULL,'42b29d0771f3b7ef',NULL,'0.13571774227146','0.86760020616241'), -(31,'23529000714163','Burgess','Dawn',NULL,NULL,'',NULL,NULL,'7907 Library Rd.',NULL,'Springfield, MA','44224',NULL,'(212) 555-1212',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'MPL','PT','1985-05-23','2020-12-31',NULL,NULL,NULL,'',NULL,NULL,NULL,NULL,NULL,'M','23529000714163',NULL,NULL,'42b29d0771f3b7ef',NULL,'0.93084783473131','0.051438585902973'), -(32,'23529000318858','Bennett','Pamela',NULL,NULL,'',NULL,NULL,'9898 Library Rd.',NULL,'Centerville, CA','44310',NULL,'(212) 555-1212',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'1946-09-16','MPL','ST','1984-05-08','2020-12-31',NULL,NULL,NULL,'',NULL,NULL,NULL,NULL,NULL,'M','23529000318858',NULL,NULL,'42b29d0771f3b7ef',NULL,'0.46464945605458','0.16893155329761'), -(33,'23529000526047','Hatfield','Lester',NULL,NULL,'',NULL,NULL,'393 Library Rd.',NULL,'Centerville, CA','44262',NULL,'(212) 555-1212',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'1983-01-22','SPL','ST','1988-07-30','2020-12-31',NULL,NULL,NULL,'',NULL,NULL,NULL,NULL,NULL,'F','23529000526047',NULL,NULL,'42b29d0771f3b7ef',NULL,'0.45070942905798','0.74675251613069'), -(34,'23529000686353','Harper','Ruben',NULL,NULL,'',NULL,NULL,'6963 Library Rd.',NULL,'Springfield, MA','44224',NULL,'(212) 555-1212',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'1917-09-24','SPL','S','1991-02-19','2020-12-31',NULL,NULL,NULL,'',NULL,NULL,NULL,NULL,NULL,'M','23529000686353',NULL,NULL,'42b29d0771f3b7ef',NULL,'0.38163432421315','0.66791410340733'), -(35,'23529000719519','Kinney','Mae',NULL,NULL,'',NULL,NULL,'2114 Library Rd.',NULL,'Springfield, MA','44224',NULL,'(212) 555-1212',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'SPL','J','1991-12-29','2020-12-31',NULL,NULL,NULL,'',NULL,NULL,287,NULL,NULL,'F','23529000719519',NULL,NULL,'42b29d0771f3b7ef',NULL,'0.19466757513086','0.96959559616595'), -(36,'23529000179433','Williamson','Catherine',NULL,NULL,'',NULL,NULL,'5879 Library Rd.',NULL,'Springfield, MA','44224',NULL,'(212) 555-1212',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'1930-12-18','FRL','K','1985-01-23','2020-12-31',NULL,NULL,NULL,'',NULL,NULL,288,NULL,NULL,'M','23529000179433',NULL,NULL,'42b29d0771f3b7ef',NULL,'0.26397528617082','0.41108967308988'), -(37,'23529000197047','Charles','Lisa',NULL,NULL,'',NULL,NULL,'6813 Library Rd.',NULL,'Springfield, MA','44224',NULL,'(212) 555-1212',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'1982-10-14','MPL','K','1988-11-14','2020-12-31',NULL,NULL,NULL,'',NULL,NULL,289,NULL,NULL,'M','23529000197047',NULL,NULL,'42b29d0771f3b7ef',NULL,'0.26352253767058','0.084343699816934'), -(38,'23529000695412','Singleton','Bob',NULL,NULL,'',NULL,NULL,'4554 Library Rd.',NULL,'Centerville, CA','44262',NULL,'(212) 555-1212',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'1983-04-15','FRL','PT','1991-07-01','2020-12-31',NULL,NULL,NULL,'',NULL,NULL,NULL,NULL,NULL,'F','23529000695412',NULL,NULL,'42b29d0771f3b7ef',NULL,'0.63115091680656','0.90272351531566'), -(39,'23529000603242','Mcconnell','Terrence',NULL,NULL,'',NULL,NULL,'7200 Library Rd.',NULL,'Springfield, MA','44224',NULL,'(212) 555-1212',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'1949-06-10','SPL','PT','1984-05-31','2020-12-31',NULL,NULL,NULL,'',NULL,NULL,NULL,NULL,NULL,'F','23529000603242',NULL,NULL,'42b29d0771f3b7ef',NULL,'0.62016485689223','0.39265376924784'), -(40,'23529000589458','Knight','Mabel',NULL,NULL,'',NULL,NULL,'8997 Library Rd.',NULL,'Springfield, MA','44224',NULL,'(212) 555-1212',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'1952-12-22','SPL','T','1985-04-17','2020-12-31',NULL,NULL,NULL,'',NULL,NULL,320,NULL,NULL,'M','23529000589458',NULL,NULL,'42b29d0771f3b7ef',NULL,'0.10277430629616','0.33591025447092'), -(41,'23529000054503','Odom','Gloria',NULL,NULL,'',NULL,NULL,'9156 Library Rd.',NULL,'Springfield, MA','44224',NULL,'(212) 555-1212',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'1952-04-05','SPL','S','1992-07-21','2020-12-31',NULL,NULL,NULL,'',NULL,NULL,NULL,NULL,NULL,'M','23529000054503',NULL,NULL,'42b29d0771f3b7ef',NULL,'0.37122838419976','0.84841118831971'), -(42,'23529000126806','Hopkins','Brent',NULL,NULL,'',NULL,NULL,'6692 Library Rd.',NULL,'Santa Cruz, CA','21234',NULL,'(212) 555-1212',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'1977-11-13','SPL','T','1984-06-28','2020-12-31',NULL,NULL,NULL,'',NULL,NULL,337,NULL,NULL,'F','23529000126806',NULL,NULL,'42b29d0771f3b7ef',NULL,'0.12837081973289','0.096620564438981'), -(43,'23529000104159','Benson','Clinton',NULL,NULL,'',NULL,NULL,'4695 Library Rd.',NULL,'Atwater, OH','21234',NULL,'(212) 555-1212',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'MPL','YA','1985-10-28','2020-12-31',NULL,NULL,NULL,'',NULL,NULL,NULL,NULL,NULL,'F','23529000104159',NULL,NULL,'42b29d0771f3b7ef',NULL,'0.097990393729872','0.20009030606606'), -(44,'23529000395609','Stuart','Shannon',NULL,NULL,'',NULL,NULL,'5011 Library Rd.',NULL,'Springfield, MA','44224',NULL,'(212) 555-1212',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'1951-02-13','FRL','PT','1986-01-16','2020-12-31',NULL,NULL,NULL,'',NULL,NULL,NULL,NULL,NULL,'M','23529000395609',NULL,NULL,'42b29d0771f3b7ef',NULL,'0.70648037987433','0.93213101190713'), -(45,'23529000050113','Alford','Jordan',NULL,NULL,'',NULL,NULL,'5129 Library Rd.',NULL,'Centerville, CA','44262',NULL,'(212) 555-1212',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'1952-05-27','MPL','ST','1985-09-09','2020-12-31',NULL,NULL,NULL,'',NULL,NULL,NULL,NULL,NULL,'M','23529000050113',NULL,NULL,'42b29d0771f3b7ef',NULL,'0.54121395064631','0.90967674824379'), -(46,'23529000053836','Mcfarland','Marilyn',NULL,NULL,'',NULL,NULL,'4600 Library Rd.',NULL,'Springfield, MA','44224',NULL,'(212) 555-1212',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'SPL','PT','1984-05-09','2020-12-31',NULL,NULL,NULL,'',NULL,NULL,NULL,NULL,NULL,'F','23529000053836',NULL,NULL,'42b29d0771f3b7ef',NULL,'0.92474192001367','0.89467938607063'), -(47,'23529000774373','Morris','Lauren',NULL,NULL,'',NULL,NULL,'5436 Library Rd.',NULL,'Springfield, MA','44224',NULL,'(212) 555-1212',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'SPL','PT','1986-03-20','2020-12-31',NULL,NULL,NULL,'',NULL,NULL,NULL,NULL,NULL,'F','23529000774373',NULL,NULL,'42b29d0771f3b7ef',NULL,'0.69917120104578','0.81181787775067'), -(48,'23529000117565','Palmer','Bill',NULL,NULL,'',NULL,NULL,'2761 Library Rd.',NULL,'Springfield, MA','44224',NULL,'(212) 555-1212',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'1984-01-10','SPL','PT','1991-10-07','2020-12-31',NULL,NULL,NULL,'',NULL,NULL,NULL,NULL,NULL,'M','23529000117565',NULL,NULL,'42b29d0771f3b7ef',NULL,'0.96157581634966','0.37242547922994'), -(49,'23529000651225','Burton','Mary',NULL,NULL,'',NULL,NULL,'8131 Library Rd.',NULL,'Santa Cruz, CA','21234',NULL,'(212) 555-1212',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'1977-12-25','MPL','PT','1985-05-30','2020-12-31',NULL,NULL,NULL,'',NULL,NULL,NULL,NULL,NULL,'F','23529000651225',NULL,NULL,'42b29d0771f3b7ef',NULL,'0.97739997783434','0.76972348221552'), -(50,'23529001223636','Peters','Tommy',NULL,NULL,'',NULL,NULL,'5171 Library Rd.',NULL,'Portland, ME','44236',NULL,'(212) 555-1212',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'1951-06-14','SPL','PT','1984-05-09','2020-12-31',NULL,NULL,NULL,'',NULL,NULL,NULL,NULL,NULL,'F','23529001223636',NULL,NULL,'42b29d0771f3b7ef',NULL,'0.91641750830823','0.27291712562825'); +INSERT INTO `borrowers` (`borrowernumber`, `cardnumber`, `surname`, `firstname`, `title`, `othernames`, `initials`, `streetnumber`, `streettype`, `address`, `address2`, `city`, `zipcode`, `email`, `phone`, `mobile`, `fax`, `emailpro`, `phonepro`, `B_streetnumber`, `B_streettype`, `B_address`, `B_city`, `B_zipcode`, `B_email`, `B_phone`, `dateofbirth`, `branchcode`, `categorycode`, `dateenrolled`, `dateexpiry`, `gonenoaddress`, `lost`, `debarred`, `contactname`, `contactfirstname`, `contacttitle`, `guarantorid`, `borrowernotes`, `relationship`, `sex`, `userid`, `opacnote`, `contactnote`, `password`, `sort1`, `sort2`) VALUES +(1,'1','Admin','Koha',NULL,NULL,'',NULL,NULL,'',NULL,'',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'MPL','S',NULL,'2099-12-31',NULL,NULL,NULL,'',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL), +(2,'23529000152273','Welch','Marcus',NULL,NULL,'',NULL,NULL,'5601 Library Rd.',NULL,'Springfield, MA','44224',NULL,'(212) 555-1212',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'FRL','ST','1985-10-24','2020-12-31',NULL,NULL,NULL,'',NULL,NULL,NULL,NULL,NULL,'M','23529000152273',NULL,NULL,'42b29d0771f3b7ef','0.43609457503641','0.38613959717205'), +(3,'23529000445172','Daniels','Tanya',NULL,NULL,'',NULL,NULL,'2035 Library Rd.',NULL,'Portland, ME','44236',NULL,'(212) 555-1212',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'1966-10-14','MPL','PT','1990-08-22','2020-12-31',NULL,NULL,NULL,'',NULL,NULL,NULL,NULL,NULL,'F','23529000445172',NULL,NULL,'42b29d0771f3b7ef','0.62241429148467','0.95365269664084'), +(4,'23529000105040','Dillon','Eva',NULL,NULL,'',NULL,NULL,'8916 Library Rd.',NULL,'Springfield, MA','44224',NULL,'(212) 555-1212',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'1952-04-03','MPL','PT','1987-07-01','2020-12-31',NULL,NULL,NULL,'',NULL,NULL,NULL,NULL,NULL,'F','23529000105040',NULL,NULL,'42b29d0771f3b7ef','0.90102063948384','0.64414513823031'), +(5,'23529001000463','Acosta','Edna',NULL,NULL,'',NULL,NULL,'7896 Library Rd.',NULL,'Portland, OR','44240',NULL,'(212) 555-1212',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'1980-04-24','MPL','PT','1990-11-23','2020-12-31',NULL,NULL,NULL,'',NULL,NULL,NULL,NULL,NULL,'F','23529001000463',NULL,NULL,'42b29d0771f3b7ef','0.72642678648832','0.6644830635418'), +(6,'23529000591678','Glass','Virgil',NULL,NULL,'',NULL,NULL,'6250 Library Rd.',NULL,'Santa Cruz, CA','44224',NULL,'(212) 555-1212',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'1974-06-19','SPL','T','1985-02-13','2020-12-31',NULL,NULL,NULL,'',NULL,NULL,102,NULL,NULL,'M','23529000591678',NULL,NULL,'42b29d0771f3b7ef','0.1431349889777','0.72222578499673'), +(7,'23529000080862','Middleton','Bobbie',NULL,NULL,'',NULL,NULL,'4619 Library Rd.',NULL,'Springfield, MA','44224',NULL,'(212) 555-1212',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'1953-07-24','SPL','J','1991-03-13','2020-12-31',NULL,NULL,NULL,'',NULL,NULL,104,NULL,NULL,'M','23529000080862',NULL,NULL,'42b29d0771f3b7ef','0.18172398878422','0.74194261966454'), +(8,'23529000358268','Rivera','Olga',NULL,NULL,'',NULL,NULL,'6058 Library Rd.',NULL,'Mayfield Heights, OH','21234',NULL,'(212) 555-1212',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'1979-12-22','PVL','T','1986-04-17','2020-12-31',NULL,NULL,NULL,'',NULL,NULL,112,NULL,NULL,'F','23529000358268',NULL,NULL,'42b29d0771f3b7ef','0.16454116270369','0.59687798525847'), +(9,'23529000429697','Waters','Annie',NULL,NULL,'',NULL,NULL,'5444 Library Rd.',NULL,'Springfield, MA','44224',NULL,'(212) 555-1212',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'1932-10-27','FRL','ST','1984-05-07','2020-12-31',NULL,NULL,NULL,'',NULL,NULL,NULL,NULL,NULL,'F','23529000429697',NULL,NULL,'42b29d0771f3b7ef','0.49076646891494','0.66319841953292'), +(10,'23529000068917','Herring','Frances',NULL,NULL,'',NULL,NULL,'4024 Library Rd.',NULL,'Springfield, MA','44224',NULL,'(212) 555-1212',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'1953-02-13','SPL','PT','1993-03-12','2020-12-31',NULL,NULL,NULL,'',NULL,NULL,NULL,NULL,NULL,'F','23529000068917',NULL,NULL,'42b29d0771f3b7ef','0.84369272165344','0.22886838040209'), +(11,'23529000065863','Hawkins','Sam',NULL,NULL,'',NULL,NULL,'665 Library Rd.',NULL,'Springfield, MA','44224',NULL,'(212) 555-1212',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'1924-03-26','SPL','PT','1984-05-09','2020-12-31',NULL,NULL,NULL,'',NULL,NULL,NULL,NULL,NULL,'F','23529000065863',NULL,NULL,'42b29d0771f3b7ef','0.61326376778378','0.37971372844625'), +(12,'23529000878885','Mcknight','Marie',NULL,NULL,'',NULL,NULL,'4241 Library Rd.',NULL,'Centerville, CA','44262',NULL,'(212) 555-1212',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'SPL','YA','1990-08-06','2020-12-31',NULL,NULL,NULL,'',NULL,NULL,NULL,NULL,NULL,'F','23529000878885',NULL,NULL,'42b29d0771f3b7ef','0.058777369613552','0.15474569346266'), +(13,'23529000132291','Edwards','Roy',NULL,NULL,'',NULL,NULL,'9929 Library Rd.',NULL,'Portland, OR','44240',NULL,'(212) 555-1212',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'1928-09-09','MPL','PT','1992-03-10','2020-12-31',NULL,NULL,NULL,'',NULL,NULL,NULL,NULL,NULL,'F','23529000132291',NULL,NULL,'42b29d0771f3b7ef','0.59739638920631','0.5227448963772'), +(14,'23529000035726','Mccullough','Phillip',NULL,NULL,'',NULL,NULL,'410 Library Rd.',NULL,'Portland, ME','44236',NULL,'(212) 555-1212',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'1943-11-23','SPL','PT','1986-01-23','2020-12-31',NULL,NULL,NULL,'',NULL,NULL,NULL,NULL,NULL,'F','23529000035726',NULL,NULL,'42b29d0771f3b7ef','0.82153534500071','0.53944206660561'), +(15,'23529001203323','Peterson','Gordon',NULL,NULL,'',NULL,NULL,'6285 Library Rd.',NULL,'Springfield, MA','44224',NULL,'(212) 555-1212',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'1963-10-01','SPL','J','1984-09-11','2020-12-31',NULL,NULL,NULL,'',NULL,NULL,131,NULL,NULL,'F','23529001203323',NULL,NULL,'42b29d0771f3b7ef','0.23260432875958','0.54469547471469'), +(16,'23529000991266','Walker','Andrea',NULL,NULL,'',NULL,NULL,'4992 Library Rd.',NULL,'Portland, OR','44240',NULL,'(212) 555-1212',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'1956-08-05','FRL','IL','1993-06-23','2020-12-31',NULL,NULL,NULL,'',NULL,NULL,NULL,NULL,NULL,'F','23529000991266',NULL,NULL,'42b29d0771f3b7ef','0.025664418028355','0.49423569673135'), +(17,'23529000711078','King','Casey',NULL,NULL,'',NULL,NULL,'3438 Library Rd.',NULL,'Springfield, MA','44224',NULL,'(212) 555-1212',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'1953-09-23','SPL','S','1992-05-18','2020-12-31',NULL,NULL,NULL,'',NULL,NULL,NULL,NULL,NULL,'F','23529000711078',NULL,NULL,'42b29d0771f3b7ef','0.39418526030535','0.48821924206635'), +(18,'23529000809559','Delgado','Floyd',NULL,NULL,'',NULL,NULL,'6661 Library Rd.',NULL,'Springfield, MA','44224',NULL,'(212) 555-1212',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'1968-02-01','MPL','K','1988-08-05','2020-12-31',NULL,NULL,NULL,'',NULL,NULL,160,NULL,NULL,'M','23529000809559',NULL,NULL,'42b29d0771f3b7ef','0.25854046014933','0.82804460528125'), +(19,'23529000035676','Acevedo','Henry',NULL,NULL,'',NULL,NULL,'4345 Library Rd.',NULL,'Springfield, MA','44224',NULL,'(212) 555-1212',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'1958-05-30','MPL','S','1990-09-23','2020-12-31',NULL,NULL,NULL,'',NULL,NULL,NULL,NULL,NULL,'F','23529000035676',NULL,NULL,'42b29d0771f3b7ef','0.36460167669188','0.33887459834933'), +(20,'23529000033739','Frazier','Sonia',NULL,NULL,'',NULL,NULL,'8165 Library Rd.',NULL,'Springfield, MA','44224',NULL,'(212) 555-1212',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'1945-09-16','MPL','PT','1984-06-21','2020-12-31',NULL,NULL,NULL,'',NULL,NULL,NULL,NULL,NULL,'F','23529000033739',NULL,NULL,'42b29d0771f3b7ef','0.60056799240463','0.98621619770882'), +(21,'23529000139858','Ballard','Ronnie',NULL,NULL,'',NULL,NULL,'5730 Library Rd.',NULL,'Centerville, CA','44262',NULL,'(212) 555-1212',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'1956-02-23','MPL','T','1989-06-01','2020-12-31',NULL,NULL,NULL,'',NULL,NULL,177,NULL,NULL,'F','23529000139858',NULL,NULL,'42b29d0771f3b7ef','0.12937704206386','0.68823664792649'), +(22,'23529000224510','Gaines','Joyce',NULL,NULL,'',NULL,NULL,'6012 Library Rd.',NULL,'Springfield, MA','44224',NULL,'(212) 555-1212',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'1957-08-01','SPL','YA','1992-09-16','2020-12-31',NULL,NULL,NULL,'',NULL,NULL,NULL,NULL,NULL,'F','23529000224510',NULL,NULL,'42b29d0771f3b7ef','0.053052144174513','0.20055080782673'), +(23,'23529000120056','Bryan','Johnny',NULL,NULL,'',NULL,NULL,'8180 Library Rd.',NULL,'Springfield, MA','44224',NULL,'(212) 555-1212',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'1961-10-24','MPL','PT','1990-08-22','2020-12-31',NULL,NULL,NULL,'',NULL,NULL,NULL,NULL,NULL,'F','23529000120056',NULL,NULL,'42b29d0771f3b7ef','0.84359763734378','0.61633579397233'), +(24,'23529000218553','Duffy','Joann',NULL,NULL,'',NULL,NULL,'1555 Library Rd.',NULL,'Portland, OR','44240',NULL,'(212) 555-1212',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'1942-02-17','MPL','ST','1984-08-02','2020-12-31',NULL,NULL,NULL,'',NULL,NULL,NULL,NULL,NULL,'F','23529000218553',NULL,NULL,'42b29d0771f3b7ef','0.55088608856395','0.90542309163643'), +(25,'23529000662701','Mejia','Armando',NULL,NULL,'',NULL,NULL,'8209 Library Rd.',NULL,'Portland, OR','44240',NULL,'(212) 555-1212',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'1939-07-22','SPL','PT','1990-09-26','2020-12-31',NULL,NULL,NULL,'',NULL,NULL,NULL,NULL,NULL,'M','23529000662701',NULL,NULL,'42b29d0771f3b7ef','0.87445722322395','0.65601687194409'), +(26,'23529000092040','Hendrix','Keith',NULL,NULL,'',NULL,NULL,'6546 Library Rd.',NULL,'Springfield, MA','44224',NULL,'(212) 555-1212',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'1944-04-05','SPL','PT','1987-12-12','2020-12-31',NULL,NULL,NULL,'',NULL,NULL,NULL,NULL,NULL,'M','23529000092040',NULL,NULL,'42b29d0771f3b7ef','0.65671272078223','0.31551301881253'), +(27,'23529000058017','Turner','Sherri',NULL,NULL,'',NULL,NULL,'1731 Library Rd.',NULL,'Springfield, MA','44224',NULL,'(212) 555-1212',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'1964-11-21','FRL','PT','1987-09-14','2020-12-31',NULL,NULL,NULL,'',NULL,NULL,NULL,NULL,NULL,'F','23529000058017',NULL,NULL,'42b29d0771f3b7ef','0.60742696244961','0.090595786544118'), +(28,'23529000299603','Cherry','Lillie',NULL,NULL,'',NULL,NULL,'263 Library Rd.',NULL,'Springfield, MA','44224',NULL,'(212) 555-1212',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'1931-01-19','MPL','PT','1993-03-19','2020-12-31',NULL,NULL,NULL,'',NULL,NULL,NULL,NULL,NULL,'F','23529000299603',NULL,NULL,'42b29d0771f3b7ef','0.6306980761054','0.88170305162827'), +(29,'23529000121682','Schneider','Nicole',NULL,NULL,'',NULL,NULL,'1008 Library Rd.',NULL,'Centerville, CA','44306',NULL,'(212) 555-1212',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'1954-01-12','FRL','ST','1985-09-09','2020-12-31',NULL,NULL,NULL,'',NULL,NULL,NULL,NULL,NULL,'F','23529000121682',NULL,NULL,'42b29d0771f3b7ef','0.5164210605588','0.93699617864284'), +(30,'23529000240482','White','Jamie',NULL,NULL,'',NULL,NULL,'937 Library Rd.',NULL,'Springfield, MA','44224',NULL,'(212) 555-1212',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'1950-04-22','FRL','T','1990-02-03','2020-12-31',NULL,NULL,NULL,'',NULL,NULL,254,NULL,NULL,'F','23529000240482',NULL,NULL,'42b29d0771f3b7ef','0.13571774227146','0.86760020616241'), +(31,'23529000714163','Burgess','Dawn',NULL,NULL,'',NULL,NULL,'7907 Library Rd.',NULL,'Springfield, MA','44224',NULL,'(212) 555-1212',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'MPL','PT','1985-05-23','2020-12-31',NULL,NULL,NULL,'',NULL,NULL,NULL,NULL,NULL,'M','23529000714163',NULL,NULL,'42b29d0771f3b7ef','0.93084783473131','0.051438585902973'), +(32,'23529000318858','Bennett','Pamela',NULL,NULL,'',NULL,NULL,'9898 Library Rd.',NULL,'Centerville, CA','44310',NULL,'(212) 555-1212',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'1946-09-16','MPL','ST','1984-05-08','2020-12-31',NULL,NULL,NULL,'',NULL,NULL,NULL,NULL,NULL,'M','23529000318858',NULL,NULL,'42b29d0771f3b7ef','0.46464945605458','0.16893155329761'), +(33,'23529000526047','Hatfield','Lester',NULL,NULL,'',NULL,NULL,'393 Library Rd.',NULL,'Centerville, CA','44262',NULL,'(212) 555-1212',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'1983-01-22','SPL','ST','1988-07-30','2020-12-31',NULL,NULL,NULL,'',NULL,NULL,NULL,NULL,NULL,'F','23529000526047',NULL,NULL,'42b29d0771f3b7ef','0.45070942905798','0.74675251613069'), +(34,'23529000686353','Harper','Ruben',NULL,NULL,'',NULL,NULL,'6963 Library Rd.',NULL,'Springfield, MA','44224',NULL,'(212) 555-1212',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'1917-09-24','SPL','S','1991-02-19','2020-12-31',NULL,NULL,NULL,'',NULL,NULL,NULL,NULL,NULL,'M','23529000686353',NULL,NULL,'42b29d0771f3b7ef','0.38163432421315','0.66791410340733'), +(35,'23529000719519','Kinney','Mae',NULL,NULL,'',NULL,NULL,'2114 Library Rd.',NULL,'Springfield, MA','44224',NULL,'(212) 555-1212',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'SPL','J','1991-12-29','2020-12-31',NULL,NULL,NULL,'',NULL,NULL,287,NULL,NULL,'F','23529000719519',NULL,NULL,'42b29d0771f3b7ef','0.19466757513086','0.96959559616595'), +(36,'23529000179433','Williamson','Catherine',NULL,NULL,'',NULL,NULL,'5879 Library Rd.',NULL,'Springfield, MA','44224',NULL,'(212) 555-1212',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'1930-12-18','FRL','K','1985-01-23','2020-12-31',NULL,NULL,NULL,'',NULL,NULL,288,NULL,NULL,'M','23529000179433',NULL,NULL,'42b29d0771f3b7ef','0.26397528617082','0.41108967308988'), +(37,'23529000197047','Charles','Lisa',NULL,NULL,'',NULL,NULL,'6813 Library Rd.',NULL,'Springfield, MA','44224',NULL,'(212) 555-1212',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'1982-10-14','MPL','K','1988-11-14','2020-12-31',NULL,NULL,NULL,'',NULL,NULL,289,NULL,NULL,'M','23529000197047',NULL,NULL,'42b29d0771f3b7ef','0.26352253767058','0.084343699816934'), +(38,'23529000695412','Singleton','Bob',NULL,NULL,'',NULL,NULL,'4554 Library Rd.',NULL,'Centerville, CA','44262',NULL,'(212) 555-1212',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'1983-04-15','FRL','PT','1991-07-01','2020-12-31',NULL,NULL,NULL,'',NULL,NULL,NULL,NULL,NULL,'F','23529000695412',NULL,NULL,'42b29d0771f3b7ef','0.63115091680656','0.90272351531566'), +(39,'23529000603242','Mcconnell','Terrence',NULL,NULL,'',NULL,NULL,'7200 Library Rd.',NULL,'Springfield, MA','44224',NULL,'(212) 555-1212',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'1949-06-10','SPL','PT','1984-05-31','2020-12-31',NULL,NULL,NULL,'',NULL,NULL,NULL,NULL,NULL,'F','23529000603242',NULL,NULL,'42b29d0771f3b7ef','0.62016485689223','0.39265376924784'), +(40,'23529000589458','Knight','Mabel',NULL,NULL,'',NULL,NULL,'8997 Library Rd.',NULL,'Springfield, MA','44224',NULL,'(212) 555-1212',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'1952-12-22','SPL','T','1985-04-17','2020-12-31',NULL,NULL,NULL,'',NULL,NULL,320,NULL,NULL,'M','23529000589458',NULL,NULL,'42b29d0771f3b7ef','0.10277430629616','0.33591025447092'), +(41,'23529000054503','Odom','Gloria',NULL,NULL,'',NULL,NULL,'9156 Library Rd.',NULL,'Springfield, MA','44224',NULL,'(212) 555-1212',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'1952-04-05','SPL','S','1992-07-21','2020-12-31',NULL,NULL,NULL,'',NULL,NULL,NULL,NULL,NULL,'M','23529000054503',NULL,NULL,'42b29d0771f3b7ef','0.37122838419976','0.84841118831971'), +(42,'23529000126806','Hopkins','Brent',NULL,NULL,'',NULL,NULL,'6692 Library Rd.',NULL,'Santa Cruz, CA','21234',NULL,'(212) 555-1212',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'1977-11-13','SPL','T','1984-06-28','2020-12-31',NULL,NULL,NULL,'',NULL,NULL,337,NULL,NULL,'F','23529000126806',NULL,NULL,'42b29d0771f3b7ef','0.12837081973289','0.096620564438981'), +(43,'23529000104159','Benson','Clinton',NULL,NULL,'',NULL,NULL,'4695 Library Rd.',NULL,'Atwater, OH','21234',NULL,'(212) 555-1212',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'MPL','YA','1985-10-28','2020-12-31',NULL,NULL,NULL,'',NULL,NULL,NULL,NULL,NULL,'F','23529000104159',NULL,NULL,'42b29d0771f3b7ef','0.097990393729872','0.20009030606606'), +(44,'23529000395609','Stuart','Shannon',NULL,NULL,'',NULL,NULL,'5011 Library Rd.',NULL,'Springfield, MA','44224',NULL,'(212) 555-1212',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'1951-02-13','FRL','PT','1986-01-16','2020-12-31',NULL,NULL,NULL,'',NULL,NULL,NULL,NULL,NULL,'M','23529000395609',NULL,NULL,'42b29d0771f3b7ef','0.70648037987433','0.93213101190713'), +(45,'23529000050113','Alford','Jordan',NULL,NULL,'',NULL,NULL,'5129 Library Rd.',NULL,'Centerville, CA','44262',NULL,'(212) 555-1212',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'1952-05-27','MPL','ST','1985-09-09','2020-12-31',NULL,NULL,NULL,'',NULL,NULL,NULL,NULL,NULL,'M','23529000050113',NULL,NULL,'42b29d0771f3b7ef','0.54121395064631','0.90967674824379'), +(46,'23529000053836','Mcfarland','Marilyn',NULL,NULL,'',NULL,NULL,'4600 Library Rd.',NULL,'Springfield, MA','44224',NULL,'(212) 555-1212',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'SPL','PT','1984-05-09','2020-12-31',NULL,NULL,NULL,'',NULL,NULL,NULL,NULL,NULL,'F','23529000053836',NULL,NULL,'42b29d0771f3b7ef','0.92474192001367','0.89467938607063'), +(47,'23529000774373','Morris','Lauren',NULL,NULL,'',NULL,NULL,'5436 Library Rd.',NULL,'Springfield, MA','44224',NULL,'(212) 555-1212',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'SPL','PT','1986-03-20','2020-12-31',NULL,NULL,NULL,'',NULL,NULL,NULL,NULL,NULL,'F','23529000774373',NULL,NULL,'42b29d0771f3b7ef','0.69917120104578','0.81181787775067'), +(48,'23529000117565','Palmer','Bill',NULL,NULL,'',NULL,NULL,'2761 Library Rd.',NULL,'Springfield, MA','44224',NULL,'(212) 555-1212',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'1984-01-10','SPL','PT','1991-10-07','2020-12-31',NULL,NULL,NULL,'',NULL,NULL,NULL,NULL,NULL,'M','23529000117565',NULL,NULL,'42b29d0771f3b7ef','0.96157581634966','0.37242547922994'), +(49,'23529000651225','Burton','Mary',NULL,NULL,'',NULL,NULL,'8131 Library Rd.',NULL,'Santa Cruz, CA','21234',NULL,'(212) 555-1212',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'1977-12-25','MPL','PT','1985-05-30','2020-12-31',NULL,NULL,NULL,'',NULL,NULL,NULL,NULL,NULL,'F','23529000651225',NULL,NULL,'42b29d0771f3b7ef','0.97739997783434','0.76972348221552'), +(50,'23529001223636','Peters','Tommy',NULL,NULL,'',NULL,NULL,'5171 Library Rd.',NULL,'Portland, ME','44236',NULL,'(212) 555-1212',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'1951-06-14','SPL','PT','1984-05-09','2020-12-31',NULL,NULL,NULL,'',NULL,NULL,NULL,NULL,NULL,'F','23529001223636',NULL,NULL,'42b29d0771f3b7ef','0.91641750830823','0.27291712562825'); --- a/installer/data/mysql/kohastructure.sql +++ a/installer/data/mysql/kohastructure.sql @@ -610,7 +610,6 @@ CREATE TABLE `deletedborrowers` ( -- stores data related to the patrons/borrower `relationship` varchar(100) default NULL, -- used for children to include the relationship to their guarentor `sex` varchar(1) default NULL, -- patron/borrower's gender `password` varchar(60) default NULL, -- patron/borrower's encrypted password - `flags` int(11) default NULL, -- will include a number associated with the staff member's permissions `userid` varchar(75) default NULL, -- patron/borrower's opac and/or staff client log in `opacnote` mediumtext, -- a note on the patron/borrower's account that is visible in the OPAC and staff client `contactnote` varchar(255) default NULL, -- a note related to the patron/borrower's alternate address @@ -1642,7 +1641,6 @@ CREATE TABLE `borrowers` ( -- this table includes information about your patrons `relationship` varchar(100) default NULL, -- used for children to include the relationship to their guarentor `sex` varchar(1) default NULL, -- patron/borrower's gender `password` varchar(60) default NULL, -- patron/borrower's encrypted password - `flags` int(11) default NULL, -- will include a number associated with the staff member's permissions `userid` varchar(75) default NULL, -- patron/borrower's opac and/or staff client log in `opacnote` mediumtext, -- a note on the patron/borrower's account that is visible in the OPAC and staff client `contactnote` varchar(255) default NULL, -- a note related to the patron/borrower's alternate address @@ -2235,15 +2233,6 @@ CREATE TABLE `tags_index` ( -- a weighted list of all tags and where they are us -- Table structure for table `userflags` -- -DROP TABLE IF EXISTS `userflags`; -CREATE TABLE `userflags` ( - `bit` int(11) NOT NULL default 0, - `flag` varchar(30) default NULL, - `flagdesc` varchar(255) default NULL, - `defaulton` int(11) default NULL, - PRIMARY KEY (`bit`) -) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; - -- -- Table structure for table `virtualshelves` -- @@ -2425,20 +2414,6 @@ CREATE TABLE language_script_mapping ( ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; -- --- Table structure for table `permissions` --- - -DROP TABLE IF EXISTS `permissions`; -CREATE TABLE `permissions` ( - `module_bit` int(11) NOT NULL DEFAULT 0, - `code` varchar(64) NOT NULL DEFAULT '', - `description` varchar(255) DEFAULT NULL, - PRIMARY KEY (`module_bit`, `code`), - CONSTRAINT `permissions_ibfk_1` FOREIGN KEY (`module_bit`) REFERENCES `userflags` (`bit`) - ON DELETE CASCADE ON UPDATE CASCADE -) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; - --- -- Table structure for table `serialitems` -- @@ -2453,21 +2428,55 @@ CREATE TABLE `serialitems` ( ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; -- --- Table structure for table `user_permissions` +-- Table structure for table permission_modules -- -DROP TABLE IF EXISTS `user_permissions`; -CREATE TABLE `user_permissions` ( - `borrowernumber` int(11) NOT NULL DEFAULT 0, - `module_bit` int(11) NOT NULL DEFAULT 0, - `code` varchar(64) DEFAULT NULL, - CONSTRAINT `user_permissions_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) - ON DELETE CASCADE ON UPDATE CASCADE, - CONSTRAINT `user_permissions_ibfk_2` FOREIGN KEY (`module_bit`, `code`) REFERENCES `permissions` (`module_bit`, `code`) +DROP TABLE IF EXISTS permission_modules; +CREATE TABLE permission_modules ( + permission_module_id int(11) NOT NULL auto_increment, + module varchar(32) NOT NULL, + description varchar(255) DEFAULT NULL, + PRIMARY KEY (permission_module_id), + UNIQUE KEY (module) +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; + +-- +-- Table structure for table `permissions` +-- + +DROP TABLE IF EXISTS permissions; +CREATE TABLE permissions ( + permission_id int(11) NOT NULL auto_increment, + module varchar(32) NOT NULL, + code varchar(64) NOT NULL, + description varchar(255) DEFAULT NULL, + PRIMARY KEY (permission_id), + UNIQUE KEY (code), + CONSTRAINT permissions_to_modules_ibfk1 FOREIGN KEY (module) REFERENCES permission_modules (module) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; -- +-- Table structure for table borrower_permissions +-- + +DROP TABLE IF EXISTS borrower_permissions; +CREATE TABLE borrower_permissions ( + borrower_permission_id int(11) NOT NULL auto_increment, + borrowernumber int(11) NOT NULL, + permission_module_id int(11) NOT NULL, + permission_id int(11) NOT NULL, + PRIMARY KEY (borrower_permission_id), + UNIQUE KEY (borrowernumber, permission_module_id, permission_id), + CONSTRAINT borrower_permissions_ibfk_1 FOREIGN KEY (borrowernumber) REFERENCES borrowers (borrowernumber) + ON DELETE CASCADE ON UPDATE CASCADE, + CONSTRAINT borrower_permissions_ibfk_2 FOREIGN KEY (permission_id) REFERENCES permissions (permission_id) + ON DELETE CASCADE ON UPDATE CASCADE, + CONSTRAINT borrower_permissions_ibfk_3 FOREIGN KEY (permission_module_id) REFERENCES permission_modules (permission_module_id) + ON DELETE CASCADE ON UPDATE CASCADE +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; + +-- -- Table structure for table `tmp_holdsqueue` -- @@ -3420,7 +3429,6 @@ CREATE TABLE IF NOT EXISTS `borrower_modifications` ( `relationship` varchar(100) DEFAULT NULL, `sex` varchar(1) DEFAULT NULL, `password` varchar(30) DEFAULT NULL, - `flags` int(11) DEFAULT NULL, `userid` varchar(75) DEFAULT NULL, `opacnote` mediumtext, `contactnote` varchar(255) DEFAULT NULL, --- a/installer/data/mysql/userflags.sql +++ a/installer/data/mysql/userflags.sql @@ -1,22 +1,22 @@ -INSERT INTO userflags (bit, flag, flagdesc, defaulton) VALUES -(0,'superlibrarian','Access to all librarian functions',0), -(1,'circulate','Check out and check in items',0), -(2,'catalogue','Required for staff login. Staff access, allows viewing of catalogue in staff client.',0), -(3,'parameters','Manage Koha system settings (Administration panel)',0), -(4,'borrowers','Add or modify patrons',0), -(5,'permissions','Set user permissions',0), -(6,'reserveforothers','Place and modify holds for patrons',0), -(9,'editcatalogue','Edit catalog (Modify bibliographic/holdings data)',0), -(10,'updatecharges','Manage patrons fines and fees',0), -(11,'acquisition','Acquisition and/or suggestion management',0), -(12,'management','Set library management parameters (deprecated)',0), -(13,'tools','Use all tools (expand for granular tools permissions)',0), -(14,'editauthorities','Edit authorities',0), -(15,'serials','Manage serial subscriptions',0), -(16,'reports','Allow access to the reports module',0), -(17,'staffaccess','Allow staff members to modify permissions for other staff members',0), -(18,'coursereserves','Course reserves',0), -(19, 'plugins', 'Koha plugins', '0'), -(20, 'lists', 'Lists', 0), -(21, 'clubs', 'Patron clubs', '0') +INSERT INTO permission_modules (module, description) VALUES +('superlibrarian','Access to all librarian functions'), +('circulate','Check out and check in items'), +('catalogue','Required for staff login. Staff access, allows viewing of catalogue in staff client.'), +('parameters','Manage Koha system settings (Administration panel)'), +('borrowers','Add or modify patrons'), +('permissions','Set user permissions'), +('reserveforothers','Place and modify holds for patrons'), +('editcatalogue','Edit catalog (Modify bibliographic/holdings data)'), +('updatecharges','Manage patrons fines and fees'), +('acquisition','Acquisition and/or suggestion management'), +('management','Set library management parameters (deprecated)'), +('tools','Use all tools (expand for granular tools permissions)'), +('editauthorities','Edit authorities'), +('serials','Manage serial subscriptions'), +('reports','Allow access to the reports module'), +('staffaccess','Allow staff members to modify permissions for other staff members'), +('coursereserves','Course reserves'), +('plugins', 'Koha plugins'), +('lists', 'Lists'), +('clubs', 'Patron clubs') ; --- a/installer/data/mysql/userpermissions.sql +++ a/installer/data/mysql/userpermissions.sql @@ -1,85 +1,87 @@ -INSERT INTO permissions (module_bit, code, description) VALUES - ( 1, 'circulate_remaining_permissions', 'Remaining circulation permissions'), - ( 1, 'override_renewals', 'Override blocked renewals'), - ( 1, 'overdues_report', 'Execute overdue items report'), - ( 1, 'force_checkout', 'Force checkout if a limitation exists'), - ( 1, 'manage_restrictions', 'Manage restrictions for accounts'), - ( 1, 'self_checkout', 'Perform self checkout at the OPAC. It should be used for the patron matching the AutoSelfCheckID'), - ( 3, 'parameters_remaining_permissions', 'Remaining system parameters permissions'), - ( 3, 'manage_circ_rules', 'Manage circulation rules'), - ( 6, 'place_holds', 'Place holds for patrons'), - ( 6, 'modify_holds_priority', 'Modify holds priority'), - ( 9, 'edit_catalogue', 'Edit catalog (Modify bibliographic/holdings data)'), - ( 9, 'fast_cataloging', 'Fast cataloging'), - ( 9, 'edit_items', 'Edit items'), - ( 9, 'edit_items_restricted', 'Limit item modification to subfields defined in the SubfieldsToAllowForRestrictedEditing preference (please note that edit_item is still required)'), - ( 9, 'delete_all_items', 'Delete all items at once'), - (10, 'writeoff', 'Write off fines and fees'), - (10, 'remaining_permissions', 'Remaining permissions for managing fines and fees'), - (11, 'vendors_manage', 'Manage vendors'), - (11, 'contracts_manage', 'Manage contracts'), - (11, 'period_manage', 'Manage periods'), - (11, 'budget_manage', 'Manage budgets'), - (11, 'budget_modify', 'Modify budget (can''t create lines, but can modify existing ones)'), - (11, 'planning_manage', 'Manage budget plannings'), - (11, 'order_manage', 'Manage orders & basket'), - (11, 'order_manage_all', 'Manage all orders and baskets, regardless of restrictions on them'), - (11, 'group_manage', 'Manage orders & basketgroups'), - (11, 'order_receive', 'Manage orders & basket'), - (11, 'budget_add_del', 'Add and delete budgets (but can''t modify budgets)'), - (11, 'budget_manage_all', 'Manage all budgets'), - (11, 'edi_manage', 'Manage EDIFACT transmissions'), - (13, 'edit_news', 'Write news for the OPAC and staff interfaces'), - (13, 'label_creator', 'Create printable labels and barcodes from catalog and patron data'), - (13, 'edit_calendar', 'Define days when the library is closed'), - (13, 'moderate_comments', 'Moderate patron comments'), - (13, 'edit_notices', 'Define notices'), - (13, 'edit_notice_status_triggers', 'Set notice/status triggers for overdue items'), - (13, 'edit_quotes', 'Edit quotes for quote-of-the-day feature'), - (13, 'view_system_logs', 'Browse the system logs'), - (13, 'inventory', 'Perform inventory (stocktaking) of your catalog'), - (13, 'stage_marc_import', 'Stage MARC records into the reservoir'), - (13, 'manage_staged_marc', 'Manage staged MARC records, including completing and reversing imports'), - (13, 'export_catalog', 'Export bibliographic and holdings data'), - (13, 'import_patrons', 'Import patron data'), - (13, 'edit_patrons', 'Perform batch modification of patrons'), - (13, 'delete_anonymize_patrons', 'Delete old borrowers and anonymize circulation history (deletes borrower reading history)'), - (13, 'batch_upload_patron_images', 'Upload patron images in a batch or one at a time'), - (13, 'schedule_tasks', 'Schedule tasks to run'), - (13, 'items_batchmod', 'Perform batch modification of items'), - (13, 'items_batchmod_restricted', 'Limit batch item modification to subfields defined in the SubfieldsToAllowForRestrictedBatchmod preference (please note that items_batchmod is still required)'), - (13, 'items_batchdel', 'Perform batch deletion of items'), - (13, 'manage_csv_profiles', 'Manage CSV export profiles'), - (13, 'moderate_tags', 'Moderate patron tags'), - (13, 'rotating_collections', 'Manage rotating collections'), - (13, 'upload_local_cover_images', 'Upload local cover images'), - (13, 'manage_patron_lists', 'Add, edit and delete patron lists and their contents'), - (13, 'records_batchmod', 'Perform batch modification of records (biblios or authorities)'), - (13, 'marc_modification_templates', 'Manage marc modification templates'), - (13, 'records_batchdel', 'Perform batch deletion of records (bibliographic or authority)'), - (13, 'upload_general_files', 'Upload any file'), - (13, 'upload_manage', 'Manage uploaded files'), - (15, 'check_expiration', 'Check the expiration of a serial'), - (15, 'claim_serials', 'Claim missing serials'), - (15, 'create_subscription', 'Create a new subscription'), - (15, 'delete_subscription', 'Delete an existing subscription'), - (15, 'edit_subscription', 'Edit an existing subscription'), - (15, 'receive_serials', 'Serials receiving'), - (15, 'renew_subscription', 'Renew a subscription'), - (15, 'routing', 'Routing'), - (15, 'superserials', 'Manage subscriptions from any branch (only applies when IndependentBranches is used)'), - (16, 'execute_reports', 'Execute SQL reports'), - (16, 'create_reports', 'Create SQL reports'), - (16, 'delete_reports', 'Delete SQL reports'), - (18, 'manage_courses', 'Add, edit and delete courses'), - (18, 'add_reserves', 'Add course reserves'), - (18, 'delete_reserves', 'Remove course reserves'), - (19, 'manage', 'Manage plugins ( install / uninstall )'), - (19, 'tool', 'Use tool plugins'), - (19, 'report', 'Use report plugins'), - (19, 'configure', 'Configure plugins'), - (20, 'delete_public_lists', 'Delete public lists'), - (21, 'edit_templates', 'Create and update club templates'), - (21, 'edit_clubs', 'Create and update clubs'), - (21, 'enroll', 'Enroll patrons in clubs') +INSERT INTO permissions (module, code, description) VALUES + ( 'superlibrarian', 'superlibrarian', 'Access to all librarian functions'), + ( 'circulate', 'circulate_remaining_permissions', 'Remaining circulation permissions'), + ( 'circulate', 'override_renewals', 'Override blocked renewals'), + ( 'circulate', 'overdues_report', 'Execute overdue items report'), + ( 'circulate', 'force_checkout', 'Force checkout if a limitation exists'), + ( 'circulate', 'manage_restrictions', 'Manage restrictions for accounts'), + ( 'catalogue', 'staff_login', 'Allow staff login.'), + ( 'parameters', 'parameters_remaining_permissions', 'Remaining system parameters permissions'), + ( 'parameters', 'manage_circ_rules', 'manage circulation rules'), + ( 'borrowers', 'view_borrowers', 'Show borrower details and search for borrowers.'), + ( 'permissions', 'set_permissions', 'Set user permissions'), + ( 'reserveforothers','place_holds', 'Place holds for patrons'), + ( 'reserveforothers','modify_holds_priority', 'Modify holds priority'), + ( 'editcatalogue', 'edit_catalogue', 'Edit catalog (Modify bibliographic/holdings data)'), + ( 'editcatalogue', 'fast_cataloging', 'Fast cataloging'), + ( 'editcatalogue', 'edit_items', 'Edit items'), + ( 'editcatalogue', 'edit_items_restricted', 'Limit item modification to subfields defined in the SubfieldsToAllowForRestrictedEditing preference (please note that edit_item is still required)'), + ( 'editcatalogue', 'delete_all_items', 'Delete all items at once'), + ( 'updatecharges', 'writeoff', 'Write off fines and fees'), + ( 'updatecharges', 'remaining_permissions', 'Remaining permissions for managing fines and fees'), + ( 'acquisition', 'vendors_manage', 'Manage vendors'), + ( 'acquisition', 'contracts_manage', 'Manage contracts'), + ( 'acquisition', 'period_manage', 'Manage periods'), + ( 'acquisition', 'budget_manage', 'Manage budgets'), + ( 'acquisition', 'budget_modify', 'Modify budget (can''t create lines, but can modify existing ones)'), + ( 'acquisition', 'planning_manage', 'Manage budget plannings'), + ( 'acquisition', 'order_manage', 'Manage orders & basket'), + ( 'acquisition', 'order_manage_all', 'Manage all orders and baskets, regardless of restrictions on them'), + ( 'acquisition', 'group_manage', 'Manage orders & basketgroups'), + ( 'acquisition', 'order_receive', 'Manage orders & basket'), + ( 'acquisition', 'budget_add_del', 'Add and delete budgets (but can''t modify budgets)'), + ( 'acquisition', 'budget_manage_all', 'Manage all budgets'), + ( 'management', 'management', 'Set library management parameters (deprecated)'), + ( 'tools', 'edit_news', 'Write news for the OPAC and staff interfaces'), + ( 'tools', 'label_creator', 'Create printable labels and barcodes from catalog and patron data'), + ( 'tools', 'edit_calendar', 'Define days when the library is closed'), + ( 'tools', 'moderate_comments', 'Moderate patron comments'), + ( 'tools', 'edit_notices', 'Define notices'), + ( 'tools', 'edit_notice_status_triggers', 'Set notice/status triggers for overdue items'), + ( 'tools', 'edit_quotes', 'Edit quotes for quote-of-the-day feature'), + ( 'tools', 'view_system_logs', 'Browse the system logs'), + ( 'tools', 'inventory', 'Perform inventory (stocktaking) of your catalog'), + ( 'tools', 'stage_marc_import', 'Stage MARC records into the reservoir'), + ( 'tools', 'manage_staged_marc', 'Managed staged MARC records, including completing and reversing imports'), + ( 'tools', 'export_catalog', 'Export bibliographic and holdings data'), + ( 'tools', 'import_patrons', 'Import patron data'), + ( 'tools', 'edit_patrons', 'Perform batch modification of patrons'), + ( 'tools', 'delete_anonymize_patrons', 'Delete old borrowers and anonymize circulation history (deletes borrower reading history)'), + ( 'tools', 'batch_upload_patron_images', 'Upload patron images in a batch or one at a time'), + ( 'tools', 'schedule_tasks', 'Schedule tasks to run'), + ( 'tools', 'items_batchmod', 'Perform batch modification of items'), + ( 'tools', 'items_batchmod_restricted', 'Limit batch item modification to subfields defined in the SubfieldsToAllowForRestrictedBatchmod preference (please note that items_batchmod is still required)'), + ( 'tools', 'items_batchdel', 'Perform batch deletion of items'), + ( 'tools', 'manage_csv_profiles', 'Manage CSV export profiles'), + ( 'tools', 'moderate_tags', 'Moderate patron tags'), + ( 'tools', 'rotating_collections', 'Manage rotating collections'), + ( 'tools', 'upload_local_cover_images', 'Upload local cover images'), + ( 'tools', 'manage_patron_lists', 'Add, edit and delete patron lists and their contents'), + ( 'tools', 'records_batchmod', 'Perform batch modification of records (biblios or authorities)'), + ( 'tools', 'marc_modification_templates', 'Manage marc modification templates'), + ( 'tools', 'records_batchdel', 'Perform batch deletion of records (bibliographic or authority)'), + ( 'editauthorities', 'edit_authorities', 'Edit authorities'), + ( 'serials', 'check_expiration', 'Check the expiration of a serial'), + ( 'serials', 'claim_serials', 'Claim missing serials'), + ( 'serials', 'create_subscription', 'Create a new subscription'), + ( 'serials', 'delete_subscription', 'Delete an existing subscription'), + ( 'serials', 'edit_subscription', 'Edit an existing subscription'), + ( 'serials', 'receive_serials', 'Serials receiving'), + ( 'serials', 'renew_subscription', 'Renew a subscription'), + ( 'serials', 'routing', 'Routing'), + ( 'serials', 'superserials', 'Manage subscriptions from any branch (only applies when IndependentBranches is used)'), + ( 'reports', 'execute_reports', 'Execute SQL reports'), + ( 'reports', 'create_reports', 'Create SQL reports'), + ( 'staffaccess', 'staff_access_permissions', 'Allow staff members to modify permissions for other staff members'), + ( 'coursereserves', 'manage_courses', 'Add, edit and delete courses'), + ( 'coursereserves', 'add_reserves', 'Add course reserves'), + ( 'coursereserves', 'delete_reserves', 'Remove course reserves'), + ( 'plugins', 'manage', 'Manage plugins ( install / uninstall )'), + ( 'plugins', 'tool', 'Use tool plugins'), + ( 'plugins', 'report', 'Use report plugins'), + ( 'plugins', 'configure', 'Configure plugins'), + ( 'lists', 'delete_public_lists', 'Delete public lists'), + ( 'clubs', 'edit_templates', 'Create and update club templates'), + ( 'clubs', 'edit_clubs', 'Create and update clubs'), + ( 'clubs', 'enroll', 'Enroll patrons in clubs') ; --- a/koha-tmpl/intranet-tmpl/prog/en/modules/members/member-flags.tt +++ a/koha-tmpl/intranet-tmpl/prog/en/modules/members/member-flags.tt @@ -11,23 +11,23 @@ $("#permissionstree").treeview({animated: "fast", collapsed: true}); // Enforce Superlibrarian Privilege Mutual Exclusivity - if($('input[id="flag-0"]:checked').length){ + if($('input[id="flag-superlibrarian"]:checked').length){ if ($('input[name="flag"]:checked').length > 1){ alert(_("Inconsistency detected! The superlibrarian privilege is mutually exclusive of other privileges, as it includes them all. This patron's privileges will now be reset to include only superlibrarian.")); } $('input[name="flag"]').each(function() { - if($(this).attr('id') != "flag-0"){ + if($(this).attr('id') != "flag-superlibrarian"){ $(this).prop('disabled', true); $(this).prop('checked', false); } }); } - $('input#flag-0').click(function() { - if($('input[id="flag-0"]:checked').length){ + $('input#flag-superlibrarian').click(function() { + if($('input[id="flag-superlibrarian"]:checked').length){ $('input[name="flag"]').each(function() { - if($(this).attr('id') != "flag-0"){ + if($(this).attr('id') != "flag-superlibrarian"){ $(this).prop('disabled', true); $(this).prop('checked', false); } @@ -131,9 +131,9 @@ [% FOREACH loo IN loop %] [% IF ( loo.expand ) %] -
  • +
  • [% ELSE %] -
  • +
  • [% END %] [% IF ( loo.checked ) %] --- a/members/member-flags.pl +++ a/members/member-flags.pl @@ -13,7 +13,9 @@ use C4::Auth qw(:DEFAULT :EditPermissions); use C4::Context; use C4::Members; use C4::Members::Attributes qw(GetBorrowerAttributes); -#use C4::Acquisitions; +use Koha::Auth::PermissionManager; + +use Koha::Exception::BadParameter; use Koha::Patron::Categories; @@ -38,7 +40,7 @@ my ($template, $loggedinuser, $cookie) = get_template_and_user({ debug => 1, }); - +my $permissionManager = Koha::Auth::PermissionManager->new(); my %member2; $member2{'borrowernumber'}=$member; @@ -52,122 +54,36 @@ if ($input->param('newflags')) { my $dbh=C4::Context->dbh(); - + #Cast CGI-params into a permissions HASH. my @perms = $input->multi_param('flag'); - my %all_module_perms = (); my %sub_perms = (); foreach my $perm (@perms) { - if ($perm !~ /:/) { - $all_module_perms{$perm} = 1; + if ($perm eq 'superlibrarian') { + $sub_perms{superlibrarian}->{superlibrarian} = 1; + } + elsif ($perm !~ /:/) { + #DEPRECATED, GUI still sends the module flags here even though they have been removed from the DB. } else { my ($module, $sub_perm) = split /:/, $perm, 2; - push @{ $sub_perms{$module} }, $sub_perm; + $sub_perms{$module}->{$sub_perm} = 1; } } - # construct flags - my $module_flags = 0; - my $sth=$dbh->prepare("SELECT bit,flag FROM userflags ORDER BY bit"); - $sth->execute(); - while (my ($bit, $flag) = $sth->fetchrow_array) { - if (exists $all_module_perms{$flag}) { - $module_flags += 2**$bit; - } - } - - $sth = $dbh->prepare("UPDATE borrowers SET flags=? WHERE borrowernumber=?"); - $sth->execute($module_flags, $member); - - # deal with subpermissions - $sth = $dbh->prepare("DELETE FROM user_permissions WHERE borrowernumber = ?"); - $sth->execute($member); - $sth = $dbh->prepare("INSERT INTO user_permissions (borrowernumber, module_bit, code) - SELECT ?, bit, ? - FROM userflags - WHERE flag = ?"); - foreach my $module (keys %sub_perms) { - next if exists $all_module_perms{$module}; - foreach my $sub_perm (@{ $sub_perms{$module} }) { - $sth->execute($member, $sub_perm, $module); - } - } + $permissionManager->revokeAllPermissions($member); + $permissionManager->grantPermissions($member, \%sub_perms); print $input->redirect("/cgi-bin/koha/members/moremember.pl?borrowernumber=$member"); } else { - my $flags = C4::Members::patronflags( $bor ); - my $accessflags; - my $dbh = C4::Context->dbh(); - # FIXME This needs to be improved to avoid doing the same query - my $sth = $dbh->prepare("select bit,flag from userflags"); - $sth->execute; - while ( my ( $bit, $flag ) = $sth->fetchrow ) { - if ( $bor->{flags} && $bor->{flags} & 2**$bit ) { - $accessflags->{$flag} = 1; - } - } + my $all_perms = $permissionManager->listKohaPermissionsAsHASH(); + my $user_perms = $permissionManager->getBorrowerPermissions($member); - my $all_perms = get_all_subpermissions(); - my $user_perms = get_user_subpermissions($bor->{'userid'}); - $sth = $dbh->prepare("SELECT bit, flag FROM userflags ORDER BY bit"); - $sth->execute; + $all_perms = markBorrowerGrantedPermissions($all_perms, $user_perms); my @loop; - while (my ($bit, $flag) = $sth->fetchrow) { - my $checked=''; - if ($accessflags->{$flag}) { - $checked= 1; - } - - my %row = ( bit => $bit, - flag => $flag, - checked => $checked, - ); - - my @sub_perm_loop = (); - my $expand_parent = 0; - if ($checked) { - if (exists $all_perms->{$flag}) { - $expand_parent = 1; - foreach my $sub_perm (sort keys %{ $all_perms->{$flag} }) { - push @sub_perm_loop, { - id => "${flag}_$sub_perm", - perm => "$flag:$sub_perm", - code => $sub_perm, - checked => 1 - }; - } - } - } else { - if (exists $user_perms->{$flag}) { - $expand_parent = 1; - # put selected ones first - foreach my $sub_perm (sort keys %{ $user_perms->{$flag} }) { - push @sub_perm_loop, { - id => "${flag}_$sub_perm", - perm => "$flag:$sub_perm", - code => $sub_perm, - checked => 1 - }; - } - } - # then ones not selected - if (exists $all_perms->{$flag}) { - foreach my $sub_perm (sort keys %{ $all_perms->{$flag} }) { - push @sub_perm_loop, { - id => "${flag}_$sub_perm", - perm => "$flag:$sub_perm", - code => $sub_perm, - checked => 0 - } unless exists $user_perms->{$flag} and exists $user_perms->{$flag}->{$sub_perm}; - } - } - } - $row{expand} = $expand_parent; - if ($#sub_perm_loop > -1) { - $row{sub_perm_loop} = \@sub_perm_loop; - } - push @loop, \%row; + push @loop, preparePermissionModuleForDisplay($all_perms, 'superlibrarian'); + foreach my $module (sort(keys(%$all_perms))) { + push @loop, preparePermissionModuleForDisplay($all_perms, $module) unless $module eq 'superlibrarian'; } if ( $bor->{'category_type'} eq 'C') { @@ -219,3 +135,75 @@ $template->param( output_html_with_http_headers $input, $cookie, $template->output; } + +=head markBorrowerGrantedPermissions + +Adds a 'checked'-value for all subpermissions in the all-Koha-Permissions-list +that the current borrower has been granted. +@PARAM1 HASHRef of all Koha permissions and modules. +@PARAM1 ARRAYRef of all the granted Koha::Auth::BorrowerPermission-objects. +@RETURNS @PARAM1, slightly checked. +=cut + +sub markBorrowerGrantedPermissions { + my ($all_perms, $user_perms) = @_; + + foreach my $borrowerPermission (@$user_perms) { + my $module = $borrowerPermission->getPermissionModule->module; + my $code = $borrowerPermission->getPermission->code; + $all_perms->{$module}->{permissions}->{$code}->{checked} = 1; + } + return $all_perms; +} + +=head checkIfAllModulePermissionsGranted + +@RETURNS Boolean, 1 if all permissions granted. +=cut + +sub checkIfAllModulePermissionsGranted { + my ($moduleHash) = @_; + foreach my $code (keys(%{$moduleHash->{permissions}})) { + unless ($moduleHash->{permissions}->{$code}->{checked}) { + return 0; + } + } + return 1; +} + +sub preparePermissionModuleForDisplay { + my ($all_perms, $module) = @_; + + my $moduleHash = $all_perms->{$module}; + my $checked = checkIfAllModulePermissionsGranted($moduleHash); + + my %row = ( + bit => $module, + flag => $module, + checked => $checked, + flagdesc => $moduleHash->{description} ); + + my @sub_perm_loop = (); + my $expand_parent = 0; + + if ($module ne 'superlibrarian') { + foreach my $sub_perm (sort keys %{ $all_perms->{$module}->{permissions} }) { + my $sub_perm_checked = $all_perms->{$module}->{permissions}->{$sub_perm}->{checked}; + $expand_parent = 1 if $sub_perm_checked; + + push @sub_perm_loop, { + id => "${module}_$sub_perm", + perm => "$module:$sub_perm", + code => $sub_perm, + description => $all_perms->{$module}->{permissions}->{$sub_perm}->{description}, + checked => $sub_perm_checked || 0, + }; + } + + $row{expand} = $expand_parent; + if ($#sub_perm_loop > -1) { + $row{sub_perm_loop} = \@sub_perm_loop; + } + } + return \%row; +} --- a/opac/opac-user.pl +++ a/opac/opac-user.pl @@ -293,7 +293,7 @@ $template->param( ); # current alert subscriptions -my $alerts = getalert($borrowernumber); +my $alerts = getalert($borrowernumber) if $borrowernumber; foreach ( @$alerts ) { $_->{ $_->{type} } = 1; $_->{relatedto} = findrelatedto( $_->{type}, $_->{externalid} ); --- a/t/Patron.t +++ a/t/Patron.t @@ -93,7 +93,7 @@ my $patron = Koha::Patron->new( relationship => 'myRelationship', sex => 'M', password => 'hfkurhfe976634èj!', - flags => '55555', +# flags => '55555', userid => '87987', opacnote => 'myOpacnote', contactnote => 'myContactnote', @@ -115,7 +115,8 @@ my $patron = Koha::Patron->new( # patron Accessor tests subtest 'Accessor tests' => sub { - plan tests => 65; +# plan tests => 65; #skipped flags test + plan tests => 64; is( $patron->borrowernumber, '12345', 'borrowernumber accessor returns correct value' ); is( $patron->cardnumber, '1234567890', 'cardnumber accessor returns correct value' ); is( $patron->surname, 'mySurname', 'surname accessor returns correct value' ); @@ -164,7 +165,7 @@ subtest 'Accessor tests' => sub { is( $patron->relationship, 'myRelationship', 'relationship accessor returns correct value' ); is( $patron->sex, 'M', 'sex accessor returns correct value' ); is( $patron->password, 'hfkurhfe976634èj!', 'password accessor returns correct value' ); - is( $patron->flags, '55555', 'flags accessor returns correct value' ); +# is( $patron->flags, '55555', 'flags accessor returns correct value' ); is( $patron->userid, '87987', 'userid accessor returns correct value' ); is( $patron->opacnote, 'myOpacnote', 'opacnote accessor returns correct value' ); is( $patron->contactnote, 'myContactnote', 'contactnote accessor returns correct value' ); @@ -185,7 +186,8 @@ subtest 'Accessor tests' => sub { # patron Set tests subtest 'Set tests' => sub { - plan tests => 65; +# plan tests => 65; skipped flags test + plan tests => 64; $patron->set( { @@ -237,7 +239,7 @@ subtest 'Set tests' => sub { relationship => 'SmyRelationship', sex => 'F', password => 'zerzerzer#', - flags => '666666', +# flags => '666666', userid => '98233', opacnote => 'SmyOpacnote', contactnote => 'SmyContactnote', @@ -305,7 +307,7 @@ subtest 'Set tests' => sub { is( $patron->relationship, 'SmyRelationship', 'relationship field set ok' ); is( $patron->sex, 'F', 'sex field set ok' ); is( $patron->password, 'zerzerzer#', 'password field set ok' ); - is( $patron->flags, '666666', 'flags field set ok' ); +# is( $patron->flags, '666666', 'flags field set ok' ); is( $patron->userid, '98233', 'userid field set ok' ); is( $patron->opacnote, 'SmyOpacnote', 'opacnote field set ok' ); is( $patron->contactnote, 'SmyContactnote', 'contactnote field set ok' ); --- a/t/db_dependent/Auth/haspermission.t +++ a/t/db_dependent/Auth/haspermission.t @@ -20,7 +20,7 @@ # along with Koha; if not, see . use Modern::Perl; -use Test::More tests => 13; +use Test::More tests => 16; use Koha::Database; use t::lib::TestBuilder; @@ -104,5 +104,15 @@ $r = haspermission( $borr2->{userid}, { }); is( ref($r), 'HASH', 'Borrower2/tools granular two upload subperms' ); +# Check haspermission for undef userid +$r = haspermission( undef, undef ); +is( ref($r), 'HASH', 'ok'); + +$r = haspermission( undef, { borrowers => '*' }); +is( $r, 0, 'ok'); + +$r = haspermission( undef, { reserveforothers => 'place_holds' }); +is( $r, 0, 'ok'); + # End $schema->storage->txn_rollback; --- a/t/db_dependent/Koha/Auth.t +++ a/t/db_dependent/Koha/Auth.t @@ -22,7 +22,10 @@ use Modern::Perl; use Test::More; use Try::Tiny; #Even Selenium::Remote::Driver uses Try::Tiny :) +use Koha::Auth::PermissionManager; + use t::lib::Page::Mainpage; +use t::lib::Page::Opac::OpacMain; use t::lib::TestObjects::PatronFactory; @@ -36,17 +39,36 @@ my $borrowers = $borrowerFactory->createTestGroup([ surname => 'Kivi', cardnumber => '1A01', branchcode => 'CPL', - flags => '1', #superlibrarian, not exactly a very good way of doing permission testing? userid => 'mini_admin', password => $password, }, + {firstname => 'Admin', + surname => 'Administrative', + cardnumber => 'maxi_admin', + branchcode => 'FPL', + userid => 'maxi_admin', + password => $password, + }, ], undef, $testContext); +my $permissionManager = Koha::Auth::PermissionManager->new(); +$permissionManager->grantPermission($borrowers->{'1A01'}, 'catalogue', 'staff_login'); +$permissionManager->grantPermission($borrowers->{'maxi_admin'}, 'superlibrarian', 'superlibrarian'); + ##Test context set, starting testing: eval { #run in a eval-block so we don't die without tearing down the test context - testPasswordLogin(); + my $mainpage = t::lib::Page::Mainpage->new(); + testPasswordLoginLogout($mainpage); + testSuperuserPasswordLoginLogout($mainpage); + testSuperlibrarianPasswordLoginLogout($mainpage); + $mainpage->quit(); + my $opacmain = t::lib::Page::Opac::OpacMain->new(); + testOpacPasswordLoginLogout($opacmain); + testSuperuserPasswordLoginLogout($opacmain); + testOpacSuperlibrarianPasswordLoginLogout($opacmain); + $opacmain->quit(); }; if ($@) { #Catch all leaking errors and gracefully terminate. warn $@; @@ -66,7 +88,30 @@ sub tearDown { ### STARTING TEST IMPLEMENTATIONS ### ###################################################### -sub testPasswordLogin { - my $mainpage = t::lib::Page::Mainpage->new(); - $mainpage->isPasswordLoginAvailable()->doPasswordLogin($borrowers->{'1A01'}->userid(), $password)->quit(); -} +sub testPasswordLoginLogout { + my ($mainpage) = @_; + $mainpage->isPasswordLoginAvailable()->doPasswordLogin($borrowers->{'1A01'}->userid(), $password) + ->isLoggedInBranchCode($borrowers->{'1A01'}->branchcode()) + ->doPasswordLogout(); +} +sub testOpacPasswordLoginLogout { + my ($mainpage) = @_; + $mainpage->isPasswordLoginAvailable()->doPasswordLogin($borrowers->{'1A01'}->userid(), $password) + ->doPasswordLogout(); +} +sub testSuperuserPasswordLoginLogout { + my ($mainpage) = @_; + $mainpage->isPasswordLoginAvailable()->doPasswordLogin(C4::Context->config('user'), C4::Context->config('pass')) + ->doPasswordLogout(); +} +sub testSuperlibrarianPasswordLoginLogout { + my ($mainpage) = @_; + $mainpage->isPasswordLoginAvailable()->doPasswordLogin($borrowers->{'maxi_admin'}->userid(), $password) + ->isLoggedInBranchCode($borrowers->{'maxi_admin'}->branchcode()) + ->doPasswordLogout(); +} +sub testOpacSuperlibrarianPasswordLoginLogout { + my ($mainpage) = @_; + $mainpage->isPasswordLoginAvailable()->doPasswordLogin($borrowers->{'maxi_admin'}->userid(), $password) + ->doPasswordLogout(); +} --- a/t/db_dependent/Koha/Auth/BorrowerPermission.t +++ a/t/db_dependent/Koha/Auth/BorrowerPermission.t @@ -0,0 +1,102 @@ +#!/usr/bin/perl + +# Copyright 2015 Vaara-kirjastot +# +# This file is part of Koha. +# +# Koha is free software; you can redistribute it and/or modify it +# under the terms of the GNU General Public License as published by +# the Free Software Foundation; either version 3 of the License, or +# (at your option) any later version. +# +# Koha is distributed in the hope that it will be useful, but +# WITHOUT ANY WARRANTY; without even the implied warranty of +# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the +# GNU General Public License for more details. +# +# You should have received a copy of the GNU General Public License +# along with Koha; if not, see . + +use Modern::Perl; +use Test::More; + +use Koha::Auth::BorrowerPermission; +use Koha::Auth::BorrowerPermissions; + +use t::lib::TestObjects::ObjectFactory; +use t::lib::TestObjects::PatronFactory; + +##Setting up the test context +my $testContext = {}; + +my $borrowerFactory = t::lib::TestObjects::PatronFactory->new(); +my $borrowers = $borrowerFactory->createTestGroup([ + {firstname => 'Olli-Antti', + surname => 'Kivi', + cardnumber => '1A01', + branchcode => 'CPL', + }, + {firstname => 'Alli-Ontti', + surname => 'Ivik', + cardnumber => '1A02', + branchcode => 'CPL', + }, + ], undef, $testContext); + +##Test context set, starting testing: +eval { #run in a eval-block so we don't die without tearing down the test context + ##Basic id-based creation. + my $borrowerPermissionById = Koha::Auth::BorrowerPermission->new({borrowernumber => $borrowers->{'1A01'}->borrowernumber, permission_module_id => 1, permission_id => 1}); + $borrowerPermissionById->store(); + my @borrowerPermissionById = Koha::Auth::BorrowerPermissions->search({borrowernumber => $borrowers->{'1A01'}->borrowernumber}); + is(scalar(@borrowerPermissionById), 1, "BorrowerPermissions, id-based creation:> Borrower has only one permission"); + is($borrowerPermissionById[0]->permission_module_id, 1, "BorrowerPermissions, id-based creation:> Same permission_module_id"); + is($borrowerPermissionById[0]->permission_id, 1, "BorrowerPermissions, id-based creation:> Same permission_id"); + + ##Basic name-based creation. + my $borrowerPermissionByName = Koha::Auth::BorrowerPermission->new({borrowernumber => $borrowers->{'1A02'}->borrowernumber, permissionModule => 'circulate', permission => 'manage_restrictions'}); + $borrowerPermissionByName->store(); + my @borrowerPermissionByName = Koha::Auth::BorrowerPermissions->search({borrowernumber => $borrowers->{'1A02'}->borrowernumber}); + is(scalar(@borrowerPermissionByName), 1, "BorrowerPermissions, name-based creation:> Borrower has only one permission"); + is($borrowerPermissionByName[0]->getPermissionModule->module, 'circulate', "BorrowerPermissions, name-based creation:> Same permission_module"); + is($borrowerPermissionByName[0]->getPermission->code, 'manage_restrictions', "BorrowerPermissions, name-based creation:> Same permission"); + + ##Testing setter/getter for Borrower + my $borrower1A01 = $borrowerPermissionById->getBorrower(); + is($borrower1A01->cardnumber, "1A01", "BorrowerPermissions, setter/getter:> getBorrower() 1A01"); + my $borrower1A02 = $borrowerPermissionByName->getBorrower(); + is($borrower1A02->cardnumber, "1A02", "BorrowerPermissions, setter/getter:> getBorrower() 1A02"); + + $borrowerPermissionById->setBorrower($borrower1A02); + is($borrowerPermissionById->getBorrower()->cardnumber, "1A02", "BorrowerPermissions, setter/getter:> setBorrower() 1A02"); + $borrowerPermissionByName->setBorrower($borrower1A01); + is($borrowerPermissionByName->getBorrower()->cardnumber, "1A01", "BorrowerPermissions, setter/getter:> setBorrower() 1A01"); + + ##Testing getter for PermissionModule + my $permissionModule1 = $borrowerPermissionById->getPermissionModule(); + is($permissionModule1->permission_module_id, 1, "BorrowerPermissions, setter/getter:> getPermissionModule() 1"); + my $permissionModuleCirculate = $borrowerPermissionByName->getPermissionModule(); + is($permissionModuleCirculate->module, "circulate", "BorrowerPermissions, setter/getter:> getPermissionModule() circulate"); + + #Not testing setters because changing the module might not make any sense. + #Then we would need to make sure we dont end up with bad permissionModule->permission combinations. + + ##Testing getter for Permission + my $permission1 = $borrowerPermissionById->getPermission(); + is($permission1->permission_id, 1, "BorrowerPermissions, setter/getter:> getPermission() 1"); + my $permissionManage_restrictions = $borrowerPermissionByName->getPermission(); + is($permissionManage_restrictions->code, "manage_restrictions", "BorrowerPermissions, setter/getter:> getPermission() manage_restrictions"); +}; +if ($@) { #Catch all leaking errors and gracefully terminate. + warn $@; + tearDown(); + exit 1; +} + +##All tests done, tear down test context +$borrowerFactory->tearDownTestContext($testContext); +done_testing; + +sub tearDown { + t::lib::TestObjects::ObjectFactory->tearDownTestContext($testContext); +} --- a/t/db_dependent/Koha/Auth/PermissionManager.t +++ a/t/db_dependent/Koha/Auth/PermissionManager.t @@ -0,0 +1,221 @@ +#!/usr/bin/perl + +# Copyright 2015 Vaara-kirjastot +# +# This file is part of Koha. +# +# Koha is free software; you can redistribute it and/or modify it +# under the terms of the GNU General Public License as published by +# the Free Software Foundation; either version 3 of the License, or +# (at your option) any later version. +# +# Koha is distributed in the hope that it will be useful, but +# WITHOUT ANY WARRANTY; without even the implied warranty of +# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the +# GNU General Public License for more details. +# +# You should have received a copy of the GNU General Public License +# along with Koha; if not, see . + +use Modern::Perl; +use Test::More; +use Try::Tiny; +use Scalar::Util qw(blessed); + +use Koha::Auth::PermissionManager; + +use t::lib::TestObjects::ObjectFactory; +use t::lib::TestObjects::PatronFactory; + + +##Setting up the test context +my $testContext = {}; + +my $borrowerFactory = t::lib::TestObjects::PatronFactory->new(); +my $borrowers = $borrowerFactory->createTestGroup([ + {firstname => 'Olli-Antti', + surname => 'Kivi', + cardnumber => '1A01', + branchcode => 'CPL', + }, + {firstname => 'Alli-Ontti', + surname => 'Ivik', + cardnumber => '1A02', + branchcode => 'CPL', + }, + ], undef, $testContext); + +##Test context set, starting testing: +eval { #run in a eval-block so we don't die without tearing down the test context + my $permissionManager = Koha::Auth::PermissionManager->new(); + my ($permissionModule, $permission, $failureCaughtFlag, $permissionsList); + + ##Test getBorrowerPermissions + $permissionManager->grantPermission($borrowers->{'1A01'}, 'circulate', 'force_checkout'); + $permissionManager->grantPermission($borrowers->{'1A01'}, 'circulate', 'manage_restrictions'); + $permissionsList = $permissionManager->getBorrowerPermissions($borrowers->{'1A01'}); + is($permissionsList->[0]->getPermission->code, 'force_checkout', "PermissionManager, getBorrowerPermissions:> Check 1."); + is($permissionsList->[1]->getPermission->code, 'manage_restrictions', "PermissionManager, getBorrowerPermissions:> Check 2."); + $permissionManager->revokePermission($borrowers->{'1A01'}, 'circulate', 'force_checkout'); + $permissionManager->revokePermission($borrowers->{'1A01'}, 'circulate', 'manage_restrictions'); + + ##Test grantPermissions && revokeAllPermissions + $permissionManager->grantPermissions($borrowers->{'1A01'}, + { borrowers => 'view_borrowers', + reserveforothers => ['place_holds'], + tools => ['edit_news', 'edit_notices'], + acquisition => { + budget_add_del => 1, + budget_modify => 1, + }, + }); + $permissionManager->hasPermission($borrowers->{'1A01'}, 'borrowers', 'view_borrowers'); + $permissionManager->hasPermission($borrowers->{'1A01'}, 'tools', 'edit_notices'); + $permissionManager->hasPermission($borrowers->{'1A01'}, 'acquisition', 'budget_modify'); + $permissionsList = $permissionManager->getBorrowerPermissions($borrowers->{'1A01'}); + is(scalar(@$permissionsList), 6, "PermissionManager, grantPermissions:> Permissions as HASH, ARRAY and Scalar."); + + $permissionManager->revokeAllPermissions($borrowers->{'1A01'}); + $permissionsList = $permissionManager->getBorrowerPermissions($borrowers->{'1A01'}); + is(scalar(@$permissionsList), 0, "PermissionManager, revokeAllPermissions:> No permissions left."); + + ##Test listKohaPermissionsAsHASH + my $listedPermissions = $permissionManager->listKohaPermissionsAsHASH(); + ok(ref($listedPermissions->{circulate}->{permissions}->{force_checkout}) eq 'HASH', "PermissionManager, listKohaPermissionsAsHASH:> Check 1."); + ok(ref($listedPermissions->{editcatalogue}->{permissions}->{edit_catalogue}) eq 'HASH', "PermissionManager, listKohaPermissionsAsHASH:> Check 2."); + ok(defined($listedPermissions->{reports}->{permissions}->{create_reports}->{description}), "PermissionManager, listKohaPermissionsAsHASH:> Check 3."); + ok(defined($listedPermissions->{permissions}->{description}), "PermissionManager, listKohaPermissionsAsHASH:> Check 4."); + + + + ### TESTING WITH unique keys, instead of the recommended Koha::Objects. ### + #Arguably this makes for more clear tests cases :) + ##Add/get PermissionModule + $permissionModule = $permissionManager->addPermissionModule({module => 'test', description => 'Just testing this module.'}); + is($permissionModule->module, "test", "PermissionManager from names, add/getPermissionModule:> Module added."); + $permissionModule = $permissionManager->getPermissionModule('test'); + is($permissionModule->module, "test", "PermissionManager from names, add/getPermissionModule:> Module got."); + + ##Add/get Permission + $permission = $permissionManager->addPermission({module => 'test', code => 'testperm', description => 'Just testing this permission.'}); + is($permission->code, "testperm", "PermissionManager from names, add/getPermission:> Permission added."); + $permission = $permissionManager->getPermission('testperm'); + is($permission->code, "testperm", "PermissionManager from names, add/getPermission:> Permission got."); + + ##Grant permission + $permissionManager->grantPermission($borrowers->{'1A01'}, 'test', 'testperm'); + ok($permissionManager->hasPermission($borrowers->{'1A01'}, 'test', 'testperm'), "PermissionManager from names, grant/hasPermission:> Borrower granted permission."); + + ##hasPermission with wildcard + ok($permissionManager->hasPermission($borrowers->{'1A01'}, 'test', '*'), "PermissionManager from names, hasPermission:> Wildcard permission."); + + ##hasPermissions with wildcard + ok($permissionManager->hasPermissions($borrowers->{'1A01'}, {test => ['*']}), "PermissionManager from names, hasPermission:> Wildcard permissions from array."); + + ##Revoke permission + $permissionManager->revokePermission($borrowers->{'1A01'}, 'test', 'testperm'); + $failureCaughtFlag = 0; + try { + $permissionManager->hasPermission($borrowers->{'1A01'}, 'test', 'testperm'); + } catch { + if (blessed($_) && $_->isa('Koha::Exception::NoPermission')) { + $failureCaughtFlag = 1; + } + else { + die $_; #Somekind of another problem arised and rethrow it. + } + }; + ok($failureCaughtFlag, "PermissionManager from names, revoke/hasPermission:> Borrower revoked permission."); + + ##Delete permissions and modules we just made. When we delete the module first, the permissions is ON CASCADE DELETEd + $permissionManager->delPermissionModule('test'); + $permissionModule = $permissionManager->getPermissionModule('test'); + ok(not(defined($permissionModule)), "PermissionManager from names, delPermissionModule:> Module deleted."); + + $failureCaughtFlag = 0; + try { + #This subpermission is now deleted due to cascading delete of the parent permissionModule + #We catch the exception gracefully and report test success + $permissionManager->delPermission('testperm'); + } catch { + if (blessed($_) && $_->isa('Koha::Exception::UnknownObject')) { + $failureCaughtFlag = 1; + } + else { + die $_; #Somekind of another problem arised and rethrow it. + } + }; + ok($failureCaughtFlag, "PermissionManager from names, delPermission:> Permission already deleted, exception caught."); + $permission = $permissionManager->getPermission('testperm'); + ok(not(defined($permission)), "PermissionManager from names, delPermission:> Permission deleted."); + + + + ### TESTING WITH Koha::Object parameters instead. ### + ##Add/get PermissionModule + $permissionModule = $permissionManager->addPermissionModule({module => 'test', description => 'Just testing this module.'}); + is($permissionModule->module, "test", "PermissionManager from objects, add/getPermissionModule:> Module added."); + $permissionModule = $permissionManager->getPermissionModule($permissionModule); + is($permissionModule->module, "test", "PermissionManager from objects, add/getPermissionModule:> Module got."); + + ##Add/get Permission + $permission = $permissionManager->addPermission({module => 'test', code => 'testperm', description => 'Just testing this permission.'}); + is($permission->code, "testperm", "PermissionManager from objects, add/getPermission:> Permission added."); + $permission = $permissionManager->getPermission($permission); + is($permission->code, "testperm", "PermissionManager from objects, add/getPermission:> Permission got."); + + ##Grant permission + $permissionManager->grantPermission($borrowers->{'1A01'}, $permissionModule, $permission); + ok($permissionManager->hasPermission($borrowers->{'1A01'}, $permissionModule, $permission), "PermissionManager from objects, grant/hasPermission:> Borrower granted permission."); + + ##hasPermission with wildcard + ok($permissionManager->hasPermission($borrowers->{'1A01'}, $permissionModule, '*'), "PermissionManager from objects, hasPermission:> Wildcard permission."); + + ##hasPermissions with wildcard, we quite cannot use a blessed Object as a HASH key + #ok($permissionManager->hasPermissions($borrowers->{'1A01'}, {$permissionModule->module() => ['*']}), "PermissionManager from objects, hasPermission:> Wildcard permissions from array."); + + ##Revoke permission + $permissionManager->revokePermission($borrowers->{'1A01'}, $permissionModule, $permission); + $failureCaughtFlag = 0; + try { + $permissionManager->hasPermission($borrowers->{'1A01'}, $permissionModule, $permission); + } catch { + if (blessed($_) && $_->isa('Koha::Exception::NoPermission')) { + $failureCaughtFlag = 1; + } + else { + die $_; #Somekind of another problem arised and rethrow it. + } + }; + ok($failureCaughtFlag, "PermissionManager from objects, revoke/hasPermission:> Borrower revoked permission."); + + ##Delete permissions and modules we just made + $permissionManager->delPermission($permission); + $permission = $permissionManager->getPermission('testperm'); + ok(not(defined($permission)), "PermissionManager from objects, delPermission:> Permission deleted."); + + $permissionManager->delPermissionModule($permissionModule); + $permissionModule = $permissionManager->getPermissionModule('test'); + ok(not(defined($permissionModule)), "PermissionManager from objects, delPermissionModule:> Module deleted."); + + + + ##Testing superlibrarian permission + $permissionManager->revokeAllPermissions($borrowers->{'1A01'}); + $permissionManager->grantPermission($borrowers->{'1A01'}, 'superlibrarian', 'superlibrarian'); + ok($permissionManager->hasPermission($borrowers->{'1A01'}, 'staffaccess', 'staff_access_permissions'), "PermissionManager, superuser permission:> Superuser has all permissions 1."); + ok($permissionManager->hasPermission($borrowers->{'1A01'}, 'tools', 'batch_upload_patron_images'), "PermissionManager, superuser permission:> Superuser has all permissions 2."); +}; +if ($@) { #Catch all leaking errors and gracefully terminate. + warn $@; + tearDown(); + exit 1; +} + +##All tests done, tear down test context +tearDown(); +done_testing; + +sub tearDown { + t::lib::TestObjects::ObjectFactory->tearDownTestContext($testContext); +} --- a/t/db_dependent/Members/member-flags.t +++ a/t/db_dependent/Members/member-flags.t @@ -0,0 +1,100 @@ +#!/usr/bin/env perl + +# Copyright 2015 Open Source Freedom Fighters +# +# This file is part of Koha. +# +# Koha is free software; you can redistribute it and/or modify it +# under the terms of the GNU General Public License as published by +# the Free Software Foundation; either version 3 of the License, or +# (at your option) any later version. +# +# Koha is distributed in the hope that it will be useful, but +# WITHOUT ANY WARRANTY; without even the implied warranty of +# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the +# GNU General Public License for more details. +# +# You should have received a copy of the GNU General Public License +# along with Koha; if not, see . + +use Modern::Perl; + +use Test::More; +use Try::Tiny; #Even Selenium::Remote::Driver uses Try::Tiny :) +use Scalar::Util qw(blessed); + +use t::lib::Page::Members::MemberFlags; +use t::lib::TestObjects::PatronFactory; +use Koha::Auth::PermissionManager; + + +##Setting up the test context +my $testContext = {}; + +my $password = '1234'; +my $borrowerFactory = t::lib::TestObjects::PatronFactory->new(); +my $borrowers = $borrowerFactory->createTestGroup([ + {firstname => 'Olli-Antti', + surname => 'Kivi', + cardnumber => '1A01', + branchcode => 'CPL', + userid => 'mini_admin', + password => $password, + }, + ], undef, $testContext); + +##Test context set, starting testing: +eval { #run in a eval-block so we don't die without tearing down the test context + + testGrantRevokePermissions(); + +}; +if ($@) { #Catch all leaking errors and gracefully terminate. + warn $@; + tearDown(); + exit 1; +} + +##All tests done, tear down test context +tearDown(); +done_testing; + +sub tearDown { + t::lib::TestObjects::ObjectFactory->tearDownTestContext($testContext); +} + +sub testGrantRevokePermissions { + my $permissionManager = Koha::Auth::PermissionManager->new(); + $permissionManager->grantPermissions($borrowers->{'1A01'}, {permissions => 'set_permissions', + catalogue => 'staff_login', + staffaccess => 'staff_access_permissions', + circulate => 'override_renewals', + borrowers => 'view_borrowers', + }); + + my $memberflags = t::lib::Page::Members::MemberFlags->new({borrowernumber => $borrowers->{'1A01'}->borrowernumber}); + + $memberflags->isPasswordLoginAvailable()->doPasswordLogin($borrowers->{'1A01'}->userid, $password) + ->togglePermission('editcatalogue', 'delete_all_items') #Add this + ->togglePermission('editcatalogue', 'edit_items') #Add this + ->togglePermission('circulate', 'override_renewals') #Remove this permission + ->submitPermissionTree(); + + ok($permissionManager->hasPermissions($borrowers->{'1A01'},{editcatalogue => ['delete_all_items', 'edit_items']}), + "member-flags.pl:> Granting new permissions succeeded."); + + my $failureCaughtFlag = 0; + try { + $permissionManager->hasPermission($borrowers->{'1A01'}, 'circulate', 'override_renewals'); + } catch { + if (blessed($_) && $_->isa('Koha::Exception::NoPermission')) { + $failureCaughtFlag = 1; + } + else { + die $_; #Somekind of another problem arised and rethrow it. + } + }; + ok($failureCaughtFlag, "member-flags.pl:> Revoking permissions succeeded."); + + $permissionManager->revokeAllPermissions($borrowers->{'1A01'}); +} --- a/t/db_dependent/Opac/opac-search.t +++ a/t/db_dependent/Opac/opac-search.t @@ -0,0 +1,79 @@ +#!/usr/bin/env perl + +# Copyright 2015 Open Source Freedom Fighters +# +# This file is part of Koha. +# +# Koha is free software; you can redistribute it and/or modify it +# under the terms of the GNU General Public License as published by +# the Free Software Foundation; either version 3 of the License, or +# (at your option) any later version. +# +# Koha is distributed in the hope that it will be useful, but +# WITHOUT ANY WARRANTY; without even the implied warranty of +# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the +# GNU General Public License for more details. +# +# You should have received a copy of the GNU General Public License +# along with Koha; if not, see . + +use Modern::Perl; + +use Test::More; + +use t::lib::Page::Opac::OpacSearch; +use t::lib::TestObjects::PatronFactory; + + +##Setting up the test context +my $testContext = {}; + +my $password = '1234'; +my $borrowerFactory = t::lib::TestObjects::PatronFactory->new(); +my $borrowers = $borrowerFactory->createTestGroup([ + {firstname => 'Olli-Antti', + surname => 'Kivi', + cardnumber => '1A01', + branchcode => 'CPL', + userid => 'mini_admin', + password => $password, + }, + ], undef, $testContext); + +##Test context set, starting testing: +eval { #run in a eval-block so we don't die without tearing down the test context + + my $opacsearch = t::lib::Page::Opac::OpacSearch->new(); + testAnonymousSearchHistory($opacsearch); + +}; +if ($@) { #Catch all leaking errors and gracefully terminate. + warn $@; + tearDown(); + exit 1; +} + +##All tests done, tear down test context +tearDown(); +done_testing; + +sub tearDown { + t::lib::TestObjects::ObjectFactory->tearDownTestContext($testContext); +} + +sub testAnonymousSearchHistory { + my $opacsearch = shift; + + $opacsearch->doSetSearchFieldTerm(1, 'Author', 'nengard')->doSearchSubmit()->navigateAdvancedSearch() + ->doSetSearchFieldTerm(1, 'Author', 'joubu')->doSearchSubmit()->navigateAdvancedSearch() + ->doSetSearchFieldTerm(1, 'Author', 'khall')->doSearchSubmit()->navigateHome() + ->doPasswordLogin($borrowers->{'1A01'}->userid, $password)->navigateAdvancedSearch() + ->doSetSearchFieldTerm(1, 'Author', 'magnuse')->doSearchSubmit()->navigateAdvancedSearch() + ->doSetSearchFieldTerm(1, 'Author', 'cait')->doSearchSubmit()->navigateSearchHistory() + ->testDoSearchHistoriesExist(['nengard', + 'joubu', + 'khall', + 'magnuse', + 'cait']) + ->quit(); +} --