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