How to create an Oracle database user with limited access to the AVUSER schema in RSA Identity Governance & Lifecycle

[#1]

Article Number

000028811

Applies To

RSA Product Set: RSA Identity Governance & Lifecycle
RSA Version/Condition: All
 

Issue

This RSA Knowledge Base Article explains how to create a new Oracle database user with limited and specifically defined access to information in the AVUSER schema.
 

Resolution

In this example a new user called ACMREPORTER is created with read only access to the first and last name fields of user records in the T_MASTER_ENTERPRISE_USERS table.

1. Login to SQL as user SYS.
2. Create the ACMREPORTER user.

create user ACMREPORTER identified by ACMREPORTER;
grant connect to ACMREPORTER;
grant create session to ACMREPORTER;

3. Create view TMEU_ACM_USERS.

create or replace view TMEU_ACM_USERS as
select first_name, last_name from T_MASTER_ENTERPRISE_USERS;
create public synonym TMEU_ACM_USERS for AVUSER.TMEU_ACM_USERS;

Repeat this step for every table/view to which ACMREPORTER should have access.

4. Grant SELECT on the new view TMEU_ACM_USERS to the new user ACMREPORTER.

grant select on TMEU_ACM_USERS to ACMREPORTER;

Repeat this step for every every table/view to which ACMREPORTER should have access.

5. Test that ACMREPORTER can execute the following query:

select * from TMEU_ACM_USERS;