DatabaseProcApplicationCreatedLinks
sybsystemprocssp_jdbc_computeprivs  14 déc. 14Defects Propagation Dependencies

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


exec sp_procxmode 'sp_jdbc_computeprivs', 'AnyMode'
go

Grant Execute on sp_jdbc_computeprivs to public
go
DEFECTS
 QCAR 6 Cartesian product between tables #useful_groups and [#distinct_grantors] 135
 QCAR 6 Cartesian product between tables sybsystemprocs..sysusers su and [#distinct_grantors g] 243
 MINU 4 Unique Index with nullable columns sybsystemprocs..sysprotects sybsystemprocs..sysprotects
 CUNU 3 Cursor not updated: cursor should contain 'for read only' clause user_protect 143
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 123
 MAW1 3 Warning message on %name% sybsystemprocs..syscolumns.id: Warning message on syscolumns 166
 MAW1 3 Warning message on %name% sybsystemprocs..sysprotects.id: Warning message on sysprotects 176
 MAW1 3 Warning message on %name% sybsystemprocs..syscolumns.id: Warning message on syscolumns 910
 MBLI 3 Integer Value of Binary Literal is Platform Dependant 193
 MBLI 3 Integer Value of Binary Literal is Platform Dependant 200
 MBLI 3 Integer Value of Binary Literal is Platform Dependant 207
 MBLI 3 Integer Value of Binary Literal is Platform Dependant 241
 MBLI 3 Integer Value of Binary Literal is Platform Dependant 242
 MBLI 3 Integer Value of Binary Literal is Platform Dependant 276
 MBLI 3 Integer Value of Binary Literal is Platform Dependant 277
 MBLI 3 Integer Value of Binary Literal is Platform Dependant 278
 MBLI 3 Integer Value of Binary Literal is Platform Dependant 353
 MBLI 3 Integer Value of Binary Literal is Platform Dependant 354
 MBLI 3 Integer Value of Binary Literal is Platform Dependant 355
 MBLI 3 Integer Value of Binary Literal is Platform Dependant 457
 MBLI 3 Integer Value of Binary Literal is Platform Dependant 458
 MBLI 3 Integer Value of Binary Literal is Platform Dependant 459
 MBLI 3 Integer Value of Binary Literal is Platform Dependant 631
 MBLI 3 Integer Value of Binary Literal is Platform Dependant 632
 MBLI 3 Integer Value of Binary Literal is Platform Dependant 633
 MBLI 3 Integer Value of Binary Literal is Platform Dependant 634
 MBLI 3 Integer Value of Binary Literal is Platform Dependant 635
 MBLI 3 Integer Value of Binary Literal is Platform Dependant 922
 MBLI 3 Integer Value of Binary Literal is Platform Dependant 945
 MGTP 3 Grant to public sybsystemprocs..sp_jdbc_computeprivs  
 MGTP 3 Grant to public sybsystemprocs..syscolumns  
 MGTP 3 Grant to public sybsystemprocs..sysobjects  
 MGTP 3 Grant to public sybsystemprocs..sysprotects  
 MGTP 3 Grant to public sybsystemprocs..sysusers  
 MNER 3 No Error Check should check @@error after insert 173
 MNER 3 No Error Check should check @@error after insert 192
 MNER 3 No Error Check should check @@error after insert 199
 MNER 3 No Error Check should check @@error after insert 206
 MNER 3 No Error Check should check @@error after insert 213
 MNER 3 No Error Check should check @@error after insert 220
 MNER 3 No Error Check should check @@error after insert 230
 MNER 3 No Error Check should check @@error after insert 240
 MNER 3 No Error Check should check @@error after insert 254
 MNER 3 No Error Check should check return value of exec 443
 MNER 3 No Error Check should check return value of exec 445
 MNER 3 No Error Check should check return value of exec 447
 MNER 3 No Error Check should check return value of exec 542
 MNER 3 No Error Check should check return value of exec 547
 MNER 3 No Error Check should check return value of exec 552
 MNER 3 No Error Check should check @@error after update 570
 MNER 3 No Error Check should check @@error after update 599
 MNER 3 No Error Check should check @@error after update 629
 MNER 3 No Error Check should check return value of exec 674
 MNER 3 No Error Check should check return value of exec 680
 MNER 3 No Error Check should check @@error after update 692
 MNER 3 No Error Check should check @@error after update 700
 MNER 3 No Error Check should check return value of exec 721
 MNER 3 No Error Check should check return value of exec 726
 MNER 3 No Error Check should check @@error after update 738
 MNER 3 No Error Check should check @@error after update 745
 MNER 3 No Error Check should check return value of exec 763
 MNER 3 No Error Check should check return value of exec 769
 MNER 3 No Error Check should check @@error after update 782
 MNER 3 No Error Check should check @@error after update 789
 MNER 3 No Error Check should check @@error after update 816
 MNER 3 No Error Check should check @@error after update 823
 MNER 3 No Error Check should check @@error after update 850
 MNER 3 No Error Check should check @@error after update 857
 MNER 3 No Error Check should check return value of exec 920
 MNER 3 No Error Check should check @@error after insert 927
 MNER 3 No Error Check should check return value of exec 943
 MNER 3 No Error Check should check @@error after insert 950
 MNER 3 No Error Check should check return value of exec 961
 MNER 3 No Error Check should check @@error after insert 968
 MNER 3 No Error Check should check return value of exec 976
 MNER 3 No Error Check should check @@error after insert 982
 MNER 3 No Error Check should check return value of exec 990
 MNER 3 No Error Check should check @@error after insert 996
 MUCO 3 Useless Code Useless Brackets in create proc 3
 MUCO 3 Useless Code Useless Brackets 257
 MUCO 3 Useless Code Useless Brackets 269
 MUCO 3 Useless Code Useless Brackets 285
 MUCO 3 Useless Code Useless Brackets 300
 MUCO 3 Useless Code Useless Brackets 314
 MUCO 3 Useless Code Useless Brackets 329
 MUCO 3 Useless Code Useless Brackets 339
 MUCO 3 Useless Code Useless Brackets 364
 MUCO 3 Useless Code Useless Brackets 367
 MUCO 3 Useless Code Useless Brackets 382
 MUCO 3 Useless Code Useless Brackets 397
 MUCO 3 Useless Code Useless Brackets 412
 MUCO 3 Useless Code Useless Brackets 423
 MUCO 3 Useless Code Useless Brackets 469
 MUCO 3 Useless Code Useless Brackets 472
 MUCO 3 Useless Code Useless Brackets 487
 MUCO 3 Useless Code Useless Brackets 502
 MUCO 3 Useless Code Useless Brackets 517
 MUCO 3 Useless Code Useless Brackets 527
 MUCO 3 Useless Code Useless Brackets 557
 MUCO 3 Useless Code Useless Brackets 560
 MUCO 3 Useless Code Useless Brackets 592
 MUCO 3 Useless Code Useless Brackets 649
 MUCO 3 Useless Code Useless Brackets 664
 MUCO 3 Useless Code Useless Brackets 672
 MUCO 3 Useless Code Useless Brackets 678
 MUCO 3 Useless Code Useless Brackets 690
 MUCO 3 Useless Code Useless Brackets 710
 MUCO 3 Useless Code Useless Brackets 719
 MUCO 3 Useless Code Useless Brackets 725
 MUCO 3 Useless Code Useless Brackets 736
 MUCO 3 Useless Code Useless Brackets 753
 MUCO 3 Useless Code Useless Brackets 761
 MUCO 3 Useless Code Useless Brackets 767
 MUCO 3 Useless Code Useless Brackets 780
 MUCO 3 Useless Code Useless Brackets 801
 MUCO 3 Useless Code Useless Brackets 803
 MUCO 3 Useless Code Useless Brackets 806
 MUCO 3 Useless Code Useless Brackets 814
 MUCO 3 Useless Code Useless Brackets 835
 MUCO 3 Useless Code Useless Brackets 837
 MUCO 3 Useless Code Useless Brackets 840
 MUCO 3 Useless Code Useless Brackets 848
 MUCO 3 Useless Code Useless Brackets 877
 MUCO 3 Useless Code Useless Brackets 903
 MUCO 3 Useless Code Useless Brackets 918
 MUCO 3 Useless Code Useless Brackets 925
 MUCO 3 Useless Code Useless Brackets 939
 MUCO 3 Useless Code Useless Brackets 948
 MUCO 3 Useless Code Useless Brackets 966
 MUCO 3 Useless Code Useless Brackets 980
 MUCO 3 Useless Code Useless Brackets 994
 QAFM 3 Var Assignment from potentially many rows 291
 QAFM 3 Var Assignment from potentially many rows 306
 QAFM 3 Var Assignment from potentially many rows 320
 QAFM 3 Var Assignment from potentially many rows 373
 QAFM 3 Var Assignment from potentially many rows 388
 QAFM 3 Var Assignment from potentially many rows 403
 QAFM 3 Var Assignment from potentially many rows 478
 QAFM 3 Var Assignment from potentially many rows 493
 QAFM 3 Var Assignment from potentially many rows 508
 QAFM 3 Var Assignment from potentially many rows 667
 QAFM 3 Var Assignment from potentially many rows 713
 QAFM 3 Var Assignment from potentially many rows 755
 QAFM 3 Var Assignment from potentially many rows 908
 QDIS 3 Check correct use of 'select distinct' 134
 QDIS 3 Check correct use of 'select distinct' 255
 QGWO 3 Group by/Distinct/Union without order by 231
 QGWO 3 Group by/Distinct/Union without order by 255
 QJWT 3 Join or Sarg Without Index on temp table 610
 QNAJ 3 Not using ANSI Inner Join 243
 QNAJ 3 Not using ANSI Inner Join 256
 QNUA 3 Should use Alias: Column grantor should use alias #useful_groups 134
 QNUA 3 Should use Alias: Column grp_id should use alias #useful_groups 134
 QNUA 3 Should use Alias: Table #distinct_grantors 135
 QNUA 3 Should use Alias: Table #useful_groups 135
 QNUA 3 Should use Alias: Column gid should use alias su 241
 QNUA 3 Should use Alias: Column gid should use alias su 255
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: syscolumns.csyscolumns unique clustered
(id, number, colid)
Intersection: {id}
166
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysprotects.csysprotects unique clustered
(id, action, grantor, uid, protecttype, predid)
Intersection: {action, id}
176
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: syscolumns.csyscolumns unique clustered
(id, number, colid)
Intersection: {id}
910
 QTJ1 3 Table only appears in inner join clause 256
 VUNU 3 Variable is not used @dbid 86
 CRDO 2 Read Only Cursor Marker (has a 'distinct' option) 134
 CUPD 2 Updatable Cursor Marker (updatable by default) 143
 CUPD 2 Updatable Cursor Marker (updatable by default) 154
 MSUB 2 Subquery Marker 189
 MSUB 2 Subquery Marker 196
 MSUB 2 Subquery Marker 203
 MSUB 2 Subquery Marker 210
 MSUB 2 Subquery Marker 217
 MSUB 2 Subquery Marker 285
 MSUB 2 Subquery Marker 300
 MSUB 2 Subquery Marker 314
 MSUB 2 Subquery Marker 329
 MSUB 2 Subquery Marker 339
 MSUB 2 Subquery Marker 367
 MSUB 2 Subquery Marker 382
 MSUB 2 Subquery Marker 397
 MSUB 2 Subquery Marker 412
 MSUB 2 Subquery Marker 423
 MSUB 2 Subquery Marker 472
 MSUB 2 Subquery Marker 487
 MSUB 2 Subquery Marker 502
 MSUB 2 Subquery Marker 517
 MSUB 2 Subquery Marker 527
 MSUC 2 Correlated Subquery Marker 608
 MTR1 2 Metrics: Comments Ratio Comments: 26% 3
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 146 = 145dec - 1exi + 2 3
 MTR3 2 Metrics: Query Complexity Complexity: 516 3

DEPENDENCIES
PROCS AND TABLES USED
read_writes table tempdb..#column_privileges (1) 
writes table tempdb..#results_table (1) 
reads table sybsystemprocs..sysusers  
read_writes table tempdb..#sysprotects (1) 
read_writes table tempdb..#useful_groups (1) 
reads table sybsystemprocs..sysprotects  
calls proc sybsystemprocs..syb_aux_printprivs  
   calls proc sybsystemprocs..syb_aux_colbit  
reads table sybsystemprocs..sysobjects  
calls proc sybsystemprocs..syb_aux_privunion  
reads table sybsystemprocs..syscolumns  
read_writes table tempdb..#distinct_grantors (1) 
calls proc sybsystemprocs..syb_aux_privexor  
   calls proc sybsystemprocs..syb_aux_expandbitmap