DatabaseProcApplicationCreatedLinks
sybsystemprocssp_ddlgen_permissions  14 déc. 14Defects Propagation Dependencies

1     
2     /*
3     ** sp_ddlgen_permissions
4     **
5     **	Common sproc to implement permissions checking for all DDL gen commands.
6     **	For now we implement a simple rule that user has to have 'sa_role' to
7     **	generate DDL. In future if/when object-level DDL generation is allowed,
8     **	we have to make this permission checking more flexible by pushing it
9     **	into each @objtype-DDL-generating sproc.
10    **
11    ** Object-type specific permission checks:
12    **
13    **  database:
14    ** 	If @objtype = 'database' (only one currently supported) and 
15    ** 	if granular permissions is not enabled the user must be dbo or 
16    ** 	have sa_role.  
17    ** 	If granular permissions is enabled and the @objname is not a wild
18    ** 	card or a local tempdb on another instance then the user must be dbo 
19    **	or have 'own database' permission.
20    ** 	Otherwise if the database name is a wild card or a local tempdb on
21    **	another instance then the user must have 'own any database'
22    **
23    **	Note: When the database is a local tempdb on a remote node, we don't 
24    **	check for aliased dbo because we need to avoid raising error msg 969. 
25    **	Same reason goes for why we check 'own any database'.
26    **
27    ** Parameters:
28    **	@objtype	- Type of object for which DDL is being generated.
29    **	@objname	- Name of the object.
30    **
31    ** Returns:
32    **	0		- Permissions failed.
33    **	Non-zero	- If user has permission or doesn't require it.
34    */
35    create or replace procedure sp_ddlgen_permissions(
36        @objtype varchar(30)
37        , @objname varchar(30)
38    ) as
39        begin -- {
40            declare @is_dbo int
41                , @procname varchar(60)
42                , @lcl_sarole int
43                , @status3 int
44                , @stat3_tempdb int
45                , @suid int
46                , @dummy int
47                , @sdc_local_temp_db int
48    
49                , @retval int
50                , @permission_name varchar(255)
51                , @dbname varchar(255)
52                , @gp_status int
53                , @gp_enabled int
54                , @cmd varchar(255)
55                , @suidstr varchar(10)
56                , @altsuid int
57                , @wildcard int
58                , @reqprmchk int
59                , @is_remote_tdb int
60                , @dbid int
61    
62    
63    
64            select @retval = 0
65                , @is_dbo = 0
66                , @reqprmchk = 0
67                , @sdc_local_temp_db = 0
68                , @is_remote_tdb = 0
69    
70    
71            select @is_dbo = 0
72            select @sdc_local_temp_db = 0
73    
74            select @wildcard = charindex('%', @objname)
75            /* Check if the @objtype requires a permission check */
76            if (@objtype = 'database')
77            begin
78                select @reqprmchk = 1
79                select @dbid = db_id(@objname)
80                if (@wildcard = 0 and @@clustermode = "shared disk cluster")
81                begin
82                    /* Is database a tempdb on another node? */
83                    exec @is_remote_tdb = sp_check_remote_tempdb @dbid,
84                        'noerrmsg'
85                end
86            end
87    
88            /* If not wildcard then check for dbo. */
89            if (@wildcard = 0 and @reqprmchk = 1)
90            begin -- {
91                select @dbname = @objname
92                /* Get the status bit that specifies tempdb in status3*/
93                select @stat3_tempdb = number
94                from master.dbo.spt_values
95                where type = "D3"
96                    and name = "TEMPDB STATUS MASK"
97                /* Get suid of the dbo */
98                select @status3 = status3,
99                    @suid = suid
100               from master.dbo.sysdatabases
101               where name = @objname
102               /* 
103               ** Check to see if the database is a local tempdb 
104               ** in a shared disk cluster.
105               */
106               select @sdc_local_temp_db = 1
107               where ((@@clustermode = 'shared disk cluster')
108                       and ((@status3 & @stat3_tempdb) in
109                               (select number
110                               from master.dbo.spt_values
111                               where name in ("local user temp db",
112                                       "local system temp db"))))
113   
114               if ((@sdc_local_temp_db = 1) and (@suid = suser_id()))
115               begin
116                   select @is_dbo = 1
117               end
118               /*
119               ** If sdc, only check for aliased dbo when the database is
120               ** not a local tempdb on a different node.
121               */
122               if (@is_dbo = 0 and @is_remote_tdb = 0)
123               begin -- {
124   
125                   select @procname = @objname + "..sp_aux_checkroleperm"
126                   select @permission_name = "own database"
127                   select @dbname = @objname
128   
129   
130                   execute @gp_status = @procname "dbo",
131                       @permission_name, @dbname, @gp_enabled output
132   
133                   if (@gp_status = 0)
134                   begin
135                       select @is_dbo = 1
136                   end
137               end -- }
138           end -- }
139   
140           if (@is_dbo = 0 and @reqprmchk = 1)
141           begin -- {
142               if (@wildcard != 0 or @is_remote_tdb = 1)
143               begin
144                   /* 
145                   ** Wildcard or a local tempdb on a different node 
146                   ** requires 'own any database' permission.
147                   */
148                   select @permission_name = "own any database"
149                   select @dbname = NULL
150               end
151               else
152               begin
153                   /*
154                   ** Object name is not a wildcard including local
155                   ** tempdbs on the current node require own database
156                   ** permission.
157                   */
158                   select @permission_name = "own database"
159                   select @dbname = @objname
160               end
161               execute @gp_status = sp_aux_checkroleperm "sa_role",
162                   @permission_name, @dbname, @gp_enabled output
163   
164               if (@gp_enabled = 0)
165               begin
166                   if (proc_role("sa_role") = 0)
167                   begin
168                       raiserror 17230
169                       return (0)
170                   end
171               end
172               else
173               begin
174                   select @dummy = proc_auditperm(@permission_name,
175                           @gp_status, @objname)
176                   if (@gp_status != 0)
177                   begin
178                       return (0)
179                   end
180               end
181           end -- }
182           /* Either a dbo or a privileged user or doesn't require perm check */
183           return (1)
184       end -- }
185   

DEFECTS
 MTYP 4 Assignment type mismatch @role_perm_name: varchar(32) = varchar(255) 162
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: spt_values.spt_valuesclust clustered
(number, type)
Intersection: {type}
95
 TNOU 4 Table with no unique index master..spt_values master..spt_values
 MGTP 3 Grant to public master..spt_values  
 MGTP 3 Grant to public master..sysdatabases  
 MUCO 3 Useless Code Useless Brackets in create proc 35
 MUCO 3 Useless Code Useless Begin-End Pair 39
 MUCO 3 Useless Code Useless Brackets 76
 MUCO 3 Useless Code Useless Brackets 80
 MUCO 3 Useless Code Useless Brackets 89
 MUCO 3 Useless Code Useless Brackets 107
 MUCO 3 Useless Code Useless Brackets 114
 MUCO 3 Useless Code Useless Brackets 122
 MUCO 3 Useless Code Useless Brackets 133
 MUCO 3 Useless Code Useless Brackets 140
 MUCO 3 Useless Code Useless Brackets 142
 MUCO 3 Useless Code Useless Brackets 164
 MUCO 3 Useless Code Useless Brackets 166
 MUCO 3 Useless Code Useless Brackets 169
 MUCO 3 Useless Code Useless Brackets 176
 MUCO 3 Useless Code Useless Brackets 178
 MUCO 3 Useless Code Useless Brackets 183
 QAFM 3 Var Assignment from potentially many rows 93
 QPNC 3 No column in condition 107
 VNRD 3 Variable is not read @retval 64
 VNRD 3 Variable is not read @dummy 174
 VUNU 3 Variable is not used @lcl_sarole 42
 VUNU 3 Variable is not used @cmd 54
 VUNU 3 Variable is not used @suidstr 55
 VUNU 3 Variable is not used @altsuid 56
 MDYE 2 Dynamic Exec Marker exec @gp_status 130
 MSUB 2 Subquery Marker 109
 MTR1 2 Metrics: Comments Ratio Comments: 40% 35
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 19 = 20dec - 3exi + 2 35
 MTR3 2 Metrics: Query Complexity Complexity: 66 35

DEPENDENCIES
PROCS AND TABLES USED
calls proc sybsystemprocs..sp_aux_checkroleperm  
   reads table master..sysconfigures (1)  
   reads table master..syscurconfigs (1)  
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  
reads table master..spt_values (1)  
reads table master..sysdatabases (1)