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


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 189
 MUSP 4 Unquoted String Parameter sybsystemprocs..sp_autoformat: @fulltabname 229
 MUSP 4 Unquoted String Parameter sybsystemprocs..sp_autoformat: @fulltabname 327
 QCAS 4 Cartesian product with single row between tables master..syslogins lg and [master..syslogins lr], 5 tables with rc=1 280
 QCAS 4 Cartesian product with single row between tables master..syslogins lg and [master..syslogins lr], 5 tables with rc=1 319
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 62
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 97
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 167
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 263
 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' 37
 MNAC 3 Not using ANSI 'is null' 59
 MNAC 3 Not using ANSI 'is null' 65
 MNAC 3 Not using ANSI 'is null' 77
 MNAC 3 Not using ANSI 'is null' 155
 MNAC 3 Not using ANSI 'is null' 169
 MNAC 3 Not using ANSI 'is null' 245
 MNAC 3 Not using ANSI 'is null' 247
 MNAC 3 Not using ANSI 'is null' 253
 MNAC 3 Not using ANSI 'is null' 269
 MNAC 3 Not using ANSI 'is null' 282
 MNAC 3 Not using ANSI 'is null' 291
 MNAC 3 Not using ANSI 'is null' 321
 MNER 3 No Error Check should check return value of exec 186
 MNER 3 No Error Check should check return value of exec 189
 MNER 3 No Error Check should check return value of exec 226
 MNER 3 No Error Check should check return value of exec 229
 MNER 3 No Error Check should check @@error after truncate 231
 MNER 3 No Error Check should check @@error after truncate 239
 MNER 3 No Error Check should check @@error after insert 279
 MNER 3 No Error Check should check @@error after insert 303
 MNER 3 No Error Check should check @@error after insert 318
 MNER 3 No Error Check should check return value of exec 327
 MUCO 3 Useless Code Useless Brackets 33
 MUCO 3 Useless Code Useless Brackets 37
 MUCO 3 Useless Code Useless Brackets 49
 MUCO 3 Useless Code Useless Brackets 59
 MUCO 3 Useless Code Useless Brackets 65
 MUCO 3 Useless Code Useless Brackets 69
 MUCO 3 Useless Code Useless Brackets 77
 MUCO 3 Useless Code Useless Brackets 84
 MUCO 3 Useless Code Useless Brackets 90
 MUCO 3 Useless Code Useless Brackets 93
 MUCO 3 Useless Code Useless Brackets 114
 MUCO 3 Useless Code Useless Brackets 123
 MUCO 3 Useless Code Useless Brackets 132
 MUCO 3 Useless Code Useless Brackets 135
 MUCO 3 Useless Code Useless Brackets 144
 MUCO 3 Useless Code Useless Brackets 150
 MUCO 3 Useless Code Useless Brackets 155
 MUCO 3 Useless Code Useless Brackets 163
 MUCO 3 Useless Code Useless Brackets 169
 MUCO 3 Useless Code Useless Brackets 176
 MUCO 3 Useless Code Useless Brackets 199
 MUCO 3 Useless Code Useless Brackets 208
 MUCO 3 Useless Code Useless Brackets 210
 MUCO 3 Useless Code Useless Brackets 220
 MUCO 3 Useless Code Useless Brackets 240
 MUCO 3 Useless Code Useless Brackets 242
 MUCO 3 Useless Code Useless Brackets 245
 MUCO 3 Useless Code Useless Brackets 253
 MUCO 3 Useless Code Useless Brackets 269
 MUCO 3 Useless Code Useless Brackets 275
 MUCO 3 Useless Code Useless Brackets 277
 MUCO 3 Useless Code Useless Brackets 291
 MUCO 3 Useless Code Useless Brackets 330
 MUCO 3 Useless Code Useless Brackets 343
 MUCO 3 Useless Code Useless Brackets 349
 MUCO 3 Useless Code Useless Brackets 353
 MUIN 3 Column created using implicit nullability 153
 MUIN 3 Column created using implicit nullability 251
 QAFM 3 Var Assignment from potentially many rows 61
 QAFM 3 Var Assignment from potentially many rows 96
 QAFM 3 Var Assignment from potentially many rows 166
 QAFM 3 Var Assignment from potentially many rows 262
 QCTC 3 Conditional Table Creation 153
 QCTC 3 Conditional Table Creation 251
 QNAJ 3 Not using ANSI Inner Join 281
 QNAJ 3 Not using ANSI Inner Join 305
 QNAJ 3 Not using ANSI Inner Join 320
 QPNC 3 No column in condition 322
 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}
62
 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}
97
 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}
167
 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}
263
 VNRD 3 Variable is not read @dummy 146
 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) 202
 MSUB 2 Subquery Marker 85
 MSUB 2 Subquery Marker 194
 MTR1 2 Metrics: Comments Ratio Comments: 31% 9
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 40 = 50dec - 12exi + 2 9
 MTR3 2 Metrics: Query Complexity Complexity: 199 9
 PRED_QUERY_COLLECTION 2 {l=master..syslogins, l2=master..syslogins} 0 304

DEPENDENCIES
PROCS AND TABLES USED
calls proc sybsystemprocs..sp_autoformat  
   reads table master..systypes (1)  
   reads table tempdb..syscolumns (1)  
   read_writes table tempdb..#colinfo_af (1) 
   calls proc sybsystemprocs..sp_autoformat  
   reads table master..syscolumns (1)  
   reads table tempdb..systypes (1)  
   calls proc sybsystemprocs..sp_namecrack  
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..sysattributes (1)  
writes table tempdb..#lrbindings (1) 
writes table tempdb..#lrattrib (1) 
reads table master..syslogins (1)