DatabaseProcApplicationCreatedLinks
sybsystemprocssp_dropobjectdef  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_dropobjectdef"  18309
8     **
9     ** 18300, "A server name is not permitted in the local object_name."
10    ** 18301, "Database name '%1!' is not the current database."
11    ** 18302, "User '%1!' is not a valid user in the '%2!' database."
12    ** 18309, "You must be the System Administrator (sa) or Database Owner (dbo) to drop a definition for another user's table."
13    ** 18310, "Table has not been defined."
14    ** 18311, "You cannot remove a table definition for a table that has been created. Drop the table first."
15    */
16    create or replace procedure sp_dropobjectdef
17        @tablename varchar(1023) /* table name */
18    as begin
19    
20            declare @uid int,
21                @dso_class smallint,
22                @attrib smallint,
23                @dot tinyint,
24                @db_name varchar(255),
25                @nullarg char(1),
26                @nodb varchar(511),
27                @user_name varchar(255),
28                @tabname varchar(255),
29                @retcode int,
30                @dummy int,
31                @gp_enabled int
32    
33    
34    
35            set nocount on
36    
37            /*
38            ** The table name cannot contain a server name
39            */
40            if @tablename like "%.%.%.%"
41            begin
42                /*
43                ** 18300, "A server name is not permitted in the local object_name."     
44                */
45                raiserror 18300
46                return (1)
47            end
48    
49            /*
50            ** Check that @tablename refers to object in current database,
51            ** and determine correct user id.
52            */
53            if @tablename like "%.%.%"
54            begin
55                select @dot = charindex(".", @tablename)
56                select @db_name = substring(@tablename, 1, @dot - 1)
57    
58                if @db_name != db_name()
59                begin
60                    /*
61                    ** 18301, "Database name '%1!' is not the current database."
62                    */
63                    raiserror 18301, @db_name
64                    return (1)
65                end
66    
67                select @nodb = substring(@tablename, @dot + 1, 511)
68                select @dot = charindex(".", @nodb)
69                select @user_name = substring(@nodb, 1, @dot - 1)
70    
71                if @user_name is NULL
72                begin
73                    select @uid = user_id()
74                end
75                else
76                begin
77                    select @uid = user_id(@user_name)
78                    if @uid is NULL
79                    begin
80                        select @db_name = db_name()
81                        /*
82                        **  18302, "User '%1!' is not a valid user in the '%2!' database."
83                        */
84                        raiserror 18302, @user_name, @db_name
85                        return (1)
86                    end
87                end
88    
89                select @tabname = substring(@nodb, @dot + 1, 255)
90    
91            end
92            else if @tablename like "%.%"
93            begin
94                select @dot = charindex(".", @tablename)
95                select @user_name = substring(@tablename, 1, @dot - 1)
96    
97                if @user_name is NULL
98                begin
99                    select @uid = user_id()
100               end
101               else
102               begin
103                   select @uid = user_id(@user_name)
104                   if @uid is NULL
105                   begin
106                       select @db_name = db_name() /*
107                       **  18302, "User '%1!' is not a valid user in the '%2!' database."
108                       */
109                       raiserror 18302, @user_name, @db_name
110                       return (1)
111                   end
112               end
113   
114               select @tabname = substring(@tablename, @dot + 1, 255)
115   
116           end
117           else
118           begin
119               select @uid = user_id()
120               select @tabname = @tablename
121           end
122   
123           /*
124           ** Only sa or dbo can drop a definition for another user's table.
125           */
126           if @uid != user_id()
127           BEGIN
128               /*
129               ** If granular permissions is not enabled,
130               ** the user must be dbo or have sa role. If granular permissions
131               ** is enabled then check if the user has 'manage database'
132               ** permission.
133               ** Both proc_role and proc_auditperm will
134               ** do auditing if required and print error message if required.
135               ** User table must be in current database so passing
136               ** NULL as the database name.
137               */
138               select @nullarg = NULL
139               execute @retcode = sp_aux_checkroleperm
140                   "dbo",
141                   "drop any table",
142                   @db_name,
143                   @gp_enabled output
144   
145               if (@gp_enabled = 0)
146               begin
147                   if (@retcode > 0)
148                   begin
149                       execute @retcode = sp_aux_checkroleperm
150                           "sa_role",
151                           @nullarg,
152                           @nullarg,
153                           @gp_enabled output
154                       if (proc_role("sa_role") = 0)
155                       begin
156                           return 1
157                       end
158                   end
159               end
160               else
161               begin
162                   select @dummy = proc_auditperm("drop any table",
163                           @retcode, @db_name)
164               end
165               if (@retcode != 0)
166                   return (1)
167           END
168   
169           /*
170           ** Strip out quotes from table name if quoted identifier is
171           ** on
172           */
173           if @tabname like '"%"'
174           begin
175               select @tabname = substring(@tabname, 2, char_length(@tabname) - 2)
176           end
177   
178   
179           /*
180           ** see if the table has already been defined.
181           */
182           select @dso_class = 9, @attrib = 1
183   
184           if not exists (select * from sysattributes
185                   where class = @dso_class and attribute = @attrib and
186                       object_cinfo = @tabname and object_info1 = @uid)
187           begin
188               /*
189               **  18310, "Table has not been defined."
190               */
191               raiserror 18310
192               return (1)
193           end
194   
195           /*
196           ** see if the table has already been created.
197           */
198           if exists (select * from sysobjects where name = @tabname
199                       and uid = @uid and type = 'U')
200           begin
201               /*
202               ** 18311, "You cannot remove .... Drop the table first."
203               */
204               raiserror 18311
205               return (1)
206           end
207   
208           /*
209           ** Ok; delete the row from sysattributes.
210           */
211           delete sysattributes
212           where class = @dso_class and
213               attribute = @attrib and
214               object_cinfo = @tabname and
215               object_info1 = @uid
216   
217           return (0)
218       end
219   
220   


exec sp_procxmode 'sp_dropobjectdef', 'AnyMode'
go

Grant Execute on sp_dropobjectdef to public
go
DEFECTS
 MINU 4 Unique Index with nullable columns sybsystemprocs..sysattributes sybsystemprocs..sysattributes
 MTYP 4 Assignment type mismatch @tabname: varchar(255) = varchar(1023) 120
 MGTP 3 Grant to public sybsystemprocs..sp_dropobjectdef  
 MGTP 3 Grant to public sybsystemprocs..sysattributes  
 MGTP 3 Grant to public sybsystemprocs..sysobjects  
 MNER 3 No Error Check should check return value of exec 149
 MNER 3 No Error Check should check @@error after delete 211
 MUCO 3 Useless Code Useless Begin-End Pair 18
 MUCO 3 Useless Code Useless Brackets 46
 MUCO 3 Useless Code Useless Brackets 64
 MUCO 3 Useless Code Useless Brackets 85
 MUCO 3 Useless Code Useless Brackets 110
 MUCO 3 Useless Code Useless Brackets 145
 MUCO 3 Useless Code Useless Brackets 147
 MUCO 3 Useless Code Useless Brackets 154
 MUCO 3 Useless Code Useless Brackets 165
 MUCO 3 Useless Code Useless Brackets 166
 MUCO 3 Useless Code Useless Brackets 192
 MUCO 3 Useless Code Useless Brackets 205
 MUCO 3 Useless Code Useless Brackets 217
 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}
185
 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}
212
 VNRD 3 Variable is not read @gp_enabled 153
 VNRD 3 Variable is not read @dummy 162
 MSUB 2 Subquery Marker 184
 MSUB 2 Subquery Marker 198
 MTR1 2 Metrics: Comments Ratio Comments: 38% 16
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 15 = 22dec - 9exi + 2 16
 MTR3 2 Metrics: Query Complexity Complexity: 88 16

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

CALLERS
called by proc sybsystemprocs..sp_droptabledef