[Info-ingres] Create user nosecurity_audit

[Paul White]

Hi all,
A curiosity discovered after migrating to 10.2.
It is part of an routine to copy production data to a reporting location.
The script runs as a DBA user and fails when loading users into iidbdb.
 
sql iidbdb < users.sql
CREATE USER blah1 WITH NOPROFILE, NOGROUP, NOPRIVILEGES,
DEFAULT_PRIVILEGES=all, NOEXPIRE_DATE, NOSECURITY_AUDIT;
Executing . . .

E_US18D3 You do not have privileges to issue 'CREATE/DROP USER/PROFILE'
statements.
Please contact your System Administrator for further assistance.

Same outcome with 
sql -uingres iidbdb < users.sql
sql -u"$ingres" iidbdb < users.sql

We discovered the DBA user needs MAINTAIN_AUDIT privilege.

It fails first time with E_US18D3.  Restarting the session enables the
MAINTAIN_AUDIT privilege.
I have not found a way to do this in a single script.  Set session
authorization did not help.


sql iidbdb < fixpriv.sql
alter USER pwhite add privileges (MAINTAIN_AUDIT);
\q

sql iidbdb < users.sql
CREATE USER blah1 WITH NOPROFILE, NOGROUP, NOPRIVILEGES,
DEFAULT_PRIVILEGES=all, NOEXPIRE_DATE, NOSECURITY_AUDIT;
continue


&
Paul