Re: java.sql.SQLException: DROP USER: 'kxn_user' owns a schema

18 Aug 2020

      Hi Walter,

The user kxn_user is set as the authorization of schema kxn_schema in 
your CREATE SCHEMA kxn_schema AUTHORIZATION kxn_user.
Dropping the user would create an invalid reference from schema 
kxn_schema, which is not allowed due to referential integrity.
You would need to associate a different authorization/owner to schema 
kxn_schema before dropping the user kxn_user. Unfortunately there is no 
SQL command (something like ALTER SCHEMA kxn_schema OWNER TO monetdb) in 
MonetDB for this available yet.

What you can do instead is:
ALTER USER kxn_user SET SCHEMA sys;

DROP SCHEMA kxn_schema;
-- assuming no objects are associated the schema kxn_schema yet

CREATE SCHEMA kxn_schema AUTHORIZATION monetdb;
-- restore the dropped schema but with a different authorization

-- now you can execute
DROP USER kxn_user;

Regards,
Martin van Dinther

On 18-08-2020 10:07, Walter Weinmann wrote:
...
*The following SQL statements are executed successfully:
*
SELECT count(*) FROM sys.users WHERE name = 'kxn_user'
    DROP SCHEMA IF EXISTS kxn_schema CASCADE
    CREATE USER kxn_user WITH UNENCRYPTED PASSWORD 'monetdb' NAME
    'Dbseeder User' SCHEMA sys
    CREATE SCHEMA kxn_schema AUTHORIZATION kxn_user
    ALTER USER kxn_user SET SCHEMA kxn_schema
    CREATE TABLE COUNTRY (
         PK_COUNTRY_ID                    BIGINT                    NOT NULL
 PRIMARY KEY,
         COUNTRY_MAP                      BLOB,
         CREATED                          TIMESTAMP                 NOT
    NULL,
         ISO3166                          VARCHAR(50),
         MODIFIED                         TIMESTAMP,
         NAME                             VARCHAR(100)              NOT NULL
                                                                    UNIQUE
    )
*The following SQL statement produces an error message:*
DROP USER kxn_user
java.sql.SQLException: DROP USER: 'kxn_user' owns a schema
         at 
nl.cwi.monetdb.jdbc.MonetConnection$ResponseList.executeQuery(Unknown 
Source)
         at 
nl.cwi.monetdb.jdbc.MonetConnection$ResponseList.processQuery(Unknown 
Source)
         at nl.cwi.monetdb.jdbc.MonetStatement.internalExecute(Unknown 
Source)
         at nl.cwi.monetdb.jdbc.MonetStatement.execute(Unknown Source)
         at 
ch.konnexions.db_seeder.jdbc.AbstractJdbcSeeder.dropUser(AbstractJdbcSeeder.java:814)
         at 
ch.konnexions.db_seeder.jdbc.monetdb.MonetdbSeeder.setupDatabase(MonetdbSeeder.java:103)
         at 
ch.konnexions.db_seeder.jdbc.AbstractJdbcSeeder.createData(AbstractJdbcSeeder.java:359)
         at 
ch.konnexions.db_seeder.DatabaseSeeder.main(DatabaseSeeder.java:141)
Processing of the script was aborted, error code=1
*How can I delete the user in this constellation without errors ?
*
_______________________________________________
users-list mailing list
users-list@monetdb.org
https://www.monetdb.org/mailman/listinfo/users-list