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