Minimum required Oracle privileges for Insight to work
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:
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
- Windows Server 2008 R2 Enterprise Edition SP1 (64 bit)
- WebSphere Application Server v7.0 and fix pack 19
- Insight 1.1.1.1
- 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
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.
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
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.
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 ?