Tuesday, October 3, 2017, 5:02:21 AM, Pierre wrote:

> I want to add readonly user creation/granting after database initialisation.

> I create the database using a bunch of SQL scripts. After the last
> script runs, I create a user using "create user MYUSER password
> 'myPassword'" the I grant this user read only (select, references)
> access to every table of the database using EXECUTE BLOCK :

> https://gist.github.com/zedalaye/6300b679e56349c40b973d35bb4c91ee


> On Transaction Commit, I get this error :

> add record error violation of PRIMARY or UNIQUE KEY constraint
> "INTEG_2" on table "PLG$USERS" Problematic key value is
> ("PLG$USER_NAME" = 'RO_USER') Unsuccessful execution caused by
> system error that does not preclude succe ssful execution of
> subsequent statements GDS Code: 336723987 - SQL Code: -901 - Error Code: 19.

CREATE USER adds users to the security database, whichever one you have
configured as security database. The key violation occurs because
either (1) you already created that user using that plug-in or (2) the
user does not exist.

Regarding (1), you can have more than one user having the same user
name as long as they are created using different plug-ins. For your
scripts it would be wise to specify the plug-in explicitly with a
USING PLUGIN clause, even if you want to use the default plug-in.

Regarding (2), remember that the CREATE USER command is run from the
connection to the application database, but under the surface, the
instructions are applied to PLG$USERS in the security database. Your
GRANT commands apply to the application database. The CREATE (ALTER/DROP)
USER command needs to be committed for its effects to become visible
for reference by statements affecting the application database.

If your problem is caused by (2), I think you will need to isolate
your CREATE USER statement(s) into a different SP, to be run and
committed in a separate transaction, before you run the script
assigning the permissions in the application database.

Helen