DatabaseProcApplicationCreatedLinks
sybsystemprocssp_aux_computeprivs  14 déc. 14Defects Propagation Dependencies

1     
2     create or replace 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  

DEFECTS
 QCAR 6 Cartesian product between tables #useful_groups and [#distinct_grantors] 144
 QCAR 6 Cartesian product between tables sybsystemprocs..sysusers su and [#distinct_grantors g] 249
 MINU 4 Unique Index with nullable columns sybsystemprocs..sysprotects sybsystemprocs..sysprotects
 MUSP 4 Unquoted String Parameter sybsystemprocs..sp_autoformat: @fulltabname 1007
 CUNU 3 Cursor not updated: cursor should contain 'for read only' clause user_protect 152
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 87
 MAW1 3 Warning message on %name% sybsystemprocs..syscolumns.id: Warning message on syscolumns 173
 MAW1 3 Warning message on %name% sybsystemprocs..sysprotects.id: Warning message on sysprotects 183
 MAW1 3 Warning message on %name% sybsystemprocs..syscolumns.id: Warning message on syscolumns 911
 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 214
 MBLI 3 Integer Value of Binary Literal is Platform Dependant 247
 MBLI 3 Integer Value of Binary Literal is Platform Dependant 248
 MBLI 3 Integer Value of Binary Literal is Platform Dependant 281
 MBLI 3 Integer Value of Binary Literal is Platform Dependant 282
 MBLI 3 Integer Value of Binary Literal is Platform Dependant 283
 MBLI 3 Integer Value of Binary Literal is Platform Dependant 358
 MBLI 3 Integer Value of Binary Literal is Platform Dependant 359
 MBLI 3 Integer Value of Binary Literal is Platform Dependant 360
 MBLI 3 Integer Value of Binary Literal is Platform Dependant 462
 MBLI 3 Integer Value of Binary Literal is Platform Dependant 463
 MBLI 3 Integer Value of Binary Literal is Platform Dependant 464
 MBLI 3 Integer Value of Binary Literal is Platform Dependant 636
 MBLI 3 Integer Value of Binary Literal is Platform Dependant 637
 MBLI 3 Integer Value of Binary Literal is Platform Dependant 638
 MBLI 3 Integer Value of Binary Literal is Platform Dependant 639
 MBLI 3 Integer Value of Binary Literal is Platform Dependant 640
 MBLI 3 Integer Value of Binary Literal is Platform Dependant 922
 MBLI 3 Integer Value of Binary Literal is Platform Dependant 941
 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 180
 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 227
 MNER 3 No Error Check should check @@error after insert 237
 MNER 3 No Error Check should check @@error after insert 246
 MNER 3 No Error Check should check @@error after insert 259
 MNER 3 No Error Check should check return value of exec 448
 MNER 3 No Error Check should check return value of exec 450
 MNER 3 No Error Check should check return value of exec 452
 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 return value of exec 557
 MNER 3 No Error Check should check @@error after update 575
 MNER 3 No Error Check should check @@error after update 604
 MNER 3 No Error Check should check @@error after update 634
 MNER 3 No Error Check should check return value of exec 678
 MNER 3 No Error Check should check return value of exec 684
 MNER 3 No Error Check should check @@error after update 696
 MNER 3 No Error Check should check @@error after update 704
 MNER 3 No Error Check should check return value of exec 725
 MNER 3 No Error Check should check return value of exec 730
 MNER 3 No Error Check should check @@error after update 742
 MNER 3 No Error Check should check @@error after update 749
 MNER 3 No Error Check should check return value of exec 767
 MNER 3 No Error Check should check return value of exec 773
 MNER 3 No Error Check should check @@error after update 785
 MNER 3 No Error Check should check @@error after update 792
 MNER 3 No Error Check should check @@error after update 819
 MNER 3 No Error Check should check @@error after update 826
 MNER 3 No Error Check should check @@error after update 853
 MNER 3 No Error Check should check @@error after update 860
 MNER 3 No Error Check should check return value of exec 921
 MNER 3 No Error Check should check @@error after insert 927
 MNER 3 No Error Check should check return value of exec 940
 MNER 3 No Error Check should check @@error after insert 946
 MNER 3 No Error Check should check return value of exec 955
 MNER 3 No Error Check should check @@error after insert 962
 MNER 3 No Error Check should check return value of exec 969
 MNER 3 No Error Check should check @@error after insert 975
 MNER 3 No Error Check should check return value of exec 982
 MNER 3 No Error Check should check @@error after insert 988
 MNER 3 No Error Check should check return value of exec 1007
 MUCO 3 Useless Code Useless Brackets in create proc 2
 MUCO 3 Useless Code Useless Brackets 262
 MUCO 3 Useless Code Useless Brackets 274
 MUCO 3 Useless Code Useless Brackets 290
 MUCO 3 Useless Code Useless Brackets 305
 MUCO 3 Useless Code Useless Brackets 319
 MUCO 3 Useless Code Useless Brackets 334
 MUCO 3 Useless Code Useless Brackets 344
 MUCO 3 Useless Code Useless Brackets 369
 MUCO 3 Useless Code Useless Brackets 372
 MUCO 3 Useless Code Useless Brackets 387
 MUCO 3 Useless Code Useless Brackets 402
 MUCO 3 Useless Code Useless Brackets 417
 MUCO 3 Useless Code Useless Brackets 428
 MUCO 3 Useless Code Useless Brackets 474
 MUCO 3 Useless Code Useless Brackets 477
 MUCO 3 Useless Code Useless Brackets 492
 MUCO 3 Useless Code Useless Brackets 507
 MUCO 3 Useless Code Useless Brackets 522
 MUCO 3 Useless Code Useless Brackets 532
 MUCO 3 Useless Code Useless Brackets 562
 MUCO 3 Useless Code Useless Brackets 565
 MUCO 3 Useless Code Useless Brackets 597
 MUCO 3 Useless Code Useless Brackets 654
 MUCO 3 Useless Code Useless Brackets 668
 MUCO 3 Useless Code Useless Brackets 676
 MUCO 3 Useless Code Useless Brackets 682
 MUCO 3 Useless Code Useless Brackets 694
 MUCO 3 Useless Code Useless Brackets 714
 MUCO 3 Useless Code Useless Brackets 723
 MUCO 3 Useless Code Useless Brackets 729
 MUCO 3 Useless Code Useless Brackets 740
 MUCO 3 Useless Code Useless Brackets 757
 MUCO 3 Useless Code Useless Brackets 765
 MUCO 3 Useless Code Useless Brackets 771
 MUCO 3 Useless Code Useless Brackets 783
 MUCO 3 Useless Code Useless Brackets 804
 MUCO 3 Useless Code Useless Brackets 806
 MUCO 3 Useless Code Useless Brackets 809
 MUCO 3 Useless Code Useless Brackets 817
 MUCO 3 Useless Code Useless Brackets 838
 MUCO 3 Useless Code Useless Brackets 840
 MUCO 3 Useless Code Useless Brackets 843
 MUCO 3 Useless Code Useless Brackets 851
 MUCO 3 Useless Code Useless Brackets 878
 MUCO 3 Useless Code Useless Brackets 904
 MUCO 3 Useless Code Useless Brackets 919
 MUCO 3 Useless Code Useless Brackets 925
 MUCO 3 Useless Code Useless Brackets 937
 MUCO 3 Useless Code Useless Brackets 944
 MUCO 3 Useless Code Useless Brackets 960
 MUCO 3 Useless Code Useless Brackets 973
 MUCO 3 Useless Code Useless Brackets 986
 MUCO 3 Useless Code Useless Brackets 1005
 MUIN 3 Column created using implicit nullability 95
 MUIN 3 Column created using implicit nullability 107
 MUIN 3 Column created using implicit nullability 116
 MUIN 3 Column created using implicit nullability 124
 QAFM 3 Var Assignment from potentially many rows 296
 QAFM 3 Var Assignment from potentially many rows 311
 QAFM 3 Var Assignment from potentially many rows 325
 QAFM 3 Var Assignment from potentially many rows 378
 QAFM 3 Var Assignment from potentially many rows 393
 QAFM 3 Var Assignment from potentially many rows 408
 QAFM 3 Var Assignment from potentially many rows 483
 QAFM 3 Var Assignment from potentially many rows 498
 QAFM 3 Var Assignment from potentially many rows 513
 QAFM 3 Var Assignment from potentially many rows 671
 QAFM 3 Var Assignment from potentially many rows 717
 QAFM 3 Var Assignment from potentially many rows 759
 QAFM 3 Var Assignment from potentially many rows 909
 QDIS 3 Check correct use of 'select distinct' 143
 QDIS 3 Check correct use of 'select distinct' 260
 QGWO 3 Group by/Distinct/Union without order by 238
 QGWO 3 Group by/Distinct/Union without order by 260
 QJWT 3 Join or Sarg Without Index on temp table 615
 QNAJ 3 Not using ANSI Inner Join 249
 QNAJ 3 Not using ANSI Inner Join 261
 QNUA 3 Should use Alias: Column grantor should use alias #distinct_grantors 143
 QNUA 3 Should use Alias: Column grp_id should use alias #useful_groups 143
 QNUA 3 Should use Alias: Table #distinct_grantors 144
 QNUA 3 Should use Alias: Table #useful_groups 144
 QNUA 3 Should use Alias: Column gid should use alias su 247
 QNUA 3 Should use Alias: Column gid should use alias su 260
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: syscolumns.csyscolumns unique clustered
(id, number, colid)
Intersection: {id}
173
 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}
183
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: syscolumns.csyscolumns unique clustered
(id, number, colid)
Intersection: {id}
911
 QTJ1 3 Table only appears in inner join clause 261
 VNRD 3 Variable is not read @dbid 79
 CRDO 2 Read Only Cursor Marker (has a 'distinct' option) 143
 CUPD 2 Updatable Cursor Marker (updatable by default) 152
 CUPD 2 Updatable Cursor Marker (updatable by default) 162
 MSUB 2 Subquery Marker 196
 MSUB 2 Subquery Marker 203
 MSUB 2 Subquery Marker 210
 MSUB 2 Subquery Marker 217
 MSUB 2 Subquery Marker 224
 MSUB 2 Subquery Marker 290
 MSUB 2 Subquery Marker 305
 MSUB 2 Subquery Marker 319
 MSUB 2 Subquery Marker 334
 MSUB 2 Subquery Marker 344
 MSUB 2 Subquery Marker 372
 MSUB 2 Subquery Marker 387
 MSUB 2 Subquery Marker 402
 MSUB 2 Subquery Marker 417
 MSUB 2 Subquery Marker 428
 MSUB 2 Subquery Marker 477
 MSUB 2 Subquery Marker 492
 MSUB 2 Subquery Marker 507
 MSUB 2 Subquery Marker 522
 MSUB 2 Subquery Marker 532
 MSUC 2 Correlated Subquery Marker 613
 MTR1 2 Metrics: Comments Ratio Comments: 27% 2
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 145 = 144dec - 1exi + 2 2
 MTR3 2 Metrics: Query Complexity Complexity: 527 2

DEPENDENCIES
PROCS AND TABLES USED
calls proc sybsystemprocs..syb_aux_privunion  
writes table tempdb..#results_table (1) 
read_writes table tempdb..#distinct_grantors (1) 
calls proc sybsystemprocs..syb_aux_printprivs  
   calls proc sybsystemprocs..syb_aux_colbit  
reads table sybsystemprocs..syscolumns  
reads table sybsystemprocs..sysusers  
calls proc sybsystemprocs..sp_autoformat  
   writes table sybsystemprocs..sp_autoformat_rset_002 
   reads table master..systypes (1)  
   writes table sybsystemprocs..sp_autoformat_rset_004 
   writes table sybsystemprocs..sp_autoformat_rset_003 
   calls proc sybsystemprocs..sp_autoformat  
   read_writes table tempdb..#colinfo_af (1) 
   reads table tempdb..systypes (1)  
   writes table sybsystemprocs..sp_autoformat_rset_001 
   reads table tempdb..syscolumns (1)  
   calls proc sybsystemprocs..sp_namecrack  
   reads table master..syscolumns (1)  
   writes table sybsystemprocs..sp_autoformat_rset_005 
reads table sybsystemprocs..sysobjects  
calls proc sybsystemprocs..syb_aux_privexor  
   calls proc sybsystemprocs..syb_aux_expandbitmap  
read_writes table tempdb..#sysprotects (1) 
read_writes table tempdb..#column_privileges (1) 
reads table sybsystemprocs..sysprotects  
read_writes table tempdb..#useful_groups (1) 

CALLERS
called by proc sybsystemprocs..sp_table_privileges  
called by proc sybsystemprocs..sp_column_privileges