topic Re: Use Oracle as DB for Data profiler Reports in Data Quality

Use Oracle as DB for Data profiler Reports

Anonymous — Sat, 16 Nov 2024 12:58:31 GMT

Hi,
I am trying to set Oracle database (instead of HSQL) as the default database to store data regarding my reports. I am using a login ID which has permissions to create table. But I am getting an error "In creating database it has not enough privilege, please modify the privilege for this user". PFA screen shot of the same.
Does the user login ID require permissions to create database? Does this mean the information will be stored within a new schema in the Oracle database?
Please let me know what other privileges the user ID needs to have to accomplish the above successfully.
Thanks!
Mythili

Re: Use Oracle as DB for Data profiler Reports

Anonymous — Wed, 20 Apr 2011 09:42:46 GMT

Hi Mythili S,
Thanks for your message,when you change database(Oracle) to store data,you must set up permissions of Oracle's user,the user's permissions must
contain three kinds as follow:
"create any table"
"insert any table"
"unlimited tablespace"
Then you can try again.

Re: Use Oracle as DB for Data profiler Reports

Sebastiao_Qlik — Wed, 20 Apr 2011 09:56:28 GMT

Hi Mythili,
Which version of Oracle are you using?
does the CSMARTVOL already exist?
if it does not exist, the studio will try to create it (hence the permissions to create a schema), but if it exists, it should be able to create the tables in it without the need of schema creation permissions.

Re: Use Oracle as DB for Data profiler Reports

Anonymous — Wed, 20 Apr 2011 16:33:14 GMT

Hi Sebastiao,
We are using Oracle 11g.
CSMARTVOL already exists.
I am unable to save the settings itself. The error is thrown as soon as I click on "Apply".
Thanks!
Mythili

Re: Use Oracle as DB for Data profiler Reports

Sebastiao_Qlik — Wed, 20 Apr 2011 16:38:52 GMT

ok, we have tested today on 10g, but could not reproduce your issue.
Can you give us more details about your permission settings?
Does your schema already contain other tables? or is it empty?

Re: Use Oracle as DB for Data profiler Reports

Anonymous — Wed, 20 Apr 2011 17:27:38 GMT

Sebastiao,
The schema already contains tables and is in use since a long time.
The ID with which I tried is the functional ID and it has all privileges, including creation of tables.
I am really curious why the error message says "In creating database it has not enough privilege......"
Thanks!
Mythili

Re: Use Oracle as DB for Data profiler Reports

Sebastiao_Qlik — Wed, 20 Apr 2011 18:01:46 GMT

We will investigate this issue and let you know the reason as soon as we understand.
Thanks

Re: Use Oracle as DB for Data profiler Reports

Sebastiao_Qlik — Fri, 29 Apr 2011 09:24:51 GMT

Mythili,
can you tell us the result of the following two commands:

select t.username,t.granted_role from user_role_privs t

select t.username,t.privilege from user_sys_privs t

Re: Use Oracle as DB for Data profiler Reports

Anonymous — Fri, 29 Apr 2011 12:39:24 GMT

Sebastiao,
PFA results of the queries.
Thanks!
Mythili

Re: Use Oracle as DB for Data profiler Reports

Sebastiao_Qlik — Fri, 29 Apr 2011 13:23:09 GMT

If you grant your user to have the following 3 privileges, then you should be able to initialize the datamart structure.
"create any table"
"insert any table"
"unlimited tablespace"

Re: Use Oracle as DB for Data profiler Reports

Anonymous — Fri, 29 Apr 2011 14:40:30 GMT

Sebastiao,
So in which schema will the tables be created. Will TDQ be creating a new schema?
Thanks!
Mythili

Re: Use Oracle as DB for Data profiler Reports

Sebastiao_Qlik — Fri, 29 Apr 2011 15:40:51 GMT

No, TDQ will not create a new schema. You must enter an existing schema name in which you want to see the tables in the preference page.
You may create a user called "tdq" and the tables and views will be created in this schema if the user "hr" has the appropriate permissions.

Re: Use Oracle as DB for Data profiler Reports

Anonymous — Wed, 04 May 2011 14:48:28 GMT

Sebastiao,
We grated the 3 privileges mentioned above to our functional ID and now we are able to use Oracle as the reporting DB. Thank You!
Is there any document which can give us more details regarding the way the data is stored in the multiple TDQ_* tables which are now created in Oracle? This would throw some light on what can be expected in the various tables and how effectively we can utilize that data.
Thanks!
Mythili

Re: Use Oracle as DB for Data profiler Reports

Sebastiao_Qlik — Thu, 05 May 2011 10:00:44 GMT

great!
yes, you can read the appendix in the Talend Data Quality user guide. It contains a description of the datamart structure.

Re: Use Oracle as DB for Data profiler Reports

_AnonymousUser — Mon, 16 May 2011 18:38:33 GMT

Sebastiao,
I have another follow-up question on this thread, once the datamart structure is initialized then can we drop the elevated privileges given to user ID (CSMARTVOL) i.e. drop
"create any table"
"unlimited tablespace"
And go back to original privileges? Please refer to link below for the original privileges
http://www.talendforge.org/forum/postgallery.php?pid=55640&filename=roles_and_privileges.JPG
Thanks,
-Arun

Re: Use Oracle as DB for Data profiler Reports

Sebastiao_Qlik — Mon, 16 May 2011 18:49:53 GMT

As long as you do not upgrade your studio, I guess that you should be able to drop these privileges.
Let me know if you have an issue by doing this.