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

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);

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);