DatabaseProcApplicationCreatedLinks
sybsystemprocssp_import_qpgroup  31 Aug 14Defects 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 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 char(30)
33    
34    
35        set nocount on
36    
37        select @dbname = db_name()
38    
39        if user_id() != 1 and charindex("sa_role", show_role()) = 0
40        begin
41            /* 17230, "You must be the System Administrator (SA) or the Database Owner (dbo) to execute this procedure." */
42            raiserror 17230
43            return 2
44        end
45    
46        /*
47        ** AP group handling may not be done within a transaction. 
48        */
49        if @@trancount > 0
50        begin
51            /*
52            ** 17260, "Can't run %1! from within a transaction."
53            */
54            raiserror 17260, "sp_import_qpgroup"
55            return 1
56        end
57    
58        /* Don't start a transaction for the user, he does not expect it. */
59        set chained off
60    
61        /* Don't hold long READ locks, the user might not appreciate it. */
62        set transaction isolation level 1
63    
64        exec sp_aux_get_qpgroup @group, @gid out
65    
66        select @uid = user_id(@usr)
67    
68        if @uid is null
69        begin
70            /* 17703, "The name supplied is not a user, group, or aliased." */
71            raiserror 17703
72            return 2
73        end
74    
75        if @gid is null
76        begin
77            /* if the group doesn't exist, add it */
78            exec sp_add_qpgroup @group
79            exec sp_aux_get_qpgroup @group, @gid out
80        end
81        else
82        begin
83            /* if it exists, check that it's empty */
84            if exists (select 1
85                    from sysqueryplans
86                    where gid = @gid
87                        and uid = @uid)
88            begin
89                /* 18660, "There is already a query plans group named '%1!' in this database." */
90                raiserror 18660, @group
91                return 2
92            end
93        end /* Adaptive Server has expanded all '*' elements in the following statement */
94    
95        /*
96        ** Create 2 work tables, one with the structure of SYSQUERYPLANS, 
97        ** to hold the APs to be imported, another one to hold the old-new
98        ** AP id mapping. Note that the first table will be useless when 
99        ** dynamic cursors will work in TSQL, as @tab could be used instead.
100       */
101   
102       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
103       from sysqueryplans
104       where 1 = 0
105   
106       create table #ids_map(old_id int, new_id int)
107   
108       execute ("insert into #aps 
109   			select * from " + @tab
110       )
111   
112       /*
113       ** For each AP to insert, generate the new ID and update the 
114       ** old-new AP ID map. 
115       */
116   
117       declare ids cursor for select distinct id from #aps
118   
119       open ids
120       fetch ids into @old_id
121   
122       while @@sqlstatus = 0
123       begin
124           create plan 'select 1' '()' into @group
125           and set @new_id
126   
127           delete sysqueryplans where id = @new_id
128           insert #ids_map values (@old_id, @new_id)
129   
130           fetch ids into @old_id
131       end
132   
133       /*
134       ** This transaction also writes a log record for replicating the
135       ** invocation of this procedure. If logexec() fails, the transaction
136       ** is aborted.
137       **
138       ** IMPORTANT: The name rs_logexec is significant and is used by
139       ** Replication Server.
140       */
141       begin transaction rs_logexec
142   
143       /* finally, feed SYSQUERYPLANS */
144       insert sysqueryplans
145       select @uid, @gid, t.hashkey, m.new_id,
146           t.type, t.sequence, t.status, t.text,
147           t.dbid, t.qpdate, t.sprocid, t.hashkey2,
148           t.key1, t.key2, t.key3, t.key4
149       from #aps t, #ids_map m
150       where t.id = m.old_id
151   
152       /*
153       ** Write the log record to replicate this invocation
154       ** of the stored procedure.
155       */
156       if (logexec() != 1)
157       begin
158           /*
159           ** 17756, "The execution of the stored procedure
160           **         '%1!' in database '%2!' was aborted
161           **          because there was an error in writing
162           **          the replication log record."
163           */
164           raiserror 17756, "sp_import_qpgroup", @dbname
165           rollback transaction rs_logexec
166           return (1)
167       end
168   
169       commit transaction rs_logexec
170       return 0
171   


exec sp_procxmode 'sp_import_qpgroup', 'AnyMode'
go

Grant Execute on sp_import_qpgroup to public
go
DEFECTS
 MPSI 4 Possible SQL Injection @tab 108
 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}
127
 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 64
 MNER 3 No Error Check should check return value of exec 78
 MNER 3 No Error Check should check return value of exec 79
 MNER 3 No Error Check should check @@error after select into 102
 MNER 3 No Error Check should check @@error after delete 127
 MNER 3 No Error Check should check @@error after insert 128
 MNER 3 No Error Check should check @@error after insert 144
 MUCO 3 Useless Code Useless Brackets 156
 MUCO 3 Useless Code Useless Brackets 166
 MUIN 3 Column created using implicit nullability 106
 QGWO 3 Group by/Distinct/Union without order by 117
 QISO 3 Set isolation level 62
 QJWT 3 Join or Sarg Without Index on temp table 150
 QNAJ 3 Not using ANSI Inner Join 149
 QPNC 3 No column in condition 104
 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}
86
 CRDO 2 Read Only Cursor Marker (has a 'distinct' option) 117
 MDYS 2 Dynamic SQL Marker 108
 MSUB 2 Subquery Marker 84
 MTR1 2 Metrics: Comments Ratio Comments: 48% 22
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 6 = 10dec - 6exi + 2 22
 MTR3 2 Metrics: Query Complexity Complexity: 65 22

DEPENDENCIES
PROCS AND TABLES USED
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  
   read_writes table sybsystemprocs..sysattributes  
calls proc sybsystemprocs..sp_aux_get_qpgroup  
read_writes table sybsystemprocs..sysqueryplans  
read_writes table tempdb..#ids_map (1) 
read_writes table tempdb..#aps (1)