1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
|
CREATE GENERATOR USERS_ROLES_ID_SEQ;
SET GENERATOR USERS_ROLES_ID_SEQ TO 3;
DROP GENERATOR USERS_GROUPS_ID_SEQ;
CREATE TABLE LIBERTY_CONTENT_ROLE_MAP
(
CONTENT_ID Integer NOT NULL,
ROLE_ID Integer NOT NULL,
PRIMARY KEY (CONTENT_ID,ROLE_ID)
);
COMMIT;
INSERT INTO LIBERTY_CONTENT_ROLE_MAP ( CONTENT_ID, ROLE_ID )
SELECT lg.CONTENT_ID, lg.GROUP_ID AS ROLE_ID FROM LIBERTY_CONTENT_GROUP_MAP lg;
DROP TABLE LIBERTY_CONTENT_GROUP_MAP;
ALTER TABLE LIBERTY_CONTENT_PERMISSIONS DROP CONSTRAINT LIBERTY_CONTENT_PERM_GROUP_REF;
ALTER TABLE LIBERTY_CONTENT_PERMISSIONS DROP CONSTRAINT INTEG_83;
ALTER TABLE LIBERTY_CONTENT_PERMISSIONS ALTER GROUP_ID TO ROLE_ID;
ALTER TABLE LIBERTY_CONTENT_PERMISSIONS
ADD CONSTRAINT PK_LIBERTY_CONTENT_PERMISSIONS
PRIMARY KEY (ROLE_ID, PERM_NAME, CONTENT_ID);
ALTER TABLE THEMES_LAYOUTS ALTER GROUPS TO ROLES;
CREATE TABLE USERS_ROLES
(
ROLE_ID Integer NOT NULL,
USER_ID Integer NOT NULL,
ROLE_NAME Varchar(30),
IS_DEFAULT Varchar(1),
ROLE_DESC Varchar(255),
ROLE_HOME Varchar(255),
IS_PUBLIC Varchar(1),
AFTER_REGISTRATION_PAGE Varchar(255),
PRIMARY KEY (ROLE_ID)
);
CREATE TABLE USERS_ROLES_MAP
(
USER_ID Integer NOT NULL,
ROLE_ID Integer NOT NULL,
PRIMARY KEY (USER_ID,ROLE_ID)
);
CREATE TABLE USERS_ROLE_PERMISSIONS
(
ROLE_ID Integer NOT NULL,
PERM_NAME Varchar(30) NOT NULL,
PERM_VALUE Varchar(1) DEFAULT '',
PRIMARY KEY (ROLE_ID,PERM_NAME)
);
ALTER TABLE USERS_USERS ALTER DEFAULT_GROUP_ID TO DEFAULT_ROLE_ID;
ALTER TABLE LIBERTY_CONTENT_ROLE_MAP ADD CONSTRAINT PROTECTOR_CONTENT_REF
FOREIGN KEY (CONTENT_ID) REFERENCES LIBERTY_CONTENT (CONTENT_ID);
ALTER TABLE LIBERTY_CONTENT_ROLE_MAP ADD CONSTRAINT PROTECTOR_ROLE_REF
FOREIGN KEY (ROLE_ID) REFERENCES USERS_ROLES (ROLE_ID);
CREATE INDEX LIBERTY_CONTENT_PERM_ROLE_IDX ON LIBERTY_CONTENT_PERMISSIONS (ROLE_ID);
ALTER TABLE LIBERTY_CONTENT_PERMISSIONS ADD CONSTRAINT LIBERTY_CONTENT_PERM_ROLE_REF
FOREIGN KEY (ROLE_ID) REFERENCES USERS_ROLES (ROLE_ID);
ALTER TABLE USERS_ROLES ADD CONSTRAINT USERS_ROLES_USER_REF
FOREIGN KEY (USER_ID) REFERENCES USERS_USERS (USER_ID);
CREATE INDEX USERS_ROLES_USER_IDX ON USERS_ROLES (USER_ID);
CREATE UNIQUE INDEX USERS_ROLES_USER_NAME_IDX ON USERS_ROLES (USER_ID,ROLE_NAME);
ALTER TABLE USERS_ROLES_MAP ADD CONSTRAINT USERS_ROLES_MAP_ROLE_REF
FOREIGN KEY (ROLE_ID) REFERENCES USERS_ROLES (ROLE_ID);
ALTER TABLE USERS_ROLES_MAP ADD CONSTRAINT USERS_ROLES_MAP_USER_REF
FOREIGN KEY (USER_ID) REFERENCES USERS_USERS (USER_ID);
CREATE INDEX USERS_ROLES_MAP_ROLE_IDX ON USERS_ROLES_MAP (ROLE_ID);
CREATE INDEX USERS_ROLES_MAP_USER_IDX ON USERS_ROLES_MAP (USER_ID);
ALTER TABLE USERS_ROLE_PERMISSIONS ADD CONSTRAINT USERS_ROLE_PERM_PERM_REF
FOREIGN KEY (PERM_NAME) REFERENCES USERS_PERMISSIONS (PERM_NAME);
ALTER TABLE USERS_ROLE_PERMISSIONS ADD CONSTRAINT USERS_ROLE_PERM_ROLE_REF
FOREIGN KEY (ROLE_ID) REFERENCES USERS_ROLES (ROLE_ID);
CREATE INDEX USERS_ROLE_PERM_PERM_IDX ON USERS_ROLE_PERMISSIONS (PERM_NAME);
CREATE INDEX USERS_ROLE_PERM_ROLE_IDX ON USERS_ROLE_PERMISSIONS (ROLE_ID);
GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE
ON LIBERTY_CONTENT_ROLE_MAP TO SYSDBA WITH GRANT OPTION;
GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE
ON USERS_PERMISSIONS TO SYSDBA WITH GRANT OPTION;
GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE
ON USERS_ROLES TO SYSDBA WITH GRANT OPTION;
GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE
ON USERS_ROLES_MAP TO SYSDBA WITH GRANT OPTION;
GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE
ON USERS_ROLE_PERMISSIONS TO SYSDBA WITH GRANT OPTION;
COMMIT;
INSERT INTO USERS_ROLES
SELECT * FROM USERS_GROUPS;
INSERT INTO USERS_ROLES_MAP
SELECT * FROM USERS_GROUPS_MAP;
INSERT INTO USERS_ROLE_PERMISSIONS
SELECT * FROM USERS_GROUP_PERMISSIONS;
DROP TABLE USERS_GROUP_PERMISSIONS;
DROP TABLE USERS_GROUPS_MAP;
DROP TABLE USERS_GROUPS;
DROP INDEX LIBERTY_CONTENT_PERM_GROUP_IDX;
INSERT INTO USERS_PERMISSIONS (PERM_NAME, PERM_DESC, PERM_LEVEL, PACKAGE) VALUES ('p_users_assign_role_perms', 'Can assign permissions to personal roles', 'editors', 'users');
INSERT INTO USERS_PERMISSIONS (PERM_NAME, PERM_DESC, PERM_LEVEL, PACKAGE) VALUES ('p_users_assign_role_members', 'Can assign users to personal roles', 'registered', 'users');
INSERT INTO USERS_PERMISSIONS (PERM_NAME, PERM_DESC, PERM_LEVEL, PACKAGE) VALUES ('p_users_role_subroles', 'Can include other roles in roles', 'editors', 'users');
INSERT INTO USERS_PERMISSIONS (PERM_NAME, PERM_DESC, PERM_LEVEL, PACKAGE) VALUES ('p_users_create_personal_roles', 'Can create personal user roles', 'editors', 'users');
UPDATE USERS_ROLE_PERMISSIONS SET PERM_NAME = 'p_users_assign_role_members' WHERE PERM_NAME = 'p_users_assign_group_members';
UPDATE USERS_ROLE_PERMISSIONS SET PERM_NAME = 'p_users_assign_role_perms' WHERE PERM_NAME = 'p_users_assign_group_perms';
UPDATE USERS_ROLE_PERMISSIONS SET PERM_NAME = 'p_users_role_subroles' WHERE PERM_NAME = 'p_users_group_subgroups';
UPDATE USERS_ROLE_PERMISSIONS SET PERM_NAME = 'p_users_create_personal_roles' WHERE PERM_NAME = 'p_users_create_personal_groups';
UPDATE LIBERTY_CONTENT_PERMISSIONS SET PERM_NAME = 'p_users_assign_role_members' WHERE PERM_NAME = 'p_users_assign_group_members';
UPDATE LIBERTY_CONTENT_PERMISSIONS SET PERM_NAME = 'p_users_assign_role_perms' WHERE PERM_NAME = 'p_users_assign_group_perms';
UPDATE LIBERTY_CONTENT_PERMISSIONS SET PERM_NAME = 'p_users_role_subroles' WHERE PERM_NAME = 'p_users_group_subgroups';
UPDATE LIBERTY_CONTENT_PERMISSIONS SET PERM_NAME = 'p_users_create_personal_roles' WHERE PERM_NAME = 'p_users_create_personal_groups';
DELETE FROM USERS_PERMISSIONS WHERE PERM_NAME = 'p_users_assign_group_members';
DELETE FROM USERS_PERMISSIONS WHERE PERM_NAME = 'p_users_assign_group_perms';
DELETE FROM USERS_PERMISSIONS WHERE PERM_NAME = 'p_users_group_subgroups';
DELETE FROM USERS_PERMISSIONS WHERE PERM_NAME = 'p_users_create_personal_groups';
UPDATE KERNEL_CONFIG SET
CONFIG_NAME = 'protector_single_role',
PACKAGE = 'protector',
CONFIG_VALUE = 'y'
WHERE CONFIG_NAME = 'protector_single_group';
|
