Updating Users in Enmasse

Overview

Two circumstances prompt mass updates to user privileges in the ZUAUSR tables: 1) A Change is made in the structure of ZUAUSR classes for the sake of better management and 2) Banner functionality has increased and all users must be retroactively granted those roles in Banner as well as recording the same in ZUAUSR.

Structural Changes Made in the ZUAUSR Tables

Banner Functionality Necessitates Increased Roles

The process required to add scripts to ZUAUSR is discussed in Creating, Modifying, or Deleting ZUAUSR Processing Scripts. The process to add new Banner classes to ZUAUSR is discussed at Integrating a New Banner Role into ZUAUSR. To get a list of users needing the new class added in ZUAUSR, you can execute the query and select just the usernames. I'd suggest verifying that there are no unexpected IDs returned before adding the new class in ZUAUSR.

select username
from dba_users
where username not in
(select grantee from dba_role_privs where granted_role='TERMINATED')
and profile='USER_GENERAL'
and username not in ('BFINMGR','SYAUD01','SYFINEV')
and username not in (select client from sys.proxy_users);

To add the privilege in Banner:

select 'alter user ' || username || ' grant connect through banproxy;'
from dba_users
where username not in
(select grantee from dba_role_privs where granted_role='TERMINATED')
and profile='USER_GENERAL'
and username not in ('BFINMGR','SYAUD01','SYFINEV')
and username not in (select client from sys.proxy_users);