db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Rick Hillegas (JIRA)" <j...@apache.org>
Subject [jira] [Updated] (DERBY-5395) By default, only the DBO should be allowed to run several of the diagnostic VTIs.
Date Wed, 31 Aug 2011 18:38:09 GMT

     [ https://issues.apache.org/jira/browse/DERBY-5395?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]

Rick Hillegas updated DERBY-5395:
---------------------------------

    Attachment: SafeCacheViewer.java

Attaching SafeCacheViewer.java. This is a table function which selects the safe columns from
the statement cache vti. If the DBO registers this table function with definer's rights and
grants EXECUTE privilege to PUBLIC, then anyone can view the safe columns of the statement
cache. This technique can be used to grant other users privilege to view the safe bits of
the diagnostic vtis whose access will be controlled when the patch is committed. Here's a
script which shows this technique in action:

ij version 10.9
ij> connect 'jdbc:derby:memory:db;create=true;user=test_dbo;password=test_dbopassword'
as admin_conn;
ij> create function safeCacheViewer()
returns table
(
    id char( 36 ),
    schemaName varchar( 128 ),
    valid boolean,
    compiled_at timestamp
)
language java parameter style derby_jdbc_result_set reads sql data
external security definer
external name 'SafeCacheViewer.safeCacheViewer';
0 rows inserted/updated/deleted
ij> grant execute on function safeCacheViewer to public;
0 rows inserted/updated/deleted
ij> connect 'jdbc:derby:memory:db;user=ruth;password=ruthpassword' as ruth_conn;
ij(RUTH_CONN)> -- fails permissions hurdle
select * from syscs_diag.statement_cache;
ERROR 4251D: Only the database owner can view this data.
ij(RUTH_CONN)> -- succeeds
select * from table( test_dbo.safeCacheViewer() ) s;
ID                                  |SCHEMANAME                                          
                                                                           |VALID|COMPILED_AT
                 
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
ace4c0a3-0132-211b-dc8c-0000042be988|RUTH                                                
                                                                           |true |NULL   
                     
4d3680a5-0132-211b-dc8c-0000042be988|TEST_DBO                                            
                                                                           |true |NULL   
                     
341cc09e-0132-211b-dc8c-0000042be988|TEST_DBO                                            
                                                                           |true |NULL   
                     
0b5b0099-0132-211b-dc8c-0000042be988|TEST_DBO                                            
                                                                           |true |NULL   
                     

4 rows selected

> By default, only the DBO should be allowed to run several of the diagnostic VTIs.
> ---------------------------------------------------------------------------------
>
>                 Key: DERBY-5395
>                 URL: https://issues.apache.org/jira/browse/DERBY-5395
>             Project: Derby
>          Issue Type: Bug
>          Components: SQL
>    Affects Versions: 10.9.0.0
>            Reporter: Rick Hillegas
>            Assignee: Rick Hillegas
>         Attachments: SafeCacheViewer.java, derby-5395-01-ac-protectVTIs.diff
>
>
> Only the DBO should be allowed to run the following VTIs:
>   syscs_diag.statement_cache
>   syscs_diag.transaction_table
>   syscs_diag.error_log_reader( )
>   syscs_diag.statement_duration()

--
This message is automatically generated by JIRA.
For more information on JIRA, see: http://www.atlassian.com/software/jira

        

Mime
View raw message