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
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
|
/**
This tag will take the provided database and all it's tables and grant the
provided group access to both the database and it's tables. Will return true
if it was successful in performing the operation.
AssignDatabaseToGroup('database name', 'group name');
@param string #database the name of the database you wish to grant access to
@param string #group the name of the group you wish to allow access to the database
@return always returns true unless an error was thrown
@author Adam Randall
@version 1.0.0
*/
define_tag('AssignDatabaseToGroup',
-namespace = '_global_Admin_',
-required = 'database',
-required = 'group');
admin_reloaddatasource('MySQLDS');
fail_if(!admin_currentgroups->find('ADMINISTRATORS')->size, -9960, 'Must be run as the global admin');
fail_if(!#database->size, -9956, 'Invalid Database Name');
fail_if(!#group->size, -9956, 'Invalid Group Name');
local(
'i',
'id_host' = 0,
'id_db' = 0,
'id_group' = 0,
'ids_table' = array);
inline(-database = 'lasso_internal');
// fine the ID of the specified database name or database alias
inline(
-log = 'none',
-table = 'security_datasource_databases',
-sql = '\
select \
id, \
id_host
from \
security_datasource_databases \
where \
(\
name like \'' encode_sql92(#database) '\' and \
(alias = \'\' or alias is null)\
) or \
alias like \'' encode_sql92(#database) '\'');
if(found_count);
#id_db = integer(field('id'));
#id_host = integer(field('id_host'));
/if;
/inline;
// check to see if we found the database
fail_if(#id_db <= 0, -9956, 'The specified database name/alias was not found');
// find the ID of the specified group
inline(
-log = 'none',
-table = 'security_groups',
-sql = '\
select \
id \
from \
security_groups \
where \
name like \'' encode_sql92(#group) '\'');
found_count ? #id_group = integer(field('id'));
/inline;
// check to see if we found the group
fail_if(#id_group <= 0, -9956, 'The specified group was not found');
// find the group host permissions
inline(
-log = 'none',
-table = 'security_group_host_map',
-sql = 'select * from security_group_host_map where id_group = ' #id_group ' and id_host = ' #id_host);
// if found, add Q to the allow field
if(found_count);
if(field('allow') !>> 'Q');
inline(
-log = 'none',
-sql = 'update security_group_host_map set allow = ' (field('allow')->size ? ',Q' | 'Q') ' where id = ' field('id'));
fail_if(error_code != 0, error_code, 'Updating Host Group Permissions: ' error_msg);
/inline;
/if;
else;
// if not found, add the new host record with Q in the Allow field
inline(
-log = 'none',
-sql = 'insert into security_group_host_map values (null, ' #id_host ', ' #id_group ', Q, null)');
fail_if(error_code != 0, error_code, 'Inserting Host Group Permissions: ' error_msg);
/inline;
/if;
/inline;
// delete existing group db permissions
inline(
-log = 'none',
-sql = 'delete from security_group_db_map where id_group = ' #id_group ' and id_database = ' #id_db);
fail_if(error_code != 0, error_code, 'Deleting DB Group Permissions: ' error_msg);
/inline;
// insert new group db permissions
inline(
-log = 'none',
-table = 'security_group_db_map',
-sql = 'insert into security_group_db_map values (null, ' #id_db ', ' #id_group ', \'I,S,A,U,D,X,Q\', null, null)');
fail_if(error_code != 0, error_code, 'Insert DB Group Permissions: ' error_msg);
/inline;
// find all existing group table ids that are bound to our db id
inline(
-log = 'none',
-table = 'security_group_table_map',
-sql = '\
select \
a.id as id, \
b.id as id_table \
from \
security_group_table_map as a \
left join security_database_tables as b on (a.id_table = b.id) \
where \
a.id_group = ' #id_group ' and \
b.id_database = ' #id_db,
-maxrecords = 'all');
records;
#ids_table->insert(field('id'));
/records;
/inline;
// delete found group table ids
if(#ids_table->size);
inline(
-log = 'none',
-sql = 'delete from security_group_table_map where id in (' #ids_table->join(',') ')');
fail_if(error_code != 0, error_code, 'Deleting Table Group Permissions: ' error_msg);
/inline;
/if;
// find all table ids
inline(
-log = 'none',
-table = 'security_database_tables',
-sql = 'select id from security_database_tables where id_database = ' #id_db,
-maxrecords = 'all');
records;
// add the new table permissions
inline(
-log = 'none',
-table = 'security_group_table_map',
-sql = 'insert into security_group_table_map values (null, ' field('id') ', ' #id_group ', \'I,S,A,U,D,Q\', null, null)');
fail_if(error_code != 0, error_code, 'Insert Table Group Permissions: ' error_msg);
/inline;
/records;
/inline;
/inline;
admin_refreshsecurity;
return(true);
/define_tag;
|
