Database	Proc	Application	Created	Links
sybsystemprocs	sp_securityprofile		31 Aug 14	Defects 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