| Database | Proc | Application | Created | Links |
| sybsystemprocs | sp_ddlgen_permissions | | 31 Aug 14 | Defects 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 wild-cards are used for @objname, need sa_role privilege. 15 ** (This can probably be improved in the future to allow DBO of a 16 ** set of dbs named 'mydb%' to run this sproc w/o sa_role.) 17 ** . If a single-db name is provided, you need either sa_role or DBO. 18 ** 19 ** Parameters: 20 ** @objtype - Type of object for which DDL is being generated. 21 ** @objname - Name of the object. 22 ** 23 ** Returns: 24 ** 0 - Permissions failed. 25 ** Non-zero - Otherwise. 26 */ 27 create procedure sp_ddlgen_permissions( 28 @objtype varchar(30) 29 , @objname varchar(30) 30 ) as 31 begin 32 declare @is_dbo int 33 , @procname varchar(45) 34 , @lcl_sarole int 35 , @status3 int 36 , @stat3_tempdb int 37 , @suid int 38 , @dummy int 39 , @sdc_local_temp_db int 40 41 select @is_dbo = 0 42 select @sdc_local_temp_db = 0 43 44 select @stat3_tempdb = number 45 from master.dbo.spt_values 46 where type = "D3" 47 and name = "TEMPDB STATUS MASK" 48 49 -- Find whether the user has sa_role or not. -- 50 /* 51 ** Why do not we use proc_role("sa_role") to achieve this? 52 ** The function proc_role(), when executed by a user with 53 ** no sa_role/sso_role prints error message which is 54 ** not desired in our case. Fortunately show_role() doesn't 55 ** do such mischief. Hence, this not-so-straightforward 56 ** way of finding it. 57 */ 58 select @lcl_sarole = charindex("sa_role", show_role()) 59 60 if (@objtype = 'database') 61 begin 62 -- If dbname w/o patterns was specified, check if user is 63 -- DBO. This DBO check has to be carefully done in case of 64 -- local user/system temp db in sdc, which can be followed 65 -- from the pseudo code flow shown here. 66 -- 67 -- if (single-database) 68 -- { 69 -- /* Special handling for local-temp-db */ 70 -- if (sdc-mode and local-temp-db) 71 -- { 72 -- if (user_has_sa_role) 73 -- pass permission; 74 -- 75 -- else if (current user is dbo of this db) 76 -- pass permission; 77 -- 78 -- else 79 -- { 80 -- call sp_is_dbo; 81 -- /* 82 -- ** The above call is multi-purpose 83 -- ** call. What it does 84 -- ** if (database_is_inaccessible) 85 -- ** { 86 -- ** Error out. 87 -- ** } 88 -- ** else 89 -- ** { 90 -- ** Check whether the user is 91 -- ** dbo taking into consideration, 92 -- ** the aliased dbo too. 93 -- ** } 94 -- */ 95 -- 96 -- if (not dbo) 97 -- raise the error 17230 98 -- } 99 -- } 100 -- else /* Normal handling*/ 101 -- { 102 -- /* check if the user is_dbo */ 103 -- call sp_is_dbo; 104 -- 105 -- if (not dbo) 106 -- raise the error 17230 107 -- } 108 -- } 109 -- else /* multiple databases specified with wild-card */ 110 -- { 111 -- /* It can be executed only by sa_role-user */ 112 -- 113 -- if (user_has_sa_role) 114 -- pass permission; 115 -- 116 -- else 117 -- do not pass permission; 118 -- raise the error 567 119 -- } 120 -- 121 -- Thus, the procedure ends. 122 -- 123 if (@lcl_sarole > 0) 124 begin 125 return 1 126 end 127 128 if (charindex('%', @objname) = 0) 129 begin 130 select @status3 = status3, 131 @suid = suid 132 from master.dbo.sysdatabases 133 where name = @objname 134 135 select @sdc_local_temp_db = 1 136 where ((@@clustermode = 'shared disk cluster') 137 and ((@status3 & @stat3_tempdb) in 138 (select number 139 from master.dbo.spt_values 140 where name in ("local user temp db", 141 "local system temp db")))) 142 143 if ((@sdc_local_temp_db = 1) and (@suid = suser_id())) 144 begin 145 return 1 146 end 147 else 148 begin 149 select @procname = 150 @objname + "..sp_is_dbo" 151 exec @is_dbo = @procname @objname 152 153 -- Return an error/failure if the sub-proc 154 -- were not to be found. 155 -- 156 if (@@error != 0) 157 return 0 158 159 if (@is_dbo != 1) 160 begin 161 raiserror 17230 162 return 0 163 end 164 end 165 end 166 else 167 begin 168 -- In case of multiple database, only the user having 169 -- "sa_role" can execute sp_ddlgen. However, if the 170 -- user had sa_role, the procedure must have already 171 -- returned by now. 172 -- This means user doesn't have sa_role. Hence, raise 173 -- server error message 567. 174 -- "You must have the following role(s) to execute this 175 -- command/procedure: '%.*s'. Please contact a user 176 -- with the appropriate role for help." 177 -- This can be achieved by calling the builtin function 178 -- proc_role("sa_role"). Basically, this builtin when 179 -- executed by non-sa_role gives the error message 567. 180 -- 181 select @dummy = proc_role("sa_role") 182 return 0 183 end 184 end 185 end 186
| DEPENDENCIES |
| PROCS AND TABLES USED reads table master..sysdatabases (1)  reads table master..spt_values (1) |
