DatabaseProcApplicationCreatedLinks
sybsystemprocssp_import_qpgroup  14 déc. 14Defects Propagation Dependencies

1     
2     /* Sccsid = "%Z% generic/sproc/%M% %I% %G%" */
3     
4     /*
5     ** Messages for "sp_import_qpgroup"
6     **
7     ** 17230, "You must be the System Administrator (SA) or the Database Owner (dbo) to execute this procedure."
8     ** 17260, "Can't run %1! from within a transaction."
9     ** 17703, "The name supplied is not a user, group, or aliased."
10    ** 17756, "The execution of the stored procedure '%1!' in database
11    **         '%2!' was aborted because there was an error in writing the
12    **         replication log record."
13    ** 18660, "There is already a query plans group named '%1!' in this database."
14    */
15    
16    /*
17    ** sp_import_qpgroup inserts in SYSQUERYPLANS the opaque user table 
18    ** created by sp_export_qpgroup. By that , it creates a new group 
19    ** (with the given name) for a specific user. 
20    */
21    
22    create or replace procedure sp_import_qpgroup
23        @tab varchar(255),
24        @usr varchar(30),
25        @group varchar(30)
26    as
27        declare
28            @gid int
29            , @uid int
30            , @old_id int
31            , @new_id int
32            , @dbname varchar(30)
33            , @nullarg char(1)
34            , @dummy int
35            , @status int
36            , @gp_enabled int
37    
38    
39        set nocount on
40    
41        select @dbname = db_name()
42    
43        /* 
44        ** If granular permissions is not enabled then sa_role is required.
45        ** If granular permissions is enabled then the permission 
46        ** 'manage abstract_plans' is required.  proc_role and 
47        ** proc_auditperm will also do auditing if required. Both will 
48        ** also print error message if required.
49        */
50    
51        select @nullarg = NULL
52        execute @status = sp_aux_checkroleperm "dbo", "manage abstract plans",
53            @dbname, @gp_enabled output
54    
55        /* For Auditing */
56        if (@gp_enabled = 0)
57        begin
58            if (@status != 0)
59            begin
60                execute @status = sp_aux_checkroleperm "sa_role",
61                    @nullarg, @nullarg, @gp_enabled output
62                if (@status != 0)
63                begin
64    
65                    /* 17230, "You must be the System Administrator (SA) or the Database Owner (dbo) to execute this procedure." */
66                    raiserror 17230
67                    return (2)
68                end
69            end
70        end
71        else
72        begin
73            select @dummy = proc_auditperm("manage abstract plans", @status,
74                    @dbname)
75        end
76    
77        if (@status != 0)
78            return (2)
79    
80        /*
81        ** AP group handling may not be done within a transaction. 
82        */
83        if @@trancount > 0
84        begin
85            /*
86            ** 17260, "Can't run %1! from within a transaction."
87            */
88            raiserror 17260, "sp_import_qpgroup"
89            return 1
90        end
91    
92        /* Don't start a transaction for the user, he does not expect it. */
93        set chained off
94    
95        /* Don't hold long READ locks, the user might not appreciate it. */
96        set transaction isolation level 1
97    
98        exec sp_aux_get_qpgroup @group, @gid out
99    
100       select @uid = user_id(@usr)
101   
102       if @uid is null
103       begin
104           /* 17703, "The name supplied is not a user, group, or aliased." */
105           raiserror 17703
106           return 2
107       end
108   
109       if @gid is null
110       begin
111           /* if the group doesn't exist, add it */
112           exec sp_add_qpgroup @group
113           exec sp_aux_get_qpgroup @group, @gid out
114       end
115       else
116       begin
117           /* if it exists, check that it's empty */
118           if exists (select 1
119                   from sysqueryplans
120                   where gid = @gid
121                       and uid = @uid)
122           begin
123               /* 18660, "There is already a query plans group named '%1!' in this database." */
124               raiserror 18660, @group
125               return 2
126           end
127       end /* Adaptive Server has expanded all '*' elements in the following statement */
128   
129       /*
130       ** Create 2 work tables, one with the structure of SYSQUERYPLANS, 
131       ** to hold the APs to be imported, another one to hold the old-new
132       ** AP id mapping. Note that the first table will be useless when 
133       ** dynamic cursors will work in TSQL, as @tab could be used instead.
134       */
135   
136       select sysqueryplans.uid, sysqueryplans.gid, sysqueryplans.hashkey, sysqueryplans.id, sysqueryplans.[type], sysqueryplans.sequence, sysqueryplans.status, sysqueryplans.[text], sysqueryplans.dbid, sysqueryplans.qpdate, sysqueryplans.sprocid, sysqueryplans.hashkey2, sysqueryplans.key1, sysqueryplans.key2, sysqueryplans.key3, sysqueryplans.key4 into #aps
137       from sysqueryplans
138       where 1 = 0
139   
140       create table #ids_map(old_id int, new_id int)
141   
142       execute ("insert into #aps 
143   			select * from " + @tab
144       )
145   
146       /*
147       ** For each AP to insert, generate the new ID and update the 
148       ** old-new AP ID map. 
149       */
150   
151       declare ids cursor for select distinct id from #aps
152   
153       open ids
154       fetch ids into @old_id
155   
156       while @@sqlstatus = 0
157       begin
158           create plan 'select 1' '()' into @group
159           and set @new_id
160   
161           delete sysqueryplans where id = @new_id
162           insert #ids_map values (@old_id, @new_id)
163   
164           fetch ids into @old_id
165       end
166   
167       /*
168       ** This transaction also writes a log record for replicating the
169       ** invocation of this procedure. If logexec() fails, the transaction
170       ** is aborted.
171       **
172       ** IMPORTANT: The name rs_logexec is significant and is used by
173       ** Replication Server.
174       */
175       begin transaction rs_logexec
176   
177       /* finally, feed SYSQUERYPLANS */
178       insert sysqueryplans
179       select @uid, @gid, t.hashkey, m.new_id,
180           t.type, t.sequence, t.status, t.text,
181           t.dbid, t.qpdate, t.sprocid, t.hashkey2,
182           t.key1, t.key2, t.key3, t.key4
183       from #aps t, #ids_map m
184       where t.id = m.old_id
185   
186       /*
187       ** Write the log record to replicate this invocation
188       ** of the stored procedure.
189       */
190       if (logexec() != 1)
191       begin
192           /*
193           ** 17756, "The execution of the stored procedure
194           **         '%1!' in database '%2!' was aborted
195           **          because there was an error in writing
196           **          the replication log record."
197           */
198           raiserror 17756, "sp_import_qpgroup", @dbname
199           rollback transaction rs_logexec
200           return (1)
201       end
202   
203       commit transaction rs_logexec
204       return 0
205   


exec sp_procxmode 'sp_import_qpgroup', 'AnyMode'
go

Grant Execute on sp_import_qpgroup to public
go
DEFECTS
 MPSI 4 Possible SQL Injection @tab 142
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: sysqueryplans.csysqueryplans unique clustered
(uid, gid, hashkey, id, type, sequence)
Intersection: {id}
161
 MAW1 3 Warning message on %name% sybsystemprocs..sysqueryplans.id: Warning message on sysqueryplans 136
 MAW1 3 Warning message on %name% tempdb..#aps.id: Warning message on #aps_crby_sybsystemprocs__sp_import_qpgroup 151
 MAW1 3 Warning message on %name% sybsystemprocs..sysqueryplans.id: Warning message on sysqueryplans 161
 MAW1 3 Warning message on %name% tempdb..#aps.id: Warning message on #aps_crby_sybsystemprocs__sp_import_qpgroup 184
 MDYN 3 Proc uses Dynamic SQL but is not flagged with Dynamic Ownership Chain 22
 MGTP 3 Grant to public sybsystemprocs..sp_import_qpgroup  
 MGTP 3 Grant to public sybsystemprocs..sysqueryplans  
 MNER 3 No Error Check should check return value of exec 98
 MNER 3 No Error Check should check return value of exec 112
 MNER 3 No Error Check should check return value of exec 113
 MNER 3 No Error Check should check @@error after select into 136
 MNER 3 No Error Check should check @@error after delete 161
 MNER 3 No Error Check should check @@error after insert 162
 MNER 3 No Error Check should check @@error after insert 178
 MUCO 3 Useless Code Useless Brackets 56
 MUCO 3 Useless Code Useless Brackets 58
 MUCO 3 Useless Code Useless Brackets 62
 MUCO 3 Useless Code Useless Brackets 67
 MUCO 3 Useless Code Useless Brackets 77
 MUCO 3 Useless Code Useless Brackets 78
 MUCO 3 Useless Code Useless Brackets 190
 MUCO 3 Useless Code Useless Brackets 200
 MUIN 3 Column created using implicit nullability 140
 QGWO 3 Group by/Distinct/Union without order by 151
 QISO 3 Set isolation level 96
 QJWT 3 Join or Sarg Without Index on temp table 184
 QNAJ 3 Not using ANSI Inner Join 183
 QPNC 3 No column in condition 138
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysqueryplans.csysqueryplans unique clustered
(uid, gid, hashkey, id, type, sequence)
Intersection: {gid, uid}
120
 VNRD 3 Variable is not read @gp_enabled 61
 VNRD 3 Variable is not read @dummy 73
 CRDO 2 Read Only Cursor Marker (has a 'distinct' option) 151
 MDYS 2 Dynamic SQL Marker 142
 MSUB 2 Subquery Marker 118
 MTR1 2 Metrics: Comments Ratio Comments: 47% 22
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 9 = 13dec - 6exi + 2 22
 MTR3 2 Metrics: Query Complexity Complexity: 75 22

DATA PROPAGATION detailed
ColumnWritten To
@groupsysattributes.object_cinfo  
@usrsysqueryplans.uid  

DEPENDENCIES
PROCS AND TABLES USED
read_writes table tempdb..#ids_map (1) 
read_writes table tempdb..#aps (1) 
calls proc sybsystemprocs..sp_add_qpgroup  
   calls proc sybsystemprocs..sp_aux_get_qpgroup  
      reads table sybsystemprocs..sysattributes  
      calls proc sybsystemprocs..sp_aux_sargs_qpgroup  
   calls proc sybsystemprocs..sp_aux_sargs_qpgroup  
   calls proc sybsystemprocs..sp_aux_checkroleperm  
      reads table master..sysconfigures (1)  
      reads table master..syscurconfigs (1)  
   read_writes table sybsystemprocs..sysattributes  
read_writes table sybsystemprocs..sysqueryplans  
calls proc sybsystemprocs..sp_aux_get_qpgroup  
calls proc sybsystemprocs..sp_aux_checkroleperm