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