DatabaseProcApplicationCreatedLinks
sybsystemprocssp_column_privileges  31 Aug 14Defects Dependencies

1     
2     /* Sccsid = "%Z% generic/sproc/src/%M% %I% %G%" */
3     
4     create procedure sp_column_privileges(
5         @table_name varchar(257),
6         @table_owner varchar(32) = null,
7         @table_qualifier varchar(32) = null,
8         @column_name varchar(257) = null)
9     
10    as
11    
12        declare @owner_id int
13        declare @full_table_name char(287)
14        declare @tab_id int /* object id of the table specified */
15    
16        set nocount on
17        /*
18        ** set the transaction isolation level
19        */
20        if @@trancount = 0
21        begin
22            set chained off
23        end
24        else
25        begin
26            /* we are inside a transaction. catalog procedure sp_column privileges
27            ** can't be run inside a transaction 
28            */
29            raiserror 18040, "sp_column_privileges"
30            return (1)
31        end
32    
33        set transaction isolation level 1
34    
35        /*
36        **  Check to see that the table is qualified with database name
37        */
38        if @table_name like "%.%.%"
39        begin
40            /* 18021, "Object name can only be qualified with owner name" */
41            raiserror 18021
42            return (1)
43        end
44    
45        /*  If this is a temporary table; object does not belong to 
46        **  this database; (we should be in our temporary database)
47        */
48        if (@table_name like "#%" and db_name() != db_name(tempdb_id()))
49        begin
50            /* 
51            ** 17676, "This may be a temporary object. Please execute 
52            ** procedure from your temporary database."
53            */
54            raiserror 17676
55            return (1)
56        end
57    
58        /*
59        ** The table_qualifier should be same as the database name. Do the sanity check
60        ** if it is specified
61        */
62        if (@table_qualifier is null) or (@table_qualifier = '')
63            /* set the table qualifier name */
64            select @table_qualifier = db_name()
65        else
66        begin
67            if db_name() != @table_qualifier
68            begin
69                raiserror 18039
70                return (1)
71            end
72        end
73    
74        /* 
75        ** if the table owner is not specified, it will be taken as the id of the
76        ** user executing this procedure. Otherwise find the explicit table name prefixed
77        ** by the owner id
78        */
79        if (@table_owner is null) or (@table_owner = '')
80            select @full_table_name = @table_name
81        else
82        begin
83            if (@table_name like "%.%") and
84                substring(@table_name, 1, charindex(".", @table_name) - 1) != @table_owner
85            begin
86                /* 18011, Object name must be qualified with the owner name * */
87                raiserror 18011
88                return (1)
89            end
90    
91            if not (@table_name like "%.%")
92                select @full_table_name = @table_owner + '.' + @table_name
93            else
94                select @full_table_name = @table_name
95    
96        end
97    
98        /* 
99        ** check to see if the specified table exists or not
100       */
101   
102       select @tab_id = object_id(@full_table_name)
103       if (@tab_id is null)
104       begin
105           raiserror 17492
106           return (1)
107       end
108   
109   
110       /*
111       ** check to see if the @tab_id indeeed represents a table or a view
112       */
113   
114       if not exists (select *
115               from sysobjects
116               where (@tab_id = id) and
117                   ((type = 'U') or
118                       (type = 'S') or
119                       (type = 'V')))
120       begin
121           raiserror 17492
122           return (1)
123       end
124   
125   
126       /*
127       ** if the column name is not specified, set the column name to wild 
128       ** character such it matches all the columns in the table
129       */
130       if @column_name is null
131           select @column_name = '%'
132   
133       else
134       begin
135           /*
136           ** check to see if the specified column is indeed a column belonging
137           ** to the table
138           */
139           if not exists (select *
140                   from syscolumns
141                   where (id = @tab_id) and
142                       (name like @column_name))
143           begin
144               raiserror 17563, @column_name
145               return (1)
146           end
147       end
148   
149       /* Create temp table to store results from sp_aux_computeprivs */
150       create table #results_table
151       (table_qualifier varchar(32),
152           table_owner varchar(32),
153           table_name varchar(257),
154           column_name varchar(257) NULL,
155           grantor varchar(32),
156           grantee varchar(32),
157           privilege varchar(32),
158           is_grantable varchar(3))
159   
160   
161       /*
162       ** declare cursor to cycle through all possible columns
163       */
164       declare cursor_columns cursor
165       for select name from syscolumns
166       where (id = @tab_id)
167           and (name like @column_name)
168   
169       /*
170       ** For each column in the list, generate privileges
171       */
172       open cursor_columns
173       fetch cursor_columns into @column_name
174       while (@@sqlstatus = 0)
175       begin
176   
177           /* 
178           ** compute the table owner id
179           */
180   
181           select @owner_id = uid
182           from sysobjects
183           where id = @tab_id
184   
185   
186           /*
187           ** get table owner name
188           */
189   
190           select @table_owner = name
191           from sysusers
192           where uid = @owner_id
193   
194           exec sp_aux_computeprivs @table_name, @table_owner, @table_qualifier,
195               @column_name, 1, @tab_id
196   
197           set nocount off
198   
199           fetch cursor_columns into @column_name
200       end
201   
202       close cursor_columns
203       deallocate cursor cursor_columns
204   
205       /* Print out results */
206       /* Adaptive Server has expanded all '*' elements in the following statement */ select distinct #results_table.table_qualifier, #results_table.table_owner, #results_table.table_name, #results_table.column_name, #results_table.grantor, #results_table.grantee, #results_table.privilege, #results_table.is_grantable into #temp_result from #results_table
207   
208       exec sp_autoformat @fulltabname = #temp_result,
209           @orderby = "order by column_name, grantee"
210   
211       drop table #temp_result
212       drop table #results_table
213       return (0)
214   


exec sp_procxmode 'sp_column_privileges', 'AnyMode'
go

Grant Execute on sp_column_privileges to public
go
DEFECTS
 MEST 4 Empty String will be replaced by Single Space 62
 MEST 4 Empty String will be replaced by Single Space 79
 MUSP 4 Unquoted String Parameter sybsystemprocs..sp_autoformat: @fulltabname 208
 CUNU 3 Cursor not updated: cursor should contain 'for read only' clause cursor_columns 165
 MGTP 3 Grant to public sybsystemprocs..sp_column_privileges  
 MGTP 3 Grant to public sybsystemprocs..syscolumns  
 MGTP 3 Grant to public sybsystemprocs..sysobjects  
 MGTP 3 Grant to public sybsystemprocs..sysusers  
 MLCH 3 Char type with length>30 char(287) 13
 MNER 3 No Error Check should check return value of exec 194
 MNER 3 No Error Check should check @@error after select into 206
 MNER 3 No Error Check should check return value of exec 208
 MUCO 3 Useless Code Useless Brackets in create proc 4
 MUCO 3 Useless Code Useless Brackets 30
 MUCO 3 Useless Code Useless Brackets 42
 MUCO 3 Useless Code Useless Brackets 48
 MUCO 3 Useless Code Useless Brackets 55
 MUCO 3 Useless Code Useless Brackets 70
 MUCO 3 Useless Code Useless Brackets 88
 MUCO 3 Useless Code Useless Brackets 103
 MUCO 3 Useless Code Useless Brackets 106
 MUCO 3 Useless Code Useless Brackets 122
 MUCO 3 Useless Code Useless Brackets 145
 MUCO 3 Useless Code Useless Brackets 174
 MUCO 3 Useless Code Useless Brackets 213
 MUIN 3 Column created using implicit nullability 150
 QGWO 3 Group by/Distinct/Union without order by 206
 QISO 3 Set isolation level 33
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: syscolumns.csyscolumns unique clustered
(id, number, colid)
Intersection: {id}
141
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: syscolumns.csyscolumns unique clustered
(id, number, colid)
Intersection: {id}
166
 CUPD 2 Updatable Cursor Marker (updatable by default) 165
 MSUB 2 Subquery Marker 114
 MSUB 2 Subquery Marker 139
 MTR1 2 Metrics: Comments Ratio Comments: 32% 4
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 15 = 22dec - 9exi + 2 4
 MTR3 2 Metrics: Query Complexity Complexity: 93 4

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