DatabaseProcApplicationCreatedLinks
sybsystemprocssp_aux_securityprofile_getattr  31 Aug 14Defects Dependencies

1     
2     create procedure sp_aux_securityprofile_getattr
3         @lrid int,
4         @lrname varchar(30),
5         @lrstatus int,
6         @lrdefdb varchar(30),
7         @lrdeflang varchar(30)
8     as
9         declare @lrstaleperiod varchar(6)
10            , @lrlastlogin varchar(5)
11            , @lrlgscript varchar(92)
12            , @max_sdr_srid int /* Max system defined role id */
13    
14        /* Initialize variables */
15        select @max_sdr_srid = 31 /* Value of server's MAX_SDR_ROLES */
16    
17        /* Retrieve the 'track lastlogin' attribute. */
18        select @lrlastlogin =
19            case (select int_value from master.dbo.sysattributes
20                where class = 39 and attribute = 1 and object = @lrid)
21                when 1 then "TRUE"
22                when 0 then "FALSE"
23                else NULL
24            end
25        /* Retrieve the 'stale period' attribute. */
26        select @lrstaleperiod = (select char_value from master.dbo.sysattributes
27                where class = 39 and attribute = 2 and object = @lrid)
28        /* Retrieve the 'login script' attribute. */
29        select @lrlgscript = (select char_value from master.dbo.sysattributes
30                where class = 39 and attribute = 3 and object = @lrid)
31    
32        /* Storing the login profile name. */
33        insert into #lrattrib values ("login profile", @lrname)
34        /* Storing the login profile id. */
35        insert into #lrattrib values ("profile id", convert(varchar(92), @lrid))
36        /* Storing the default status. */
37        if ((@lrstatus & 1024) = 1024)
38        begin
39            insert into #lrattrib values ("default", "yes")
40        end
41        else
42        begin
43            insert into #lrattrib values ("default", "no")
44        end
45        /* Storing the default database. */
46        if (@lrdefdb <> NULL)
47        begin
48            insert into #lrattrib values ("default database",
49                @lrdefdb)
50        end
51        /* Storing the default language. */
52        if (@lrdeflang <> NULL)
53        begin
54            insert into #lrattrib values ("default language",
55                @lrdeflang)
56        end
57        /* 
58        ** Store the list of roles that will be automatically activated
59        ** on login. System defined roles are automatically activated.
60        */
61        insert into #lrattrib(name, value)
62        select "auto activated roles", role_name(srid)
63        from master.dbo.sysloginroles
64        where suid = @lrid and ((status & 1) = 1 or
65                (srid >= 0 and srid <= @max_sdr_srid))
66    
67        /* Store the list of roles that needs to be manually activated */
68        insert into #lrattrib(name, value)
69        select "manually activated roles", role_name(srid)
70        from master.dbo.sysloginroles
71        where suid = @lrid
72            and not (srid >= 0 and srid <= @max_sdr_srid)
73            and status = 0
74        /* 
75        ** Check if an authentication mechanism is specified (2048).
76        ** If so, store the authentication mechanism. 
77        */
78        if ((@lrstatus & 2048) = 2048)
79        begin --{
80            if ((@lrstatus & 32) = 32)
81            begin
82                insert into #lrattrib values ("authenticate with", "ASE")
83            end
84            else if ((@lrstatus & 64) = 64)
85            begin
86                insert into #lrattrib values ("authenticate with", "LDAP")
87            end
88            else if ((@lrstatus & 128) = 128)
89            begin
90                insert into #lrattrib values ("authenticate with", "PAM")
91            end
92            else if ((@lrstatus & 256) = 256)
93            begin
94                insert into #lrattrib values ("authenticate with", "KERBEROS")
95            end
96            else
97            begin
98                insert into #lrattrib values ("authenticate with", "ANY")
99            end
100       end --}
101   
102       /* Store 'track lastlogin' attribute. */
103       if (@lrlastlogin <> NULL)
104       begin
105           insert into #lrattrib values ("track lastlogin", @lrlastlogin)
106       end
107       /* Store 'stale period' attribute. */
108       if (@lrstaleperiod <> NULL)
109       begin
110           insert into #lrattrib values ("stale period", @lrstaleperiod)
111       end
112       /* Store 'login script' attribute. */
113       if (@lrlgscript <> NULL)
114       begin
115           insert into #lrattrib values ("login script", @lrlgscript)
116       end
117
DEFECTS
 MINU 4 Unique Index with nullable columns master..sysattributes master..sysattributes
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 20
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 27
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 30
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 73
 TNOU 4 Table with no unique index master..sysloginroles master..sysloginroles
 MGTP 3 Grant to public master..sysattributes  
 MNAC 3 Not using ANSI 'is null' 46
 MNAC 3 Not using ANSI 'is null' 52
 MNAC 3 Not using ANSI 'is null' 103
 MNAC 3 Not using ANSI 'is null' 108
 MNAC 3 Not using ANSI 'is null' 113
 MNER 3 No Error Check should check @@error after insert 33
 MNER 3 No Error Check should check @@error after insert 35
 MNER 3 No Error Check should check @@error after insert 39
 MNER 3 No Error Check should check @@error after insert 43
 MNER 3 No Error Check should check @@error after insert 48
 MNER 3 No Error Check should check @@error after insert 54
 MNER 3 No Error Check should check @@error after insert 61
 MNER 3 No Error Check should check @@error after insert 68
 MNER 3 No Error Check should check @@error after insert 82
 MNER 3 No Error Check should check @@error after insert 86
 MNER 3 No Error Check should check @@error after insert 90
 MNER 3 No Error Check should check @@error after insert 94
 MNER 3 No Error Check should check @@error after insert 98
 MNER 3 No Error Check should check @@error after insert 105
 MNER 3 No Error Check should check @@error after insert 110
 MNER 3 No Error Check should check @@error after insert 115
 MUCO 3 Useless Code Useless Brackets 37
 MUCO 3 Useless Code Useless Brackets 46
 MUCO 3 Useless Code Useless Brackets 52
 MUCO 3 Useless Code Useless Brackets 78
 MUCO 3 Useless Code Useless Brackets 80
 MUCO 3 Useless Code Useless Brackets 84
 MUCO 3 Useless Code Useless Brackets 88
 MUCO 3 Useless Code Useless Brackets 92
 MUCO 3 Useless Code Useless Brackets 103
 MUCO 3 Useless Code Useless Brackets 108
 MUCO 3 Useless Code Useless Brackets 113
 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, class, attribute}		 20
 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, class, attribute}		 27
 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, class, attribute}		 30
 QSWV 3 Sarg with variable @max_sdr_srid, Candidate Index: sysloginroles.csysloginroles clustered(suid) S 65
 QSWV 3 Sarg with variable @max_sdr_srid, Candidate Index: sysloginroles.csysloginroles clustered(suid) S 72
 MSUB 2 Subquery Marker 19
 MSUB 2 Subquery Marker 26
 MSUB 2 Subquery Marker 29
 MTR1 2 Metrics: Comments Ratio Comments: 20% 2
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 18 = 17dec - 1exi + 2 2
 MTR3 2 Metrics: Query Complexity Complexity: 82 2

DEPENDENCIES
PROCS AND TABLES USED
reads table master..sysattributes (1)  
reads table master..sysloginroles (1)  
writes table tempdb..#lrattrib (1) 

CALLERS
called by proc sybsystemprocs..sp_securityprofile