View | Details | Raw Unified | Return to bug 20813
Collapse All | Expand All

(-)a/installer/data/mysql/atomicupdate/permissions.pl (+121 lines)
Line 0 Link Here
1
use Modern::Perl;
2
3
return {
4
    bug_number => "20813",
5
    description => "Revamp user permissions system",
6
    up => sub {
7
        my ($args) = @_;
8
        my ($dbh, $out) = @$args{qw(dbh out)};
9
10
        $dbh->do(q{
11
ALTER TABLE user_permissions
12
    DROP FOREIGN KEY user_permissions_ibfk_2,
13
    DROP index user_permissions_ibfk_2,
14
    DROP COLUMN module_bit;
15
        });
16
17
        $dbh->do(q{
18
ALTER TABLE permissions
19
    DROP PRIMARY KEY,
20
    DROP FOREIGN KEY permissions_ibfk_1,
21
    ADD PRIMARY KEY ( code ),
22
    ADD COLUMN `parent` VARCHAR(64) NULL DEFAULT NULL FIRST,
23
    ADD KEY parent ( parent );
24
        });
25
26
        $dbh->do(q{
27
ALTER TABLE permissions ADD FOREIGN KEY ( parent ) REFERENCES permissions ( code );
28
        });
29
30
        $dbh->do(q{
31
ALTER TABLE user_permissions ADD CONSTRAINT user_permissions_ibfk_2 FOREIGN KEY (code) REFERENCES permissions(code);
32
        });
33
34
        $dbh->do(q{
35
INSERT INTO permissions ( parent, code, description ) VALUES ( NULL, 'superlibrarian', 'Access to all librarian functions' );
36
        });
37
38
        $dbh->do(q{
39
INSERT INTO permissions ( parent, code, description )
40
SELECT 'superlibrarian', flag, flagdesc FROM userflags
41
 WHERE flag != 'superlibrarian';
42
        });
43
44
        $dbh->do(q{
45
UPDATE permissions LEFT JOIN userflags ON ( userflags.bit = permissions.module_bit ) SET parent = flag WHERE flag != 'superlibrarian';
46
        });
47
48
        $dbh->do(q{
49
INSERT INTO user_permissions ( borrowernumber, code ) SELECT borrowernumber, 'superlibrarian'   FROM borrowers WHERE  substring( lpad( conv( borrowers.flags, 10, 2 ), 21, '0' ),  -1, 1 ) = '1';
50
        });
51
        $dbh->do(q{
52
INSERT INTO user_permissions ( borrowernumber, code ) SELECT borrowernumber, 'circulate'        FROM borrowers WHERE  substring( lpad( conv( borrowers.flags, 10, 2 ), 21, '0' ),  -2, 1 ) = '1';
53
        });
54
        $dbh->do(q{
55
INSERT INTO user_permissions ( borrowernumber, code ) SELECT borrowernumber, 'catalogue'        FROM borrowers WHERE  substring( lpad( conv( borrowers.flags, 10, 2 ), 21, '0' ),  -3, 1 ) = '1';
56
        });
57
        $dbh->do(q{
58
INSERT INTO user_permissions ( borrowernumber, code ) SELECT borrowernumber, 'parameters'       FROM borrowers WHERE  substring( lpad( conv( borrowers.flags, 10, 2 ), 21, '0' ),  -4, 1 ) = '1';
59
        });
60
        $dbh->do(q{
61
INSERT INTO user_permissions ( borrowernumber, code ) SELECT borrowernumber, 'borrowers'        FROM borrowers WHERE  substring( lpad( conv( borrowers.flags, 10, 2 ), 21, '0' ),  -5, 1 ) = '1';
62
        });
63
        $dbh->do(q{
64
INSERT INTO user_permissions ( borrowernumber, code ) SELECT borrowernumber, 'permissions'      FROM borrowers WHERE  substring( lpad( conv( borrowers.flags, 10, 2 ), 21, '0' ),  -6, 1 ) = '1';
65
        });
66
        $dbh->do(q{
67
INSERT INTO user_permissions ( borrowernumber, code ) SELECT borrowernumber, 'reserveforothers' FROM borrowers WHERE  substring( lpad( conv( borrowers.flags, 10, 2 ), 21, '0' ),  -7, 1 ) = '1';
68
        });
69
        $dbh->do(q{
70
INSERT INTO user_permissions ( borrowernumber, code ) SELECT borrowernumber, 'editcatalogue'    FROM borrowers WHERE  substring( lpad( conv( borrowers.flags, 10, 2 ), 21, '0' ),  -8, 1 ) = '1';
71
        });
72
        $dbh->do(q{
73
INSERT INTO user_permissions ( borrowernumber, code ) SELECT borrowernumber, 'updatecharges'    FROM borrowers WHERE  substring( lpad( conv( borrowers.flags, 10, 2 ), 21, '0' ),  -9, 1 ) = '1';
74
        });
75
        $dbh->do(q{
76
INSERT INTO user_permissions ( borrowernumber, code ) SELECT borrowernumber, 'acquisition'      FROM borrowers WHERE  substring( lpad( conv( borrowers.flags, 10, 2 ), 21, '0' ), -10, 1 ) = '1';
77
        });
78
        $dbh->do(q{
79
INSERT INTO user_permissions ( borrowernumber, code ) SELECT borrowernumber, 'management'       FROM borrowers WHERE  substring( lpad( conv( borrowers.flags, 10, 2 ), 21, '0' ), -11, 1 ) = '1';
80
        });
81
        $dbh->do(q{
82
INSERT INTO user_permissions ( borrowernumber, code ) SELECT borrowernumber, 'tools'            FROM borrowers WHERE  substring( lpad( conv( borrowers.flags, 10, 2 ), 21, '0' ), -12, 1 ) = '1';
83
        });
84
        $dbh->do(q{
85
INSERT INTO user_permissions ( borrowernumber, code ) SELECT borrowernumber, 'editauthorities'  FROM borrowers WHERE  substring( lpad( conv( borrowers.flags, 10, 2 ), 21, '0' ), -13, 1 ) = '1';
86
        });
87
        $dbh->do(q{
88
INSERT INTO user_permissions ( borrowernumber, code ) SELECT borrowernumber, 'serials'          FROM borrowers WHERE  substring( lpad( conv( borrowers.flags, 10, 2 ), 21, '0' ), -14, 1 ) = '1';
89
        });
90
        $dbh->do(q{
91
INSERT INTO user_permissions ( borrowernumber, code ) SELECT borrowernumber, 'reports'          FROM borrowers WHERE  substring( lpad( conv( borrowers.flags, 10, 2 ), 21, '0' ), -15, 1 ) = '1';
92
        });
93
        $dbh->do(q{
94
INSERT INTO user_permissions ( borrowernumber, code ) SELECT borrowernumber, 'staffaccess'      FROM borrowers WHERE  substring( lpad( conv( borrowers.flags, 10, 2 ), 21, '0' ), -16, 1 ) = '1';
95
        });
96
        $dbh->do(q{
97
INSERT INTO user_permissions ( borrowernumber, code ) SELECT borrowernumber, 'coursereserves'   FROM borrowers WHERE  substring( lpad( conv( borrowers.flags, 10, 2 ), 21, '0' ), -17, 1 ) = '1';
98
        });
99
        $dbh->do(q{
100
INSERT INTO user_permissions ( borrowernumber, code ) SELECT borrowernumber, 'plugins'          FROM borrowers WHERE  substring( lpad( conv( borrowers.flags, 10, 2 ), 21, '0' ), -18, 1 ) = '1';
101
        });
102
        $dbh->do(q{
103
INSERT INTO user_permissions ( borrowernumber, code ) SELECT borrowernumber, 'lists'            FROM borrowers WHERE  substring( lpad( conv( borrowers.flags, 10, 2 ), 21, '0' ), -19, 1 ) = '1';
104
        });
105
        $dbh->do(q{
106
INSERT INTO user_permissions ( borrowernumber, code ) SELECT borrowernumber, 'clubs'            FROM borrowers WHERE  substring( lpad( conv( borrowers.flags, 10, 2 ), 21, '0' ), -20, 1 ) = '1';
107
        });
108
109
        $dbh->do(q{
110
ALTER TABLE permissions DROP COLUMN module_bit;
111
        });
112
113
        $dbh->do(q{
114
ALTER TABLE borrowers DROP COLUMN flags;
115
        });
116
117
        $dbh->do(q{
118
DROP TABLE userflags;
119
        });
120
    },
121
}
(-)a/installer/data/mysql/kohastructure.sql (-31 / +12 lines)
Lines 1274-1280 CREATE TABLE `borrower_modifications` ( Link Here
1274
  `relationship` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
1274
  `relationship` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
1275
  `sex` varchar(1) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
1275
  `sex` varchar(1) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
1276
  `password` varchar(30) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
1276
  `password` varchar(30) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
1277
  `flags` int(11) DEFAULT NULL,
1278
  `userid` varchar(75) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
1277
  `userid` varchar(75) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
1279
  `opacnote` longtext COLLATE utf8mb4_unicode_ci DEFAULT NULL,
1278
  `opacnote` longtext COLLATE utf8mb4_unicode_ci DEFAULT NULL,
1280
  `contactnote` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
1279
  `contactnote` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
Lines 1392-1398 CREATE TABLE `borrowers` ( Link Here
1392
  `relationship` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'used for children to include the relationship to their guarantor',
1391
  `relationship` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'used for children to include the relationship to their guarantor',
1393
  `sex` varchar(1) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'patron/borrower''s gender',
1392
  `sex` varchar(1) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'patron/borrower''s gender',
1394
  `password` varchar(60) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'patron/borrower''s Bcrypt encrypted password',
1393
  `password` varchar(60) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'patron/borrower''s Bcrypt encrypted password',
1395
  `flags` int(11) DEFAULT NULL COMMENT 'will include a number associated with the staff member''s permissions',
1396
  `userid` varchar(75) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'patron/borrower''s opac and/or staff interface log in',
1394
  `userid` varchar(75) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'patron/borrower''s opac and/or staff interface log in',
1397
  `opacnote` longtext COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'a note on the patron/borrower''s account that is visible in the OPAC and staff interface',
1395
  `opacnote` longtext COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'a note on the patron/borrower''s account that is visible in the OPAC and staff interface',
1398
  `contactnote` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'a note related to the patron/borrower''s alternate address',
1396
  `contactnote` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'a note related to the patron/borrower''s alternate address',
Lines 2399-2405 CREATE TABLE `deletedborrowers` ( Link Here
2399
  `relationship` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'used for children to include the relationship to their guarantor',
2397
  `relationship` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'used for children to include the relationship to their guarantor',
2400
  `sex` varchar(1) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'patron/borrower''s gender',
2398
  `sex` varchar(1) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'patron/borrower''s gender',
2401
  `password` varchar(60) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'patron/borrower''s encrypted password',
2399
  `password` varchar(60) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'patron/borrower''s encrypted password',
2402
  `flags` int(11) DEFAULT NULL COMMENT 'will include a number associated with the staff member''s permissions',
2403
  `userid` varchar(75) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'patron/borrower''s opac and/or staff interface log in',
2400
  `userid` varchar(75) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'patron/borrower''s opac and/or staff interface log in',
2404
  `opacnote` longtext COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'a note on the patron/borrower''s account that is visible in the OPAC and staff interface',
2401
  `opacnote` longtext COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'a note on the patron/borrower''s account that is visible in the OPAC and staff interface',
2405
  `contactnote` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'a note related to the patron/borrower''s alternate address',
2402
  `contactnote` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'a note related to the patron/borrower''s alternate address',
Lines 4069-4079 DROP TABLE IF EXISTS `permissions`; Link Here
4069
/*!40101 SET @saved_cs_client     = @@character_set_client */;
4066
/*!40101 SET @saved_cs_client     = @@character_set_client */;
4070
/*!40101 SET character_set_client = utf8 */;
4067
/*!40101 SET character_set_client = utf8 */;
4071
CREATE TABLE `permissions` (
4068
CREATE TABLE `permissions` (
4072
  `module_bit` int(11) NOT NULL DEFAULT 0,
4069
  `parent` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
4073
  `code` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
4070
  `code` varchar(64) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
4074
  `description` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
4071
  `description` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
4075
  PRIMARY KEY (`module_bit`,`code`),
4072
  PRIMARY KEY (`code`),
4076
  CONSTRAINT `permissions_ibfk_1` FOREIGN KEY (`module_bit`) REFERENCES `userflags` (`bit`) ON DELETE CASCADE ON UPDATE CASCADE
4073
  KEY `parent` (`parent`),
4074
  CONSTRAINT `permissions_ibfk_1` FOREIGN KEY (`parent`) REFERENCES `permissions` (`code`)
4077
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
4075
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
4078
/*!40101 SET character_set_client = @saved_cs_client */;
4076
/*!40101 SET character_set_client = @saved_cs_client */;
4079
4077
Lines 5158-5187 CREATE TABLE `uploaded_files` ( Link Here
5158
DROP TABLE IF EXISTS `user_permissions`;
5156
DROP TABLE IF EXISTS `user_permissions`;
5159
/*!40101 SET @saved_cs_client     = @@character_set_client */;
5157
/*!40101 SET @saved_cs_client     = @@character_set_client */;
5160
/*!40101 SET character_set_client = utf8 */;
5158
/*!40101 SET character_set_client = utf8 */;
5159
DROP TABLE IF EXISTS `user_permissions`;
5161
CREATE TABLE `user_permissions` (
5160
CREATE TABLE `user_permissions` (
5162
  `borrowernumber` int(11) NOT NULL DEFAULT 0,
5161
  `borrowernumber` int(11) NOT NULL DEFAULT '0',
5163
  `module_bit` int(11) NOT NULL DEFAULT 0,
5162
  `code` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
5164
  `code` varchar(64) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
5165
  KEY `user_permissions_ibfk_1` (`borrowernumber`),
5163
  KEY `user_permissions_ibfk_1` (`borrowernumber`),
5166
  KEY `user_permissions_ibfk_2` (`module_bit`,`code`),
5164
  KEY `user_permissions_ibfk_2` (`code`),
5167
  CONSTRAINT `user_permissions_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
5165
  CONSTRAINT `user_permissions_ibfk_2` FOREIGN KEY (`code`) REFERENCES `permissions` (`code`),
5168
  CONSTRAINT `user_permissions_ibfk_2` FOREIGN KEY (`module_bit`, `code`) REFERENCES `permissions` (`module_bit`, `code`) ON DELETE CASCADE ON UPDATE CASCADE
5166
  CONSTRAINT `user_permissions_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE
5169
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
5170
/*!40101 SET character_set_client = @saved_cs_client */;
5171
5172
--
5173
-- Table structure for table `userflags`
5174
--
5175
5176
DROP TABLE IF EXISTS `userflags`;
5177
/*!40101 SET @saved_cs_client     = @@character_set_client */;
5178
/*!40101 SET character_set_client = utf8 */;
5179
CREATE TABLE `userflags` (
5180
  `bit` int(11) NOT NULL DEFAULT 0,
5181
  `flag` varchar(30) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
5182
  `flagdesc` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
5183
  `defaulton` int(11) DEFAULT NULL,
5184
  PRIMARY KEY (`bit`)
5185
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
5167
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
5186
/*!40101 SET character_set_client = @saved_cs_client */;
5168
/*!40101 SET character_set_client = @saved_cs_client */;
5187
5169
5188
- 

Return to bug 20813