DatabaseProcApplicationCreatedLinks
sybsystemprocssp_restore_system_role  14 déc. 14Defects Propagation Dependencies

1     create or replace procedure sp_restore_system_role
2         @rolename varchar(255) = NULL,
3         @alldbs char(7) = NULL
4     as
5         begin -- {
6             declare @nullarg char(1)
7             declare @dummy int
8             declare @status int
9             declare @gp_enabled int /* Is granular permissions enabled? */
10            declare @sqlcmd varchar(768) /* Stores delete command */
11            declare @rvkcmd varchar(768) /* Stores delete for revoke */
12            declare @updcmd varchar(768) /* Stores delete for revoke */
13            declare @dbid int /* Database id to do deletion */
14            declare @dbname varchar(255) /* Database name */
15            declare @baddbstat int
16            declare @baddbstat2 int
17            declare @baddbstat3 int
18            declare @is_remote_tdb int
19            declare @msg varchar(128)
20    
21    
22            select @nullarg = NULL
23            /*
24            ** Do not allow this system procedure to be run from within a transaction
25            ** to avoid creating a multi-database transaction where the 'master'
26            ** database is not the co-ordinating database.
27            */
28            if @@trancount > 0
29            begin
30                /*
31                ** 17260, "Can't run %1! from within a transaction."
32                */
33                raiserror 17260, "sp_restore_system_role"
34                return (1)
35            end
36            else
37            begin
38                set chained off
39            end
40    
41            /* 
42            ** Exclusive lock on objects being changed, held until transaction ends.
43            ** No shared locks
44            */
45            set transaction isolation level 1
46    
47            /* 
48            ** Print Usage string if help is requested or the @alldbs is not
49            ** recognizable.
50            */
51            if (@rolename = null or @rolename = 'help' or
52                    (@alldbs != null and
53                        (lower(ltrim(rtrim(@alldbs))) != "all_dbs")))
54            begin
55                /* 17034, "Usage: sp_restore_system_role 'role_name' [,'all_dbs']" */
56                exec sp_getmessage 17034, @msg output
57                print @msg
58                return (0)
59            end
60    
61            if lower(ltrim(rtrim(@rolename))) not in ("sa_role", "sso_role",
62                    "oper_role", "replication_role", "keycustodian_role",
63                    "sa_serverprivs_role", "dbo")
64            begin
65                /* Illegal role name '%1!' specified." */
66                raiserror 17678, @rolename
67                return 1
68            end
69            if (lower(ltrim(rtrim(@rolename))) = "sa_role")
70            begin
71                /* 
72                ** If granular permissions is enabled and the role is 'sa_role'
73                ** then the permission 'manage server permissions' is required.
74                ** proc_auditperm will also do auditing if required. It will 
75                ** also print error message if required.  
76                */
77    
78                execute @status = sp_aux_checkroleperm "sa_role",
79                    "manage server permissions", @nullarg,
80                    @gp_enabled output
81    
82                /* For Auditing */
83                if (@gp_enabled = 0)
84                begin
85                    select @dummy = proc_role("sa_role")
86                end
87                else
88                begin
89                    select @dummy =
90                        proc_auditperm("manage server permissions",
91                            @status)
92                end
93    
94                if (@status != 0)
95                begin
96                    return (1)
97                end
98            end
99            else
100           begin
101               /* 
102               ** If granular permissions is enabled then the permission 
103               ** 'manage security permissions' is required.  proc_auditperm 
104               ** will also do auditing if required. It will also print error 
105               ** message if required.
106               */
107   
108               execute @status = sp_aux_checkroleperm "sso_role",
109                   "manage security permissions", @nullarg,
110                   @gp_enabled output
111   
112               /* For Auditing */
113               if (@gp_enabled = 0)
114               begin
115                   select @dummy = proc_role("sso_role")
116               end
117               else
118               begin
119                   select @dummy =
120                       proc_auditperm("manage security permissions",
121                           @status)
122               end
123   
124               if (@status != 0)
125               begin
126                   return (1)
127               end
128           end
129           if (lower(ltrim(rtrim(@alldbs))) like "all%")
130           begin
131               /* 
132               ** If doing all dbs then 'use any database' permission required.
133               */
134               execute @status = sp_aux_checkroleperm @nullarg,
135                   "use any database", @nullarg, @gp_enabled output
136               select @dummy = proc_auditperm("use any database", @status)
137   
138               if (@status != 0)
139               begin
140                   return (1)
141               end
142           end
143   
144           /*
145           ** Restore roles  only in those databases that are writable
146           **
147           ** sysdatabases.status not in 0x20 (in load), 0x40 (not rec),
148           ** 0x80 (bypass), 0x100 (suspect), 0x400 (rdonly), 0x8000 (emergency)
149           **
150           ** sysdatabases.status2 not in 0x10 (offline), 0x400 (online for
151           ** standby access)
152           **
153           ** sysdatabases.status3 not in 0x1 (user proxy), 0x2 (ha proxy),
154           ** 0x8 (shutdown), 0x10 (failedover), 0x80 (quiesced), 0x400000
155           ** (archive)
156           */
157           select @baddbstat = 34272, @baddbstat2 = 1040,
158               @baddbstat3 = 4194459
159   
160           select dbid
161           into #sysdatabases
162           from master.dbo.sysdatabases
163           where status & @baddbstat = 0
164               and status2 & @baddbstat2 = 0
165               and status3 & @baddbstat3 = 0
166           order by dbid
167   
168           /*
169           ** If the current database is marked for replication, the T-SQL
170           ** built-in 'logexec()' will log for replication the execution
171           ** instance of this system procedure.  Otherwise, the T-SQL
172           ** built-in 'logexec()' is a no-op.
173           */
174           select @dbid = db_id()
175           if (logexec(@dbid) != 1)
176           begin
177               /*
178               ** 17756, "The execution of the stored procedure '%1!'
179               **	 in database '%2!' was aborted because there
180               **	 was an error in writing the replication log
181               **	 record."
182               */
183               select @dbname = db_name(@dbid)
184               raiserror 17756, "sp_restore_system_role", @dbname
185               return (1)
186           end
187   
188           /* 
189           ** Remove granted roles from roles. 
190           ** You can't grant a role to user 'dbo'. 
191           */
192           if (lower(ltrim(rtrim(@rolename))) != "dbo")
193           begin -- {
194               /* 
195               ** Build command to
196               ** revoke those roles that have been assigned to the role. 
197               ** class = 8 is user defined role class.  System roles fall
198               ** under this class when granted to other roles.
199               ** attribute = 2 is the value for roles corresponding to 
200               ** role hiearchy.
201               */
202               select @rvkcmd = "delete "
203                   + "master.dbo.sysattributes "
204                   + " where class = 8"
205                   + " and attribute = 2"
206                   + " and object = "
207                   + convert(varchar(4), role_id(@rolename))
208   
209               exec (@rvkcmd)
210           end --}  != dbo 
211   
212           if (lower(ltrim(rtrim(@rolename))) in
213                   ("sa_role", "sso_role", "replication_role",
214                       "keycustodian_role", "oper_role",
215                       "sa_serverprivs_role", "dbo"))
216           begin -- {
217               /* Restoring '%1!' */
218               exec sp_getmessage 17033, @msg output
219               print @msg, @rolename
220   
221               if (lower(ltrim(rtrim(@alldbs))) like "all%")
222               begin -- {
223                   /* Cursor for traversing databases */
224                   declare sysdbc1 cursor for
225                   select dbid from #sysdatabases
226                   /* Scan sysprocedures in each database */
227                   open sysdbc1
228                   fetch sysdbc1 into @dbid
229   
230                   /* 
231                   ** Delete all permissions for role in each database.
232                   ** Grant the out-of-the-box permissions in each 
233                   ** database for the role.
234                   */
235                   while (@@sqlstatus = 0)
236                   begin -- {
237                       /*
238                       ** In SDC, if dbname is not specified, 
239                       ** skip local tempdbs that are only 
240                       ** accessible from remote instances.
241                       */
242                       if (@@clustermode = "shared disk cluster")
243                       begin
244                           exec @is_remote_tdb =
245                           sp_check_remote_tempdb @dbid,
246                               'skip'
247                           if (@is_remote_tdb = 1)
248                           begin
249                               fetch sysdbc1 into @dbid
250                               continue
251                           end
252                       end
253                       select @dbname = db_name(@dbid)
254                       /* We won't update sybsecurity */
255                       if (@dbname = "sybsecurity")
256                       begin
257                           fetch sysdbc1 into @dbid
258                           continue
259                       end
260                       /* 
261                       ** Command for deleting permissions for role 
262                       ** from sysprotects 
263                       */
264                       if (lower(ltrim(rtrim(@rolename))) = "sso_role")
265                       begin
266                           /*
267                           ** If the rolename is "sso_role" don't delete
268                           ** Grants for select sysobjects(id = 1)
269                           ** select sysdatabases (id = 30), 
270                           ** select sysencryptkeys (id = 98), 
271                           ** select syslisteners (id = 47),
272                           ** select sysloginrolesIid = 49), 
273                           ** select syslogins(id = 33),
274                           ** select syssrvroles(id = 48)
275                           ** because dbcc upgd_grantrev_sysrole_perms will
276                           ** not restore them.
277                           ** Don't delete the permission MANAGE SECURITY
278                           ** PERMISSIONS (114) for sso_role.
279                           */
280                           select @sqlcmd = "delete "
281                               + @dbname
282                               + ".dbo.sysprotects "
283                               + "where "
284                               + "(action != 114) and "
285                               + "(action != 193 or id "
286                               + "not in (1,30,33,47,48,"
287                               + "49,98)) "
288                               + " and  uid =(select uid from "
289                               + @dbname
290                               + ".dbo.sysusers where name = '"
291                               + @rolename + "')"
292                       end
293                       else
294                       begin
295                           select @sqlcmd = "delete "
296                               + @dbname
297                               + ".dbo.sysprotects "
298                               + "where "
299                               + "uid =(select uid from "
300                               + @dbname
301                               + ".dbo.sysusers where name = '"
302                               + @rolename + "')"
303                       end
304   
305                       select @updcmd = "update "
306                           + @dbname
307                           + ".dbo.sysprotects "
308                           + "set grantor = 1 "
309                           + "where uid =  (select uid from "
310                           + @dbname
311                           + ".dbo.sysusers where name = '"
312                           + @rolename + "')"
313                       exec (@sqlcmd)
314                       dbcc upgd_grantrev_sysrole_perms('grant',
315                           @dbname, @rolename)
316                       exec (@updcmd)
317                       fetch sysdbc1 into @dbid
318                   end -- }
319                   close sysdbc1
320               end -- }
321               else
322               begin -- {
323                   select @dbname = db_name()
324                   /*
325                   ** In SDC, if dbname is not specified, 
326                   ** skip local tempdbs that are only 
327                   ** accessible from remote instances.
328                   */
329                   if (@@clustermode = "shared disk cluster")
330                   begin
331                       exec @is_remote_tdb =
332                       sp_check_remote_tempdb @dbid,
333                           'skip'
334                       if (@is_remote_tdb = 1)
335                       begin
336                           goto finish
337                       end
338                   end
339                   /* 
340                   ** Command for deleting permissions for role 
341                   ** from sysprotects 
342                   */
343   
344                   if (lower(ltrim(rtrim(@rolename))) = "sso_role")
345                   begin
346                       /*
347                       ** If the rolename is "sso_role" don't delete
348                       ** Grants for select sysobjects(id = 1)
349                       ** select sysdatabases (id = 30), 
350                       ** select sysencryptkeys (id = 98), 
351                       ** select syslisteners (id = 47),
352                       ** select sysloginrolesIid = 49), 
353                       ** select syslogins(id = 33),
354                       ** select syssrvroles(id = 48)
355                       ** because dbcc upgd_grantrev_sysrole_perms will
356                       ** not restore them.
357                       ** Don't delete the permission MANAGE SECURITY
358                       ** PERMISSIONS (114) for sso_role.
359                       ** 
360                       */
361                       select @sqlcmd = "delete "
362                           + @dbname
363                           + ".dbo.sysprotects "
364                           + "where "
365                           + "(action != 114) and "
366                           + "(action != 193 or id "
367                           + "not in (1,30,33,47,48,49,98)) "
368                           + "and  uid =(select uid from "
369                           + @dbname
370                           + ".dbo.sysusers where name = '"
371                           + @rolename + "')"
372                   end
373                   else
374                   begin
375                       select @sqlcmd = "delete "
376                           + @dbname
377                           + ".dbo.sysprotects "
378                           + "where "
379                           + "uid =(select uid from "
380                           + @dbname
381                           + ".dbo.sysusers where name = '"
382                           + @rolename + "')"
383                   end
384   
385                   select @updcmd = "update "
386                       + @dbname
387                       + ".dbo.sysprotects "
388                       + "set grantor = 1 "
389                       + "where uid =  (select uid from "
390                       + @dbname
391                       + ".dbo.sysusers where name = '"
392                       + @rolename + "')"
393                   exec (@sqlcmd)
394                   dbcc upgd_grantrev_sysrole_perms('grant', @dbname,
395                       @rolename)
396                   exec (@updcmd)
397               end -- }
398           end -- }
399   finish:
400           grant all to null
401           return 0
402       end -- } End of procedure.
403   


exec sp_procxmode 'sp_restore_system_role', 'AnyMode'
go

Grant Execute on sp_restore_system_role to public
go
DEFECTS
 CUNU 3 Cursor not updated: cursor should contain 'for read only' clause sysdbc1 225
 MGTP 3 Grant to public master..sysdatabases  
 MGTP 3 Grant to public sybsystemprocs..sp_restore_system_role  
 MNAC 3 Not using ANSI 'is null' 51
 MNAC 3 Not using ANSI 'is null' 52
 MNER 3 No Error Check should check return value of exec 56
 MNER 3 No Error Check should check return value of exec 134
 MNER 3 No Error Check should check @@error after select into 160
 MNER 3 No Error Check should check return value of exec 218
 MUCO 3 Useless Code Useless Begin-End Pair 5
 MUCO 3 Useless Code Useless Brackets 34
 MUCO 3 Useless Code Useless Brackets 51
 MUCO 3 Useless Code Useless Brackets 58
 MUCO 3 Useless Code Useless Brackets 69
 MUCO 3 Useless Code Useless Brackets 83
 MUCO 3 Useless Code Useless Brackets 94
 MUCO 3 Useless Code Useless Brackets 96
 MUCO 3 Useless Code Useless Brackets 113
 MUCO 3 Useless Code Useless Brackets 124
 MUCO 3 Useless Code Useless Brackets 126
 MUCO 3 Useless Code Useless Brackets 129
 MUCO 3 Useless Code Useless Brackets 138
 MUCO 3 Useless Code Useless Brackets 140
 MUCO 3 Useless Code Useless Brackets 175
 MUCO 3 Useless Code Useless Brackets 185
 MUCO 3 Useless Code Useless Brackets 192
 MUCO 3 Useless Code Useless Brackets 212
 MUCO 3 Useless Code Useless Brackets 221
 MUCO 3 Useless Code Useless Brackets 235
 MUCO 3 Useless Code Useless Brackets 242
 MUCO 3 Useless Code Useless Brackets 247
 MUCO 3 Useless Code Useless Brackets 255
 MUCO 3 Useless Code Useless Brackets 264
 MUCO 3 Useless Code Useless Brackets 329
 MUCO 3 Useless Code Useless Brackets 334
 MUCO 3 Useless Code Useless Brackets 344
 QISO 3 Set isolation level 45
 VNRD 3 Variable is not read @gp_enabled 135
 VNRD 3 Variable is not read @dummy 136
 CUPD 2 Updatable Cursor Marker (updatable by default) 225
 MDYS 2 Dynamic SQL Marker 209
 MDYS 2 Dynamic SQL Marker 313
 MDYS 2 Dynamic SQL Marker 316
 MDYS 2 Dynamic SQL Marker 393
 MDYS 2 Dynamic SQL Marker 396
 MTR1 2 Metrics: Comments Ratio Comments: 40% 1
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 22 = 28dec - 8exi + 2 1
 MTR3 2 Metrics: Query Complexity Complexity: 128 1

DEPENDENCIES
PROCS AND TABLES USED
calls proc sybsystemprocs..sp_check_remote_tempdb  
   calls proc sybsystemprocs..sp_getmessage  
      reads table master..syslanguages (1)  
      reads table master..sysmessages (1)  
      calls proc sybsystemprocs..sp_validlang  
         reads table master..syslanguages (1)  
      reads table sybsystemprocs..sysusermessages  
read_writes table tempdb..#sysdatabases (1) 
calls proc sybsystemprocs..sp_aux_checkroleperm  
   reads table master..sysconfigures (1)  
   reads table master..syscurconfigs (1)  
reads table master..sysdatabases (1)  
calls proc sybsystemprocs..sp_getmessage