[
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
