One Star

Use Oracle as DB for Data profiler Reports

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

15 REPLIES
Employee

Re: Use Oracle as DB for Data profiler Reports

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.
Employee

Re: Use Oracle as DB for Data profiler Reports

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.
One Star

Re: Use Oracle as DB for Data profiler Reports

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
Employee

Re: Use Oracle as DB for Data profiler Reports

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?
One Star

Re: Use Oracle as DB for Data profiler Reports

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
Employee

Re: Use Oracle as DB for Data profiler Reports

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

Re: Use Oracle as DB for Data profiler Reports

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
One Star

Re: Use Oracle as DB for Data profiler Reports

Sebastiao,
PFA results of the queries.
Thanks!
Mythili
Employee

Re: Use Oracle as DB for Data profiler Reports

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"
One Star

Re: Use Oracle as DB for Data profiler Reports

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

Re: Use Oracle as DB for Data profiler Reports

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.
One Star

Re: Use Oracle as DB for Data profiler Reports

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
Employee

Re: Use Oracle as DB for Data profiler Reports

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

Re: Use Oracle as DB for Data profiler Reports

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
Employee

Re: Use Oracle as DB for Data profiler Reports

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.