DatabaseProcApplicationCreatedLinks
sybsystemprocssp_oledb_gettableprivileges  31 Aug 14Defects Dependencies

1     
2     /* Sccsid = "%Z% generic/sproc/src/%M% %I% %G%" */
3     
4     create procedure sp_oledb_gettableprivileges(
5         @table_name varchar(771) = null,
6         @table_schema varchar(32) = null,
7         @table_catalog varchar(32) = null,
8         @grantor varchar(32) = null,
9         @grantee varchar(32) = null)
10    as
11    
12        declare @owner_id int
13        declare @full_table_name varchar(1543)
14        declare @tab_id int /* object id of the table specified */
15    
16        declare @tab_name varchar(255)
17        declare @tab_owner varchar(32)
18        declare @table_id int /* object id of the
19        table specified*/
20        declare @startedInTransaction bit
21    
22        if (@@trancount > 0)
23            select @startedInTransaction = 1
24        else
25            select @startedInTransaction = 0
26    
27    
28        set nocount on
29        set transaction isolation level 1
30    
31        if (@startedInTransaction = 1)
32            save transaction oledb_keep_temptable_tx
33    
34    
35        /*
36        **  Check to see that the table is qualified with the database name
37        */
38        if @table_name like "%.%.%"
39        begin
40            /* 18021, "Object name can only be qualified with owner name." */
41            raiserror 18021
42            return (1)
43        end
44    
45        /*  If this is a temporary table; object does not belong to 
46        **  this database; (we should be in our temporary database)
47        */
48        if (@table_name like "#%" and db_name() != db_name(tempdb_id()))
49        begin
50            /* 
51            ** 17676, "This may be a temporary object. Please execute 
52            ** procedure from your temporary database."
53            */
54            raiserror 17676
55            return (1)
56        end
57    
58        /*
59        ** Results Table needs to be created so that sp_oledb_computeprivs has a temp
60        ** table to reference when the procedure is compiled.  Otherwise, the calling
61        ** stored procedure will create the temp table for sp_oledb_computeprivs.
62        */
63        create table #results_table
64        (TABLE_CATALOG varchar(32),
65            TABLE_SCHEMA varchar(32),
66            TABLE_NAME varchar(255),
67            column_name varchar(255) NULL,
68            GRANTOR varchar(32),
69            GRANTEE varchar(32),
70            PRIVILEGE_TYPE varchar(32),
71            IS_GRANTABLE varchar(3))
72    
73        /*
74        ** The table_catalog should be same as the database name. Do the sanity check
75        ** if it is specified
76        */
77        if (@table_catalog is null) or (@table_catalog = '')
78            /* set the table qualifier name */
79            select @table_catalog = db_name()
80        else
81        begin
82            if db_name() != @table_catalog
83            begin
84                /* 18039, "Table qualifier must be name of current database." */
85                goto SelectFKClause
86            end
87        end
88    
89        /* 
90        ** if the table owner is not specified, it will be taken as the id of the
91        ** user executing this procedure. Otherwise find the explicit table name prefixed
92        ** by the owner id
93        */
94        if (@table_schema is null) or (@table_schema = '')
95            select @full_table_name = @table_name
96        else
97        begin
98            if (@table_name like "%.%") and
99                substring(@table_name, 1, charindex(".", @table_name) - 1) != @table_schema
100           begin
101               /* 18011, Object name must be qualified with the owner name */
102               raiserror 18011
103               return (1)
104           end
105   
106           if not (@table_name like "%.%")
107               select @full_table_name = @table_schema + '.' + @table_name
108           else
109               select @full_table_name = @table_name
110       end
111   
112       /* 
113       ** check to see if the specified table exists or not
114       */
115   
116       select @tab_id = object_id(@full_table_name)
117       if (@tab_id is null)
118       begin
119           /* 17492, "The table or view named doesn't exist in the current database." */
120           goto SelectFKClause
121       end
122   
123   
124       /*
125       ** check to see if the @tab_id indeeed represents a table or a view
126       */
127   
128       if not exists (select *
129               from sysobjects
130               where (@tab_id = id) and
131                   ((type = 'U') or
132                       (type = 'S') or
133                       (type = 'V')))
134       begin
135           /* 17492, "The table or view named doesn't exist in the current database." */
136           goto SelectFKClause
137       end
138   
139       /* 
140       ** compute the table owner id
141       */
142   
143       select @owner_id = uid
144       from sysobjects
145       where id = @tab_id
146   
147   
148   
149       /*
150       ** get table owner name
151       */
152   
153       select @table_schema = name
154       from sysusers
155       where uid = @owner_id
156   
157       /* Now, create a temporary table to hold a list of all the possible
158       tables that we could get with the trio of table name, table owner and
159       table catalog. Then, populate that table.*/
160   
161       create table #odbc_tprivs
162       (tab_id int primary key not null,
163           tab_name varchar(255),
164           tab_owner varchar(32) null,
165           uid int,
166           type varchar(10))
167   
168       insert #odbc_tprivs
169       SELECT id, name, user_name(uid), uid, type
170       FROM sysobjects s
171       WHERE name LIKE @table_name ESCAPE '\'
172           AND user_name(uid) LIKE @table_schema ESCAPE '\'
173           AND charindex(substring(type, 1, 1), 'SUV') != 0
174   
175       declare tablepriv_cursor cursor for
176       select tab_name, tab_owner, tab_id from #odbc_tprivs
177   
178       open tablepriv_cursor
179   
180       fetch tablepriv_cursor into @tab_name, @tab_owner, @table_id
181   
182       while (@@sqlstatus != 2)
183       begin
184   
185           exec sp_oledb_computeprivs @tab_name, @tab_owner, @table_catalog,
186               NULL, 0, @table_id
187           fetch tablepriv_cursor into @tab_name, @tab_owner, @table_id
188   
189       end
190   
191       close tablepriv_cursor
192       /* Output the results table */
193       update #results_table set IS_GRANTABLE = '0' where IS_GRANTABLE = "NO"
194       update #results_table set IS_GRANTABLE = '1' where IS_GRANTABLE = "YES"
195   
196   SelectFKClause:
197       select GRANTOR, GRANTEE, TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME,
198           PRIVILEGE_TYPE = case when r.PRIVILEGE_TYPE = 'REFERENCE' then "REFERENCES"
199               else r.PRIVILEGE_TYPE end,
200           IS_GRANTABLE = convert(bit, IS_GRANTABLE) from #results_table r
201       order by TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, PRIVILEGE_TYPE
202   
203       set nocount off
204   
205       if (@startedInTransaction = 1)
206           rollback transaction oledb_keep_temptable_tx
207   
208       return (0)
209   
210   


exec sp_procxmode 'sp_oledb_gettableprivileges', 'AnyMode'
go

Grant Execute on sp_oledb_gettableprivileges to public
go
RESULT SETS
sp_oledb_gettableprivileges_rset_001

DEFECTS
 MEST 4 Empty String will be replaced by Single Space 77
 MEST 4 Empty String will be replaced by Single Space 94
 CUNU 3 Cursor not updated: cursor should contain 'for read only' clause tablepriv_cursor 176
 MGTP 3 Grant to public sybsystemprocs..sp_oledb_gettableprivileges  
 MGTP 3 Grant to public sybsystemprocs..sysobjects  
 MGTP 3 Grant to public sybsystemprocs..sysusers  
 MNER 3 No Error Check should check @@error after insert 168
 MNER 3 No Error Check should check return value of exec 185
 MNER 3 No Error Check should check @@error after update 193
 MNER 3 No Error Check should check @@error after update 194
 MUCO 3 Useless Code Useless Brackets in create proc 4
 MUCO 3 Useless Code Useless Brackets 22
 MUCO 3 Useless Code Useless Brackets 31
 MUCO 3 Useless Code Useless Brackets 42
 MUCO 3 Useless Code Useless Brackets 48
 MUCO 3 Useless Code Useless Brackets 55
 MUCO 3 Useless Code Useless Brackets 103
 MUCO 3 Useless Code Useless Brackets 117
 MUCO 3 Useless Code Useless Brackets 182
 MUCO 3 Useless Code Useless Brackets 205
 MUCO 3 Useless Code Useless Brackets 208
 MUIN 3 Column created using implicit nullability 63
 MUIN 3 Column created using implicit nullability 161
 QISO 3 Set isolation level 29
 VUNU 3 Variable is not used @grantor 8
 VUNU 3 Variable is not used @grantee 9
 CUPD 2 Updatable Cursor Marker (updatable by default) 176
 MRST 2 Result Set Marker 197
 MSUB 2 Subquery Marker 128
 MTR1 2 Metrics: Comments Ratio Comments: 30% 4
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 18 = 20dec - 4exi + 2 4
 MTR3 2 Metrics: Query Complexity Complexity: 98 4

DEPENDENCIES
PROCS AND TABLES USED
reads table sybsystemprocs..sysusers  
read_writes table tempdb..#odbc_tprivs (1) 
read_writes table tempdb..#results_table (1) 
reads table sybsystemprocs..sysobjects  
calls proc sybsystemprocs..sp_oledb_computeprivs  
   read_writes table tempdb..#distinct_grantors (1) 
   reads table sybsystemprocs..syscolumns  
   calls proc sybsystemprocs..syb_aux_privexor  
      calls proc sybsystemprocs..syb_aux_expandbitmap  
   read_writes table tempdb..#sysprotects (1) 
   reads table sybsystemprocs..sysusers  
   calls proc sybsystemprocs..syb_aux_printprivs  
      calls proc sybsystemprocs..syb_aux_colbit  
   read_writes table tempdb..#column_privileges (1) 
   reads table sybsystemprocs..sysprotects  
   calls proc sybsystemprocs..syb_aux_privunion  
   read_writes table tempdb..#useful_groups (1) 
   reads table sybsystemprocs..sysobjects  
   writes table tempdb..#results_table (1)