Security needed to view column definitions in _SYS_BIC

[patrickbachmann]

Hi folks,

I have a developer that when they try to go into _SYS_BIC and look at COLUMN VIEWS that are generated from views that they themselves have created, they can not right click on them and choose OPEN DEFINITION.  They get an error that they don't have the needed privilege.  I however am able to do this so I experimented with all of my roles until I determined that it seems the MONITORING role is allowing me to view this.  Now analyzing the MONITORING role I believe it's the system privilege called 'CATALOG READ' that is responsible for allowing this.  However I can't prove this theory because I'm not authorized to assign CATALOG READ directly to myself (ie: just the system privilege without assigning MONITORING role which also has other elements). 

Can somebody confirm if CATALOG READ is indeed what allows user to click OPEN DEFINITION on column views?  And also are you aware of any other role besides MONITORING to provide this access?

Thanks!

-Patrick

[rindia · Accepted solution]

Hi Patrick,

To test your scenario, i created one new user (user1) with the help of system user and assigned the below privileges to user1:

Granted Roles: Modeling, Public

SQL Privileges: _SYS_BI, _SYS_BIC, REPOSITORY_REST(SYS) (Execute, Select)

System Privileges: CATALOG READ

Package Privileges: pkg_name

          REPO.READ:  Read access to the selected package (both native and imported).

          REPO.EDIT_NATIVE_OBJECTS:  Authorization to modify objects in packages originating in the system the user is working in.

          REPO.ACTIVATE_NATIVE_OBJECTS:  Authorization to activate/reactivate objects in package the user is working in.

          REPO.MAINTAIN_NATIVE_PACKAGES: Authorization to update or delete native packages or  create sub-packages of packages the user is working.

With the above privileges, user1 can

1. Expand _sys_bic schema and can do 'Open definition'

2. Can create information views in his pkg (pkg_name) only

3. Can validate and activate the views and can even delete.

4. Can create sub-packages in his pkg and at the same time can delete the package if it is empty.

For your user, check whether system privilege CATALOGUE_READ, is check box ticked "Grantable to other Users and Roles".

Also please be informed that instead of assigning privileges to directly users, there is also one more way to create Roles with required privileges and assign that role to developers. This will be easy to maintain

Also let me know is there any other privileges issues are arising.

Regards

Raj

[patrickbachmann · Accepted solution]

Raj, thanks for the response.  I had our security guy (whom has more privileges than myself) do a test and assign CATALOG READ directly to the user along with _SYS_BI, _SYS_BIC, REPOSITORY_REST etc and it worked. 

Thanks,

-Patrick

[rindia · Accepted solution]

Good to know and thanks for informing Patrick