DatabaseProcApplicationCreatedLinks
sybsystemprocssp_table_privileges  14 déc. 14Defects Propagation Dependencies

1     
2     /* Sccsid = "%Z% generic/sproc/src/%M% %I% %G%" */
3     
4     create or replace procedure sp_table_privileges(
5         @table_name varchar(257),
6         @table_owner varchar(257) = null,
7         @table_qualifier varchar(257) = null)
8     as
9     
10        declare @owner_id int
11        declare @full_table_name varchar(520)
12        declare @tab_id int /* object id of the table specified */
13    
14    
15        set nocount on
16        /*
17        ** set the transaction isolation level
18        */
19        if @@trancount = 0
20        begin
21            set chained off
22        end
23        else
24        begin
25            /* 18040, "Catalog procedure %1! can not be run in a transaction." */
26            raiserror 18040, "sp_column_privileges"
27            return (1)
28        end
29    
30        set transaction isolation level 1
31    
32        /*
33        **  Check to see that the table is qualified with the database name
34        */
35        if @table_name like "%.%.%"
36        begin
37            /* 18021, "Object name can only be qualified with owner name." */
38            raiserror 18021
39            return (1)
40        end
41    
42        /*  If this is a temporary table; object does not belong to 
43        **  this database; (we should be in our temporary database)
44        */
45        if (@table_name like "#%" and db_name() != db_name(tempdb_id()))
46        begin
47            /* 
48            ** 17676, "This may be a temporary object. Please execute 
49            ** procedure from your temporary database."
50            */
51            raiserror 17676
52            return (1)
53        end
54    
55    
56        /*
57        ** The table_qualifier should be same as the database name. Do the sanity check
58        ** if it is specified
59        */
60        if (@table_qualifier is null) or (@table_qualifier = '')
61            /* set the table qualifier name */
62            select @table_qualifier = db_name()
63        else
64        begin
65            if db_name() != @table_qualifier
66            begin
67                /* 18039, "Table qualifier must be name of current database." */
68                raiserror 18039
69                return (1)
70            end
71        end
72    
73        /* 
74        ** if the table owner is not specified, it will be taken as the id of the
75        ** user executing this procedure. Otherwise find the explicit table name prefixed
76        ** by the owner id
77        */
78        if (@table_owner is null) or (@table_owner = '')
79            select @full_table_name = @table_name
80        else
81        begin
82            if (@table_name like "%.%") and
83                substring(@table_name, 1, charindex(".", @table_name) - 1) != @table_owner
84            begin
85                /* 18011, Object name must be qualified with the owner name */
86                raiserror 18011
87                return (1)
88            end
89    
90            if not (@table_name like "%.%")
91                select @full_table_name = @table_owner + '.' + @table_name
92            else
93                select @full_table_name = @table_name
94        end
95    
96        /* 
97        ** check to see if the specified table exists or not
98        */
99    
100       select @tab_id = object_id(@full_table_name)
101       if (@tab_id is null)
102       begin
103           /* 17492, "The table or view named doesn't exist in the current database." */
104           raiserror 17492
105           return (1)
106       end
107   
108   
109       /*
110       ** check to see if the @tab_id indeeed represents a table or a view
111       */
112   
113       if not exists (select *
114               from sysobjects
115               where (@tab_id = id) and
116                   ((type = 'U') or
117                       (type = 'S') or
118                       (type = 'V')))
119       begin
120           /* 17492, "The table or view named doesn't exist in the current database." */
121           raiserror 17492
122           return (1)
123       end
124   
125       /* 
126       ** compute the table owner id
127       */
128   
129       select @owner_id = uid
130       from sysobjects
131       where id = @tab_id
132   
133   
134   
135       /*
136       ** get table owner name
137       */
138   
139       select @table_owner = name
140       from sysusers
141       where uid = @owner_id
142   
143   
144       /* Create temp table to store results from sp_aux_computeprivs */
145       create table #results_table
146       (table_qualifier varchar(257),
147           table_owner varchar(257),
148           table_name varchar(257),
149           column_name varchar(257) NULL,
150           grantor varchar(257),
151           grantee varchar(257),
152           privilege varchar(257),
153           is_grantable varchar(3))
154   
155       exec sp_aux_computeprivs @table_name, @table_owner, @table_qualifier,
156           NULL, 0, @tab_id
157   
158       set nocount off
159   
160       return (0)
161   
162   


exec sp_procxmode 'sp_table_privileges', 'AnyMode'
go

Grant Execute on sp_table_privileges to public
go
DEFECTS
 MEST 4 Empty String will be replaced by Single Space 60
 MEST 4 Empty String will be replaced by Single Space 78
 MTYP 4 Assignment type mismatch @table_owner: varchar(32) = varchar(257) 155
 MTYP 4 Assignment type mismatch @table_qualifier: varchar(32) = varchar(257) 155
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 115
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 131
 MGTP 3 Grant to public sybsystemprocs..sp_table_privileges  
 MGTP 3 Grant to public sybsystemprocs..sysobjects  
 MGTP 3 Grant to public sybsystemprocs..sysusers  
 MNER 3 No Error Check should check return value of exec 155
 MUCO 3 Useless Code Useless Brackets in create proc 4
 MUCO 3 Useless Code Useless Brackets 27
 MUCO 3 Useless Code Useless Brackets 39
 MUCO 3 Useless Code Useless Brackets 45
 MUCO 3 Useless Code Useless Brackets 52
 MUCO 3 Useless Code Useless Brackets 69
 MUCO 3 Useless Code Useless Brackets 87
 MUCO 3 Useless Code Useless Brackets 101
 MUCO 3 Useless Code Useless Brackets 105
 MUCO 3 Useless Code Useless Brackets 122
 MUCO 3 Useless Code Useless Brackets 160
 MUIN 3 Column created using implicit nullability 145
 QISO 3 Set isolation level 30
 MSUB 2 Subquery Marker 113
 MTR1 2 Metrics: Comments Ratio Comments: 36% 4
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 12 = 18dec - 8exi + 2 4
 MTR3 2 Metrics: Query Complexity Complexity: 66 4

DEPENDENCIES
PROCS AND TABLES USED
reads table sybsystemprocs..sysobjects  
calls proc sybsystemprocs..sp_aux_computeprivs  
   calls proc sybsystemprocs..syb_aux_privunion  
   writes table tempdb..#results_table (1) 
   read_writes table tempdb..#distinct_grantors (1) 
   calls proc sybsystemprocs..syb_aux_printprivs  
      calls proc sybsystemprocs..syb_aux_colbit  
   reads table sybsystemprocs..syscolumns  
   reads table sybsystemprocs..sysusers  
   calls proc sybsystemprocs..sp_autoformat  
      writes table sybsystemprocs..sp_autoformat_rset_002 
      reads table master..systypes (1)  
      writes table sybsystemprocs..sp_autoformat_rset_004 
      writes table sybsystemprocs..sp_autoformat_rset_003 
      calls proc sybsystemprocs..sp_autoformat  
      read_writes table tempdb..#colinfo_af (1) 
      reads table tempdb..systypes (1)  
      writes table sybsystemprocs..sp_autoformat_rset_001 
      reads table tempdb..syscolumns (1)  
      calls proc sybsystemprocs..sp_namecrack  
      reads table master..syscolumns (1)  
      writes table sybsystemprocs..sp_autoformat_rset_005 
   reads table sybsystemprocs..sysobjects  
   calls proc sybsystemprocs..syb_aux_privexor  
      calls proc sybsystemprocs..syb_aux_expandbitmap  
   read_writes table tempdb..#sysprotects (1) 
   read_writes table tempdb..#column_privileges (1) 
   reads table sybsystemprocs..sysprotects  
   read_writes table tempdb..#useful_groups (1) 
writes table tempdb..#results_table (1) 
reads table sybsystemprocs..sysusers