DatabaseProcApplicationCreatedLinks
sybsystemprocssp_securityprofile  14 déc. 14Defects Propagation Dependencies

1     
2     /*
3     ** Messages for "sp_securityprofile"
4     ** 18435, "A default login profile does not exist."
5     ** 18999, "An error occurred while fetching data from a temporary table. If there are no other error messages and this error persists, please contact Sybase Technical Support."
6     ** 17031, "Login profile '%1!' does not exist."
7     */
8     
9     create or replace procedure sp_securityprofile
10        @cmd varchar(10) = NULL,
11        @type char(13) = NULL,
12        @name varchar(30) = NULL,
13        @bindobjtype char(5) = NULL,
14        @bindobjname varchar(30) = NULL
15    as
16        declare @lrname varchar(30)
17            , @lrstatus int
18            , @authmech varchar(15)
19            , @lrdefdb varchar(30)
20            , @lrdeflang varchar(30)
21            , @lrid int
22            , @lrrname varchar(30)
23            , @lrrid int
24            , @objname varchar(30)
25            , @deflrname varchar(30)
26            , @currlrid int
27            , @reqlrid int
28            , @reqstatus int
29            , @def_lp_status int
30            , @dopermcheck bit
31            , @dummy int
32            , @nullarg char(1)
33            , @status int
34            , @gp_enabled int
35    
36    
37    
38        if (@cmd = 'help')
39        begin
40            goto usage
41        end
42        else if (@type = null)
43        begin
44            /* First 2 arguments are mandatory */
45            goto usage
46        end
47    
48        /* Initialize */
49        select @dopermcheck = 0
50        select @currlrid = - 1
51        select @def_lp_status = 1024
52    
53        /* Check if permission checks are needed. */
54        if (@cmd = "bindings")
55        begin
56            select @dopermcheck = 1
57        end
58        else --{
59        begin
60            /* Obtain the login profile id of the current login's login profile. */
61            select @currlrid = lpid from master.dbo.syslogins
62            where name = suser_name()
63            /* If there is no profile directly associated, obtain the default. */
64            if (@currlrid = NULL)
65            begin
66                select @currlrid = object from master.dbo.sysattributes
67                where class = 39 and attribute = 4 and
68                    object_type = "LR"
69    
70                if (@name = 'default' and @currlrid = NULL)
71                begin
72                    /* 18435, "A default login profile does not exist." */
73                    raiserror 18435
74                    return (1)
75                end
76            end
77    
78            /*
79            ** There is no login profile associated either directly or by default
80            ** with the current user.
81            */
82            if ((@currlrid = - 1) or (@currlrid = NULL))
83            begin
84                select @dopermcheck = 1
85            end
86            else
87            begin
88                /* Check if requested login profile exists. */
89                if ((@name != 'default') and
90                        (not exists (select 1 from master.dbo.syslogins
91                                where name like @name)))
92                begin
93                    /* 17031, "Login profile '%1!' does not exist." */
94                    raiserror 17031, @name
95                    return (1)
96                end
97    
98                if (@name = 'default')
99                begin
100                   /* Retreive the id of the default login profile. */
101                   select @reqlrid = object from master.dbo.sysattributes
102                   where class = 39 and attribute = 4 and
103                       object_type = "LR"
104               end
105               else
106               begin
107                   /* Retreive the id of the specified login profile. */
108                   select @reqlrid = suid, @reqstatus = status
109                   from master.dbo.syslogins
110                   where name = @name
111               end
112   
113               /*
114               ** Permission checks needs to be done when the requested
115               ** login profile (@name) is a wild card
116               ** or the current login is not associated with it. 
117               ** No permission check required for default login profile.
118               */
119               if ((@name != 'default')
120                       and ((@reqstatus & @def_lp_status) != @def_lp_status)
121                       and (@reqlrid != @currlrid))
122               begin
123                   select @dopermcheck = 1
124               end
125           end
126       end --}
127   
128       /* 
129       ** If granular permissions is not enabled then sso_role may be required.
130       ** If granular permissions is enabled then the permission 'manage any 
131       ** login profile' may be required.  proc_role and proc_auditperm will also do 
132       ** auditing if required. Both will also print error message if required.
133       */
134   
135       select @nullarg = NULL
136       execute @status = sp_aux_checkroleperm "sso_role", "manage any login profile",
137           @nullarg, @gp_enabled output
138   
139       /*
140       ** Display an error and return 1 if @dopermcheck is set to 1 and we don't
141       ** have the role or permission.
142       */
143       if (@dopermcheck = 1 and @status != 0)
144       begin
145           if (@gp_enabled = 0)
146           begin
147               select @dummy = proc_role("sso_role")
148           end
149           else
150           begin
151               select @dummy = proc_auditperm("manage any login profile",
152                       @status)
153           end
154           return 1
155       end
156   
157       /* For  Auditing. */
158       if (@status = 0)
159       begin
160           if (@gp_enabled = 0)
161           begin
162               select @dummy = proc_role("sso_role")
163           end
164           else
165           begin
166               select @dummy = proc_auditperm("manage any login profile",
167                       @status)
168           end
169       end
170   
171       if (@cmd = "attributes")
172       begin --{
173           /* Temporary table for displaying the attributes and their values. */
174           create table #lrattrib(name varchar(30), value varchar(92))
175           /* Check inputs. */
176           if ((@type <> "login profile") or (@name = NULL))
177           begin
178               goto usage
179           end
180           /* 
181           ** Check if the attributes of a default login profile need to be
182           ** displayed.
183           */
184           if (@name = "default")
185           begin --{
186               /* Retreive the id of the default login profile. */
187               select @lrid = object from master.dbo.sysattributes
188               where class = 39 and attribute = 4 and
189                   object_type = "LR"
190               if (@lrid = NULL)
191               begin
192                   /*
193                   ** 18435, "A default login profile does not 
194                   ** exist."
195                   */
196                   raiserror 18435
197                   return (1)
198               end
199               select @lrname = name, @lrstatus = status, @lrdefdb = dbname,
200                   @lrdeflang = language
201               from master.dbo.syslogins
202               where suid = @lrid
203               /*
204               ** Retreive the rest of the login profile attributes and store
205               ** them in #lrattrib
206               */
207               exec sp_aux_securityprofile_getattr @lrid, @lrname, @lrstatus,
208                   @lrdefdb, @lrdeflang
209               /* Display the attributes. */
210               exec sp_autoformat @fulltabname = #lrattrib,
211                   @selectlist = "'Name' = name, 'Value' = value"
212           end --}
213           else
214           begin --{
215               if not exists (select 1 from master.dbo.syslogins
216                       where name like @name)
217               begin
218                   /* 17031, "Login profile '%1!' does not exist." */
219                   raiserror 17031, @name
220                   return (1)
221               end
222               declare lrcur cursor
223               for select suid, name, status, dbname, language
224               from master.dbo.syslogins
225               where ((status & 512) = 512) and name like @name
226               for read only
227               open lrcur
228               fetch lrcur into @lrid, @lrname, @lrstatus, @lrdefdb, @lrdeflang
229               while (@@sqlstatus <> 2)
230               begin --{
231                   if (@@sqlstatus = 1)
232                   begin --{
233                       /*
234                       ** 18999, "An error occurred while fetching data from a temporary
235                       ** table. If there are no other error messages and this error
236                       ** persists, please contact Sybase Technical Support."
237                       */
238                       raiserror 18999
239                       close lrcur
240                       deallocate cursor lrcur
241                       return (1)
242                   end --}
243                   /*
244                   ** Retreive the rest of the login profile attributes and
245                   ** store them in #lrattrib
246                   */
247                   exec sp_aux_securityprofile_getattr @lrid, @lrname,
248                       @lrstatus, @lrdefdb, @lrdeflang
249                   /* Display the attributes. */
250                   exec sp_autoformat @fulltabname = #lrattrib,
251                       @selectlist = "'Name' = name, 'Value' = value"
252                   truncate table #lrattrib
253                   fetch lrcur into @lrid, @lrname, @lrstatus, @lrdefdb,
254                       @lrdeflang
255               end --}
256   
257               close lrcur
258               deallocate cursor lrcur
259           end --}
260           truncate table #lrattrib
261           return (0)
262       end --}
263       else if (@cmd = "bindings")
264       begin --{
265           /* Check inputs. */
266           if (((@bindobjtype != NULL) and (@bindobjtype != "login"))
267                   or (@type <> "login profile")
268                   or ((@bindobjtype = "login") and (@bindobjname = NULL)))
269           begin
270               goto usage
271           end
272           create table #lrbindings(lgname varchar(30), lrname varchar(30))
273           /* The login name is optional. */
274           if (@bindobjname = NULL)
275           begin
276               select @objname = "%"
277           end
278           else
279           begin
280               select @objname = @bindobjname
281           end
282           /* Retreive the id of the default login profile. */
283           select @lrid = object from master.dbo.sysattributes
284           where class = 39 and attribute = 4 and
285               object_type = "LR"
286           /* 
287           ** A default login profile must be defined if "default" clause is
288           ** used. 
289           */
290           if ((@lrid = NULL) and (@name = "default"))
291           begin
292               /*
293               ** 18435, "A default login profile does not exist."
294               */
295               raiserror 18435
296               return (1)
297           end
298           if (@name = "default")
299           begin --{
300               insert into #lrbindings(lgname, lrname)
301               select lg.name, lr.name
302               from master.dbo.syslogins lg, master.dbo.syslogins lr
303               where lg.name like @objname and lg.lpid = NULL and
304                   lr.suid = @lrid and
305                   /* Insert only logins in "lgname" */
306                   ((lg.status & 512) = 0) and
307                   (lg.name != "probe")
308           end --}
309           else
310           begin --{
311               /* The login profile name is optional. */
312               if (@name = NULL)
313               begin
314                   select @lrname = "%"
315               end
316               else
317               begin
318                   select @lrname = @name
319               end
320               /* 
321               ** Store the login accounts that are directly associated with 
322               ** a login profile.
323               */
324               insert into #lrbindings(lgname, lrname)
325               select lg.name, lr.name
326               from master.dbo.syslogins lg, master.dbo.syslogins lr
327               where lg.name like @objname and lr.name like @lrname and
328                   lg.lpid = lr.suid and
329                   /* Insert only logins in "lgname" */
330                   ((lg.status & 512) = 0) and
331                   (lg.name != "probe")
332   
333               /* Retreive the name of the default login profile. */
334               select @deflrname = name from master.dbo.syslogins where suid = @lrid
335               /*
336               ** If the specified login profile name is a default login
337               ** profile, store the login bindings.
338               */
339               insert into #lrbindings(lgname, lrname)
340               select lg.name, lr.name
341               from master.dbo.syslogins lg, master.dbo.syslogins lr
342               where lg.name like @objname and lg.lpid = NULL and
343                   lr.suid = @lrid and @deflrname like @lrname and
344                   /* Insert only logins in "lgname" */
345                   ((lg.status & 512) = 0) and
346                   (lg.name != "probe")
347           end --}
348           exec sp_autoformat @fulltabname = #lrbindings,
349               @selectlist = "'Login name' = lgname, 'Login profile name' = lrname",
350               @orderby = "ORDER BY lgname"
351           return (0)
352       end --}
353       else
354       begin
355   usage:
356           print "sp_securityprofile Usage: sp_securityprofile 'help'"
357           print "sp_securityprofile Usage: sp_securityprofile command, object"
358           print "                          	[, option1 [, option2]]"
359           print "sp_securityprofile 'attributes', 'login profile'"
360           print "		,{wildcard | login_profile_name | 'default'}"
361           print "sp_securityprofile 'bindings', 'login profile'"
362           print "		[, {wildcard | login_profile_name | 'default'}"
363           print "		[, 'login',{wildcard | login name}]]"
364           if (@cmd = 'help'
365                   and @type is null
366                   and @name is null
367                   and @bindobjtype is null
368                   and @bindobjname is null)
369           begin
370               return (0)
371           end
372           else
373           begin
374               return (1)
375           end
376       end
377   


exec sp_procxmode 'sp_securityprofile', 'AnyMode'
go

Grant Execute on sp_securityprofile to public
go
DEFECTS
 MINU 4 Unique Index with nullable columns master..sysattributes master..sysattributes
 MUSP 4 Unquoted String Parameter sybsystemprocs..sp_autoformat: @fulltabname 210
 MUSP 4 Unquoted String Parameter sybsystemprocs..sp_autoformat: @fulltabname 250
 MUSP 4 Unquoted String Parameter sybsystemprocs..sp_autoformat: @fulltabname 348
 QCAS 4 Cartesian product with single row between tables master..syslogins lg and [master..syslogins lr], 5 tables with rc=1 301
 QCAS 4 Cartesian product with single row between tables master..syslogins lg and [master..syslogins lr], 5 tables with rc=1 340
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 67
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 102
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 188
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 284
 MGTP 3 Grant to public master..sysattributes  
 MGTP 3 Grant to public master..syslogins  
 MGTP 3 Grant to public sybsystemprocs..sp_securityprofile  
 MNAC 3 Not using ANSI 'is null' 42
 MNAC 3 Not using ANSI 'is null' 64
 MNAC 3 Not using ANSI 'is null' 70
 MNAC 3 Not using ANSI 'is null' 82
 MNAC 3 Not using ANSI 'is null' 176
 MNAC 3 Not using ANSI 'is null' 190
 MNAC 3 Not using ANSI 'is null' 266
 MNAC 3 Not using ANSI 'is null' 268
 MNAC 3 Not using ANSI 'is null' 274
 MNAC 3 Not using ANSI 'is null' 290
 MNAC 3 Not using ANSI 'is null' 303
 MNAC 3 Not using ANSI 'is null' 312
 MNAC 3 Not using ANSI 'is null' 342
 MNER 3 No Error Check should check return value of exec 207
 MNER 3 No Error Check should check return value of exec 210
 MNER 3 No Error Check should check return value of exec 247
 MNER 3 No Error Check should check return value of exec 250
 MNER 3 No Error Check should check @@error after truncate 252
 MNER 3 No Error Check should check @@error after truncate 260
 MNER 3 No Error Check should check @@error after insert 300
 MNER 3 No Error Check should check @@error after insert 324
 MNER 3 No Error Check should check @@error after insert 339
 MNER 3 No Error Check should check return value of exec 348
 MUCO 3 Useless Code Useless Brackets 38
 MUCO 3 Useless Code Useless Brackets 42
 MUCO 3 Useless Code Useless Brackets 54
 MUCO 3 Useless Code Useless Brackets 64
 MUCO 3 Useless Code Useless Brackets 70
 MUCO 3 Useless Code Useless Brackets 74
 MUCO 3 Useless Code Useless Brackets 82
 MUCO 3 Useless Code Useless Brackets 89
 MUCO 3 Useless Code Useless Brackets 95
 MUCO 3 Useless Code Useless Brackets 98
 MUCO 3 Useless Code Useless Brackets 119
 MUCO 3 Useless Code Useless Brackets 143
 MUCO 3 Useless Code Useless Brackets 145
 MUCO 3 Useless Code Useless Brackets 158
 MUCO 3 Useless Code Useless Brackets 160
 MUCO 3 Useless Code Useless Brackets 171
 MUCO 3 Useless Code Useless Brackets 176
 MUCO 3 Useless Code Useless Brackets 184
 MUCO 3 Useless Code Useless Brackets 190
 MUCO 3 Useless Code Useless Brackets 197
 MUCO 3 Useless Code Useless Brackets 220
 MUCO 3 Useless Code Useless Brackets 229
 MUCO 3 Useless Code Useless Brackets 231
 MUCO 3 Useless Code Useless Brackets 241
 MUCO 3 Useless Code Useless Brackets 261
 MUCO 3 Useless Code Useless Brackets 263
 MUCO 3 Useless Code Useless Brackets 266
 MUCO 3 Useless Code Useless Brackets 274
 MUCO 3 Useless Code Useless Brackets 290
 MUCO 3 Useless Code Useless Brackets 296
 MUCO 3 Useless Code Useless Brackets 298
 MUCO 3 Useless Code Useless Brackets 312
 MUCO 3 Useless Code Useless Brackets 351
 MUCO 3 Useless Code Useless Brackets 364
 MUCO 3 Useless Code Useless Brackets 370
 MUCO 3 Useless Code Useless Brackets 374
 MUIN 3 Column created using implicit nullability 174
 MUIN 3 Column created using implicit nullability 272
 QAFM 3 Var Assignment from potentially many rows 66
 QAFM 3 Var Assignment from potentially many rows 101
 QAFM 3 Var Assignment from potentially many rows 187
 QAFM 3 Var Assignment from potentially many rows 283
 QCTC 3 Conditional Table Creation 174
 QCTC 3 Conditional Table Creation 272
 QNAJ 3 Not using ANSI Inner Join 302
 QNAJ 3 Not using ANSI Inner Join 326
 QNAJ 3 Not using ANSI Inner Join 341
 QPNC 3 No column in condition 343
 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: {class, object_type, attribute}
67
 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: {class, object_type, attribute}
102
 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: {class, object_type, attribute}
188
 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: {class, object_type, attribute}
284
 VNRD 3 Variable is not read @dummy 166
 VUNU 3 Variable is not used @authmech 18
 VUNU 3 Variable is not used @lrrname 22
 VUNU 3 Variable is not used @lrrid 23
 CRDO 2 Read Only Cursor Marker (has for read only clause) 223
 MSUB 2 Subquery Marker 90
 MSUB 2 Subquery Marker 215
 MTR1 2 Metrics: Comments Ratio Comments: 29% 9
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 43 = 53dec - 12exi + 2 9
 MTR3 2 Metrics: Query Complexity Complexity: 206 9
 PRED_QUERY_COLLECTION 2 {l=master..syslogins, l2=master..syslogins} 0 325

DEPENDENCIES
PROCS AND TABLES USED
calls proc sybsystemprocs..sp_aux_securityprofile_getattr  
   reads table master..sysloginroles (1)  
   writes table tempdb..#lrattrib (1) 
   reads table master..sysattributes (1)  
reads table master..syslogins (1)  
writes table tempdb..#lrattrib (1) 
calls proc sybsystemprocs..sp_aux_checkroleperm  
   reads table master..syscurconfigs (1)  
   reads table master..sysconfigures (1)  
writes table tempdb..#lrbindings (1) 
reads table master..sysattributes (1)  
calls proc sybsystemprocs..sp_autoformat  
   writes table sybsystemprocs..sp_autoformat_rset_002 
   reads table master..syscolumns (1)  
   writes table sybsystemprocs..sp_autoformat_rset_004 
   calls proc sybsystemprocs..sp_autoformat  
   read_writes table tempdb..#colinfo_af (1) 
   reads table tempdb..systypes (1)  
   writes table sybsystemprocs..sp_autoformat_rset_001 
   reads table tempdb..syscolumns (1)  
   writes table sybsystemprocs..sp_autoformat_rset_003 
   calls proc sybsystemprocs..sp_namecrack  
   writes table sybsystemprocs..sp_autoformat_rset_005 
   reads table master..systypes (1)