Re: [Tora-develop] issue with toOracleExtract:IndexFunction - perms on sys.col$?

[Ivan B. <iv...@cv...>]

I usually grant my to my users the role SELECT_CATALOG_ROLE
it allows you to view all the v$views and other internal data.
Except passwords for dblinks, so there are no security threats
with this grant.

Even if you do not have access to some '$' views
you can get the ddl for any object by calling

select dbms_metadata.get_ddl(<objecttype>, <objectname>, <owner>) from dual;

I would prefer to use this approach before doing all the work in C++.

Ivan


On 12/10/2010 03:55 PM, Neulinger, Nathan wrote:
> Our local DBA just started noticing a problem with using TOra (and sql
> developer for that matter) to get the Script for a table. It's
> complaining about this query:
>
> static toSQL SQLIndexFunction("toOracleExtract:IndexFunction",
>                                "SELECT c.default$\n"
>                                "  FROM sys.col$ c,\n"
>                                "       sys.all_indexes i,\n"
>                                "       sys.all_objects o\n"
>                                " WHERE i.index_name = :ind<char[100]>\n"
>                                "   AND o.object_name = i.table_name\n"
>                                "   AND c.obj# = o.object_id\n"
>                                "   AND c.name = :tab<char[100]>\n"
>                                "   AND i.owner = :own<char[100]>\n"
>                                "   AND o.owner = i.table_owner",
>                                "Get function of index column, same column
> and binds");
>
> The underlying issue is that selects against sys.col$ are saying table
> does not exist.
>
> There are two issues here that I think need to be addresses:
>
> 	1. TOra if possible should fail gracefully here and still be
> able to return the REST of the schema
> 	2. Does anyone have any idea what additional perm needs to be
> granted in Oracle to get this to work properly?
>
> Schema extraction for most other tables works fine, I'm assuming this
> one is just failing because it has a function based index column?
>
> -- Nathan
>
> ------------------------------------------------------------
> Nathan Neulinger                       nn...@ms...
> Missouri S&T Information Technology    (573) 612-1412
> System Administrator - Principal       KD0DMH
>
>
>
> ------------------------------------------------------------------------------
> _______________________________________________
> Tora-develop mailing list
> Tor...@li...
> https://lists.sourceforge.net/lists/listinfo/tora-develop
>