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