| Database | Proc | Application | Created | Links |
| sybsystemprocs | sp_ijdbc_aux_computeprivs | | 31 Aug 14 | Defects Dependencies |
1 2 /* 3 ** sp_ijdbc_aux_computeprivs 4 */ 5 6 /* Got this from ODBC / installmaster script 7 ** It is needed for the following stored procs getColumnPrivileges and 8 ** getTablePrivileges. 9 ** The only change is the adding of 'order by' in the select statement that 10 ** produces the output. 11 */ 12 13 14 create procedure sp_ijdbc_aux_computeprivs( 15 @table_name varchar(257), 16 @table_owner varchar(32), 17 @table_qualifier varchar(32), 18 @column_name varchar(257), 19 @calledfrom_colpriv tinyint, 20 @tab_id int) 21 22 as 23 24 declare @low int /* range of userids to check */ 25 declare @high int 26 declare @max_uid int /* max uid allowed for a user*/ 27 declare @grantor_name varchar(32) /* the ascii name of grantor. used for output */ 28 declare @grantee_name varchar(32) /* the ascii name of grantee. us/sysprotected for output */ 29 declare @col_count smallint /* number of columns in @table_name */ 30 declare @grantee int /* id of the grantee */ 31 declare @action smallint /* action refers to select, update...*/ 32 declare @columns varbinary(133) /* bit map of column privilegs */ 33 declare @protecttype tinyint /* grant/revoke or grant with grant option */ 34 declare @grantor int /* id of the grantor of the privilege */ 35 declare @grp_id int /* the group a user belongs to */ 36 declare @grant_type tinyint /* used as a constant */ 37 declare @revoke_type tinyint /* used as a constant */ 38 declare @select_action smallint /* used as a constant */ 39 declare @update_action smallint /* used as a constant */ 40 declare @reference_action smallint /* used as a constant */ 41 declare @insert_action smallint /* used as a constant */ 42 declare @delete_action smallint /* used as a constant */ 43 declare @public_select varbinary(133) /* stores select column bit map for public */ 44 declare @public_reference varbinary(133) /* stores reference column bit map for public */ 45 declare @public_update varbinary(133) /* stores update column bit map for public */ 46 declare @public_insert tinyint /* stores if insert has been granted to public */ 47 declare @public_delete tinyint /* store if delete has been granted to public */ 48 declare @grp_select varbinary(133) /* stores select column bit map for group */ 49 declare @grp_update varbinary(133) /* stores update column bit map for group */ 50 declare @grp_reference varbinary(133) /* stores reference column bit map for group */ 51 declare @grp_delete tinyint /* if group hs been granted delete privilege */ 52 declare @grp_insert tinyint /* if group has been granted insert privilege */ 53 declare @inherit_select varbinary(133) /* stores select column bit map for 54 inherited privs*/ 55 declare @inherit_update varbinary(133) /* stores update column bit map for 56 inherited privs*/ 57 declare @inherit_reference varbinary(133) /* stores reference column bit map for 58 inherited privs*/ 59 declare @inherit_insert tinyint /* inherited insert priv */ 60 declare @inherit_delete tinyint /* inherited delete priv */ 61 declare @select_go varbinary(133) /* user column bit map of select with grant */ 62 declare @update_go varbinary(133) /* user column bit map of update with grant */ 63 declare @reference_go varbinary(133) /* user column bitmap of reference with grant */ 64 declare @insert_go tinyint /* user insert priv with grant option */ 65 declare @delete_go tinyint /* user delete priv with grant option */ 66 declare @prev_grantor tinyint /* used to detact if the grantor has changed 67 between two consecutive tuples*/ 68 declare @col_pos smallint /* col_pos of the column we are interested in. It 69 is used to find the col-bit in the bitmap*/ 70 declare @owner_id int /* id of the owner of the table */ 71 declare @dbid smallint /* dbid for the table */ 72 declare @grantable varchar(3) /* 'YES' or 'NO' if the privilege is grantable or 73 not*/ 74 declare @is_printable tinyint /* 1, if the privilege info is to be outputted */ 75 declare @curr_column varchar(257) /* name of the column being printed */ 76 declare @save_column_name varchar(257) 77 78 if @@trancount = 0 79 begin 80 set chained off 81 end 82 83 set transaction isolation level 1 84 85 /* 86 ** Initialize all constants to be used in this procedure 87 */ 88 89 select @grant_type = 1 90 91 select @revoke_type = 2 92 93 select @select_action = 193 94 95 select @reference_action = 151 96 97 select @update_action = 197 98 99 select @delete_action = 196 100 101 select @insert_action = 195 102 103 select @max_uid = @@maxuserid 104 105 select @low = 0, @high = 32767 106 select @dbid = db_id() 107 select @save_column_name = @column_name 108 select @column_name = '%' 109 /* 110 ** compute the table owner id 111 */ 112 113 select @owner_id = uid 114 from sysobjects 115 where id = @tab_id 116 117 /* 118 ** create a temporary sysprotects table that only has grant/revoke tuples 119 ** for the requested table. This is done as an optimization as the sysprotects 120 ** table may need to be traversed several times 121 */ 122 123 create table #sysprotects 124 (uid int, 125 action smallint, 126 protecttype tinyint, 127 columns varbinary(133) NULL, 128 grantor int) 129 130 /* 131 ** This table contains all the groups including PUBLIC that users, who 132 ** have been granted privilege on this table, belong to. Also it includes 133 ** groups that have been explicitly granted privileges on the table object 134 */ 135 create table #useful_groups 136 (grp_id int) 137 138 /* 139 ** create a table that contains the list of grantors for the object requested. 140 ** We will do a cartesian product of this table with sysusers in the 141 ** current database to capture all grantor/grantee tuples 142 */ 143 144 create table #distinct_grantors 145 (grantor int) 146 147 /* 148 ** We need to create a table which will contain a row for every object 149 ** privilege to be returned to the client. 150 */ 151 152 create table #column_privileges 153 (grantee_gid int, 154 grantor int, 155 grantee int, 156 insertpriv tinyint, 157 insert_go tinyint NULL, 158 deletepriv tinyint, 159 delete_go tinyint NULL, 160 selectpriv varbinary(133) NULL, 161 select_go varbinary(133) NULL, 162 updatepriv varbinary(133) NULL, 163 update_go varbinary(133) NULL, 164 referencepriv varbinary(133) NULL, 165 reference_go varbinary(133) NULL) 166 167 /* 168 ** Results Table 169 */ 170 create table #results_table 171 (table_qualifier varchar(32), 172 table_owner varchar(32), 173 table_name varchar(257), 174 column_name varchar(257) NULL, 175 grantor varchar(32), 176 grantee varchar(32), 177 privilege varchar(32), 178 is_grantable varchar(3)) 179 /* 180 ** this cursor scans the distinct grantor, group_id pairs 181 */ 182 declare grp_cursor cursor for 183 select distinct grp_id, grantor 184 from #useful_groups, #distinct_grantors 185 order by grantor 186 187 /* 188 ** this cursor scans all the protection tuples that represent 189 ** grant/revokes to users only 190 */ 191 declare user_protect cursor for 192 select uid, action, protecttype, columns, grantor 193 from #sysprotects 194 where (uid > 0) and 195 (uid <= @max_uid) 196 197 198 /* 199 ** this cursor is used to scan #column_privileges table to output results 200 */ 201 declare col_priv_cursor cursor for 202 select grantor, grantee, insertpriv, insert_go, deletepriv, delete_go, 203 selectpriv, select_go, updatepriv, update_go, referencepriv, reference_go 204 from #column_privileges 205 206 207 208 /* 209 ** column count is needed for privilege bit-map manipulation 210 */ 211 select @col_count = count(*) 212 from syscolumns 213 where id = @tab_id 214 215 216 /* 217 ** populate the temporary sysprotects table #sysprotects 218 */ 219 220 insert into #sysprotects 221 select uid, action, protecttype, columns, grantor 222 from sysprotects where (id = @tab_id) and 223 ((action = @select_action) or 224 (action = @update_action) or 225 (action = @reference_action) or 226 (action = @insert_action) or 227 (action = @delete_action)) 228 229 /* 230 ** insert privilege tuples for the table owner. There is no explicit grants 231 ** of these privileges to the owner. So these tuples are not there in sysprotects 232 ** table 233 */ 234 235 insert into #sysprotects 236 values (@owner_id, @select_action, 0, 0x01, @owner_id) 237 insert into #sysprotects 238 values (@owner_id, @update_action, 0, 0x01, @owner_id) 239 insert into #sysprotects 240 values (@owner_id, @reference_action, 0, 0x01, @owner_id) 241 insert into #sysprotects 242 values (@owner_id, @insert_action, 0, 0x00, @owner_id) 243 insert into #sysprotects 244 values (@owner_id, @delete_action, 0, 0x00, @owner_id) 245 246 /* 247 ** populate the #distinct_grantors table with all grantors that have granted 248 ** the privilege to users or to gid or to public on the table_name 249 */ 250 251 insert into #distinct_grantors 252 select distinct grantor from #sysprotects 253 254 /* 255 ** Populate the #column_privilegs table as a cartesian product of the table 256 ** #distinct_grantors and all the users, other than groups, in the current database 257 */ 258 259 260 insert into #column_privileges 261 select gid, g.grantor, su.uid, 0, 0, 0, 0, 0x00, 0x00, 0x00, 0x00, 0x00, 262 0x00 263 from sysusers su, #distinct_grantors g 264 where (su.uid > 0) and 265 (su.uid <= @max_uid) 266 267 /* 268 ** populate #useful_groups with only those groups whose members have been granted/revoked 269 ** privilges on the @tab_id in the current database. It also contains those groups 270 ** that have been granted/revoked privileges explicitly 271 */ 272 273 insert into #useful_groups 274 select distinct gid 275 from sysusers su, #sysprotects sp 276 where (su.uid = sp.uid) 277 278 279 open grp_cursor 280 281 fetch grp_cursor into @grp_id, @grantor 282 283 /* 284 ** This loop computes all the inherited privilegs of users due 285 ** their membership in a group 286 */ 287 288 while (@@sqlstatus != 2) 289 290 begin 291 292 /* 293 ** initialize variables 294 */ 295 select @public_select = 0x00 296 select @public_update = 0x00 297 select @public_reference = 0x00 298 select @public_delete = 0 299 select @public_insert = 0 300 301 302 /* get the select privileges granted to PUBLIC */ 303 304 if (exists (select * from #sysprotects 305 where (grantor = @grantor) and 306 (uid = 0) and 307 (action = @select_action))) 308 begin 309 /* note there can't be any revoke row for PUBLIC */ 310 select @public_select = columns 311 from #sysprotects 312 where (grantor = @grantor) and 313 (uid = 0) and 314 (action = @select_action) 315 end 316 317 318 /* get the update privilege granted to public */ 319 if (exists (select * from #sysprotects 320 where (grantor = @grantor) and 321 (uid = 0) and 322 (action = @update_action))) 323 begin 324 /* note there can't be any revoke row for PUBLIC */ 325 select @public_update = columns 326 from #sysprotects 327 where (grantor = @grantor) and 328 (uid = 0) and 329 (action = @update_action) 330 end 331 332 /* get the reference privileges granted to public */ 333 if (exists (select * from #sysprotects 334 where (grantor = @grantor) and 335 (uid = 0) and 336 (action = @reference_action))) 337 begin 338 /* note there can't be any revoke row for PUBLIC */ 339 select @public_reference = columns 340 from #sysprotects 341 where (grantor = @grantor) and 342 (uid = 0) and 343 (action = @reference_action) 344 end 345 346 347 /* get the delete privilege granted to public */ 348 if (exists (select * from #sysprotects 349 where (grantor = @grantor) and 350 (uid = 0) and 351 (action = @delete_action))) 352 begin 353 /* note there can't be any revoke row for PUBLIC */ 354 select @public_delete = 1 355 end 356 357 /* get the insert privileges granted to public */ 358 if (exists (select * from #sysprotects 359 where (grantor = @grantor) and 360 (uid = 0) and 361 (action = @insert_action))) 362 begin 363 /* note there can't be any revoke row for PUBLIC */ 364 select @public_insert = 1 365 end 366 367 368 /* 369 ** initialize group privileges 370 */ 371 372 select @grp_select = 0x00 373 select @grp_update = 0x00 374 select @grp_reference = 0x00 375 select @grp_insert = 0 376 select @grp_delete = 0 377 378 /* 379 ** if the group id is other than PUBLIC, we need to find the grants to 380 ** the group also 381 */ 382 383 if (@grp_id <> 0) 384 begin 385 /* find select privilege granted to group */ 386 if (exists (select * from #sysprotects 387 where (grantor = @grantor) and 388 (uid = @grp_id) and 389 (protecttype = @grant_type) and 390 (action = @select_action))) 391 begin 392 select @grp_select = columns 393 from #sysprotects 394 where (grantor = @grantor) and 395 (uid = @grp_id) and 396 (protecttype = @grant_type) and 397 (action = @select_action) 398 end 399 400 /* find update privileges granted to group */ 401 if (exists (select * from #sysprotects 402 where (grantor = @grantor) and 403 (uid = @grp_id) and 404 (protecttype = @grant_type) and 405 (action = @update_action))) 406 begin 407 select @grp_update = columns 408 from #sysprotects 409 where (grantor = @grantor) and 410 (uid = @grp_id) and 411 (protecttype = @grant_type) and 412 (action = @update_action) 413 end 414 415 /* find reference privileges granted to group */ 416 if (exists (select * from #sysprotects 417 where (grantor = @grantor) and 418 (uid = @grp_id) and 419 (protecttype = @grant_type) and 420 (action = @reference_action))) 421 begin 422 select @grp_reference = columns 423 from #sysprotects 424 where (grantor = @grantor) and 425 (uid = @grp_id) and 426 (protecttype = @grant_type) and 427 (action = @reference_action) 428 end 429 430 /* find delete privileges granted to group */ 431 if (exists (select * from #sysprotects 432 where (grantor = @grantor) and 433 (uid = @grp_id) and 434 (protecttype = @grant_type) and 435 (action = @delete_action))) 436 begin 437 438 select @grp_delete = 1 439 end 440 441 /* find insert privilege granted to group */ 442 if (exists (select * from #sysprotects 443 where (grantor = @grantor) and 444 (uid = @grp_id) and 445 (protecttype = @grant_type) and 446 (action = @insert_action))) 447 begin 448 449 select @grp_insert = 1 450 451 end 452 453 end 454 455 /* at this stage we have computed all the grants to PUBLIC as well as 456 ** the group by a specific grantor that we are interested in. Now we will 457 ** use this info to compute the overall inherited privilegs by the users 458 ** due to their membership to the group or to PUBLIC 459 */ 460 461 462 exec sybsystemprocs.dbo.syb_aux_privunion @public_select, @grp_select, @col_count, 463 @inherit_select output 464 exec sybsystemprocs.dbo.syb_aux_privunion @public_update, @grp_update, @col_count, 465 @inherit_update output 466 exec sybsystemprocs.dbo.syb_aux_privunion @public_reference, @grp_reference, @col_count, 467 @inherit_reference output 468 469 select @inherit_insert = @public_insert + @grp_insert 470 select @inherit_delete = @public_delete + @grp_delete 471 472 /* 473 ** initialize group privileges to store revokes 474 */ 475 476 select @grp_select = 0x00 477 select @grp_update = 0x00 478 select @grp_reference = 0x00 479 select @grp_insert = 0 480 select @grp_delete = 0 481 482 /* 483 ** now we need to find if there are any revokes on the group under 484 ** consideration. We will subtract all privileges that are revoked from the 485 ** group from the inherited privileges 486 */ 487 488 if (@grp_id <> 0) 489 begin 490 /* check if there is a revoke row for select privilege*/ 491 if (exists (select * from #sysprotects 492 where (grantor = @grantor) and 493 (uid = @grp_id) and 494 (protecttype = @revoke_type) and 495 (action = @select_action))) 496 begin 497 select @grp_select = columns 498 from #sysprotects 499 where (grantor = @grantor) and 500 (uid = @grp_id) and 501 (protecttype = @revoke_type) and 502 (action = @select_action) 503 end 504 505 /* check if there is a revoke row for update privileges */ 506 if (exists (select * from #sysprotects 507 where (grantor = @grantor) and 508 (uid = @grp_id) and 509 (protecttype = @revoke_type) and 510 (action = @update_action))) 511 begin 512 select @grp_update = columns 513 from #sysprotects 514 where (grantor = @grantor) and 515 (uid = @grp_id) and 516 (protecttype = @revoke_type) and 517 (action = @update_action) 518 end 519 520 /* check if there is a revoke row for reference privilege */ 521 if (exists (select * from #sysprotects 522 where (grantor = @grantor) and 523 (uid = @grp_id) and 524 (protecttype = @revoke_type) and 525 (action = @reference_action))) 526 begin 527 select @grp_reference = columns 528 from #sysprotects 529 where (grantor = @grantor) and 530 (uid = @grp_id) and 531 (protecttype = @revoke_type) and 532 (action = @reference_action) 533 end 534 535 /* check if there is a revoke row for delete privilege */ 536 if (exists (select * from #sysprotects 537 where (grantor = @grantor) and 538 (uid = @grp_id) and 539 (protecttype = @revoke_type) and 540 (action = @delete_action))) 541 begin 542 select @grp_delete = 1 543 end 544 545 /* check if there is a revoke row for insert privilege */ 546 if (exists (select * from #sysprotects 547 where (grantor = @grantor) and 548 (uid = @grp_id) and 549 (protecttype = @revoke_type) and 550 (action = @insert_action))) 551 begin 552 select @grp_insert = 1 553 554 end 555 556 557 /* 558 ** now subtract the revoked privileges from the group 559 */ 560 561 exec sybsystemprocs.dbo.syb_aux_privexor @inherit_select, 562 @grp_select, 563 @col_count, 564 @inherit_select output 565 566 exec sybsystemprocs.dbo.syb_aux_privexor @inherit_update, 567 @grp_update, 568 @col_count, 569 @inherit_update output 570 571 exec sybsystemprocs.dbo.syb_aux_privexor @inherit_reference, 572 @grp_reference, 573 @col_count, 574 @inherit_reference output 575 576 if (@grp_delete = 1) 577 select @inherit_delete = 0 578 579 if (@grp_insert = 1) 580 select @inherit_insert = 0 581 582 end 583 584 /* 585 ** now update all the tuples in #column_privileges table for this 586 ** grantor and group id 587 */ 588 589 update #column_privileges 590 set 591 insertpriv = @inherit_insert, 592 deletepriv = @inherit_delete, 593 selectpriv = @inherit_select, 594 updatepriv = @inherit_update, 595 referencepriv = @inherit_reference 596 597 where (grantor = @grantor) and 598 (grantee_gid = @grp_id) 599 600 601 /* 602 ** the following update updates the privileges for those users 603 ** whose groups have not been explicitly granted privileges by the 604 ** grantor. So they will all have all the privileges of the PUBLIC 605 ** that were granted by the current grantor 606 */ 607 608 select @prev_grantor = @grantor 609 fetch grp_cursor into @grp_id, @grantor 610 611 if ((@prev_grantor <> @grantor) or (@@sqlstatus = 2)) 612 613 begin 614 /* Either we are at the end of the fetch or we are switching to 615 ** a different grantor. 616 */ 617 618 update #column_privileges 619 set 620 insertpriv = @public_insert, 621 deletepriv = @public_delete, 622 selectpriv = @public_select, 623 updatepriv = @public_update, 624 referencepriv = @public_reference 625 from #column_privileges cp 626 where (cp.grantor = @prev_grantor) and 627 (not EXISTS (select * 628 from #useful_groups ug 629 where ug.grp_id = cp.grantee_gid)) 630 631 end 632 end 633 634 635 close grp_cursor 636 637 638 /* 639 ** At this stage, we have populated the #column_privileges table with 640 ** all the inherited privileges 641 */ 642 643 644 /* 645 ** Now we will go through each user grant or revoke in table #sysprotects 646 ** and update the privileges in #column_privileges table 647 */ 648 open user_protect 649 650 fetch user_protect into @grantee, @action, @protecttype, @columns, @grantor 651 652 while (@@sqlstatus != 2) 653 begin 654 /* 655 ** In this loop, we can find grant row, revoke row or grant with grant option 656 ** row. We use protecttype to figure that. If it is grant, then the user specific 657 ** privileges are added to the user's inherited privileges. If it is a revoke, 658 ** then the revoked privileges are subtracted from the inherited privileges. If 659 ** it is a grant with grant option, we just store it as is because privileges can 660 ** only be granted with grant option to individual users 661 */ 662 663 /* 664 ** for select action 665 */ 666 if (@action = @select_action) 667 begin 668 /* get the inherited select privilege */ 669 select @inherit_select = selectpriv 670 from #column_privileges 671 where (grantee = @grantee) and 672 (grantor = @grantor) 673 674 if (@protecttype = @grant_type) 675 /* the grantee has a individual grant */ 676 exec sybsystemprocs.dbo.syb_aux_privunion @inherit_select, @columns, 677 @col_count, @inherit_select output 678 679 else 680 if (@protecttype = @revoke_type) 681 /* it is a revoke row */ 682 exec sybsystemprocs.dbo.syb_aux_privexor @inherit_select, @columns, 683 @col_count, @inherit_select output 684 685 else 686 /* it is a grant with grant option */ 687 688 select @select_go = @columns 689 690 /* modify the privileges for this user */ 691 692 if ((@protecttype = @revoke_type) or (@protecttype = @grant_type)) 693 begin 694 update #column_privileges 695 set selectpriv = @inherit_select 696 where (grantor = @grantor) and 697 (grantee = @grantee) 698 end 699 else 700 begin 701 702 update #column_privileges 703 set select_go = @select_go 704 where (grantor = @grantor) and 705 (grantee = @grantee) 706 end 707 end 708 709 /* 710 ** update action 711 */ 712 if (@action = @update_action) 713 begin 714 /* find out the inherited update privilege */ 715 select @inherit_update = updatepriv 716 from #column_privileges 717 where (grantee = @grantee) and 718 (grantor = @grantor) 719 720 721 if (@protecttype = @grant_type) 722 /* user has an individual grant */ 723 exec sybsystemprocs.dbo.syb_aux_privunion @inherit_update, @columns, 724 @col_count, @inherit_update output 725 726 else 727 if (@protecttype = @revoke_type) 728 exec sybsystemprocs.dbo.syb_aux_privexor @inherit_update, @columns, 729 @col_count, @inherit_update output 730 731 else 732 /* it is a grant with grant option */ 733 select @update_go = @columns 734 735 736 /* modify the privileges for this user */ 737 738 if ((@protecttype = @revoke_type) or (@protecttype = @grant_type)) 739 begin 740 update #column_privileges 741 set updatepriv = @inherit_update 742 where (grantor = @grantor) and 743 (grantee = @grantee) 744 end 745 else 746 begin 747 update #column_privileges 748 set update_go = @update_go 749 where (grantor = @grantor) and 750 (grantee = @grantee) 751 end 752 end 753 754 /* it is the reference privilege */ 755 if (@action = @reference_action) 756 begin 757 select @inherit_reference = referencepriv 758 from #column_privileges 759 where (grantee = @grantee) and 760 (grantor = @grantor) 761 762 763 if (@protecttype = @grant_type) 764 /* the grantee has a individual grant */ 765 exec sybsystemprocs.dbo.syb_aux_privunion @inherit_reference, @columns, 766 @col_count, @inherit_reference output 767 768 else 769 if (@protecttype = @revoke_type) 770 /* it is a revoke row */ 771 exec sybsystemprocs.dbo.syb_aux_privexor @inherit_reference, @columns, 772 @col_count, @inherit_reference output 773 774 else 775 /* it is a grant with grant option */ 776 select @reference_go = @columns 777 778 779 /* modify the privileges for this user */ 780 781 if ((@protecttype = @revoke_type) or (@protecttype = @grant_type)) 782 begin 783 update #column_privileges 784 set referencepriv = @inherit_reference 785 where (grantor = @grantor) and 786 (grantee = @grantee) 787 end 788 else 789 begin 790 update #column_privileges 791 set reference_go = @reference_go 792 where (grantor = @grantor) and 793 (grantee = @grantee) 794 end 795 796 end 797 798 /* 799 ** insert action 800 */ 801 802 if (@action = @insert_action) 803 begin 804 if (@protecttype = @grant_type) 805 select @inherit_insert = 1 806 else 807 if (@protecttype = @revoke_type) 808 select @inherit_insert = 0 809 else 810 select @insert_go = 1 811 812 813 /* modify the privileges for this user */ 814 815 if ((@protecttype = @revoke_type) or (@protecttype = @grant_type)) 816 begin 817 update #column_privileges 818 set insertpriv = @inherit_insert 819 where (grantor = @grantor) and 820 (grantee = @grantee) 821 end 822 else 823 begin 824 update #column_privileges 825 set insert_go = @insert_go 826 where (grantor = @grantor) and 827 (grantee = @grantee) 828 end 829 830 end 831 832 /* 833 ** delete action 834 */ 835 836 if (@action = @delete_action) 837 begin 838 if (@protecttype = @grant_type) 839 select @inherit_delete = 1 840 else 841 if (@protecttype = @revoke_type) 842 select @inherit_delete = 0 843 else 844 select @delete_go = 1 845 846 847 /* modify the privileges for this user */ 848 849 if ((@protecttype = @revoke_type) or (@protecttype = @grant_type)) 850 begin 851 update #column_privileges 852 set deletepriv = @inherit_delete 853 where (grantor = @grantor) and 854 (grantee = @grantee) 855 end 856 else 857 begin 858 update #column_privileges 859 set delete_go = @delete_go 860 where (grantor = @grantor) and 861 (grantee = @grantee) 862 end 863 864 end 865 866 fetch user_protect into @grantee, @action, @protecttype, @columns, @grantor 867 end 868 869 close user_protect 870 871 open col_priv_cursor 872 fetch col_priv_cursor into @grantor, @grantee, @inherit_insert, @insert_go, 873 @inherit_delete, @delete_go, @inherit_select, @select_go, 874 @inherit_update, @update_go, @inherit_reference, @reference_go 875 876 while (@@sqlstatus != 2) 877 begin 878 879 /* 880 ** name of the grantor 881 */ 882 select @grantor_name = name 883 from sysusers 884 where uid = @grantor 885 886 887 /* 888 ** name of the grantee 889 */ 890 891 select @grantee_name = name 892 from sysusers 893 where uid = @grantee 894 895 896 if (@column_name = '%') 897 /* 898 ** we need to print the privilege information for all columns of the table 899 */ 900 begin 901 select @col_pos = 1 902 903 while (@col_pos <= @col_count) 904 begin 905 906 select @curr_column = col_name(@tab_id, @col_pos) 907 908 /* 909 ** check for insert privileges 910 */ 911 exec sybsystemprocs.dbo.syb_aux_printprivs 912 @calledfrom_colpriv, @col_pos, @inherit_insert, @insert_go, 0x00, 0x00, 0, 913 @grantable output, @is_printable output 914 915 if (@is_printable = 1) 916 begin 917 insert into #results_table 918 values (@table_qualifier, @table_owner, @table_name, @curr_column, 919 @grantor_name, @grantee_name, 'INSERT', @grantable) 920 end 921 922 /* 923 ** check for select privileges 924 */ 925 exec sybsystemprocs.dbo.syb_aux_printprivs 926 @calledfrom_colpriv, @col_pos, 0, 0, @inherit_select, @select_go, 1, 927 @grantable output, @is_printable output 928 929 if (@is_printable = 1) 930 begin 931 insert into #results_table 932 values (@table_qualifier, @table_owner, @table_name, @curr_column, 933 @grantor_name, @grantee_name, 'SELECT', @grantable) 934 end 935 /* 936 ** check for update privileges 937 */ 938 exec sybsystemprocs.dbo.syb_aux_printprivs 939 @calledfrom_colpriv, @col_pos, 0, 0, @inherit_update, @update_go, 1, 940 @grantable output, @is_printable output 941 942 if (@is_printable = 1) 943 begin 944 insert into #results_table 945 values (@table_qualifier, @table_owner, @table_name, @curr_column, 946 @grantor_name, @grantee_name, 'UPDATE', @grantable) 947 end 948 /* 949 ** check for reference privs 950 */ 951 952 exec sybsystemprocs.dbo.syb_aux_printprivs 953 @calledfrom_colpriv, @col_pos, 0, 0, @inherit_reference, @reference_go, 1, 954 @grantable output, @is_printable output 955 956 if (@is_printable = 1) 957 begin 958 insert into #results_table 959 values (@table_qualifier, @table_owner, @table_name, @curr_column, 960 @grantor_name, @grantee_name, 'REFERENCE', @grantable) 961 end 962 963 select @col_pos = @col_pos + 1 964 end 965 end 966 else 967 begin 968 969 /* 970 ** At this point, we are either printing privilege information for a 971 ** a specific column or for table_privileges 972 */ 973 974 select @col_pos = 0 975 976 if (@calledfrom_colpriv = 1) 977 begin 978 /* 979 ** find the column position 980 */ 981 select @col_pos = colid 982 from syscolumns 983 where (id = @tab_id) and 984 (name = @column_name) 985 end 986 987 /* 988 ** check for insert privileges 989 */ 990 exec sybsystemprocs.dbo.syb_aux_printprivs 991 @calledfrom_colpriv, @col_pos, @inherit_insert, @insert_go, 0x00, 0x00, 0, 992 @grantable output, @is_printable output 993 994 if (@is_printable = 1) 995 begin 996 insert into #results_table 997 values (@table_qualifier, @table_owner, @table_name, @column_name, 998 @grantor_name, @grantee_name, 'INSERT', @grantable) 999 end 1000 1001 /* 1002 ** check for delete privileges 1003 */ 1004 1005 if (@calledfrom_colpriv = 0) 1006 /* delete privilge need only be printed if called from sp_table_privileges */ 1007 begin 1008 exec sybsystemprocs.dbo.syb_aux_printprivs 1009 @calledfrom_colpriv, @col_pos, @inherit_delete, @delete_go, 0x00, 0x00, 0, 1010 @grantable output, @is_printable output 1011 1012 if (@is_printable = 1) 1013 begin 1014 insert into #results_table 1015 values (@table_qualifier, @table_owner, @table_name, @column_name, 1016 @grantor_name, @grantee_name, 'DELETE', @grantable) 1017 end 1018 end 1019 1020 /* 1021 ** check for select privileges 1022 */ 1023 exec sybsystemprocs.dbo.syb_aux_printprivs 1024 @calledfrom_colpriv, @col_pos, 0, 0, @inherit_select, @select_go, 1, 1025 @grantable output, @is_printable output 1026 1027 1028 if (@is_printable = 1) 1029 begin 1030 insert into #results_table 1031 values (@table_qualifier, @table_owner, @table_name, @column_name, 1032 @grantor_name, @grantee_name, 'SELECT', @grantable) 1033 end 1034 /* 1035 ** check for update privileges 1036 */ 1037 exec sybsystemprocs.dbo.syb_aux_printprivs 1038 @calledfrom_colpriv, @col_pos, 0, 0, @inherit_update, @update_go, 1, 1039 @grantable output, @is_printable output 1040 1041 if (@is_printable = 1) 1042 begin 1043 insert into #results_table 1044 values (@table_qualifier, @table_owner, @table_name, @column_name, 1045 @grantor_name, @grantee_name, 'UPDATE', @grantable) 1046 end 1047 /* 1048 ** check for reference privs 1049 */ 1050 if (@calledfrom_colpriv = 1) 1051 /* reference privilge need only be printed if called from sp_column_privileges */ 1052 begin 1053 exec sybsystemprocs.dbo.syb_aux_printprivs 1054 @calledfrom_colpriv, @col_pos, 0, 0, @inherit_reference, @reference_go, 1, 1055 @grantable output, @is_printable output 1056 1057 if (@is_printable = 1) 1058 begin 1059 insert into #results_table 1060 values (@table_qualifier, @table_owner, @table_name, @column_name, 1061 @grantor_name, @grantee_name, 'REFERENCE', @grantable) 1062 end 1063 end 1064 1065 end 1066 1067 1068 fetch col_priv_cursor into @grantor, @grantee, @inherit_insert, @insert_go, 1069 @inherit_delete, @delete_go, @inherit_select, @select_go, 1070 @inherit_update, @update_go, @inherit_reference, @reference_go 1071 end 1072 1073 1074 /* 1075 ** Outputting the results table 1076 */ 1077 1078 if (@calledfrom_colpriv = 1) 1079 /* Changed to get the requested output order*/ 1080 exec sp_autoformat @fulltabname = #results_table, 1081 @selectlist = "TABLE_CAT = table_qualifier, TABLE_SCHEM = table_owner, TABLE_NAME = table_name, COLUMN_NAME = column_name, GRANTOR = grantor, GRANTEE = grantee, PRIVILEGE = privilege, IS_GRANTABLE = is_grantable", 1082 @whereclause = "column_name like @save_column_name ESCAPE '\'", 1083 @orderby = "order by column_name, privilege" 1084 1085 else 1086 /* Changed to get the requested output order*/ 1087 exec sp_autoformat @fulltabname = #results_table, 1088 @selectlist = "TABLE_CAT = table_qualifier, TABLE_SCHEM = table_owner, TABLE_NAME = table_name, GRANTOR = grantor, GRANTEE = grantee, PRIVILEGE = privilege, IS_GRANTABLE = is_grantable", 1089 @orderby = "order by table_qualifier, table_name, privilege" 1090 1091 close col_priv_cursor 1092 1093 drop table #column_privileges 1094 drop table #distinct_grantors 1095 drop table #sysprotects 1096 drop table #useful_groups 1097 drop table #results_table 1098 1099 /* 1100 ** End of sp_ijdbc_aux_computeprivs 1101 */ 1102 1103
exec sp_procxmode 'sp_ijdbc_aux_computeprivs', 'AnyMode' go Grant Execute on sp_ijdbc_aux_computeprivs to public go
| DEPENDENCIES |
| PROCS AND TABLES USED reads table sybsystemprocs..syscolumns calls proc sybsystemprocs..syb_aux_privexor calls proc sybsystemprocs..syb_aux_expandbitmap reads table sybsystemprocs..sysobjects reads table sybsystemprocs..sysusers reads table sybsystemprocs..sysprotects calls proc sybsystemprocs..syb_aux_printprivs calls proc sybsystemprocs..syb_aux_colbit calls proc sybsystemprocs..sp_autoformat  calls proc sybsystemprocs..sp_namecrack reads table tempdb..systypes (1)  calls proc sybsystemprocs..sp_autoformat reads table master..systypes (1)  reads table master..syscolumns (1) read_writes table tempdb..#colinfo_af (1) reads table tempdb..syscolumns (1) writes table tempdb..#results_table (1) calls proc sybsystemprocs..syb_aux_privunion read_writes table tempdb..#sysprotects (1) read_writes table tempdb..#useful_groups (1) read_writes table tempdb..#distinct_grantors (1) read_writes table tempdb..#column_privileges (1) |
