On 9/25/14 9:49 AM, Alessandro Manzoni wrote:
I tried granting select on sysusers, but this table is accessible only
by db owner.
Is there a way to have a simple user, maybe with a specific role, to
list user names?
Hi Alessandro,
You can accomplish this by declaring a table function with definer's
rights. First compile a class like this:
import java.sql.*;
public class UserLister
{
public static ResultSet listUsers() throws SQLException
{
Connection conn = DriverManager.getConnection(
"jdbc:default:connection" );
return conn
.prepareStatement( "select userName from sys.sysusers" )
.executeQuery();
}
}
Then you can exercise this table function as follows:
connect 'jdbc:derby:memory:db;create=true;user=dbo';
call syscs_util.syscs_create_user( 'DBO', 'dbo_password' );
call syscs_util.syscs_create_user( 'RUTH', 'ruth_password' );
-- bounce database to enable native authentication
connect 'jdbc:derby:memory:db;shutdown=true';
connect 'jdbc:derby:memory:db;user=dbo;password=dbo_password' as dbo;
create function listUsers() returns table
(
userName varchar( 128 )
)
language java parameter style derby_jdbc_result_set reads sql data
external security definer
external name 'UserLister.listUsers';
grant execute on function listUsers to ruth;
connect 'jdbc:derby:memory:db;user=ruth;password=ruth_password' as ruth;
-- ruth can't select from sys.sysusers directly
select userName from sys.sysusers;
-- but she can select from the table function which was declared with
definer's rights
select * from table( dbo.listUsers() ) lu;
Hope this helps,
-Rick
