Il 25.09.2014 21.31, Rick Hillegas ha scritto:
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
I was able!
I created my class with some static public methods.
I exported this single class into a jar file.
I registred the jar with SQLJ.INSTALL_JAR procedure with no erros
I created the function with no errors, I see the function listed inside
the schema
when I use the function I get the error that the class was non found or
not public
I called also SYSCS_SET_DATABASE_PROPERTY( 'derby.database.classpath',
(even if not mentioned on developer guide under "System procedures for
storing jar files in a database")
Now the function call works properly, even after restart derby.
Is the above correct? or I missed somthing?
Obviously when you get a new toy, it's time to play!
So I have a new question, I didn't found an answare inside docs: does
Derby supports polymorphism in functions and procedures?
