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