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
