Debian Codegraph @

src/misc/upgrade/v2/security_database.sql

raw
1 /*
2 * The contents of this file are subject to the Initial
3 * Developer's Public License Version 1.0 (the "License");
4 * you may not use this file except in compliance with the
5 * License. You may obtain a copy of the License at
6 * http://www.ibphoenix.com/main.nfs?a=ibphoenix&page=ibp_idpl.
7 *
8 * Software distributed under the License is distributed AS IS,
9 * WITHOUT WARRANTY OF ANY KIND, either express or implied.
10 * See the License for the specific language governing rights
11 * and limitations under the License.
12 *
13 * The Original Code was created by Alex Peshkov on 16-Nov-2004
14 * for the Firebird Open Source RDBMS project.
15 *
16 * Copyright (c) 2004 Alex Peshkov
17 * and all contributors signed below.
18 *
19 * All Rights Reserved.
20 * Contributor(s): ______________________________________.
21 *
22 */
23
24 -- 1. temporary table to alter domains correctly.
25 CREATE TABLE UTMP (
26 USER_NAME VARCHAR(128) CHARACTER SET ASCII,
27 SYS_USER_NAME VARCHAR(128) CHARACTER SET ASCII,
28 GROUP_NAME VARCHAR(128) CHARACTER SET ASCII,
29 UID INTEGER,
30 GID INTEGER,
31 PASSWD VARCHAR(64) CHARACTER SET BINARY,
32
33 PRIVILEGE INTEGER,
34
35 COMMENT BLOB SUB_TYPE TEXT SEGMENT SIZE 80 CHARACTER SET UNICODE_FSS,
36 FIRST_NAME VARCHAR(32) CHARACTER SET UNICODE_FSS DEFAULT _UNICODE_FSS '',
37 MIDDLE_NAME VARCHAR(32) CHARACTER SET UNICODE_FSS DEFAULT _UNICODE_FSS '',
38 LAST_NAME VARCHAR(32) CHARACTER SET UNICODE_FSS DEFAULT _UNICODE_FSS ''
39 );
40 COMMIT;
41
42 -- 2. save users data
43 INSERT INTO UTMP(USER_NAME, SYS_USER_NAME, GROUP_NAME, UID, GID, PRIVILEGE,
44 COMMENT, FIRST_NAME, MIDDLE_NAME, LAST_NAME, PASSWD)
45 SELECT USER_NAME, SYS_USER_NAME, GROUP_NAME, UID, GID, PRIVILEGE,
46 COMMENT, FIRST_NAME, MIDDLE_NAME, LAST_NAME, PASSWD
47 FROM USERS;
48 COMMIT;
49
50 -- 3. drop old tables and domains
51 DROP TABLE USERS;
52 DROP TABLE HOST_INFO;
53 COMMIT;
54
55 DROP DOMAIN COMMENT;
56 DROP DOMAIN NAME_PART;
57 DROP DOMAIN GID;
58 DROP DOMAIN HOST_KEY;
59 DROP DOMAIN HOST_NAME;
60 DROP DOMAIN PASSWD;
61 DROP DOMAIN UID;
62 DROP DOMAIN USER_NAME;
63 DROP DOMAIN PRIVILEGE;
64 COMMIT;
65
66 -- 4. create new objects in database
67 CREATE DOMAIN RDB$COMMENT AS BLOB SUB_TYPE TEXT SEGMENT SIZE 80 CHARACTER SET UNICODE_FSS;
68 CREATE DOMAIN RDB$NAME_PART AS VARCHAR(32) CHARACTER SET UNICODE_FSS DEFAULT _UNICODE_FSS '';
69 CREATE DOMAIN RDB$GID AS INTEGER;
70 CREATE DOMAIN RDB$PASSWD AS VARCHAR(64) CHARACTER SET BINARY;
71 CREATE DOMAIN RDB$UID AS INTEGER;
72 CREATE DOMAIN RDB$USER_NAME AS VARCHAR(128) CHARACTER SET UNICODE_FSS;
73 CREATE DOMAIN RDB$USER_PRIVILEGE AS INTEGER;
74 COMMIT;
75
76
77 CREATE TABLE RDB$USERS (
78 RDB$USER_NAME RDB$USER_NAME NOT NULL PRIMARY KEY,
79 /* local system user name for setuid for file permissions */
80 RDB$SYS_USER_NAME RDB$USER_NAME,
81 RDB$GROUP_NAME RDB$USER_NAME,
82 RDB$UID RDB$UID,
83 RDB$GID RDB$GID,
84 RDB$PASSWD RDB$PASSWD,
85
86 /* Privilege level of user - mark a user as having DBA privilege */
87 RDB$PRIVILEGE RDB$USER_PRIVILEGE,
88
89 RDB$COMMENT RDB$COMMENT,
90 RDB$FIRST_NAME RDB$NAME_PART,
91 RDB$MIDDLE_NAME RDB$NAME_PART,
92 RDB$LAST_NAME RDB$NAME_PART);
93 COMMIT;
94
95 CREATE VIEW USERS (USER_NAME, SYS_USER_NAME, GROUP_NAME, UID, GID, PASSWD,
96 PRIVILEGE, COMMENT, FIRST_NAME, MIDDLE_NAME, LAST_NAME, FULL_NAME) AS
97 SELECT RDB$USER_NAME, RDB$SYS_USER_NAME, RDB$GROUP_NAME, RDB$UID, RDB$GID, RDB$PASSWD,
98 RDB$PRIVILEGE, RDB$COMMENT, RDB$FIRST_NAME, RDB$MIDDLE_NAME, RDB$LAST_NAME,
99 COALESCE (RDB$first_name || _UNICODE_FSS ' ', '') ||
100 COALESCE (RDB$middle_name || _UNICODE_FSS ' ', '') ||
101 COALESCE (RDB$last_name, '')
102 FROM RDB$USERS
103 WHERE CURRENT_USER = 'SYSDBA'
104 OR CURRENT_USER = RDB$USERS.RDB$USER_NAME;
105 COMMIT;
106
107 GRANT ALL ON RDB$USERS to VIEW USERS;
108 GRANT SELECT ON USERS to PUBLIC;
109 GRANT UPDATE(PASSWD, GROUP_NAME, UID, GID, FIRST_NAME, MIDDLE_NAME, LAST_NAME)
110 ON USERS TO PUBLIC;
111 COMMIT;
112
113 -- 5. move data from temporary table and drop it
114 INSERT INTO RDB$USERS(RDB$USER_NAME, RDB$SYS_USER_NAME, RDB$GROUP_NAME, RDB$UID, RDB$GID, RDB$PRIVILEGE,
115 RDB$COMMENT, RDB$FIRST_NAME, RDB$MIDDLE_NAME, RDB$LAST_NAME, RDB$PASSWD)
116 SELECT USER_NAME, SYS_USER_NAME, GROUP_NAME, UID, GID, PRIVILEGE,
117 COMMENT, FIRST_NAME, MIDDLE_NAME, LAST_NAME, PASSWD
118 FROM UTMP;
119 COMMIT;
120
121 DROP TABLE UTMP;
122 COMMIT;