Quantcast

How To Use Applib Functions?

classic Classic list List threaded Threaded
8 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

How To Use Applib Functions?

MitchellHa
Hello,

There is probably an easy answer to this, but I am somewhat new to LucidDB and completely stumped.  I running a query and decided to format the Timestamp columns.  For this, I used applib.timestamp_to_char in my select.  However, running the query told me that I need execute permission.  This seemed reasonable, so I tried granting the permission on applib.timestamp_to_char over to my user.  But, I get a procedure not found error.  I have resorted to trying to load the applib.jar into my schema and define the function following the directions in how to set up a udf.  This hasn't helped and got me thinking I must be making this overly complicated.

How do I go about using the applib functions in my sql queries?

Thanks.

------------------------------------------------------------------------------
Throughout its 18-year history, RSA Conference consistently attracts the
world's best and brightest in the field, creating opportunities for Conference
attendees to learn about information security's most important issues through
interactions with peers, luminaries and emerging and established companies.
http://p.sf.net/sfu/rsaconf-dev2dev
_______________________________________________
luciddb-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/luciddb-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: [luciddb-users] How To Use Applib Functions?

John Sichi
Administrator
Mitchell Halligan wrote:

> Hello,
>
> There is probably an easy answer to this, but I am somewhat new to
> LucidDB and completely stumped.  I running a query and decided to
> format the Timestamp columns.  For this, I used
> applib.timestamp_to_char in my select.  However, running the query
> told me that I need execute permission.  This seemed reasonable, so I
> tried granting the permission on applib.timestamp_to_char over to my
> user.  But, I get a procedure not found error.  I have resorted to
> trying to load the applib.jar into my schema and define the function
> following the directions in how to set up a udf.  This hasn't helped
> and got me thinking I must be making this overly complicated.

Follow the example here:

http://pub.eigenbase.org/wiki/LucidDbOlap

The SPECIFIC keyword is required by SQL:200n so that GRANT doesn't have
to deal with overload ambiguities.

JVS

------------------------------------------------------------------------------
Throughout its 18-year history, RSA Conference consistently attracts the
world's best and brightest in the field, creating opportunities for Conference
attendees to learn about information security's most important issues through
interactions with peers, luminaries and emerging and established companies.
http://p.sf.net/sfu/rsaconf-dev2dev
_______________________________________________
luciddb-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/luciddb-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: [luciddb-users] How To Use Applib Functions?

MitchellHa
Thanks for the reply.  I came across the example and tried following the example.  Basically tried running every variation of this statement I can think of;

grant execute on specific procedure
applib.TIMESTAMP_TO_CHAR to <user>

All I get is, Reference to unknown routine "TIMESTAMP_TO_CHAR".  I've tried different logins, schemas, everything I can think of.

Thanks again.





----- Original Message ----
From: John V. Sichi <[hidden email]>
To: Mailing list for users of LucidDB <[hidden email]>
Sent: Sat, January 23, 2010 2:27:58 PM
Subject: Re: [luciddb-users] How To Use Applib Functions?

Mitchell Halligan wrote:

> Hello,
>
> There is probably an easy answer to this, but I am somewhat new to
> LucidDB and completely stumped.  I running a query and decided to
> format the Timestamp columns.  For this, I used
> applib.timestamp_to_char in my select.  However, running the query
> told me that I need execute permission.  This seemed reasonable, so I
> tried granting the permission on applib.timestamp_to_char over to my
> user.  But, I get a procedure not found error.  I have resorted to
> trying to load the applib.jar into my schema and define the function
> following the directions in how to set up a udf.  This hasn't helped
> and got me thinking I must be making this overly complicated.

Follow the example here:

http://pub.eigenbase.org/wiki/LucidDbOlap

The SPECIFIC keyword is required by SQL:200n so that GRANT doesn't have
to deal with overload ambiguities.

JVS

------------------------------------------------------------------------------
Throughout its 18-year history, RSA Conference consistently attracts the
world's best and brightest in the field, creating opportunities for Conference
attendees to learn about information security's most important issues through
interactions with peers, luminaries and emerging and established companies.
http://p.sf.net/sfu/rsaconf-dev2dev
_______________________________________________
luciddb-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/luciddb-users


------------------------------------------------------------------------------
Throughout its 18-year history, RSA Conference consistently attracts the
world's best and brightest in the field, creating opportunities for Conference
attendees to learn about information security's most important issues through
interactions with peers, luminaries and emerging and established companies.
http://p.sf.net/sfu/rsaconf-dev2dev
_______________________________________________
luciddb-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/luciddb-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: [luciddb-users] How To Use Applib Functions?

John Sichi
Administrator
Mitchell Halligan wrote:
> Thanks for the reply.  I came across the example and tried following
> the example.  Basically tried running every variation of this
> statement I can think of;
>
> grant execute on specific procedure applib.TIMESTAMP_TO_CHAR to
> <user>
>
> All I get is, Reference to unknown routine "TIMESTAMP_TO_CHAR".  I've
> tried different logins, schemas, everything I can think of.

Oops, in this case it would be

grant execute on specific function applib.APPLIB_STD_TIMESTAMP_TO_CHAR to
<user>;

The specific name for this function is different from its invocation
name because of some system-defined overloads.  I have to say, this is
seriously user-hostile, so maybe an extension to the standard is
warranted for granting privileges on routines by their invocation names
and returning an error in the case of ambiguity.  Feel free to log a
feature request for that in issues.eigenbase.org if it seems like a good
solution.

For now, here's how to find the mapping between routine invocation names
and their specific names:

select invocation_name, specific_name from sys_root.dba_routines;

Here's the overload I mentioned:

0: jdbc:luciddb:> select schema_name,specific_name from
sys_root.dba_routines where invocation_name='TIMESTAMP_TO_CHAR';
+--------------+-------------------------------+
| SCHEMA_NAME  |         SPECIFIC_NAME         |
+--------------+-------------------------------+
| MGMT         | STD_TIMESTAMP_TO_CHAR         |
| APPLIB       | APPLIB_STD_TIMESTAMP_TO_CHAR  |
+--------------+-------------------------------+

By the way, while testing this, I tried granting to sa (the creator of
the routine), and got an ugly internal error.  I've logged a bug for this:

http://issues.eigenbase.org/browse/FRG-391

JVS

------------------------------------------------------------------------------
Throughout its 18-year history, RSA Conference consistently attracts the
world's best and brightest in the field, creating opportunities for Conference
attendees to learn about information security's most important issues through
interactions with peers, luminaries and emerging and established companies.
http://p.sf.net/sfu/rsaconf-dev2dev
_______________________________________________
luciddb-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/luciddb-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: [luciddb-users] How To Use Applib Functions?

MitchellHa
Thanks.  This clarifies things a little more.  Unfortunately, I get the exact same error that you logged the bug for (although I'm not doing a "self grant").  I'm logged in as "sa" and granting to another user, but I get the exact same message.

Thanks again for all the help.  Hopefully the bug will be fixed soon.





----- Original Message ----
From: John V. Sichi <[hidden email]>
To: Mailing list for users of LucidDB <[hidden email]>
Sent: Sat, January 23, 2010 3:22:19 PM
Subject: Re: [luciddb-users] How To Use Applib Functions?

Mitchell Halligan wrote:
> Thanks for the reply.  I came across the example and tried following
> the example.  Basically tried running every variation of this
> statement I can think of;
>
> grant execute on specific procedure applib.TIMESTAMP_TO_CHAR to
> <user>
>
> All I get is, Reference to unknown routine "TIMESTAMP_TO_CHAR".  I've
> tried different logins, schemas, everything I can think of.

Oops, in this case it would be

grant execute on specific function applib.APPLIB_STD_TIMESTAMP_TO_CHAR to
<user>;

The specific name for this function is different from its invocation
name because of some system-defined overloads.  I have to say, this is
seriously user-hostile, so maybe an extension to the standard is
warranted for granting privileges on routines by their invocation names
and returning an error in the case of ambiguity.  Feel free to log a
feature request for that in issues.eigenbase.org if it seems like a good
solution.

For now, here's how to find the mapping between routine invocation names
and their specific names:

select invocation_name, specific_name from sys_root.dba_routines;

Here's the overload I mentioned:

0: jdbc:luciddb:> select schema_name,specific_name from
sys_root.dba_routines where invocation_name='TIMESTAMP_TO_CHAR';
+--------------+-------------------------------+
| SCHEMA_NAME  |         SPECIFIC_NAME         |
+--------------+-------------------------------+
| MGMT         | STD_TIMESTAMP_TO_CHAR         |
| APPLIB       | APPLIB_STD_TIMESTAMP_TO_CHAR  |
+--------------+-------------------------------+

By the way, while testing this, I tried granting to sa (the creator of
the routine), and got an ugly internal error.  I've logged a bug for this:

http://issues.eigenbase.org/browse/FRG-391

JVS

------------------------------------------------------------------------------
Throughout its 18-year history, RSA Conference consistently attracts the
world's best and brightest in the field, creating opportunities for Conference
attendees to learn about information security's most important issues through
interactions with peers, luminaries and emerging and established companies.
http://p.sf.net/sfu/rsaconf-dev2dev
_______________________________________________
luciddb-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/luciddb-users


------------------------------------------------------------------------------
Throughout its 18-year history, RSA Conference consistently attracts the
world's best and brightest in the field, creating opportunities for Conference
attendees to learn about information security's most important issues through
interactions with peers, luminaries and emerging and established companies.
http://p.sf.net/sfu/rsaconf-dev2dev
_______________________________________________
luciddb-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/luciddb-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: [luciddb-users] How To Use Applib Functions?

John Sichi
Administrator
Mitchell Halligan wrote:
> Thanks.  This clarifies things a little more.  Unfortunately, I get
> the exact same error that you logged the bug for (although I'm not
> doing a "self grant").  I'm logged in as "sa" and granting to another
> user, but I get the exact same message.
>
> Thanks again for all the help.  Hopefully the bug will be fixed soon.

Is it possible for you to provide the steps to reproduce starting from a
clean LucidDB installation?  I created a user bob and executed the grant
statement I sent, and it executed successfully.

If you were trying to create your own aliases for the function from your
non-sa account, there's a chance something got mixed up in there.

JVS

------------------------------------------------------------------------------
Throughout its 18-year history, RSA Conference consistently attracts the
world's best and brightest in the field, creating opportunities for Conference
attendees to learn about information security's most important issues through
interactions with peers, luminaries and emerging and established companies.
http://p.sf.net/sfu/rsaconf-dev2dev
_______________________________________________
luciddb-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/luciddb-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: [luciddb-users] How To Use Applib Functions?

MitchellHa
Since you created a new user and got it to work started me thinking.  I realized that I'd actually setup a user and schema name to be the same.  I think the "grant execute..." was confused.  To test it, I created a new user and ran the "grant execute...".  Presto!  It worked.  Now, to run my original SQL statement, I need Select (and probably all) privilege on the tables.  Rather than granting to each table, can I just grant a role to the user?  Is there a role that has all privileges?

Thanks.



----- Original Message ----
From: John V. Sichi <[hidden email]>
To: Mailing list for users of LucidDB <[hidden email]>
Sent: Sat, January 23, 2010 4:04:21 PM
Subject: Re: [luciddb-users] How To Use Applib Functions?

Mitchell Halligan wrote:
> Thanks.  This clarifies things a little more.  Unfortunately, I get
> the exact same error that you logged the bug for (although I'm not
> doing a "self grant").  I'm logged in as "sa" and granting to another
> user, but I get the exact same message.
>
> Thanks again for all the help.  Hopefully the bug will be fixed soon.

Is it possible for you to provide the steps to reproduce starting from a
clean LucidDB installation?  I created a user bob and executed the grant
statement I sent, and it executed successfully.

If you were trying to create your own aliases for the function from your
non-sa account, there's a chance something got mixed up in there.

JVS

------------------------------------------------------------------------------
Throughout its 18-year history, RSA Conference consistently attracts the
world's best and brightest in the field, creating opportunities for Conference
attendees to learn about information security's most important issues through
interactions with peers, luminaries and emerging and established companies.
http://p.sf.net/sfu/rsaconf-dev2dev
_______________________________________________
luciddb-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/luciddb-users


------------------------------------------------------------------------------
Throughout its 18-year history, RSA Conference consistently attracts the
world's best and brightest in the field, creating opportunities for Conference
attendees to learn about information security's most important issues through
interactions with peers, luminaries and emerging and established companies.
http://p.sf.net/sfu/rsaconf-dev2dev
_______________________________________________
luciddb-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/luciddb-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: [luciddb-users] How To Use Applib Functions?

John Sichi
Administrator
Mitchell Halligan wrote:
> Since you created a new user and got it to work started me thinking.
> I realized that I'd actually setup a user and schema name to be the
> same.  I think the "grant execute..." was confused.  To test it, I
> created a new user and ran the "grant execute...".  Presto!  It
> worked.  Now, to run my original SQL statement, I need Select (and
> probably all) privilege on the tables.  Rather than granting to each
> table, can I just grant a role to the user?  Is there a role that has
> all privileges?

Creating a role and granting that to the user is a good approach, but
you still have to grant privs on each table to the role itself.  There's
currently no predefined "superuser" role.

JVS

------------------------------------------------------------------------------
Throughout its 18-year history, RSA Conference consistently attracts the
world's best and brightest in the field, creating opportunities for Conference
attendees to learn about information security's most important issues through
interactions with peers, luminaries and emerging and established companies.
http://p.sf.net/sfu/rsaconf-dev2dev
_______________________________________________
luciddb-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/luciddb-users
Loading...