It's all about the answers!

Ask a question
QuestionsTagsUsersBadges
Guidelines | FAQ
Follow this question
Question details

×2,346
×136
×42
×12
×1

question asked: Oct 03 '13, 5:00 a.m.
question was seen: 6,351 times
last updated: Oct 24 '13, 11:24 p.m.

Related questions

Minimum required Oracle privileges for Insight to work

0
Amine Anouja (1●1●1) | asked Oct 03 '13, 5:00 a.m.
Hello,

What is the minimum required Oracle privileges for Insight to work. Giving DBA privileges is not acceptable.

I found this wiki article for 3.0.1 CLM DW:
https://jazz.net/wiki/bin/view/Main/MoreControlOverTheOracleDataWarehouseSetup

is the information provided in the wiki still applicable for CLM 4.0.1 (insght 1.1.1.1)?
What about RIBA schema which is not mentioned in the article?
is there any recommendation for RIETL Oracle schema?

Thanks.

My Env is:

CLM Server:
  • Windows Server 2008 R2 Enterprise Edition SP1 (64 bit)
  • WebSphere Application Server Standard Edition 8.0.0.3 64 bit [bundled]
  • CLM 4.0.1
Insight (Report and ETL) Server:

  • Windows Server 2008 R2 Enterprise Edition SP1 (64 bit)
  • WebSphere Application Server  v7.0 and fix pack 19
  • Insight 1.1.1.1
DB Server:
  • Windows Server 2008 R2 Enterprise SP1 (64 bit)
  • Oracle 11g R2
  • Hosts CLM DBs
  • Holds Insight DBs (content store, dataware house and ETL catalog)

3 answers

Most liked answers ↑|Newest answers|Oldest answers

1
permanent link
Donald Nong (14.5k●6●14) | answered Oct 04 '13, 1:19 a.m.
There should not be a significant change in terms of Oracle user privilege. So the information provided in the above link is sufficient. For schema that is not specifically mentioned in the above document, check whether there are application (Insight) related tables or views in the schema. If tables found, add the schema name in the first BEGIN block. If views found, add the schema name in the second BEGIN block as well.
0
permanent link
Amine Anouja (1●1●1) | answered Oct 22 '13, 3:12 a.m.
 Thanks Donald,

I just added RIBA in the list bellow and it worked for me.

FOR TABNAME IN (SELECT OWNER,TABLE_NAME FROM ALL_TABLES WHERE OWNER IN ('RIODS','RIDW','CONFIG','RIASSET','RISCHK','RICALM', 'RIBA'))
But DBA guy now is not happy with other privilges. He sais that they are not recommended by Oracle and they breaks corporate security policies within the company. He does not like the the term "ANY". "ANY" means you have privileges on all schemas within the same oracle instance, even those that belongs to other applications sharing the same instance as Insight. He alos does not like "WITH ADMIN OPTION"

Therefore, the following privileges are not suitables:

GRANT SELECT ANY TABLE TO RIDW WITH ADMIN OPTION;
GRANT SELECT ANY TABLE TO RICALM WITH ADMIN OPTION;
GRANT ANALYZE ANY TO <etl db user>;


Do you have a solution to replace those privileges by removing the "ANY" term?

Thanks.

Comments
Donald Nong commented Oct 22 '13, 8:09 p.m.

GRANT SELECT ANY TABLE is a system privilege and needs to be revoked completely if your DBA does not like it.
To grant object privilege as an alternative (select on a particular table), which is the same as you have done in the "for tablename" loop, you need to include all the tables owned by all the CLM users, and there is a crucial assumption - the CLM users do not access any tables not owned by themselves (particularly system tables), which I am not 100% sure about.

0
permanent link
Amine Anouja (1●1●1) | answered Oct 23 '13, 10:23 a.m.
Thanks Donald,

Regarding your sentence " the CLM users do not access any tables not owned by themselves (particularly system tables), which I am not 100% sure about. "

I think that is the purpose of "GRANT ANALYZE ANY TO <etl db="" user="">;" I know that Data Manager needs to list available schemas, schemas list can only obtained from system tables. it's why, I think, "GRANT ANALYZE ANY TO <etl db="" user="">;" is needed.

what about "WITH ADMIN OPTION"?
According to the jazz wiki article above, "WITH ADMIN OPTION" is used, do we really need it ? From I have read on oracle documentation, "WITH ADMIN OPTION" will allow the user granted the privilege to grant that privilege to other users. This means that "GRANT SELECT ANY TABLE TO RIDW WITH ADMIN OPTION;" will allow RIDW  user to grant "SELECT ANY TABLE" privilege to any other user. Do we really need it ?

Comments
Donald Nong commented Oct 24 '13, 11:24 p.m.

I don't really see any need for the "with admin option" clause. If other users require such privilege, the document should clearly say so.

Your answer

Register or to post your answer.


Dashboards and work items are no longer publicly available, so some links may be invalid. We now provide similar information through other means. Learn more here.