DatabaseProcApplicationCreatedLinks
sybsystemprocssp_export_qpgroup  14 déc. 14Defects Propagation Dependencies

1     
2     /* Sccsid = "%Z% generic/sproc/%M% %I% %G%" */
3     
4     /*
5     ** Messages for "sp_export_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    ** 18639, "There is no query plans group named '%1!' in this database."
14    */
15    
16    /*
17    ** sp_export_qpgroup exports in an user table (that it creates) 
18    ** the SYSQUERYPLANS section that corresponds to an AP group of 
19    ** a given user. This table has an opaque format (actually the 
20    ** same as SYSQUERYPLANS), and can be used by sp_import_qpgroup, 
21    ** maybe after having been transferred to another database or 
22    ** server. 
23    */
24    
25    create or replace procedure sp_export_qpgroup
26        @usr varchar(255),
27        @group varchar(255),
28        @tab varchar(255)
29    as
30        declare
31            @gid int
32            , @uid int
33            , @s_gid varchar(10)
34            , @s_uid varchar(10)
35            , @dbname varchar(30)
36            , @nullarg char(1)
37            , @dummy int
38            , @status int
39            , @gp_enabled int
40    
41    
42        set nocount on
43    
44        select @dbname = db_name()
45    
46        /*
47        ** If granular permissions is not enabled then sa_role is required.
48        ** If granular permissions is enabled then the permission
49        ** 'manage abstract_plans' is required.  proc_role and
50        ** proc_auditperm will also do auditing if required. Both will
51        ** also print error message if required.
52        */
53    
54        select @nullarg = NULL
55        execute @status = sp_aux_checkroleperm "dbo", "manage abstract plans",
56            @dbname, @gp_enabled output
57    
58        /* For Auditing */
59        if (@gp_enabled = 0)
60        begin
61            if (@status != 0)
62            begin
63                execute @status = sp_aux_checkroleperm "sa_role",
64                    @nullarg, @nullarg, @gp_enabled output
65                if (@status != 0)
66                begin
67    
68                    /* 17230, "You must be the System Administrator (SA) or the Database Owner (dbo) to execute this procedure." */
69                    raiserror 17230
70                    return (2)
71                end
72            end
73        end
74        else
75        begin
76            select @dummy = proc_auditperm("manage abstract plans", @status, @dbname)
77        end
78    
79        if (@status != 0)
80            return (2)
81    
82        /*
83        ** AP group handling may not be done within a transaction. 
84        */
85        if @@trancount > 0
86        begin
87            /*
88            ** 17260, "Can't run %1! from within a transaction."
89            */
90            raiserror 17260, "sp_export_qpgroup"
91            return 1
92        end
93    
94        /* Don't start a transaction for the user, he does not expect it. */
95        set chained off
96    
97        /* Don't hold long READ locks, the user might not appreciate it. */
98        set transaction isolation level 1
99    
100   
101       exec sp_aux_get_qpgroup @group, @gid out
102   
103       if @gid is null
104       begin
105           /* 18639, "There is no query plans group named '%1!' in this database." */
106           raiserror 18639, @group
107           return 1
108       end
109   
110       select @uid = user_id(@usr)
111   
112       if @uid is null
113       begin
114           /* 17703, "The name supplied is not a user, group, or aliased." */
115           raiserror 17703
116           return 2
117       end
118   
119       /* prepare the UID and GID as VARCHAR, for EXEC ("...") */
120       select @s_uid = convert(varchar(10), @uid)
121       select @s_gid = convert(varchar(10), @gid)
122   
123       /* create the export table and feed it with the APs */
124       execute (
125       "create table " + @tab + " (
126   			uid		int,
127   			gid		int,
128   			hashkey		int,
129   			id		int,
130   			type		smallint,
131   			sequence	smallint,
132   			status		int null,
133   			text		varchar(255) null, 
134                           dbid            int null, 
135                           qpdate          datetime null, 
136                           sprocid         int null, 
137                           hashkey2        int null, 
138                           key1            int null, 
139                           key2            int null, 
140                           key3            int null, 
141                           key4            int null  
142   		)")
143       /*
144       ** This transaction also writes a log record for replicating the
145       ** invocation of this procedure. If logexec() fails, the transaction
146       ** is aborted.
147       **
148       ** IMPORTANT: The name rs_logexec is significant and is used by
149       ** Replication Server.
150       */
151       begin transaction rs_logexec
152   
153       execute (
154       "insert into " + @tab +
155       " select * 
156   				from sysqueryplans where uid = " + @s_uid +
157       "and gid = " + @s_gid
158       )
159       /*
160       ** Write the log record to replicate this invocation
161       ** of the stored procedure.
162       */
163       if (logexec() != 1)
164       begin
165           /*
166           ** 17756, "The execution of the stored procedure
167           **         '%1!' in database '%2!' was aborted
168           **          because there was an error in writing
169           **          the replication log record."
170           */
171           exec ("drop table " + @tab)
172           raiserror 17756, "sp_export_qpgroup", @dbname
173           rollback transaction rs_logexec
174           return (1)
175       end
176   
177       commit transaction rs_logexec
178       return 0
179   


exec sp_procxmode 'sp_export_qpgroup', 'AnyMode'
go

Grant Execute on sp_export_qpgroup to public
go
DEFECTS
 MPSI 4 Possible SQL Injection @tab 124
 MPSI 4 Possible SQL Injection @tab 153
 MPSI 4 Possible SQL Injection @tab 171
 MTYP 4 Assignment type mismatch @name: varchar(30) = varchar(255) 101
 MDYN 3 Proc uses Dynamic SQL but is not flagged with Dynamic Ownership Chain 25
 MGTP 3 Grant to public sybsystemprocs..sp_export_qpgroup  
 MNER 3 No Error Check should check return value of exec 101
 MUCO 3 Useless Code Useless Brackets 59
 MUCO 3 Useless Code Useless Brackets 61
 MUCO 3 Useless Code Useless Brackets 65
 MUCO 3 Useless Code Useless Brackets 70
 MUCO 3 Useless Code Useless Brackets 79
 MUCO 3 Useless Code Useless Brackets 80
 MUCO 3 Useless Code Useless Brackets 163
 MUCO 3 Useless Code Useless Brackets 174
 QISO 3 Set isolation level 98
 VNRD 3 Variable is not read @gp_enabled 64
 VNRD 3 Variable is not read @dummy 76
 MDYS 2 Dynamic SQL Marker 124
 MDYS 2 Dynamic SQL Marker 153
 MDYS 2 Dynamic SQL Marker 171
 MTR1 2 Metrics: Comments Ratio Comments: 48% 25
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 5 = 9dec - 6exi + 2 25
 MTR3 2 Metrics: Query Complexity Complexity: 48 25

DEPENDENCIES
PROCS AND TABLES USED
calls proc sybsystemprocs..sp_aux_get_qpgroup  
   calls proc sybsystemprocs..sp_aux_sargs_qpgroup  
   reads table sybsystemprocs..sysattributes  
calls proc sybsystemprocs..sp_aux_checkroleperm  
   reads table master..syscurconfigs (1)  
   reads table master..sysconfigures (1)