DatabaseProcApplicationCreatedLinks
sybsystemprocssp_addobjectdef  14 déc. 14Defects Propagation Dependencies

1     
2     /* Sccsid = "%Z% generic/sproc/src/%M% %I% %G%" */
3     
4     /*
5     ** Omni only
6     **
7     ** Messages for "sp_addobjectdef"	18299
8     **
9     ** 17240, "'%1!' is not a valid name."
10    ** 18299, "Unknown object type '%1!'."
11    ** 18300, "A server name is not permitted in the local object_name."
12    ** 18301, "Database name '%1!' is not the current database."
13    ** 18302, "User '%1!' is not a valid user in the '%2!' database."
14    ** 18303, "You must be the System Administrator (sa) or Database Owner (dbo) to add a definition for another user's table."
15    ** 18304, "Object '%1!' has already been defined."
16    ** 18305, "Table '%1!' has already been created."
17    ** 18306, "The format for the object_loc parameter is invalid."
18    ** 18307, "Server name '%1!' does not exist in sysservers."
19    ** 18308, "The server name in object_loc must be a remote server. '%1!' is the local server."
20    */
21    create or replace procedure sp_addobjectdef
22        @tablename varchar(1023), /* local table name */
23        @objectdef varchar(1023),
24        /* remote object to which local object will be mapped */
25        @objecttype varchar(10) = "table"
26    /* object type (file, table/view, rpc) */
27    as
28        begin
29    
30            declare @uid int,
31                @server varchar(255),
32                @dbname varchar(255),
33                @owner varchar(255),
34                @object varchar(255),
35                @r_object varchar(255),
36                @nullarg char(1),
37                @retcode int,
38                @type smallint, /* type */
39                @srvclass smallint,
40                @srvid smallint,
41                @dso_class smallint,
42                @attrib smallint,
43                @objectdef_tmp varchar(1023),
44                @maxlen int,
45                @dummy int,
46                @gp_enabled int
47    
48    
49            set nocount on
50    
51    
52            /*
53            ** We cannot add an Omni proxy table to a database used as an IQ catalog
54            ** database.  Check for sysIQ_objects.
55            */
56            select @dbname = db_name()
57            exec @retcode = sp_iqdbcheck @dbname
58            if @retcode = 1
59            begin
60                return 1
61            end
62            select @dbname = ""
63    
64    
65            /*
66            ** Get a value @type from @objecttype
67            */
68            select @type = number from master.dbo.spt_values
69            where type = 'Y' and name = lower(@objecttype)
70    
71            if @@rowcount = 0 or lower(@objecttype) = 'directory'
72            begin
73                /*
74                ** 18299, "Unknown object type '%1!'."
75                */
76                raiserror 18299, @objecttype
77                return (1)
78            end
79    
80            /*
81            ** Check that @tablename refers to object in current database,
82            ** and determine correct user id.
83            */
84            exec @retcode = sp_namecrack @tablename, @server output, @dbname output,
85                @owner output, @object output
86            if @retcode != 0
87            begin
88                /*
89                ** 17240, "'%1!' is not a valid name."
90                */
91                raiserror 17240, @tablename
92                return (1)
93    
94            end
95    
96            if @server is not null
97            begin
98                /*
99                **  18300, "A server name is not permitted in the local object_name." 
100               */
101               raiserror 18300
102               return (1)
103           end
104   
105           if @dbname is not null
106           begin
107               if @dbname != db_name()
108               begin
109                   /*
110                   ** 18301, "Database name '%1!' is not your current database."
111                   */
112                   raiserror 18301, @dbname
113                   return (1)
114               end
115           end
116   
117           if @owner is NULL
118           begin
119               select @uid = user_id()
120           end
121           else
122           begin
123               select @uid = user_id(@owner)
124               if @uid is NULL
125               begin
126                   select @dbname = db_name()
127                   /*
128                   ** 18302, "User '%1!' is not a valid user in the '%2!' database."
129                   */
130                   raiserror 18302, @owner, @dbname
131                   return (1)
132               end
133           end
134   
135           if @uid != user_id()
136           BEGIN
137               /*
138               ** If granular permissions is not enabled then only dbo or a user who
139               ** has sa_role can add a definition for another user's table.
140               ** If granular permissions is enabled then only a user with 
141               ** 'manage database' permission can add a definition for another 
142               ** user's table.
143               */
144               select @nullarg = NULL
145               execute @retcode = sp_aux_checkroleperm
146                   "dbo", "manage database", @dbname,
147                   @gp_enabled output
148   
149               if (@gp_enabled = 0)
150               BEGIN
151                   if (@retcode > 0)
152                   begin
153                       /*
154                       ** check if user has sa role, proc_role will
155                       ** also do auditing if required. proc_role will also
156                       ** print error message if required.
157                       */
158                       execute @retcode = sp_aux_checkroleperm
159                           "sa_role", @nullarg, @nullarg,
160                           @gp_enabled output
161   
162                       if (proc_role("sa_role") = 0)
163                       begin
164                           return 1
165                       end
166                   end
167               END
168               ELSE
169               BEGIN
170                   select @dummy = proc_auditperm("manage database",
171                           @retcode, @dbname)
172               END
173               if (@retcode != 0)
174                   return (1)
175   
176           END
177           /*
178           ** Check that tabname is valid
179           */
180           if (@object is not null)
181           begin
182               select @maxlen = length from syscolumns
183               where id = object_id("sysobjects") and name = "name"
184   
185               if valid_name(@object, @maxlen) = 0
186               begin
187                   /*
188                   ** 17240, "'%1!' is not a valid name."
189                   */
190                   raiserror 17240, @object
191                   return 1
192               end
193           end
194   
195           /*
196           ** Strip out quotes from table name if quoted identifier is
197           ** on
198           */
199           if @object like '"%"'
200           begin
201               select @object = substring(@object, 2, char_length(@object) - 2)
202           end
203   
204   
205           /*
206           ** See if the object has already been defined.
207           */
208           select @dso_class = 9, @attrib = 1
209   
210           if exists (select * from sysattributes
211                   where class = @dso_class and attribute = @attrib and
212                       object_cinfo = @object and object_info1 = @uid)
213           begin
214               /*
215               ** 18304, "Object '%1!' has already been defined."
216               */
217               raiserror 18304, @object
218               return (1)
219           end
220   
221           /*
222           ** See if the table has already been created.
223           */
224           if @type != 5
225           begin
226               if exists (select * from sysobjects
227                       where type = 'U' and name = @object and uid = @uid)
228               begin
229                   /*
230                   ** 18305, "Table '%1!' has already been created."
231                   */
232                   raiserror 18305, @object
233                   return (1)
234               end
235           end
236   
237           /*
238           ** If we are a table/view or RPC, check table definition accordingly.
239           */
240           if @type = 1 or @type = 3
241           begin
242               execute @retcode = sp_namecrack @objectdef, @server output,
243                   @dbname output, @owner output,
244                   @r_object output
245               if @retcode != 0
246               begin
247                   /*
248                   ** If we are a table or view, see if object specification
249                   ** contains an auxiliary fields:
250                   **
251                   **  	server.db.owner.tbl;auxname
252                   */
253                   if @type = 1
254                   begin
255                       select @objectdef_tmp = substring(@objectdef, 1,
256                               CHARINDEX(";", @objectdef))
257                       execute @retcode = sp_namecrack @objectdef_tmp,
258                           @server output,
259                           @dbname output, @owner output,
260                           @r_object output
261                   end
262                   if @retcode != 0
263                   begin
264                       /*
265                       ** ** 18306, "Format for the object_loc parameter is invalid."
266                       */
267                       raiserror 18306
268                       return (1)
269   
270                   end
271               end
272               select @srvclass = srvclass,
273                   @srvid = srvid
274               from master.dbo.sysservers
275               where srvname = @server
276               if @@rowcount = 0
277               begin
278                   /*
279                   ** 18307, "Server name '%1!' does not exist in sysservers."
280                   */
281                   raiserror 18307, @server
282                   return (1)
283               end
284               else if @srvclass = 0
285               begin
286                   /*
287                   ** 18308, "The server name in object_loc must be a remote server; '%1!' is the local server."
288                   */
289                   raiserror 18308, @server
290                   return (1)
291               end
292           end
293           else if @type = 2 or @type = 4 or @type = 5
294           begin
295               select @srvid = 0
296           end
297   
298           insert into sysattributes
299           (class, attribute, object_type, object_cinfo, object_info1,
300               object_info2, object_info3, char_value)
301           values (@dso_class, @attrib, "OD", @object, @uid, @type, @srvid, @objectdef)
302   
303           return (0)
304       end
305   
306   


exec sp_procxmode 'sp_addobjectdef', 'AnyMode'
go

Grant Execute on sp_addobjectdef to public
go
DEFECTS
 MEST 4 Empty String will be replaced by Single Space 62
 MINU 4 Unique Index with nullable columns sybsystemprocs..sysattributes sybsystemprocs..sysattributes
 MTYP 4 Assignment type mismatch @dbname: varchar(30) = varchar(255) 57
 MTYP 4 Assignment type mismatch @type: smallint = int 68
 MTYP 4 Assignment type mismatch char_value: varchar(768) = varchar(1023) 301
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: spt_values.spt_valuesclust clustered
(number, type)
Intersection: {type}
69
 TNOU 4 Table with no unique index master..spt_values master..spt_values
 MAW1 3 Warning message on %name% sybsystemprocs..syscolumns.id: Warning message on syscolumns 183
 MGTP 3 Grant to public master..spt_values  
 MGTP 3 Grant to public master..sysservers  
 MGTP 3 Grant to public sybsystemprocs..sp_addobjectdef  
 MGTP 3 Grant to public sybsystemprocs..sysattributes  
 MGTP 3 Grant to public sybsystemprocs..syscolumns  
 MGTP 3 Grant to public sybsystemprocs..sysobjects  
 MNER 3 No Error Check should check return value of exec 158
 MNER 3 No Error Check should check @@error after insert 298
 MUCO 3 Useless Code Useless Begin-End Pair 28
 MUCO 3 Useless Code Useless Brackets 77
 MUCO 3 Useless Code Useless Brackets 92
 MUCO 3 Useless Code Useless Brackets 102
 MUCO 3 Useless Code Useless Brackets 113
 MUCO 3 Useless Code Useless Brackets 131
 MUCO 3 Useless Code Useless Brackets 149
 MUCO 3 Useless Code Useless Brackets 151
 MUCO 3 Useless Code Useless Brackets 162
 MUCO 3 Useless Code Useless Brackets 173
 MUCO 3 Useless Code Useless Brackets 174
 MUCO 3 Useless Code Useless Brackets 180
 MUCO 3 Useless Code Useless Brackets 218
 MUCO 3 Useless Code Useless Brackets 233
 MUCO 3 Useless Code Useless Brackets 268
 MUCO 3 Useless Code Useless Brackets 282
 MUCO 3 Useless Code Useless Brackets 290
 MUCO 3 Useless Code Useless Brackets 303
 QAFM 3 Var Assignment from potentially many rows 68
 QAFM 3 Var Assignment from potentially many rows 182
 QIWC 3 Insert with not all columns specified missing 7 columns out of 15 299
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: syscolumns.csyscolumns unique clustered
(id, number, colid)
Intersection: {id}
183
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysattributes.csysattributes unique clustered
(class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo)
Intersection: {object_info1, object_cinfo, attribute, class}
211
 VNRD 3 Variable is not read @gp_enabled 160
 VNRD 3 Variable is not read @dummy 170
 VNRD 3 Variable is not read @dbname 259
 VNRD 3 Variable is not read @owner 259
 VNRD 3 Variable is not read @r_object 260
 MSUB 2 Subquery Marker 210
 MSUB 2 Subquery Marker 226
 MTR1 2 Metrics: Comments Ratio Comments: 41% 21
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 25 = 38dec - 15exi + 2 21
 MTR3 2 Metrics: Query Complexity Complexity: 124 21

DEPENDENCIES
PROCS AND TABLES USED
calls proc sybsystemprocs..sp_aux_checkroleperm  
   reads table master..syscurconfigs (1)  
   reads table master..sysconfigures (1)  
reads table master..spt_values (1)  
read_writes table sybsystemprocs..sysattributes  
reads table sybsystemprocs..sysobjects  
calls proc sybsystemprocs..sp_namecrack  
calls proc sybsystemprocs..sp_iqdbcheck  
reads table sybsystemprocs..syscolumns  
reads table master..sysservers (1)  

CALLERS
called by proc sybsystemprocs..sp_addtabledef