| Database | Proc | Application | Created | Links |
| sybsystemprocs | sp_changegroup | | 31 Aug 14 | Defects Dependencies |
1 2 /* Sccsid = "%Z% generic/sproc/src/%M% %I% %G%" */ 3 /* 4.8 1.1 06/14/90 sproc/src/changegroup */ 4 5 /* 6 ** Messages for "sp_changegroup" 17370 7 ** 8 ** 17289, "Set your curwrite to the hurdle of current database." 9 ** 17333, "No such group exists." 10 ** (is now: "No group with the specified name exists.") 11 ** 17232, "No such user exists." 12 ** (is now: "No user with the specified name exists in the current 13 ** database.") 14 ** 17370, "Group changed." 15 ** 17756, "The execution of the stored procedure '%1!' in database 16 ** '%2!' was aborted because there was an error in writing the 17 ** replication log record." 18 ** 18773, "HA_LOG: HA consistency check failure in '%1!' on the companion server '%2!'" 19 ** 18776, "Please also run stored procedure '%1!' on the companion server '%2!'." 20 ** 18792, "Unable to find a group with name '%1!' and id '%2!' in sysusers." 21 ** 18793, "Unable to find a user with name '%1!' and id '%2!' in sysusers." 22 */ 23 24 /* 25 ** IMPORTANT: Please read the following instructions before 26 ** making changes to this stored procedure. 27 ** 28 ** To make this stored procedure compatible with High Availability (HA), 29 ** changes to certain system tables must be propagated 30 ** to the companion server under some conditions. 31 ** The tables include (but are not limited to): 32 ** syslogins, sysservers, sysattributes, systimeranges, 33 ** sysresourcelimits, sysalternates, sysdatabases, 34 ** syslanguages, sysremotelogins, sysloginroles, 35 ** sysalternates (master DB only), systypes (master DB only), 36 ** sysusers (master DB only), sysprotects (master DB only) 37 ** please refer to the HA documentation for detail. 38 ** 39 ** Here is what you need to do: 40 ** For each insert/update/delete statement, add three sections to 41 ** -- start HA transaction prior to the statement 42 ** -- add the statement 43 ** -- add HA synchronization code to propagate the change to the companion 44 ** 45 ** For example, if you are adding 46 ** insert master.dbo.syslogins ...... 47 ** the code should look like: 48 ** 1. Before that SQL statement: 49 ** 50 ** 2. Now, the SQL statement: 51 ** insert master.dbo.syslogins ...... 52 ** 3. Add a HA synchronization section right after the SQL statement: 53 ** 54 ** 55 ** You may need to do similar change for each built-in function you 56 ** want to add. 57 ** 58 ** After that, you need to add a separate part at a place where it can not 59 ** be reached by the normal execution path: 60 ** clean_all: 61 ** 62 ** return (1) 63 */ 64 65 create procedure sp_changegroup 66 @grpname varchar(30), /* group name */ 67 @username varchar(30) /* user name to add to group */ 68 as 69 70 declare @gid int /* group id */ 71 declare @uid int /* user id */ 72 declare @msg varchar(1024) 73 declare @id int /* object id */ 74 declare @grantee int /* grantee */ 75 declare @action smallint /* action (i.e select, insert) */ 76 declare @protecttype tinyint /* grant/revoke/option */ 77 declare @columns varbinary(133) /* column priv bit map */ 78 declare @grantor int /* grantor */ 79 declare @gcolumns varbinary(133) /* group column privileges */ 80 declare @pcolumns varbinary(133) /* public column privileges */ 81 declare @aggrprivs tinyint /* object level privs of group and public */ 82 declare @aggrcolumns varbinary(133) /* aggregate of group and public privileges */ 83 declare @col_count smallint 84 declare @dummy int 85 declare @dbname varchar(30) 86 declare @HA_CERTIFIED tinyint /* Is the SP HA certified ? */ 87 declare @retstat int 88 89 90 select @HA_CERTIFIED = 0 91 92 93 94 95 /* check to see if we are using HA specific SP for a HA enabled server */ 96 exec @retstat = sp_ha_check_certified 'sp_changegroup', @HA_CERTIFIED 97 if (@retstat != 0) 98 return (1) 99 100 if @@trancount = 0 101 begin 102 set chained off 103 end 104 105 set transaction isolation level 1 106 107 /* 108 ** Only the Database Owner (DBO) or 109 ** Accounts with SA or SSO role can execute it. 110 ** Call proc_role() with the required SA role. 111 */ 112 if (user_id() != 1) 113 begin 114 if (charindex("sa_role", show_role()) = 0 and 115 charindex("sso_role", show_role()) = 0) 116 begin 117 select @dummy = proc_role("sa_role") 118 select @dummy = proc_role("sso_role") 119 return (1) 120 end 121 end 122 123 if (charindex("sa_role", show_role()) > 0) 124 select @dummy = proc_role("sa_role") 125 126 if (charindex("sso_role", show_role()) > 0) 127 select @dummy = proc_role("sso_role") 128 129 /* 130 ** See if the group name exists. 131 */ 132 select @gid = uid from sysusers 133 where name = @grpname 134 and (uid = gid) 135 and not exists (select name from master.dbo.syssrvroles where name = @grpname) 136 137 /* 138 ** If no such group, quit. 139 */ 140 if @gid is NULL 141 begin 142 /* 17333, "No group with the specified name exists." */ 143 raiserror 17333 144 return (1) 145 end 146 147 /* 148 ** See if the user name exists. 149 */ 150 select @uid = uid from sysusers 151 where name = @username 152 and ((uid >= @@minuserid and uid < @@mingroupid and uid != 0) 153 or uid > @@maxgroupid) 154 155 /* 156 ** If no such user in the database, quit. 157 */ 158 if @uid is NULL 159 begin 160 161 /* 162 ** 17232, "No such user exists." 163 */ 164 raiserror 17232 165 return (1) 166 end 167 168 /* 169 ** At this stage everything is consistent with respect to parameters. 170 ** Now we need to update the GRANTS/REVOKES to the user with respect to 171 ** the new group 172 */ 173 174 begin transaction rs_logexec 175 176 /* 177 ** cursor to find tuples in sysprotect for which grantee is @uid 178 */ 179 declare priv_curs cursor for 180 select id, uid, action, protecttype, columns, grantor 181 from sysprotects 182 where uid = @uid 183 for update 184 185 /* 186 ** open the cursor and start fetching from it 187 */ 188 189 open priv_curs 190 191 fetch priv_curs into @id, @grantee, @action, @protecttype, @columns, @grantor 192 193 /* loop for all qualifying rows */ 194 while (@@sqlstatus != 2) 195 begin 196 if (@@sqlstatus = 1) 197 begin 198 /* error in fetching from the cursor */ 199 raiserror 17333 200 rollback transaction rs_logexec 201 return (1) 202 end 203 204 /* 205 ** 193 is SELECT, 197 is UPDATE, 151 is REFERENCES. These are column 206 ** level privileges 207 */ 208 if (((@action != 193) and (@action != 197)) and (@action != 151)) 209 begin 210 /* 211 ** these are object level privileges 212 ** Note that grant with grant can't be given to group/PUBLIC thus 213 ** not considered 214 */ 215 if (@protecttype = 1) 216 begin 217 /* it is a grant row */ 218 /* initialize aggregate privs */ 219 select @aggrprivs = 0 220 221 /* 222 ** check if this grant is available to public 223 */ 224 if (exists (select * from sysprotects 225 where (id = @id) and 226 (uid = 0) and 227 (action = @action) and 228 (protecttype = @protecttype) and 229 (grantor = @grantor))) 230 begin 231 select @aggrprivs = 1 232 end 233 234 235 if (@aggrprivs = 1) 236 begin 237 /* the grant of this privilege is available thru PUBLIC 238 check if there is a revoke to the new group*/ 239 if (exists (select * from sysprotects 240 where (id = @id) and 241 (uid = @gid) and 242 (action = @action) and 243 (protecttype = 2) and 244 (grantor = @grantor))) 245 begin 246 /* the revoke from group nullifies the grant from 247 PUBLIC*/ 248 select @aggrprivs = 0 249 end 250 end 251 else 252 begin 253 /* the privilege was not available thru PUBLIC, check if 254 it is available thru group*/ 255 if (exists (select * from sysprotects 256 where (id = @id) and 257 (uid = @gid) and 258 (action = @action) and 259 (protecttype = 1) and 260 (grantor = @grantor))) 261 begin 262 /* the privilege is inherited from the group */ 263 select @aggrprivs = 1 264 end 265 end 266 267 268 /* delete the grant to the user, if inherited due to the 269 membership in the group or public*/ 270 271 if (@aggrprivs = 1) 272 begin 273 delete from sysprotects where current of priv_curs 274 end 275 end 276 else if (@protecttype = 2) 277 begin 278 /* it is a revoke */ 279 if ((exists (select * from sysprotects 280 where (id = @id) and 281 (uid = @gid) and 282 (action = @action) and 283 (protecttype = @protecttype) and 284 (grantor = @grantor))) 285 or 286 (not exists (select * from sysprotects 287 where (id = @id) and 288 (uid = @gid or uid = 0) and 289 (action = @action) and 290 (protecttype = 1) and 291 (grantor = @grantor)))) 292 begin 293 /* the privilege is already revoked from the group or there is no 294 explicit grant of this privilege to either group or public, so 295 this revoke row not needed*/ 296 delete from sysprotects where current of priv_curs 297 298 end 299 end 300 end 301 else 302 begin 303 /* column level privileges */ 304 /* initialize column privilege map for public and group */ 305 select @pcolumns = 0x00 306 select @gcolumns = 0x00 307 308 /* get the number of columns in this table. It is only used for 309 ** column level privileges 310 */ 311 select @col_count = count(*) 312 from syscolumns 313 where id = @id 314 315 /* find the column level privileges to PUBLIC */ 316 if (exists (select * from sysprotects 317 where (id = @id) and 318 (uid = 0) and 319 (action = @action) and 320 (protecttype = 1) and 321 (grantor = @grantor))) 322 begin 323 select @pcolumns = columns from sysprotects 324 where (id = @id) and 325 (uid = 0) and 326 (action = @action) and 327 (protecttype = 1) and 328 (grantor = @grantor) 329 end 330 331 /* find the column level privilege to the new GROUP */ 332 if (exists (select * from sysprotects 333 where (id = @id) and 334 (uid = @gid) and 335 (action = @action) and 336 (protecttype = 1) and 337 (grantor = @grantor))) 338 begin 339 select @gcolumns = columns from sysprotects 340 where (id = @id) and 341 (uid = @gid) and 342 (action = @action) and 343 (protecttype = 1) and 344 (grantor = @grantor) 345 end 346 347 /* find the union of column privileges from public as 348 ** new group 349 */ 350 exec sybsystemprocs.dbo.syb_aux_privunion @pcolumns, @gcolumns, @col_count, @aggrcolumns output 351 352 /* find if there is a revoke row for the above privilege 353 ** in the group 354 */ 355 select @gcolumns = 0x00 356 if (exists (select * from sysprotects 357 where (id = @id) and 358 (uid = @gid) and 359 (action = @action) and 360 (protecttype = 2) and 361 (grantor = @grantor))) 362 begin 363 select @gcolumns = columns from sysprotects 364 where (id = @id) and 365 (uid = @gid) and 366 (action = @action) and 367 (protecttype = 2) and 368 (grantor = @grantor) 369 370 end 371 372 /* subtract the revoke to columns from the group. In order to subtract, 373 exor operation will do as the revoke column bit can only be for columns 374 for which the user has inherited grant*/ 375 376 exec sybsystemprocs.dbo.syb_aux_privexor @aggrcolumns, @gcolumns, @col_count, @aggrcolumns output 377 378 /* at this stage, aggrcolumns contains the effective column privileges 379 that are inherited*/ 380 381 if (@protecttype = 1) 382 begin 383 /* we encountered an explicit user specific grant row */ 384 exec sybsystemprocs.dbo.syb_aux_privnots @aggrcolumns, @col_count, @aggrcolumns output 385 386 exec sybsystemprocs.dbo.syb_aux_privsand @columns, @aggrcolumns, @col_count, @columns output 387 388 389 if (@columns = 0x00) 390 begin 391 /* delete the grant row if no column is left. This implies that 392 all explicit user grants were also inherited due to the user's 393 membership in the group or PUBLIC*/ 394 delete sysprotects where current of priv_curs 395 396 397 end 398 else 399 begin 400 /* update the list of columns in the grant */ 401 update sysprotects set columns = @columns 402 where current of priv_curs 403 end 404 end 405 else if (@protecttype = 2) 406 begin 407 /* it is a revoke row */ 408 exec sybsystemprocs.dbo.syb_aux_privsand @columns, @aggrcolumns, @col_count, @columns output 409 /* We only keep those revoke bits for which there is an inherited 410 grant from the user's membership in the group or PUBLIC*/ 411 if (@columns = 0x00) 412 begin 413 /* delete the revoke row if no column is left */ 414 delete sysprotects where current of priv_curs 415 416 end 417 else 418 begin 419 /* update the list of columns in the revoke */ 420 update sysprotects set columns = @columns 421 where current of priv_curs 422 end 423 end 424 end 425 /* 426 ** get the next qualifying tuple 427 */ 428 fetch priv_curs into @id, @grantee, @action, @protecttype, @columns, @grantor 429 end 430 431 /* 432 ** Everything is consistent so change the group. 433 */ 434 update sysusers 435 set gid = @gid 436 from sysusers 437 where uid = @uid 438 439 /* 440 ** Write the log record to replicate this invocation 441 ** of the stored procedure. 442 */ 443 if (logexec() != 1) 444 begin 445 /* 446 ** 17756, "The execution of the stored procedure '%1!' in 447 ** database '%2!' was aborted because there was an 448 ** error in writing the replication log record." 449 */ 450 select @dbname = db_name() 451 raiserror 17756, "sp_changegroup", @dbname 452 453 rollback transaction rs_logexec 454 return (1) 455 end 456 457 commit transaction rs_logexec 458 459 /* 460 ** We need to invalidate the protection cache since objects have 461 ** changed ownership. This command will invalidate the current 462 ** protection cache so when protections are checked the new and 463 ** correct protections will be used. 464 */ 465 grant all to null 466 /* 467 ** 17370, "Group changed." 468 */ 469 exec sp_getmessage 17370, @msg output 470 print @msg 471 472 473 474 return (0) 475
exec sp_procxmode 'sp_changegroup', 'AnyMode' go Grant Execute on sp_changegroup to public go
| DEPENDENCIES |
| PROCS AND TABLES USED calls proc sybsystemprocs..syb_aux_privexor calls proc sybsystemprocs..syb_aux_expandbitmap calls proc sybsystemprocs..sp_ha_check_certified  reads table tempdb..sysobjects (1)  reads table sybsystemprocs..syscolumns calls proc sybsystemprocs..syb_aux_privsand calls proc sybsystemprocs..syb_aux_expandbitmap calls proc sybsystemprocs..syb_aux_privunion calls proc sybsystemprocs..sp_getmessage  reads table master..syslanguages (1)  reads table master..sysmessages (1) calls proc sybsystemprocs..sp_validlang reads table master..syslanguages (1) reads table sybsystemprocs..sysusermessages reads table master..syssrvroles (1) read_writes table sybsystemprocs..sysprotects calls proc sybsystemprocs..syb_aux_privnots calls proc sybsystemprocs..syb_aux_expandbitmap read_writes table sybsystemprocs..sysusers |
