Mercurial > hg > MonetDB
changeset 86128:48c729f50515 userprofile
Approvals on "weird" systems.
line wrap: on
line diff
--- a/sql/test/emptydb-previous-upgrade-chain-hge/Tests/upgrade.stable.out.ppc64.int128 +++ b/sql/test/emptydb-previous-upgrade-chain-hge/Tests/upgrade.stable.out.ppc64.int128 @@ -4087,6 +4087,34 @@ update sys._tables set system = true whe update sys.functions set system = true where system <> true and schema_id = 2000 and name in ('analyze','statistics'); Running database upgrade commands: +alter table sys.db_user_info add column max_memory bigint; +alter table sys.db_user_info add column max_workers int; +alter table sys.db_user_info add column optimizer varchar(1024); +alter table sys.db_user_info add column default_role int; +alter table sys.db_user_info add column password varchar(256); +update sys.db_user_info u set max_memory = 0, max_workers = 0, optimizer = 'default_pipe', default_role = (select id from sys.auths a where a.name = u.name); +-- and copying passwords + +Running database upgrade commands: +drop view sys.dependency_schemas_on_users; +drop view sys.roles; +drop view sys.users; +drop function sys.db_users(); +CREATE VIEW sys.roles AS SELECT id, name, grantor FROM sys.auths a WHERE a.name NOT IN (SELECT u.name FROM sys.db_user_info u); +GRANT SELECT ON sys.roles TO PUBLIC; +CREATE VIEW sys.users AS SELECT name, fullname, default_schema, schema_path, max_memory, max_workers, optimizer, default_role FROM sys.db_user_info; +GRANT SELECT ON sys.users TO PUBLIC; +CREATE FUNCTION sys.db_users() RETURNS TABLE(name varchar(2048)) RETURN SELECT name FROM sys.db_user_info; +CREATE VIEW sys.dependency_schemas_on_users AS +SELECT s.id AS schema_id, s.name AS schema_name, u.name AS user_name, CAST(6 AS smallint) AS depend_type + FROM sys.db_user_info AS u, sys.schemas AS s + WHERE u.default_schema = s.id + ORDER BY s.name, u.name; +GRANT SELECT ON sys.dependency_schemas_on_users TO PUBLIC; +update sys._tables set system = true where name in ('users', 'roles', 'dependency_schemas_on_users') AND schema_id = 2000; +update sys.functions set system = true where system <> true and name in ('db_users') and schema_id = 2000 and type = 5; + +Running database upgrade commands: drop function sys.dump_database(boolean); drop procedure sys.dump_table_data(); drop procedure sys.dump_table_data(string, string); @@ -4096,6 +4124,7 @@ drop view sys.dump_sequences; drop view sys.dump_start_sequences; drop view sys.dump_tables; drop view sys.describe_tables; +drop view sys.dump_create_users; CREATE VIEW sys.describe_partition_tables AS SELECT m_sch, @@ -4240,6 +4269,16 @@ BEGIN END WHILE; END IF; END; +CREATE VIEW sys.dump_create_users AS + SELECT + 'CREATE USER ' || sys.dq(ui.name) || ' WITH ENCRYPTED PASSWORD ' || + sys.sq(sys.password_hash(ui.name)) || + ' NAME ' || sys.sq(ui.fullname) || ' SCHEMA sys' || ifthenelse(ui.schema_path = '"sys"', '', ' SCHEMA PATH ' || sys.sq(ui.schema_path)) || ';' stmt, + ui.name user_name + FROM sys.db_user_info ui, sys.schemas s + WHERE ui.default_schema = s.id + AND ui.name <> 'monetdb' + AND ui.name <> '.snapshot'; CREATE VIEW sys.describe_tables AS SELECT t.id o, @@ -4325,7 +4364,7 @@ BEGIN INSERT INTO sys.dump_statements VALUES ((SELECT COUNT(*) FROM sys.dump_statements) + 1, 'COMMIT;'); RETURN sys.dump_statements; END; -update sys._tables set system = true where name in ('describe_partition_tables', 'dump_partition_tables', 'dump_sequences', 'dump_start_sequences', 'describe_tables', 'dump_tables') AND schema_id = 2000; +update sys._tables set system = true where name in ('describe_partition_tables', 'dump_partition_tables', 'dump_sequences', 'dump_start_sequences', 'describe_tables', 'dump_tables', 'dump_create_users') AND schema_id = 2000; update sys.functions set system = true where system <> true and name in ('dump_table_data') and schema_id = 2000 and type = 2; update sys.functions set system = true where system <> true and name in ('dump_database') and schema_id = 2000 and type = 5; CREATE function sys.url_extract_host(url string, no_www bool) RETURNS STRING
--- a/sql/test/emptydb-previous-upgrade-chain/Tests/upgrade.stable.out +++ b/sql/test/emptydb-previous-upgrade-chain/Tests/upgrade.stable.out @@ -3740,6 +3740,34 @@ update sys._tables set system = true whe update sys.functions set system = true where system <> true and schema_id = 2000 and name in ('analyze','statistics'); Running database upgrade commands: +alter table sys.db_user_info add column max_memory bigint; +alter table sys.db_user_info add column max_workers int; +alter table sys.db_user_info add column optimizer varchar(1024); +alter table sys.db_user_info add column default_role int; +alter table sys.db_user_info add column password varchar(256); +update sys.db_user_info u set max_memory = 0, max_workers = 0, optimizer = 'default_pipe', default_role = (select id from sys.auths a where a.name = u.name); +-- and copying passwords + +Running database upgrade commands: +drop view sys.dependency_schemas_on_users; +drop view sys.roles; +drop view sys.users; +drop function sys.db_users(); +CREATE VIEW sys.roles AS SELECT id, name, grantor FROM sys.auths a WHERE a.name NOT IN (SELECT u.name FROM sys.db_user_info u); +GRANT SELECT ON sys.roles TO PUBLIC; +CREATE VIEW sys.users AS SELECT name, fullname, default_schema, schema_path, max_memory, max_workers, optimizer, default_role FROM sys.db_user_info; +GRANT SELECT ON sys.users TO PUBLIC; +CREATE FUNCTION sys.db_users() RETURNS TABLE(name varchar(2048)) RETURN SELECT name FROM sys.db_user_info; +CREATE VIEW sys.dependency_schemas_on_users AS +SELECT s.id AS schema_id, s.name AS schema_name, u.name AS user_name, CAST(6 AS smallint) AS depend_type + FROM sys.db_user_info AS u, sys.schemas AS s + WHERE u.default_schema = s.id + ORDER BY s.name, u.name; +GRANT SELECT ON sys.dependency_schemas_on_users TO PUBLIC; +update sys._tables set system = true where name in ('users', 'roles', 'dependency_schemas_on_users') AND schema_id = 2000; +update sys.functions set system = true where system <> true and name in ('db_users') and schema_id = 2000 and type = 5; + +Running database upgrade commands: drop function sys.dump_database(boolean); drop procedure sys.dump_table_data(); drop procedure sys.dump_table_data(string, string); @@ -3749,6 +3777,7 @@ drop view sys.dump_sequences; drop view sys.dump_start_sequences; drop view sys.dump_tables; drop view sys.describe_tables; +drop view sys.dump_create_users; CREATE VIEW sys.describe_partition_tables AS SELECT m_sch, @@ -3893,6 +3922,16 @@ BEGIN END WHILE; END IF; END; +CREATE VIEW sys.dump_create_users AS + SELECT + 'CREATE USER ' || sys.dq(ui.name) || ' WITH ENCRYPTED PASSWORD ' || + sys.sq(sys.password_hash(ui.name)) || + ' NAME ' || sys.sq(ui.fullname) || ' SCHEMA sys' || ifthenelse(ui.schema_path = '"sys"', '', ' SCHEMA PATH ' || sys.sq(ui.schema_path)) || ';' stmt, + ui.name user_name + FROM sys.db_user_info ui, sys.schemas s + WHERE ui.default_schema = s.id + AND ui.name <> 'monetdb' + AND ui.name <> '.snapshot'; CREATE VIEW sys.describe_tables AS SELECT t.id o, @@ -3978,7 +4017,7 @@ BEGIN INSERT INTO sys.dump_statements VALUES ((SELECT COUNT(*) FROM sys.dump_statements) + 1, 'COMMIT;'); RETURN sys.dump_statements; END; -update sys._tables set system = true where name in ('describe_partition_tables', 'dump_partition_tables', 'dump_sequences', 'dump_start_sequences', 'describe_tables', 'dump_tables') AND schema_id = 2000; +update sys._tables set system = true where name in ('describe_partition_tables', 'dump_partition_tables', 'dump_sequences', 'dump_start_sequences', 'describe_tables', 'dump_tables', 'dump_create_users') AND schema_id = 2000; update sys.functions set system = true where system <> true and name in ('dump_table_data') and schema_id = 2000 and type = 2; update sys.functions set system = true where system <> true and name in ('dump_database') and schema_id = 2000 and type = 5; CREATE function sys.url_extract_host(url string, no_www bool) RETURNS STRING
--- a/sql/test/emptydb-previous-upgrade-chain/Tests/upgrade.stable.out.32bit +++ b/sql/test/emptydb-previous-upgrade-chain/Tests/upgrade.stable.out.32bit @@ -3740,6 +3740,34 @@ update sys._tables set system = true whe update sys.functions set system = true where system <> true and schema_id = 2000 and name in ('analyze','statistics'); Running database upgrade commands: +alter table sys.db_user_info add column max_memory bigint; +alter table sys.db_user_info add column max_workers int; +alter table sys.db_user_info add column optimizer varchar(1024); +alter table sys.db_user_info add column default_role int; +alter table sys.db_user_info add column password varchar(256); +update sys.db_user_info u set max_memory = 0, max_workers = 0, optimizer = 'default_pipe', default_role = (select id from sys.auths a where a.name = u.name); +-- and copying passwords + +Running database upgrade commands: +drop view sys.dependency_schemas_on_users; +drop view sys.roles; +drop view sys.users; +drop function sys.db_users(); +CREATE VIEW sys.roles AS SELECT id, name, grantor FROM sys.auths a WHERE a.name NOT IN (SELECT u.name FROM sys.db_user_info u); +GRANT SELECT ON sys.roles TO PUBLIC; +CREATE VIEW sys.users AS SELECT name, fullname, default_schema, schema_path, max_memory, max_workers, optimizer, default_role FROM sys.db_user_info; +GRANT SELECT ON sys.users TO PUBLIC; +CREATE FUNCTION sys.db_users() RETURNS TABLE(name varchar(2048)) RETURN SELECT name FROM sys.db_user_info; +CREATE VIEW sys.dependency_schemas_on_users AS +SELECT s.id AS schema_id, s.name AS schema_name, u.name AS user_name, CAST(6 AS smallint) AS depend_type + FROM sys.db_user_info AS u, sys.schemas AS s + WHERE u.default_schema = s.id + ORDER BY s.name, u.name; +GRANT SELECT ON sys.dependency_schemas_on_users TO PUBLIC; +update sys._tables set system = true where name in ('users', 'roles', 'dependency_schemas_on_users') AND schema_id = 2000; +update sys.functions set system = true where system <> true and name in ('db_users') and schema_id = 2000 and type = 5; + +Running database upgrade commands: drop function sys.dump_database(boolean); drop procedure sys.dump_table_data(); drop procedure sys.dump_table_data(string, string); @@ -3749,6 +3777,7 @@ drop view sys.dump_sequences; drop view sys.dump_start_sequences; drop view sys.dump_tables; drop view sys.describe_tables; +drop view sys.dump_create_users; CREATE VIEW sys.describe_partition_tables AS SELECT m_sch, @@ -3893,6 +3922,16 @@ BEGIN END WHILE; END IF; END; +CREATE VIEW sys.dump_create_users AS + SELECT + 'CREATE USER ' || sys.dq(ui.name) || ' WITH ENCRYPTED PASSWORD ' || + sys.sq(sys.password_hash(ui.name)) || + ' NAME ' || sys.sq(ui.fullname) || ' SCHEMA sys' || ifthenelse(ui.schema_path = '"sys"', '', ' SCHEMA PATH ' || sys.sq(ui.schema_path)) || ';' stmt, + ui.name user_name + FROM sys.db_user_info ui, sys.schemas s + WHERE ui.default_schema = s.id + AND ui.name <> 'monetdb' + AND ui.name <> '.snapshot'; CREATE VIEW sys.describe_tables AS SELECT t.id o, @@ -3978,7 +4017,7 @@ BEGIN INSERT INTO sys.dump_statements VALUES ((SELECT COUNT(*) FROM sys.dump_statements) + 1, 'COMMIT;'); RETURN sys.dump_statements; END; -update sys._tables set system = true where name in ('describe_partition_tables', 'dump_partition_tables', 'dump_sequences', 'dump_start_sequences', 'describe_tables', 'dump_tables') AND schema_id = 2000; +update sys._tables set system = true where name in ('describe_partition_tables', 'dump_partition_tables', 'dump_sequences', 'dump_start_sequences', 'describe_tables', 'dump_tables', 'dump_create_users') AND schema_id = 2000; update sys.functions set system = true where system <> true and name in ('dump_table_data') and schema_id = 2000 and type = 2; update sys.functions set system = true where system <> true and name in ('dump_database') and schema_id = 2000 and type = 5; CREATE function sys.url_extract_host(url string, no_www bool) RETURNS STRING
--- a/sql/test/emptydb-previous-upgrade-chain/Tests/upgrade.stable.out.ppc64 +++ b/sql/test/emptydb-previous-upgrade-chain/Tests/upgrade.stable.out.ppc64 @@ -3740,6 +3740,34 @@ update sys._tables set system = true whe update sys.functions set system = true where system <> true and schema_id = 2000 and name in ('analyze','statistics'); Running database upgrade commands: +alter table sys.db_user_info add column max_memory bigint; +alter table sys.db_user_info add column max_workers int; +alter table sys.db_user_info add column optimizer varchar(1024); +alter table sys.db_user_info add column default_role int; +alter table sys.db_user_info add column password varchar(256); +update sys.db_user_info u set max_memory = 0, max_workers = 0, optimizer = 'default_pipe', default_role = (select id from sys.auths a where a.name = u.name); +-- and copying passwords + +Running database upgrade commands: +drop view sys.dependency_schemas_on_users; +drop view sys.roles; +drop view sys.users; +drop function sys.db_users(); +CREATE VIEW sys.roles AS SELECT id, name, grantor FROM sys.auths a WHERE a.name NOT IN (SELECT u.name FROM sys.db_user_info u); +GRANT SELECT ON sys.roles TO PUBLIC; +CREATE VIEW sys.users AS SELECT name, fullname, default_schema, schema_path, max_memory, max_workers, optimizer, default_role FROM sys.db_user_info; +GRANT SELECT ON sys.users TO PUBLIC; +CREATE FUNCTION sys.db_users() RETURNS TABLE(name varchar(2048)) RETURN SELECT name FROM sys.db_user_info; +CREATE VIEW sys.dependency_schemas_on_users AS +SELECT s.id AS schema_id, s.name AS schema_name, u.name AS user_name, CAST(6 AS smallint) AS depend_type + FROM sys.db_user_info AS u, sys.schemas AS s + WHERE u.default_schema = s.id + ORDER BY s.name, u.name; +GRANT SELECT ON sys.dependency_schemas_on_users TO PUBLIC; +update sys._tables set system = true where name in ('users', 'roles', 'dependency_schemas_on_users') AND schema_id = 2000; +update sys.functions set system = true where system <> true and name in ('db_users') and schema_id = 2000 and type = 5; + +Running database upgrade commands: drop function sys.dump_database(boolean); drop procedure sys.dump_table_data(); drop procedure sys.dump_table_data(string, string); @@ -3749,6 +3777,7 @@ drop view sys.dump_sequences; drop view sys.dump_start_sequences; drop view sys.dump_tables; drop view sys.describe_tables; +drop view sys.dump_create_users; CREATE VIEW sys.describe_partition_tables AS SELECT m_sch, @@ -3893,6 +3922,16 @@ BEGIN END WHILE; END IF; END; +CREATE VIEW sys.dump_create_users AS + SELECT + 'CREATE USER ' || sys.dq(ui.name) || ' WITH ENCRYPTED PASSWORD ' || + sys.sq(sys.password_hash(ui.name)) || + ' NAME ' || sys.sq(ui.fullname) || ' SCHEMA sys' || ifthenelse(ui.schema_path = '"sys"', '', ' SCHEMA PATH ' || sys.sq(ui.schema_path)) || ';' stmt, + ui.name user_name + FROM sys.db_user_info ui, sys.schemas s + WHERE ui.default_schema = s.id + AND ui.name <> 'monetdb' + AND ui.name <> '.snapshot'; CREATE VIEW sys.describe_tables AS SELECT t.id o, @@ -3978,7 +4017,7 @@ BEGIN INSERT INTO sys.dump_statements VALUES ((SELECT COUNT(*) FROM sys.dump_statements) + 1, 'COMMIT;'); RETURN sys.dump_statements; END; -update sys._tables set system = true where name in ('describe_partition_tables', 'dump_partition_tables', 'dump_sequences', 'dump_start_sequences', 'describe_tables', 'dump_tables') AND schema_id = 2000; +update sys._tables set system = true where name in ('describe_partition_tables', 'dump_partition_tables', 'dump_sequences', 'dump_start_sequences', 'describe_tables', 'dump_tables', 'dump_create_users') AND schema_id = 2000; update sys.functions set system = true where system <> true and name in ('dump_table_data') and schema_id = 2000 and type = 2; update sys.functions set system = true where system <> true and name in ('dump_database') and schema_id = 2000 and type = 5; CREATE function sys.url_extract_host(url string, no_www bool) RETURNS STRING
--- a/sql/test/emptydb-previous-upgrade-chain/Tests/upgrade.stable.out.ppc64.int128 +++ b/sql/test/emptydb-previous-upgrade-chain/Tests/upgrade.stable.out.ppc64.int128 @@ -4156,6 +4156,34 @@ update sys._tables set system = true whe update sys.functions set system = true where system <> true and schema_id = 2000 and name in ('analyze','statistics'); Running database upgrade commands: +alter table sys.db_user_info add column max_memory bigint; +alter table sys.db_user_info add column max_workers int; +alter table sys.db_user_info add column optimizer varchar(1024); +alter table sys.db_user_info add column default_role int; +alter table sys.db_user_info add column password varchar(256); +update sys.db_user_info u set max_memory = 0, max_workers = 0, optimizer = 'default_pipe', default_role = (select id from sys.auths a where a.name = u.name); +-- and copying passwords + +Running database upgrade commands: +drop view sys.dependency_schemas_on_users; +drop view sys.roles; +drop view sys.users; +drop function sys.db_users(); +CREATE VIEW sys.roles AS SELECT id, name, grantor FROM sys.auths a WHERE a.name NOT IN (SELECT u.name FROM sys.db_user_info u); +GRANT SELECT ON sys.roles TO PUBLIC; +CREATE VIEW sys.users AS SELECT name, fullname, default_schema, schema_path, max_memory, max_workers, optimizer, default_role FROM sys.db_user_info; +GRANT SELECT ON sys.users TO PUBLIC; +CREATE FUNCTION sys.db_users() RETURNS TABLE(name varchar(2048)) RETURN SELECT name FROM sys.db_user_info; +CREATE VIEW sys.dependency_schemas_on_users AS +SELECT s.id AS schema_id, s.name AS schema_name, u.name AS user_name, CAST(6 AS smallint) AS depend_type + FROM sys.db_user_info AS u, sys.schemas AS s + WHERE u.default_schema = s.id + ORDER BY s.name, u.name; +GRANT SELECT ON sys.dependency_schemas_on_users TO PUBLIC; +update sys._tables set system = true where name in ('users', 'roles', 'dependency_schemas_on_users') AND schema_id = 2000; +update sys.functions set system = true where system <> true and name in ('db_users') and schema_id = 2000 and type = 5; + +Running database upgrade commands: drop function sys.dump_database(boolean); drop procedure sys.dump_table_data(); drop procedure sys.dump_table_data(string, string); @@ -4165,6 +4193,7 @@ drop view sys.dump_sequences; drop view sys.dump_start_sequences; drop view sys.dump_tables; drop view sys.describe_tables; +drop view sys.dump_create_users; CREATE VIEW sys.describe_partition_tables AS SELECT m_sch, @@ -4309,6 +4338,16 @@ BEGIN END WHILE; END IF; END; +CREATE VIEW sys.dump_create_users AS + SELECT + 'CREATE USER ' || sys.dq(ui.name) || ' WITH ENCRYPTED PASSWORD ' || + sys.sq(sys.password_hash(ui.name)) || + ' NAME ' || sys.sq(ui.fullname) || ' SCHEMA sys' || ifthenelse(ui.schema_path = '"sys"', '', ' SCHEMA PATH ' || sys.sq(ui.schema_path)) || ';' stmt, + ui.name user_name + FROM sys.db_user_info ui, sys.schemas s + WHERE ui.default_schema = s.id + AND ui.name <> 'monetdb' + AND ui.name <> '.snapshot'; CREATE VIEW sys.describe_tables AS SELECT t.id o, @@ -4394,7 +4433,7 @@ BEGIN INSERT INTO sys.dump_statements VALUES ((SELECT COUNT(*) FROM sys.dump_statements) + 1, 'COMMIT;'); RETURN sys.dump_statements; END; -update sys._tables set system = true where name in ('describe_partition_tables', 'dump_partition_tables', 'dump_sequences', 'dump_start_sequences', 'describe_tables', 'dump_tables') AND schema_id = 2000; +update sys._tables set system = true where name in ('describe_partition_tables', 'dump_partition_tables', 'dump_sequences', 'dump_start_sequences', 'describe_tables', 'dump_tables', 'dump_create_users') AND schema_id = 2000; update sys.functions set system = true where system <> true and name in ('dump_table_data') and schema_id = 2000 and type = 2; update sys.functions set system = true where system <> true and name in ('dump_database') and schema_id = 2000 and type = 5; CREATE function sys.url_extract_host(url string, no_www bool) RETURNS STRING
--- a/sql/test/emptydb-previous-upgrade/Tests/upgrade.stable.out +++ b/sql/test/emptydb-previous-upgrade/Tests/upgrade.stable.out @@ -3734,6 +3734,34 @@ update sys._tables set system = true whe update sys.functions set system = true where system <> true and schema_id = 2000 and name in ('analyze','statistics'); Running database upgrade commands: +alter table sys.db_user_info add column max_memory bigint; +alter table sys.db_user_info add column max_workers int; +alter table sys.db_user_info add column optimizer varchar(1024); +alter table sys.db_user_info add column default_role int; +alter table sys.db_user_info add column password varchar(256); +update sys.db_user_info u set max_memory = 0, max_workers = 0, optimizer = 'default_pipe', default_role = (select id from sys.auths a where a.name = u.name); +-- and copying passwords + +Running database upgrade commands: +drop view sys.dependency_schemas_on_users; +drop view sys.roles; +drop view sys.users; +drop function sys.db_users(); +CREATE VIEW sys.roles AS SELECT id, name, grantor FROM sys.auths a WHERE a.name NOT IN (SELECT u.name FROM sys.db_user_info u); +GRANT SELECT ON sys.roles TO PUBLIC; +CREATE VIEW sys.users AS SELECT name, fullname, default_schema, schema_path, max_memory, max_workers, optimizer, default_role FROM sys.db_user_info; +GRANT SELECT ON sys.users TO PUBLIC; +CREATE FUNCTION sys.db_users() RETURNS TABLE(name varchar(2048)) RETURN SELECT name FROM sys.db_user_info; +CREATE VIEW sys.dependency_schemas_on_users AS +SELECT s.id AS schema_id, s.name AS schema_name, u.name AS user_name, CAST(6 AS smallint) AS depend_type + FROM sys.db_user_info AS u, sys.schemas AS s + WHERE u.default_schema = s.id + ORDER BY s.name, u.name; +GRANT SELECT ON sys.dependency_schemas_on_users TO PUBLIC; +update sys._tables set system = true where name in ('users', 'roles', 'dependency_schemas_on_users') AND schema_id = 2000; +update sys.functions set system = true where system <> true and name in ('db_users') and schema_id = 2000 and type = 5; + +Running database upgrade commands: drop function sys.dump_database(boolean); drop procedure sys.dump_table_data(); drop procedure sys.dump_table_data(string, string); @@ -3743,6 +3771,7 @@ drop view sys.dump_sequences; drop view sys.dump_start_sequences; drop view sys.dump_tables; drop view sys.describe_tables; +drop view sys.dump_create_users; CREATE VIEW sys.describe_partition_tables AS SELECT m_sch, @@ -3887,6 +3916,16 @@ BEGIN END WHILE; END IF; END; +CREATE VIEW sys.dump_create_users AS + SELECT + 'CREATE USER ' || sys.dq(ui.name) || ' WITH ENCRYPTED PASSWORD ' || + sys.sq(sys.password_hash(ui.name)) || + ' NAME ' || sys.sq(ui.fullname) || ' SCHEMA sys' || ifthenelse(ui.schema_path = '"sys"', '', ' SCHEMA PATH ' || sys.sq(ui.schema_path)) || ';' stmt, + ui.name user_name + FROM sys.db_user_info ui, sys.schemas s + WHERE ui.default_schema = s.id + AND ui.name <> 'monetdb' + AND ui.name <> '.snapshot'; CREATE VIEW sys.describe_tables AS SELECT t.id o, @@ -3972,7 +4011,7 @@ BEGIN INSERT INTO sys.dump_statements VALUES ((SELECT COUNT(*) FROM sys.dump_statements) + 1, 'COMMIT;'); RETURN sys.dump_statements; END; -update sys._tables set system = true where name in ('describe_partition_tables', 'dump_partition_tables', 'dump_sequences', 'dump_start_sequences', 'describe_tables', 'dump_tables') AND schema_id = 2000; +update sys._tables set system = true where name in ('describe_partition_tables', 'dump_partition_tables', 'dump_sequences', 'dump_start_sequences', 'describe_tables', 'dump_tables', 'dump_create_users') AND schema_id = 2000; update sys.functions set system = true where system <> true and name in ('dump_table_data') and schema_id = 2000 and type = 2; update sys.functions set system = true where system <> true and name in ('dump_database') and schema_id = 2000 and type = 5; CREATE function sys.url_extract_host(url string, no_www bool) RETURNS STRING
--- a/sql/test/emptydb-previous-upgrade/Tests/upgrade.stable.out.32bit +++ b/sql/test/emptydb-previous-upgrade/Tests/upgrade.stable.out.32bit @@ -3734,6 +3734,34 @@ update sys._tables set system = true whe update sys.functions set system = true where system <> true and schema_id = 2000 and name in ('analyze','statistics'); Running database upgrade commands: +alter table sys.db_user_info add column max_memory bigint; +alter table sys.db_user_info add column max_workers int; +alter table sys.db_user_info add column optimizer varchar(1024); +alter table sys.db_user_info add column default_role int; +alter table sys.db_user_info add column password varchar(256); +update sys.db_user_info u set max_memory = 0, max_workers = 0, optimizer = 'default_pipe', default_role = (select id from sys.auths a where a.name = u.name); +-- and copying passwords + +Running database upgrade commands: +drop view sys.dependency_schemas_on_users; +drop view sys.roles; +drop view sys.users; +drop function sys.db_users(); +CREATE VIEW sys.roles AS SELECT id, name, grantor FROM sys.auths a WHERE a.name NOT IN (SELECT u.name FROM sys.db_user_info u); +GRANT SELECT ON sys.roles TO PUBLIC; +CREATE VIEW sys.users AS SELECT name, fullname, default_schema, schema_path, max_memory, max_workers, optimizer, default_role FROM sys.db_user_info; +GRANT SELECT ON sys.users TO PUBLIC; +CREATE FUNCTION sys.db_users() RETURNS TABLE(name varchar(2048)) RETURN SELECT name FROM sys.db_user_info; +CREATE VIEW sys.dependency_schemas_on_users AS +SELECT s.id AS schema_id, s.name AS schema_name, u.name AS user_name, CAST(6 AS smallint) AS depend_type + FROM sys.db_user_info AS u, sys.schemas AS s + WHERE u.default_schema = s.id + ORDER BY s.name, u.name; +GRANT SELECT ON sys.dependency_schemas_on_users TO PUBLIC; +update sys._tables set system = true where name in ('users', 'roles', 'dependency_schemas_on_users') AND schema_id = 2000; +update sys.functions set system = true where system <> true and name in ('db_users') and schema_id = 2000 and type = 5; + +Running database upgrade commands: drop function sys.dump_database(boolean); drop procedure sys.dump_table_data(); drop procedure sys.dump_table_data(string, string); @@ -3743,6 +3771,7 @@ drop view sys.dump_sequences; drop view sys.dump_start_sequences; drop view sys.dump_tables; drop view sys.describe_tables; +drop view sys.dump_create_users; CREATE VIEW sys.describe_partition_tables AS SELECT m_sch, @@ -3887,6 +3916,16 @@ BEGIN END WHILE; END IF; END; +CREATE VIEW sys.dump_create_users AS + SELECT + 'CREATE USER ' || sys.dq(ui.name) || ' WITH ENCRYPTED PASSWORD ' || + sys.sq(sys.password_hash(ui.name)) || + ' NAME ' || sys.sq(ui.fullname) || ' SCHEMA sys' || ifthenelse(ui.schema_path = '"sys"', '', ' SCHEMA PATH ' || sys.sq(ui.schema_path)) || ';' stmt, + ui.name user_name + FROM sys.db_user_info ui, sys.schemas s + WHERE ui.default_schema = s.id + AND ui.name <> 'monetdb' + AND ui.name <> '.snapshot'; CREATE VIEW sys.describe_tables AS SELECT t.id o, @@ -3972,7 +4011,7 @@ BEGIN INSERT INTO sys.dump_statements VALUES ((SELECT COUNT(*) FROM sys.dump_statements) + 1, 'COMMIT;'); RETURN sys.dump_statements; END; -update sys._tables set system = true where name in ('describe_partition_tables', 'dump_partition_tables', 'dump_sequences', 'dump_start_sequences', 'describe_tables', 'dump_tables') AND schema_id = 2000; +update sys._tables set system = true where name in ('describe_partition_tables', 'dump_partition_tables', 'dump_sequences', 'dump_start_sequences', 'describe_tables', 'dump_tables', 'dump_create_users') AND schema_id = 2000; update sys.functions set system = true where system <> true and name in ('dump_table_data') and schema_id = 2000 and type = 2; update sys.functions set system = true where system <> true and name in ('dump_database') and schema_id = 2000 and type = 5; CREATE function sys.url_extract_host(url string, no_www bool) RETURNS STRING
--- a/sql/test/emptydb-upgrade-chain-hge/Tests/upgrade.stable.out.ppc64.int128 +++ b/sql/test/emptydb-upgrade-chain-hge/Tests/upgrade.stable.out.ppc64.int128 @@ -2955,6 +2955,34 @@ create procedure SHPload(fid integer, fi update sys.functions set system = true where schema_id = 2000 and name in ('shpattach', 'shpload'); Running database upgrade commands: +alter table sys.db_user_info add column max_memory bigint; +alter table sys.db_user_info add column max_workers int; +alter table sys.db_user_info add column optimizer varchar(1024); +alter table sys.db_user_info add column default_role int; +alter table sys.db_user_info add column password varchar(256); +update sys.db_user_info u set max_memory = 0, max_workers = 0, optimizer = 'default_pipe', default_role = (select id from sys.auths a where a.name = u.name); +-- and copying passwords + +Running database upgrade commands: +drop view sys.dependency_schemas_on_users; +drop view sys.roles; +drop view sys.users; +drop function sys.db_users(); +CREATE VIEW sys.roles AS SELECT id, name, grantor FROM sys.auths a WHERE a.name NOT IN (SELECT u.name FROM sys.db_user_info u); +GRANT SELECT ON sys.roles TO PUBLIC; +CREATE VIEW sys.users AS SELECT name, fullname, default_schema, schema_path, max_memory, max_workers, optimizer, default_role FROM sys.db_user_info; +GRANT SELECT ON sys.users TO PUBLIC; +CREATE FUNCTION sys.db_users() RETURNS TABLE(name varchar(2048)) RETURN SELECT name FROM sys.db_user_info; +CREATE VIEW sys.dependency_schemas_on_users AS +SELECT s.id AS schema_id, s.name AS schema_name, u.name AS user_name, CAST(6 AS smallint) AS depend_type + FROM sys.db_user_info AS u, sys.schemas AS s + WHERE u.default_schema = s.id + ORDER BY s.name, u.name; +GRANT SELECT ON sys.dependency_schemas_on_users TO PUBLIC; +update sys._tables set system = true where name in ('users', 'roles', 'dependency_schemas_on_users') AND schema_id = 2000; +update sys.functions set system = true where system <> true and name in ('db_users') and schema_id = 2000 and type = 5; + +Running database upgrade commands: drop function sys.dump_database(boolean); drop procedure sys.dump_table_data(); drop procedure sys.dump_table_data(string, string); @@ -2964,6 +2992,7 @@ drop view sys.dump_sequences; drop view sys.dump_start_sequences; drop view sys.dump_tables; drop view sys.describe_tables; +drop view sys.dump_create_users; CREATE VIEW sys.describe_partition_tables AS SELECT m_sch, @@ -3108,6 +3137,16 @@ BEGIN END WHILE; END IF; END; +CREATE VIEW sys.dump_create_users AS + SELECT + 'CREATE USER ' || sys.dq(ui.name) || ' WITH ENCRYPTED PASSWORD ' || + sys.sq(sys.password_hash(ui.name)) || + ' NAME ' || sys.sq(ui.fullname) || ' SCHEMA sys' || ifthenelse(ui.schema_path = '"sys"', '', ' SCHEMA PATH ' || sys.sq(ui.schema_path)) || ';' stmt, + ui.name user_name + FROM sys.db_user_info ui, sys.schemas s + WHERE ui.default_schema = s.id + AND ui.name <> 'monetdb' + AND ui.name <> '.snapshot'; CREATE VIEW sys.describe_tables AS SELECT t.id o, @@ -3193,7 +3232,7 @@ BEGIN INSERT INTO sys.dump_statements VALUES ((SELECT COUNT(*) FROM sys.dump_statements) + 1, 'COMMIT;'); RETURN sys.dump_statements; END; -update sys._tables set system = true where name in ('describe_partition_tables', 'dump_partition_tables', 'dump_sequences', 'dump_start_sequences', 'describe_tables', 'dump_tables') AND schema_id = 2000; +update sys._tables set system = true where name in ('describe_partition_tables', 'dump_partition_tables', 'dump_sequences', 'dump_start_sequences', 'describe_tables', 'dump_tables', 'dump_create_users') AND schema_id = 2000; update sys.functions set system = true where system <> true and name in ('dump_table_data') and schema_id = 2000 and type = 2; update sys.functions set system = true where system <> true and name in ('dump_database') and schema_id = 2000 and type = 5; CREATE function sys.url_extract_host(url string, no_www bool) RETURNS STRING
--- a/sql/test/emptydb-upgrade-chain/Tests/upgrade.stable.out +++ b/sql/test/emptydb-upgrade-chain/Tests/upgrade.stable.out @@ -2608,6 +2608,34 @@ create procedure SHPload(fid integer, fi update sys.functions set system = true where schema_id = 2000 and name in ('shpattach', 'shpload'); Running database upgrade commands: +alter table sys.db_user_info add column max_memory bigint; +alter table sys.db_user_info add column max_workers int; +alter table sys.db_user_info add column optimizer varchar(1024); +alter table sys.db_user_info add column default_role int; +alter table sys.db_user_info add column password varchar(256); +update sys.db_user_info u set max_memory = 0, max_workers = 0, optimizer = 'default_pipe', default_role = (select id from sys.auths a where a.name = u.name); +-- and copying passwords + +Running database upgrade commands: +drop view sys.dependency_schemas_on_users; +drop view sys.roles; +drop view sys.users; +drop function sys.db_users(); +CREATE VIEW sys.roles AS SELECT id, name, grantor FROM sys.auths a WHERE a.name NOT IN (SELECT u.name FROM sys.db_user_info u); +GRANT SELECT ON sys.roles TO PUBLIC; +CREATE VIEW sys.users AS SELECT name, fullname, default_schema, schema_path, max_memory, max_workers, optimizer, default_role FROM sys.db_user_info; +GRANT SELECT ON sys.users TO PUBLIC; +CREATE FUNCTION sys.db_users() RETURNS TABLE(name varchar(2048)) RETURN SELECT name FROM sys.db_user_info; +CREATE VIEW sys.dependency_schemas_on_users AS +SELECT s.id AS schema_id, s.name AS schema_name, u.name AS user_name, CAST(6 AS smallint) AS depend_type + FROM sys.db_user_info AS u, sys.schemas AS s + WHERE u.default_schema = s.id + ORDER BY s.name, u.name; +GRANT SELECT ON sys.dependency_schemas_on_users TO PUBLIC; +update sys._tables set system = true where name in ('users', 'roles', 'dependency_schemas_on_users') AND schema_id = 2000; +update sys.functions set system = true where system <> true and name in ('db_users') and schema_id = 2000 and type = 5; + +Running database upgrade commands: drop function sys.dump_database(boolean); drop procedure sys.dump_table_data(); drop procedure sys.dump_table_data(string, string); @@ -2617,6 +2645,7 @@ drop view sys.dump_sequences; drop view sys.dump_start_sequences; drop view sys.dump_tables; drop view sys.describe_tables; +drop view sys.dump_create_users; CREATE VIEW sys.describe_partition_tables AS SELECT m_sch, @@ -2761,6 +2790,16 @@ BEGIN END WHILE; END IF; END; +CREATE VIEW sys.dump_create_users AS + SELECT + 'CREATE USER ' || sys.dq(ui.name) || ' WITH ENCRYPTED PASSWORD ' || + sys.sq(sys.password_hash(ui.name)) || + ' NAME ' || sys.sq(ui.fullname) || ' SCHEMA sys' || ifthenelse(ui.schema_path = '"sys"', '', ' SCHEMA PATH ' || sys.sq(ui.schema_path)) || ';' stmt, + ui.name user_name + FROM sys.db_user_info ui, sys.schemas s + WHERE ui.default_schema = s.id + AND ui.name <> 'monetdb' + AND ui.name <> '.snapshot'; CREATE VIEW sys.describe_tables AS SELECT t.id o, @@ -2846,7 +2885,7 @@ BEGIN INSERT INTO sys.dump_statements VALUES ((SELECT COUNT(*) FROM sys.dump_statements) + 1, 'COMMIT;'); RETURN sys.dump_statements; END; -update sys._tables set system = true where name in ('describe_partition_tables', 'dump_partition_tables', 'dump_sequences', 'dump_start_sequences', 'describe_tables', 'dump_tables') AND schema_id = 2000; +update sys._tables set system = true where name in ('describe_partition_tables', 'dump_partition_tables', 'dump_sequences', 'dump_start_sequences', 'describe_tables', 'dump_tables', 'dump_create_users') AND schema_id = 2000; update sys.functions set system = true where system <> true and name in ('dump_table_data') and schema_id = 2000 and type = 2; update sys.functions set system = true where system <> true and name in ('dump_database') and schema_id = 2000 and type = 5; CREATE function sys.url_extract_host(url string, no_www bool) RETURNS STRING
--- a/sql/test/emptydb-upgrade-chain/Tests/upgrade.stable.out.32bit +++ b/sql/test/emptydb-upgrade-chain/Tests/upgrade.stable.out.32bit @@ -2608,6 +2608,34 @@ create procedure SHPload(fid integer, fi update sys.functions set system = true where schema_id = 2000 and name in ('shpattach', 'shpload'); Running database upgrade commands: +alter table sys.db_user_info add column max_memory bigint; +alter table sys.db_user_info add column max_workers int; +alter table sys.db_user_info add column optimizer varchar(1024); +alter table sys.db_user_info add column default_role int; +alter table sys.db_user_info add column password varchar(256); +update sys.db_user_info u set max_memory = 0, max_workers = 0, optimizer = 'default_pipe', default_role = (select id from sys.auths a where a.name = u.name); +-- and copying passwords + +Running database upgrade commands: +drop view sys.dependency_schemas_on_users; +drop view sys.roles; +drop view sys.users; +drop function sys.db_users(); +CREATE VIEW sys.roles AS SELECT id, name, grantor FROM sys.auths a WHERE a.name NOT IN (SELECT u.name FROM sys.db_user_info u); +GRANT SELECT ON sys.roles TO PUBLIC; +CREATE VIEW sys.users AS SELECT name, fullname, default_schema, schema_path, max_memory, max_workers, optimizer, default_role FROM sys.db_user_info; +GRANT SELECT ON sys.users TO PUBLIC; +CREATE FUNCTION sys.db_users() RETURNS TABLE(name varchar(2048)) RETURN SELECT name FROM sys.db_user_info; +CREATE VIEW sys.dependency_schemas_on_users AS +SELECT s.id AS schema_id, s.name AS schema_name, u.name AS user_name, CAST(6 AS smallint) AS depend_type + FROM sys.db_user_info AS u, sys.schemas AS s + WHERE u.default_schema = s.id + ORDER BY s.name, u.name; +GRANT SELECT ON sys.dependency_schemas_on_users TO PUBLIC; +update sys._tables set system = true where name in ('users', 'roles', 'dependency_schemas_on_users') AND schema_id = 2000; +update sys.functions set system = true where system <> true and name in ('db_users') and schema_id = 2000 and type = 5; + +Running database upgrade commands: drop function sys.dump_database(boolean); drop procedure sys.dump_table_data(); drop procedure sys.dump_table_data(string, string); @@ -2617,6 +2645,7 @@ drop view sys.dump_sequences; drop view sys.dump_start_sequences; drop view sys.dump_tables; drop view sys.describe_tables; +drop view sys.dump_create_users; CREATE VIEW sys.describe_partition_tables AS SELECT m_sch, @@ -2761,6 +2790,16 @@ BEGIN END WHILE; END IF; END; +CREATE VIEW sys.dump_create_users AS + SELECT + 'CREATE USER ' || sys.dq(ui.name) || ' WITH ENCRYPTED PASSWORD ' || + sys.sq(sys.password_hash(ui.name)) || + ' NAME ' || sys.sq(ui.fullname) || ' SCHEMA sys' || ifthenelse(ui.schema_path = '"sys"', '', ' SCHEMA PATH ' || sys.sq(ui.schema_path)) || ';' stmt, + ui.name user_name + FROM sys.db_user_info ui, sys.schemas s + WHERE ui.default_schema = s.id + AND ui.name <> 'monetdb' + AND ui.name <> '.snapshot'; CREATE VIEW sys.describe_tables AS SELECT t.id o, @@ -2846,7 +2885,7 @@ BEGIN INSERT INTO sys.dump_statements VALUES ((SELECT COUNT(*) FROM sys.dump_statements) + 1, 'COMMIT;'); RETURN sys.dump_statements; END; -update sys._tables set system = true where name in ('describe_partition_tables', 'dump_partition_tables', 'dump_sequences', 'dump_start_sequences', 'describe_tables', 'dump_tables') AND schema_id = 2000; +update sys._tables set system = true where name in ('describe_partition_tables', 'dump_partition_tables', 'dump_sequences', 'dump_start_sequences', 'describe_tables', 'dump_tables', 'dump_create_users') AND schema_id = 2000; update sys.functions set system = true where system <> true and name in ('dump_table_data') and schema_id = 2000 and type = 2; update sys.functions set system = true where system <> true and name in ('dump_database') and schema_id = 2000 and type = 5; CREATE function sys.url_extract_host(url string, no_www bool) RETURNS STRING
--- a/sql/test/emptydb-upgrade-chain/Tests/upgrade.stable.out.ppc64 +++ b/sql/test/emptydb-upgrade-chain/Tests/upgrade.stable.out.ppc64 @@ -2608,6 +2608,34 @@ create procedure SHPload(fid integer, fi update sys.functions set system = true where schema_id = 2000 and name in ('shpattach', 'shpload'); Running database upgrade commands: +alter table sys.db_user_info add column max_memory bigint; +alter table sys.db_user_info add column max_workers int; +alter table sys.db_user_info add column optimizer varchar(1024); +alter table sys.db_user_info add column default_role int; +alter table sys.db_user_info add column password varchar(256); +update sys.db_user_info u set max_memory = 0, max_workers = 0, optimizer = 'default_pipe', default_role = (select id from sys.auths a where a.name = u.name); +-- and copying passwords + +Running database upgrade commands: +drop view sys.dependency_schemas_on_users; +drop view sys.roles; +drop view sys.users; +drop function sys.db_users(); +CREATE VIEW sys.roles AS SELECT id, name, grantor FROM sys.auths a WHERE a.name NOT IN (SELECT u.name FROM sys.db_user_info u); +GRANT SELECT ON sys.roles TO PUBLIC; +CREATE VIEW sys.users AS SELECT name, fullname, default_schema, schema_path, max_memory, max_workers, optimizer, default_role FROM sys.db_user_info; +GRANT SELECT ON sys.users TO PUBLIC; +CREATE FUNCTION sys.db_users() RETURNS TABLE(name varchar(2048)) RETURN SELECT name FROM sys.db_user_info; +CREATE VIEW sys.dependency_schemas_on_users AS +SELECT s.id AS schema_id, s.name AS schema_name, u.name AS user_name, CAST(6 AS smallint) AS depend_type + FROM sys.db_user_info AS u, sys.schemas AS s + WHERE u.default_schema = s.id + ORDER BY s.name, u.name; +GRANT SELECT ON sys.dependency_schemas_on_users TO PUBLIC; +update sys._tables set system = true where name in ('users', 'roles', 'dependency_schemas_on_users') AND schema_id = 2000; +update sys.functions set system = true where system <> true and name in ('db_users') and schema_id = 2000 and type = 5; + +Running database upgrade commands: drop function sys.dump_database(boolean); drop procedure sys.dump_table_data(); drop procedure sys.dump_table_data(string, string); @@ -2617,6 +2645,7 @@ drop view sys.dump_sequences; drop view sys.dump_start_sequences; drop view sys.dump_tables; drop view sys.describe_tables; +drop view sys.dump_create_users; CREATE VIEW sys.describe_partition_tables AS SELECT m_sch, @@ -2761,6 +2790,16 @@ BEGIN END WHILE; END IF; END; +CREATE VIEW sys.dump_create_users AS + SELECT + 'CREATE USER ' || sys.dq(ui.name) || ' WITH ENCRYPTED PASSWORD ' || + sys.sq(sys.password_hash(ui.name)) || + ' NAME ' || sys.sq(ui.fullname) || ' SCHEMA sys' || ifthenelse(ui.schema_path = '"sys"', '', ' SCHEMA PATH ' || sys.sq(ui.schema_path)) || ';' stmt, + ui.name user_name + FROM sys.db_user_info ui, sys.schemas s + WHERE ui.default_schema = s.id + AND ui.name <> 'monetdb' + AND ui.name <> '.snapshot'; CREATE VIEW sys.describe_tables AS SELECT t.id o, @@ -2846,7 +2885,7 @@ BEGIN INSERT INTO sys.dump_statements VALUES ((SELECT COUNT(*) FROM sys.dump_statements) + 1, 'COMMIT;'); RETURN sys.dump_statements; END; -update sys._tables set system = true where name in ('describe_partition_tables', 'dump_partition_tables', 'dump_sequences', 'dump_start_sequences', 'describe_tables', 'dump_tables') AND schema_id = 2000; +update sys._tables set system = true where name in ('describe_partition_tables', 'dump_partition_tables', 'dump_sequences', 'dump_start_sequences', 'describe_tables', 'dump_tables', 'dump_create_users') AND schema_id = 2000; update sys.functions set system = true where system <> true and name in ('dump_table_data') and schema_id = 2000 and type = 2; update sys.functions set system = true where system <> true and name in ('dump_database') and schema_id = 2000 and type = 5; CREATE function sys.url_extract_host(url string, no_www bool) RETURNS STRING
--- a/sql/test/emptydb-upgrade-chain/Tests/upgrade.stable.out.ppc64.int128 +++ b/sql/test/emptydb-upgrade-chain/Tests/upgrade.stable.out.ppc64.int128 @@ -3024,6 +3024,34 @@ create procedure SHPload(fid integer, fi update sys.functions set system = true where schema_id = 2000 and name in ('shpattach', 'shpload'); Running database upgrade commands: +alter table sys.db_user_info add column max_memory bigint; +alter table sys.db_user_info add column max_workers int; +alter table sys.db_user_info add column optimizer varchar(1024); +alter table sys.db_user_info add column default_role int; +alter table sys.db_user_info add column password varchar(256); +update sys.db_user_info u set max_memory = 0, max_workers = 0, optimizer = 'default_pipe', default_role = (select id from sys.auths a where a.name = u.name); +-- and copying passwords + +Running database upgrade commands: +drop view sys.dependency_schemas_on_users; +drop view sys.roles; +drop view sys.users; +drop function sys.db_users(); +CREATE VIEW sys.roles AS SELECT id, name, grantor FROM sys.auths a WHERE a.name NOT IN (SELECT u.name FROM sys.db_user_info u); +GRANT SELECT ON sys.roles TO PUBLIC; +CREATE VIEW sys.users AS SELECT name, fullname, default_schema, schema_path, max_memory, max_workers, optimizer, default_role FROM sys.db_user_info; +GRANT SELECT ON sys.users TO PUBLIC; +CREATE FUNCTION sys.db_users() RETURNS TABLE(name varchar(2048)) RETURN SELECT name FROM sys.db_user_info; +CREATE VIEW sys.dependency_schemas_on_users AS +SELECT s.id AS schema_id, s.name AS schema_name, u.name AS user_name, CAST(6 AS smallint) AS depend_type + FROM sys.db_user_info AS u, sys.schemas AS s + WHERE u.default_schema = s.id + ORDER BY s.name, u.name; +GRANT SELECT ON sys.dependency_schemas_on_users TO PUBLIC; +update sys._tables set system = true where name in ('users', 'roles', 'dependency_schemas_on_users') AND schema_id = 2000; +update sys.functions set system = true where system <> true and name in ('db_users') and schema_id = 2000 and type = 5; + +Running database upgrade commands: drop function sys.dump_database(boolean); drop procedure sys.dump_table_data(); drop procedure sys.dump_table_data(string, string); @@ -3033,6 +3061,7 @@ drop view sys.dump_sequences; drop view sys.dump_start_sequences; drop view sys.dump_tables; drop view sys.describe_tables; +drop view sys.dump_create_users; CREATE VIEW sys.describe_partition_tables AS SELECT m_sch, @@ -3177,6 +3206,16 @@ BEGIN END WHILE; END IF; END; +CREATE VIEW sys.dump_create_users AS + SELECT + 'CREATE USER ' || sys.dq(ui.name) || ' WITH ENCRYPTED PASSWORD ' || + sys.sq(sys.password_hash(ui.name)) || + ' NAME ' || sys.sq(ui.fullname) || ' SCHEMA sys' || ifthenelse(ui.schema_path = '"sys"', '', ' SCHEMA PATH ' || sys.sq(ui.schema_path)) || ';' stmt, + ui.name user_name + FROM sys.db_user_info ui, sys.schemas s + WHERE ui.default_schema = s.id + AND ui.name <> 'monetdb' + AND ui.name <> '.snapshot'; CREATE VIEW sys.describe_tables AS SELECT t.id o, @@ -3262,7 +3301,7 @@ BEGIN INSERT INTO sys.dump_statements VALUES ((SELECT COUNT(*) FROM sys.dump_statements) + 1, 'COMMIT;'); RETURN sys.dump_statements; END; -update sys._tables set system = true where name in ('describe_partition_tables', 'dump_partition_tables', 'dump_sequences', 'dump_start_sequences', 'describe_tables', 'dump_tables') AND schema_id = 2000; +update sys._tables set system = true where name in ('describe_partition_tables', 'dump_partition_tables', 'dump_sequences', 'dump_start_sequences', 'describe_tables', 'dump_tables', 'dump_create_users') AND schema_id = 2000; update sys.functions set system = true where system <> true and name in ('dump_table_data') and schema_id = 2000 and type = 2; update sys.functions set system = true where system <> true and name in ('dump_database') and schema_id = 2000 and type = 5; CREATE function sys.url_extract_host(url string, no_www bool) RETURNS STRING
--- a/sql/test/emptydb-upgrade/Tests/upgrade.stable.out +++ b/sql/test/emptydb-upgrade/Tests/upgrade.stable.out @@ -2602,6 +2602,34 @@ insert into sys.functions values (904, ' insert into sys.functions values (905, 'sys_update_tables', 'update_tables', 'sql', 0, 2, true, false, false, 2000, true, true); Running database upgrade commands: +alter table sys.db_user_info add column max_memory bigint; +alter table sys.db_user_info add column max_workers int; +alter table sys.db_user_info add column optimizer varchar(1024); +alter table sys.db_user_info add column default_role int; +alter table sys.db_user_info add column password varchar(256); +update sys.db_user_info u set max_memory = 0, max_workers = 0, optimizer = 'default_pipe', default_role = (select id from sys.auths a where a.name = u.name); +-- and copying passwords + +Running database upgrade commands: +drop view sys.dependency_schemas_on_users; +drop view sys.roles; +drop view sys.users; +drop function sys.db_users(); +CREATE VIEW sys.roles AS SELECT id, name, grantor FROM sys.auths a WHERE a.name NOT IN (SELECT u.name FROM sys.db_user_info u); +GRANT SELECT ON sys.roles TO PUBLIC; +CREATE VIEW sys.users AS SELECT name, fullname, default_schema, schema_path, max_memory, max_workers, optimizer, default_role FROM sys.db_user_info; +GRANT SELECT ON sys.users TO PUBLIC; +CREATE FUNCTION sys.db_users() RETURNS TABLE(name varchar(2048)) RETURN SELECT name FROM sys.db_user_info; +CREATE VIEW sys.dependency_schemas_on_users AS +SELECT s.id AS schema_id, s.name AS schema_name, u.name AS user_name, CAST(6 AS smallint) AS depend_type + FROM sys.db_user_info AS u, sys.schemas AS s + WHERE u.default_schema = s.id + ORDER BY s.name, u.name; +GRANT SELECT ON sys.dependency_schemas_on_users TO PUBLIC; +update sys._tables set system = true where name in ('users', 'roles', 'dependency_schemas_on_users') AND schema_id = 2000; +update sys.functions set system = true where system <> true and name in ('db_users') and schema_id = 2000 and type = 5; + +Running database upgrade commands: drop function sys.dump_database(boolean); drop procedure sys.dump_table_data(); drop procedure sys.dump_table_data(string, string); @@ -2611,6 +2639,7 @@ drop view sys.dump_sequences; drop view sys.dump_start_sequences; drop view sys.dump_tables; drop view sys.describe_tables; +drop view sys.dump_create_users; CREATE VIEW sys.describe_partition_tables AS SELECT m_sch, @@ -2755,6 +2784,16 @@ BEGIN END WHILE; END IF; END; +CREATE VIEW sys.dump_create_users AS + SELECT + 'CREATE USER ' || sys.dq(ui.name) || ' WITH ENCRYPTED PASSWORD ' || + sys.sq(sys.password_hash(ui.name)) || + ' NAME ' || sys.sq(ui.fullname) || ' SCHEMA sys' || ifthenelse(ui.schema_path = '"sys"', '', ' SCHEMA PATH ' || sys.sq(ui.schema_path)) || ';' stmt, + ui.name user_name + FROM sys.db_user_info ui, sys.schemas s + WHERE ui.default_schema = s.id + AND ui.name <> 'monetdb' + AND ui.name <> '.snapshot'; CREATE VIEW sys.describe_tables AS SELECT t.id o, @@ -2840,7 +2879,7 @@ BEGIN INSERT INTO sys.dump_statements VALUES ((SELECT COUNT(*) FROM sys.dump_statements) + 1, 'COMMIT;'); RETURN sys.dump_statements; END; -update sys._tables set system = true where name in ('describe_partition_tables', 'dump_partition_tables', 'dump_sequences', 'dump_start_sequences', 'describe_tables', 'dump_tables') AND schema_id = 2000; +update sys._tables set system = true where name in ('describe_partition_tables', 'dump_partition_tables', 'dump_sequences', 'dump_start_sequences', 'describe_tables', 'dump_tables', 'dump_create_users') AND schema_id = 2000; update sys.functions set system = true where system <> true and name in ('dump_table_data') and schema_id = 2000 and type = 2; update sys.functions set system = true where system <> true and name in ('dump_database') and schema_id = 2000 and type = 5; CREATE function sys.url_extract_host(url string, no_www bool) RETURNS STRING
--- a/sql/test/emptydb-upgrade/Tests/upgrade.stable.out.32bit +++ b/sql/test/emptydb-upgrade/Tests/upgrade.stable.out.32bit @@ -2602,6 +2602,34 @@ insert into sys.functions values (904, ' insert into sys.functions values (905, 'sys_update_tables', 'update_tables', 'sql', 0, 2, true, false, false, 2000, true, true); Running database upgrade commands: +alter table sys.db_user_info add column max_memory bigint; +alter table sys.db_user_info add column max_workers int; +alter table sys.db_user_info add column optimizer varchar(1024); +alter table sys.db_user_info add column default_role int; +alter table sys.db_user_info add column password varchar(256); +update sys.db_user_info u set max_memory = 0, max_workers = 0, optimizer = 'default_pipe', default_role = (select id from sys.auths a where a.name = u.name); +-- and copying passwords + +Running database upgrade commands: +drop view sys.dependency_schemas_on_users; +drop view sys.roles; +drop view sys.users; +drop function sys.db_users(); +CREATE VIEW sys.roles AS SELECT id, name, grantor FROM sys.auths a WHERE a.name NOT IN (SELECT u.name FROM sys.db_user_info u); +GRANT SELECT ON sys.roles TO PUBLIC; +CREATE VIEW sys.users AS SELECT name, fullname, default_schema, schema_path, max_memory, max_workers, optimizer, default_role FROM sys.db_user_info; +GRANT SELECT ON sys.users TO PUBLIC; +CREATE FUNCTION sys.db_users() RETURNS TABLE(name varchar(2048)) RETURN SELECT name FROM sys.db_user_info; +CREATE VIEW sys.dependency_schemas_on_users AS +SELECT s.id AS schema_id, s.name AS schema_name, u.name AS user_name, CAST(6 AS smallint) AS depend_type + FROM sys.db_user_info AS u, sys.schemas AS s + WHERE u.default_schema = s.id + ORDER BY s.name, u.name; +GRANT SELECT ON sys.dependency_schemas_on_users TO PUBLIC; +update sys._tables set system = true where name in ('users', 'roles', 'dependency_schemas_on_users') AND schema_id = 2000; +update sys.functions set system = true where system <> true and name in ('db_users') and schema_id = 2000 and type = 5; + +Running database upgrade commands: drop function sys.dump_database(boolean); drop procedure sys.dump_table_data(); drop procedure sys.dump_table_data(string, string); @@ -2611,6 +2639,7 @@ drop view sys.dump_sequences; drop view sys.dump_start_sequences; drop view sys.dump_tables; drop view sys.describe_tables; +drop view sys.dump_create_users; CREATE VIEW sys.describe_partition_tables AS SELECT m_sch, @@ -2755,6 +2784,16 @@ BEGIN END WHILE; END IF; END; +CREATE VIEW sys.dump_create_users AS + SELECT + 'CREATE USER ' || sys.dq(ui.name) || ' WITH ENCRYPTED PASSWORD ' || + sys.sq(sys.password_hash(ui.name)) || + ' NAME ' || sys.sq(ui.fullname) || ' SCHEMA sys' || ifthenelse(ui.schema_path = '"sys"', '', ' SCHEMA PATH ' || sys.sq(ui.schema_path)) || ';' stmt, + ui.name user_name + FROM sys.db_user_info ui, sys.schemas s + WHERE ui.default_schema = s.id + AND ui.name <> 'monetdb' + AND ui.name <> '.snapshot'; CREATE VIEW sys.describe_tables AS SELECT t.id o, @@ -2840,7 +2879,7 @@ BEGIN INSERT INTO sys.dump_statements VALUES ((SELECT COUNT(*) FROM sys.dump_statements) + 1, 'COMMIT;'); RETURN sys.dump_statements; END; -update sys._tables set system = true where name in ('describe_partition_tables', 'dump_partition_tables', 'dump_sequences', 'dump_start_sequences', 'describe_tables', 'dump_tables') AND schema_id = 2000; +update sys._tables set system = true where name in ('describe_partition_tables', 'dump_partition_tables', 'dump_sequences', 'dump_start_sequences', 'describe_tables', 'dump_tables', 'dump_create_users') AND schema_id = 2000; update sys.functions set system = true where system <> true and name in ('dump_table_data') and schema_id = 2000 and type = 2; update sys.functions set system = true where system <> true and name in ('dump_database') and schema_id = 2000 and type = 5; CREATE function sys.url_extract_host(url string, no_www bool) RETURNS STRING
--- a/sql/test/emptydb/Tests/check.stable.out +++ b/sql/test/emptydb/Tests/check.stable.out @@ -451,7 +451,7 @@ select 'null in value_partitions.value', [ "sys._tables", "sys", "dependency_functions_on_views", "create view sys.dependency_functions_on_views as select f.schema_id as function_schema_id, f.id as function_id, f.name as function_name, v.schema_id as view_schema_id, v.id as view_id, v.name as view_name, dep.depend_type as depend_type from sys.functions as f, sys.tables as v, sys.dependencies as dep where f.id = dep.id and v.id = dep.depend_id and dep.depend_type = 5 and v.type in (1, 11) order by f.schema_id, f.name, v.schema_id, v.name;", "VIEW", true, "COMMIT", "WRITABLE", NULL ] [ "sys._tables", "sys", "dependency_keys_on_foreignkeys", "create view sys.dependency_keys_on_foreignkeys as select k.table_id as key_table_id, k.id as key_id, k.name as key_name, fk.table_id as fk_table_id, fk.id as fk_id, fk.name as fk_name, cast(k.type as smallint) as key_type, cast(11 as smallint) as depend_type from sys.keys as k, sys.keys as fk where k.id = fk.rkey order by k.name, fk.name;", "VIEW", true, "COMMIT", "WRITABLE", NULL ] [ "sys._tables", "sys", "dependency_owners_on_schemas", "create view sys.dependency_owners_on_schemas as select a.name as owner_name, s.id as schema_id, s.name as schema_name, cast(1 as smallint) as depend_type from sys.schemas as s, sys.auths as a where s.owner = a.id order by a.name, s.name;", "VIEW", true, "COMMIT", "WRITABLE", NULL ] -[ "sys._tables", "sys", "dependency_schemas_on_users", "create view sys.dependency_schemas_on_users as select s.id as schema_id, s.name as schema_name, u.name as user_name, cast(6 as smallint) as depend_type from sys.users as u, sys.schemas as s where u.default_schema = s.id order by s.name, u.name;", "VIEW", true, "COMMIT", "WRITABLE", NULL ] +[ "sys._tables", "sys", "dependency_schemas_on_users", "create view sys.dependency_schemas_on_users as select s.id as schema_id, s.name as schema_name, u.name as user_name, cast(6 as smallint) as depend_type from sys.db_user_info as u, sys.schemas as s where u.default_schema = s.id order by s.name, u.name;", "VIEW", true, "COMMIT", "WRITABLE", NULL ] [ "sys._tables", "sys", "dependency_tables_on_foreignkeys", "create view sys.dependency_tables_on_foreignkeys as select t.schema_id as table_schema_id, t.id as table_id, t.name as table_name, fk.name as fk_name, cast(k.type as smallint) as key_type, cast(11 as smallint) as depend_type from sys.tables as t, sys.keys as k, sys.keys as fk where fk.rkey = k.id and k.table_id = t.id order by t.schema_id, t.name, fk.name;", "VIEW", true, "COMMIT", "WRITABLE", NULL ] [ "sys._tables", "sys", "dependency_tables_on_functions", "create view sys.dependency_tables_on_functions as select t.schema_id as table_schema_id, t.id as table_id, t.name as table_name, f.name as function_name, f.type as function_type, dep.depend_type as depend_type from sys.functions as f, sys.tables as t, sys.dependencies as dep where t.id = dep.id and f.id = dep.depend_id and dep.depend_type = 7 and f.type <> 2 and t.type not in (1, 11) order by t.name, t.schema_id, f.name, f.id;", "VIEW", true, "COMMIT", "WRITABLE", NULL ] [ "sys._tables", "sys", "dependency_tables_on_indexes", "create view sys.dependency_tables_on_indexes as select t.schema_id as table_schema_id, t.id as table_id, t.name as table_name, i.id as index_id, i.name as index_name, i.type as index_type, cast(10 as smallint) as depend_type from sys.tables as t, sys.idxs as i where i.table_id = t.id and (i.table_id, i.name) not in (select k.table_id, k.name from sys.keys k) order by t.schema_id, t.name, i.name;", "VIEW", true, "COMMIT", "WRITABLE", NULL ] @@ -480,7 +480,7 @@ select 'null in value_partitions.value', [ "sys._tables", "sys", "dump_comments", "create view sys.dump_comments as select 'COMMENT ON ' || c.tpe || ' ' || c.fqn || ' IS ' || sys.sq(c.rem) || ';' stmt from sys.describe_comments c;", "VIEW", true, "COMMIT", "WRITABLE", NULL ] [ "sys._tables", "sys", "dump_create_roles", "create view sys.dump_create_roles as select 'CREATE ROLE ' || sys.dq(name) || ';' stmt, name user_name from sys.auths where name not in (select name from sys.db_user_info) and grantor <> 0;", "VIEW", true, "COMMIT", "WRITABLE", NULL ] [ "sys._tables", "sys", "dump_create_schemas", "create view sys.dump_create_schemas as select 'CREATE SCHEMA ' || sys.dq(s.name) || ifthenelse(a.name <> 'sysadmin', ' AUTHORIZATION ' || sys.dq(a.name), ' ') || ';' stmt, s.name schema_name from sys.schemas s, sys.auths a where s.authorization = a.id and s.system = false;", "VIEW", true, "COMMIT", "WRITABLE", NULL ] -[ "sys._tables", "sys", "dump_create_users", "create view sys.dump_create_users as select 'CREATE USER ' || sys.dq(ui.name) || ' WITH ENCRYPTED PASSWORD ' || sys.sq(sys.password_hash(ui.name)) || ' NAME ' || sys.sq(ui.fullname) || ' SCHEMA ' || sys.sq(s.name) || ' SCHEMA PATH ' || sys.sq(ui.schema_path) || ';' stmt, ui.name user_name from sys.db_user_info ui, sys.schemas s where ui.default_schema = s.id and ui.name <> 'monetdb' and ui.name <> '.snapshot';", "VIEW", true, "COMMIT", "WRITABLE", NULL ] +[ "sys._tables", "sys", "dump_create_users", "create view sys.dump_create_users as select 'CREATE USER ' || sys.dq(ui.name) || ' WITH ENCRYPTED PASSWORD ' || sys.sq(sys.password_hash(ui.name)) || ' NAME ' || sys.sq(ui.fullname) || ' SCHEMA sys' || ifthenelse(ui.schema_path = '\"sys\"', '', ' SCHEMA PATH ' || sys.sq(ui.schema_path)) || ';' stmt, ui.name user_name from sys.db_user_info ui, sys.schemas s where ui.default_schema = s.id and ui.name <> 'monetdb' and ui.name <> '.snapshot';", "VIEW", true, "COMMIT", "WRITABLE", NULL ] [ "sys._tables", "sys", "dump_foreign_keys", "create view sys.dump_foreign_keys as select 'ALTER TABLE ' || sys.fqn(fk_s, fk_t) || ' ADD CONSTRAINT ' || sys.dq(fk) || ' ' || 'FOREIGN KEY(' || group_concat(sys.dq(fk_c), ',') ||') ' || 'REFERENCES ' || sys.fqn(pk_s, pk_t) || '(' || group_concat(sys.dq(pk_c), ',') || ') ' || 'ON DELETE ' || on_delete || ' ON UPDATE ' || on_update || ';' stmt, fk_s foreign_schema_name, fk_t foreign_table_name, pk_s primary_schema_name, pk_t primary_table_name, fk key_name from sys.describe_foreign_keys group by fk_s, fk_t, pk_s, pk_t, fk, on_delete, on_update;", "VIEW", true, "COMMIT", "WRITABLE", NULL ] [ "sys._tables", "sys", "dump_function_grants", "create view sys.dump_function_grants as with func_args_all(func_id, number, max_number, func_arg) as (select a.func_id, a.number, max(a.number) over (partition by a.func_id order by a.number desc), group_concat(sys.describe_type(a.type, a.type_digits, a.type_scale), ', ') over (partition by a.func_id order by a.number) from sys.args a where a.inout = 1), func_args(func_id, func_arg) as (select func_id, func_arg from func_args_all where number = max_number) select 'GRANT ' || pc.privilege_code_name || ' ON ' || ft.function_type_keyword || ' ' || sys.fqn(s.name, f.name) || '(' || coalesce(fa.func_arg, '') || ') TO ' || ifthenelse(a.name = 'public', 'PUBLIC', sys.dq(a.name)) || case when p.grantable = 1 then ' WITH GRANT OPTION' else '' end || ';' stmt, s.name schema_name, f.name function_name, a.name grantee from sys.schemas s, sys.functions f left outer join func_args fa on f.id = fa.func_id, sys.auths a, sys.privileges p, sys.auths g, sys.function_types ft, sys.privilege_codes pc where s.id = f.schema_id and f.id = p.obj_id and p.auth_id = a.id and p.grantor = g.id and p.privileges = pc.privilege_code_id and f.type = ft.function_type_id and not f.system order by s.name, f.name, a.name, g.name, p.grantable;", "VIEW", true, "COMMIT", "WRITABLE", NULL ] [ "sys._tables", "sys", "dump_functions", "create view sys.dump_functions as select f.o o, sys.schema_guard(f.sch, f.fun, f.def) stmt, f.sch schema_name, f.fun function_name from sys.describe_functions f;", "VIEW", true, "COMMIT", "WRITABLE", NULL ] @@ -527,7 +527,7 @@ select 'null in value_partitions.value', [ "sys._tables", "sys", "queue", "create view sys.queue as select * from sys.queue();", "VIEW", true, "COMMIT", "WRITABLE", NULL ] [ "sys._tables", "sys", "range_partitions", NULL, "TABLE", true, "COMMIT", "WRITABLE", NULL ] [ "sys._tables", "sys", "rejects", "create view sys.rejects as select * from sys.rejects();", "VIEW", true, "COMMIT", "WRITABLE", NULL ] -[ "sys._tables", "sys", "roles", "create view sys.roles as select id, name, grantor from sys.auths a where a.name not in (select u.name from sys.users u);", "VIEW", true, "COMMIT", "WRITABLE", NULL ] +[ "sys._tables", "sys", "roles", "create view sys.roles as select id, name, grantor from sys.auths a where a.name not in (select u.name from sys.db_user_info u);", "VIEW", true, "COMMIT", "WRITABLE", NULL ] [ "sys._tables", "sys", "schemas", NULL, "TABLE", true, "COMMIT", "WRITABLE", NULL ] [ "sys._tables", "sys", "schemastorage", "create view sys.\"schemastorage\" as select \"schema\", count(*) as \"storages\", sum(columnsize) as columnsize, sum(heapsize) as heapsize, sum(hashes) as hashsize, sum(\"imprints\") as imprintsize, sum(orderidx) as orderidxsize from sys.\"storage\" group by \"schema\" order by \"schema\";", "VIEW", true, "COMMIT", "WRITABLE", NULL ] [ "sys._tables", "sys", "sequences", NULL, "TABLE", true, "COMMIT", "WRITABLE", NULL ] @@ -546,7 +546,7 @@ select 'null in value_partitions.value', [ "sys._tables", "sys", "triggers", NULL, "TABLE", true, "COMMIT", "WRITABLE", NULL ] [ "sys._tables", "sys", "types", NULL, "TABLE", true, "COMMIT", "WRITABLE", NULL ] [ "sys._tables", "sys", "user_role", NULL, "TABLE", true, "COMMIT", "WRITABLE", NULL ] -[ "sys._tables", "sys", "users", "create view sys.users as select u.\"name\", u.\"fullname\", u.\"default_schema\", u.\"schema_path\", u.\"max_memory\", u.\"max_workers\", u.\"optimizer\", u.\"default_role\" from \"sys\".\"db_user_info\" as u;", "VIEW", true, "COMMIT", "WRITABLE", NULL ] +[ "sys._tables", "sys", "users", "create view sys.users as select name, fullname, default_schema, schema_path, max_memory, max_workers, optimizer, default_role from sys.db_user_info;", "VIEW", true, "COMMIT", "WRITABLE", NULL ] [ "sys._tables", "sys", "value_partitions", NULL, "TABLE", true, "COMMIT", "WRITABLE", NULL ] [ "sys._tables", "sys", "var_values", "create view sys.var_values (var_name, value) as select 'current_role', current_role union all select 'current_schema', current_schema union all select 'current_timezone', current_timezone union all select 'current_user', current_user union all select 'debug', debug union all select 'last_id', last_id union all select 'optimizer', optimizer union all select 'pi', pi() union all select 'rowcnt', rowcnt;", "VIEW", true, "COMMIT", "WRITABLE", NULL ] [ "sys._tables", "tmp", "_columns", NULL, "TABLE", true, "PRESERVE", "WRITABLE", NULL ] @@ -625,10 +625,10 @@ select 'null in value_partitions.value', [ "sys._columns", "db_user_info", "fullname", "varchar", 2048, 0, NULL, true, 1, NULL, NULL ] [ "sys._columns", "db_user_info", "default_schema", "int", 9, 0, NULL, true, 2, NULL, NULL ] [ "sys._columns", "db_user_info", "schema_path", "clob", 0, 0, NULL, true, 3, NULL, NULL ] -[ "sys._columns", "db_user_info", "max_memory", "bigint", 19, 0, NULL, true, 4, NULL, NULL ] -[ "sys._columns", "db_user_info", "max_workers", "int", 9, 0, NULL, true, 5, NULL, NULL ] +[ "sys._columns", "db_user_info", "max_memory", "bigint", 64, 0, NULL, true, 4, NULL, NULL ] +[ "sys._columns", "db_user_info", "max_workers", "int", 32, 0, NULL, true, 5, NULL, NULL ] [ "sys._columns", "db_user_info", "optimizer", "varchar", 1024, 0, NULL, true, 6, NULL, NULL ] -[ "sys._columns", "db_user_info", "default_role", "int", 9, 0, NULL, true, 7, NULL, NULL ] +[ "sys._columns", "db_user_info", "default_role", "int", 32, 0, NULL, true, 7, NULL, NULL ] [ "sys._columns", "db_user_info", "password", "varchar", 256, 0, NULL, true, 8, NULL, NULL ] [ "sys._columns", "dependencies", "id", "int", 32, 0, NULL, true, 0, NULL, NULL ] [ "sys._columns", "dependencies", "depend_id", "int", 32, 0, NULL, true, 1, NULL, NULL ] @@ -1313,14 +1313,14 @@ select 'null in value_partitions.value', [ "sys._columns", "types", "schema_id", "int", 32, 0, NULL, true, 7, NULL, NULL ] [ "sys._columns", "user_role", "login_id", "int", 32, 0, NULL, true, 0, NULL, NULL ] [ "sys._columns", "user_role", "role_id", "int", 32, 0, NULL, true, 1, NULL, NULL ] -[ "sys._columns", "users", "name", "varchar", 2048, 0, NULL, true, 0, NULL, NULL ] +[ "sys._columns", "users", "name", "varchar", 1024, 0, NULL, true, 0, NULL, NULL ] [ "sys._columns", "users", "fullname", "varchar", 2048, 0, NULL, true, 1, NULL, NULL ] [ "sys._columns", "users", "default_schema", "int", 9, 0, NULL, true, 2, NULL, NULL ] [ "sys._columns", "users", "schema_path", "clob", 0, 0, NULL, true, 3, NULL, NULL ] -[ "sys._columns", "users", "max_memory", "bigint", 19, 0, NULL, true, 4, NULL, NULL ] -[ "sys._columns", "users", "max_workers", "int", 9, 0, NULL, true, 5, NULL, NULL ] +[ "sys._columns", "users", "max_memory", "bigint", 64, 0, NULL, true, 4, NULL, NULL ] +[ "sys._columns", "users", "max_workers", "int", 32, 0, NULL, true, 5, NULL, NULL ] [ "sys._columns", "users", "optimizer", "varchar", 1024, 0, NULL, true, 6, NULL, NULL ] -[ "sys._columns", "users", "default_role", "int", 9, 0, NULL, true, 7, NULL, NULL ] +[ "sys._columns", "users", "default_role", "int", 32, 0, NULL, true, 7, NULL, NULL ] [ "sys._columns", "value_partitions", "table_id", "int", 32, 0, NULL, true, 0, NULL, NULL ] [ "sys._columns", "value_partitions", "partition_id", "int", 32, 0, NULL, true, 1, NULL, NULL ] [ "sys._columns", "value_partitions", "value", "varchar", 2048, 0, NULL, true, 2, NULL, NULL ] @@ -1554,7 +1554,7 @@ select 'null in value_partitions.value', [ "sys.functions", "sys", "dayofmonth", "SYSTEM", "day", "mtime", "Internal C", "Scalar function", false, false, false, false, NULL, "res_0", "int", 32, 0, "out", "arg_1", "date", 0, 0, "in", NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL ] [ "sys.functions", "sys", "dayofweek", "SYSTEM", "dayofweek", "mtime", "Internal C", "Scalar function", false, false, false, false, NULL, "res_0", "int", 32, 0, "out", "arg_1", "date", 0, 0, "in", NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL ] [ "sys.functions", "sys", "dayofyear", "SYSTEM", "dayofyear", "mtime", "Internal C", "Scalar function", false, false, false, false, NULL, "res_0", "int", 32, 0, "out", "arg_1", "date", 0, 0, "in", NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL ] -[ "sys.functions", "sys", "db_users", "SYSTEM", "CREATE FUNCTION db_users () RETURNS TABLE(name varchar(2048)) return select name from users;", "sql", "SQL", "Function returning a table", false, false, false, true, NULL, "name", "varchar", 2048, 0, "out", NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL ] +[ "sys.functions", "sys", "db_users", "SYSTEM", "create function sys.db_users() returns table(name varchar(2048)) return select name from sys.db_user_info;", "sql", "SQL", "Function returning a table", false, false, false, true, NULL, "name", "varchar", 2048, 0, "out", NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL ] [ "sys.functions", "sys", "debug", "SYSTEM", "create function sys.debug(debug int) returns integer external name mdb.\"setDebug\";", "mdb", "MAL", "Scalar function", false, false, false, true, NULL, "result", "int", 32, 0, "out", "debug", "int", 32, 0, "in", NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL ] [ "sys.functions", "sys", "debug", "SYSTEM", "create function sys.debug(flag string) returns integer external name mdb.\"setDebug\";", "mdb", "MAL", "Scalar function", false, false, false, true, NULL, "result", "int", 32, 0, "out", "flag", "clob", 0, 0, "in", NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL ] [ "sys.functions", "sys", "debugflags", "SYSTEM", "create function sys.debugflags() returns table(flag string, val bool) external name mdb.\"getDebugFlags\";", "mdb", "MAL", "Function returning a table", false, false, false, true, NULL, "flag", "clob", 0, 0, "out", "val", "boolean", 1, 0, "out", NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL ] @@ -2560,6 +2560,7 @@ select 'null in value_partitions.value', % %1, name, name, name, name, dependency_type_name # name % char, varchar, varchar, varchar, varchar, varchar # type % 22, 3, 26, 3, 37, 9 # length +[ "table used by function", "sys", "db_user_info", "sys", "db_users", "FUNCTION" ] [ "table used by function", "sys", "_columns", "sys", "describe_columns", "FUNCTION" ] [ "table used by function", "sys", "_tables", "sys", "describe_columns", "FUNCTION" ] [ "table used by function", "sys", "comments", "sys", "describe_columns", "FUNCTION" ] @@ -2616,6 +2617,7 @@ select 'null in value_partitions.value', % %1, name, name, name, name, name, dependency_type_name # name % char, varchar, varchar, varchar, varchar, varchar, varchar # type % 23, 3, 26, 21, 3, 37, 9 # length +[ "column used by function", "sys", "db_user_info", "name", "sys", "db_users", "FUNCTION" ] [ "column used by function", "sys", "_columns", "default", "sys", "describe_columns", "FUNCTION" ] [ "column used by function", "sys", "_columns", "id", "sys", "describe_columns", "FUNCTION" ] [ "column used by function", "sys", "_columns", "name", "sys", "describe_columns", "FUNCTION" ] @@ -2897,8 +2899,8 @@ select 'null in value_partitions.value', [ "table used by view", "sys", "keys", "sys", "dependency_keys_on_foreignkeys", "VIEW" ] [ "table used by view", "sys", "auths", "sys", "dependency_owners_on_schemas", "VIEW" ] [ "table used by view", "sys", "schemas", "sys", "dependency_owners_on_schemas", "VIEW" ] +[ "table used by view", "sys", "db_user_info", "sys", "dependency_schemas_on_users", "VIEW" ] [ "table used by view", "sys", "schemas", "sys", "dependency_schemas_on_users", "VIEW" ] -[ "table used by view", "sys", "users", "sys", "dependency_schemas_on_users", "VIEW" ] [ "table used by view", "sys", "keys", "sys", "dependency_tables_on_foreignkeys", "VIEW" ] [ "table used by view", "sys", "tables", "sys", "dependency_tables_on_foreignkeys", "VIEW" ] [ "table used by view", "sys", "dependencies", "sys", "dependency_tables_on_functions", "VIEW" ] @@ -3052,7 +3054,7 @@ select 'null in value_partitions.value', [ "table used by view", "tmp", "keys", "sys", "ids", "VIEW" ] [ "table used by view", "tmp", "triggers", "sys", "ids", "VIEW" ] [ "table used by view", "sys", "auths", "sys", "roles", "VIEW" ] -[ "table used by view", "sys", "users", "sys", "roles", "VIEW" ] +[ "table used by view", "sys", "db_user_info", "sys", "roles", "VIEW" ] [ "table used by view", "sys", "storage", "sys", "schemastorage", "VIEW" ] [ "table used by view", "sys", "schemas", "sys", "statistics", "VIEW" ] [ "table used by view", "sys", "tables", "sys", "statistics", "VIEW" ] @@ -3237,10 +3239,10 @@ select 'null in value_partitions.value', [ "column used by view", "sys", "schemas", "id", "sys", "dependency_owners_on_schemas", "VIEW" ] [ "column used by view", "sys", "schemas", "name", "sys", "dependency_owners_on_schemas", "VIEW" ] [ "column used by view", "sys", "schemas", "owner", "sys", "dependency_owners_on_schemas", "VIEW" ] +[ "column used by view", "sys", "db_user_info", "default_schema", "sys", "dependency_schemas_on_users", "VIEW" ] +[ "column used by view", "sys", "db_user_info", "name", "sys", "dependency_schemas_on_users", "VIEW" ] [ "column used by view", "sys", "schemas", "id", "sys", "dependency_schemas_on_users", "VIEW" ] [ "column used by view", "sys", "schemas", "name", "sys", "dependency_schemas_on_users", "VIEW" ] -[ "column used by view", "sys", "users", "default_schema", "sys", "dependency_schemas_on_users", "VIEW" ] -[ "column used by view", "sys", "users", "name", "sys", "dependency_schemas_on_users", "VIEW" ] [ "column used by view", "sys", "keys", "id", "sys", "dependency_tables_on_foreignkeys", "VIEW" ] [ "column used by view", "sys", "keys", "name", "sys", "dependency_tables_on_foreignkeys", "VIEW" ] [ "column used by view", "sys", "keys", "rkey", "sys", "dependency_tables_on_foreignkeys", "VIEW" ] @@ -3539,7 +3541,6 @@ select 'null in value_partitions.value', [ "column used by view", "sys", "db_user_info", "name", "sys", "dump_create_users", "VIEW" ] [ "column used by view", "sys", "db_user_info", "schema_path", "sys", "dump_create_users", "VIEW" ] [ "column used by view", "sys", "schemas", "id", "sys", "dump_create_users", "VIEW" ] -[ "column used by view", "sys", "schemas", "name", "sys", "dump_create_users", "VIEW" ] [ "column used by view", "sys", "describe_foreign_keys", "fk", "sys", "dump_foreign_keys", "VIEW" ] [ "column used by view", "sys", "describe_foreign_keys", "fk_c", "sys", "dump_foreign_keys", "VIEW" ] [ "column used by view", "sys", "describe_foreign_keys", "fk_s", "sys", "dump_foreign_keys", "VIEW" ] @@ -3763,7 +3764,7 @@ select 'null in value_partitions.value', [ "column used by view", "sys", "auths", "grantor", "sys", "roles", "VIEW" ] [ "column used by view", "sys", "auths", "id", "sys", "roles", "VIEW" ] [ "column used by view", "sys", "auths", "name", "sys", "roles", "VIEW" ] -[ "column used by view", "sys", "users", "name", "sys", "roles", "VIEW" ] +[ "column used by view", "sys", "db_user_info", "name", "sys", "roles", "VIEW" ] [ "column used by view", "sys", "storage", "columnsize", "sys", "schemastorage", "VIEW" ] [ "column used by view", "sys", "storage", "hashes", "sys", "schemastorage", "VIEW" ] [ "column used by view", "sys", "storage", "heapsize", "sys", "schemastorage", "VIEW" ] @@ -4102,7 +4103,7 @@ select 'null in value_partitions.value', [ "grant on table", "triggers", "public", "SELECT", NULL, 0 ] [ "grant on table", "types", "public", "SELECT", NULL, 0 ] [ "grant on table", "user_role", "public", "SELECT", NULL, 0 ] -[ "grant on table", "users", "public", "SELECT", NULL, 0 ] +[ "grant on table", "users", "public", "SELECT", "monetdb", 0 ] [ "grant on table", "value_partitions", "public", "SELECT", NULL, 0 ] [ "grant on table", "var_values", "public", "SELECT", "monetdb", 0 ] % .%1, .t, .c, sys.a, .pc, .g, .p # table_name
--- a/sql/test/emptydb/Tests/check.stable.out.32bit +++ b/sql/test/emptydb/Tests/check.stable.out.32bit @@ -451,7 +451,7 @@ select 'null in value_partitions.value', [ "sys._tables", "sys", "dependency_functions_on_views", "create view sys.dependency_functions_on_views as select f.schema_id as function_schema_id, f.id as function_id, f.name as function_name, v.schema_id as view_schema_id, v.id as view_id, v.name as view_name, dep.depend_type as depend_type from sys.functions as f, sys.tables as v, sys.dependencies as dep where f.id = dep.id and v.id = dep.depend_id and dep.depend_type = 5 and v.type in (1, 11) order by f.schema_id, f.name, v.schema_id, v.name;", "VIEW", true, "COMMIT", "WRITABLE", NULL ] [ "sys._tables", "sys", "dependency_keys_on_foreignkeys", "create view sys.dependency_keys_on_foreignkeys as select k.table_id as key_table_id, k.id as key_id, k.name as key_name, fk.table_id as fk_table_id, fk.id as fk_id, fk.name as fk_name, cast(k.type as smallint) as key_type, cast(11 as smallint) as depend_type from sys.keys as k, sys.keys as fk where k.id = fk.rkey order by k.name, fk.name;", "VIEW", true, "COMMIT", "WRITABLE", NULL ] [ "sys._tables", "sys", "dependency_owners_on_schemas", "create view sys.dependency_owners_on_schemas as select a.name as owner_name, s.id as schema_id, s.name as schema_name, cast(1 as smallint) as depend_type from sys.schemas as s, sys.auths as a where s.owner = a.id order by a.name, s.name;", "VIEW", true, "COMMIT", "WRITABLE", NULL ] -[ "sys._tables", "sys", "dependency_schemas_on_users", "create view sys.dependency_schemas_on_users as select s.id as schema_id, s.name as schema_name, u.name as user_name, cast(6 as smallint) as depend_type from sys.users as u, sys.schemas as s where u.default_schema = s.id order by s.name, u.name;", "VIEW", true, "COMMIT", "WRITABLE", NULL ] +[ "sys._tables", "sys", "dependency_schemas_on_users", "create view sys.dependency_schemas_on_users as select s.id as schema_id, s.name as schema_name, u.name as user_name, cast(6 as smallint) as depend_type from sys.db_user_info as u, sys.schemas as s where u.default_schema = s.id order by s.name, u.name;", "VIEW", true, "COMMIT", "WRITABLE", NULL ] [ "sys._tables", "sys", "dependency_tables_on_foreignkeys", "create view sys.dependency_tables_on_foreignkeys as select t.schema_id as table_schema_id, t.id as table_id, t.name as table_name, fk.name as fk_name, cast(k.type as smallint) as key_type, cast(11 as smallint) as depend_type from sys.tables as t, sys.keys as k, sys.keys as fk where fk.rkey = k.id and k.table_id = t.id order by t.schema_id, t.name, fk.name;", "VIEW", true, "COMMIT", "WRITABLE", NULL ] [ "sys._tables", "sys", "dependency_tables_on_functions", "create view sys.dependency_tables_on_functions as select t.schema_id as table_schema_id, t.id as table_id, t.name as table_name, f.name as function_name, f.type as function_type, dep.depend_type as depend_type from sys.functions as f, sys.tables as t, sys.dependencies as dep where t.id = dep.id and f.id = dep.depend_id and dep.depend_type = 7 and f.type <> 2 and t.type not in (1, 11) order by t.name, t.schema_id, f.name, f.id;", "VIEW", true, "COMMIT", "WRITABLE", NULL ] [ "sys._tables", "sys", "dependency_tables_on_indexes", "create view sys.dependency_tables_on_indexes as select t.schema_id as table_schema_id, t.id as table_id, t.name as table_name, i.id as index_id, i.name as index_name, i.type as index_type, cast(10 as smallint) as depend_type from sys.tables as t, sys.idxs as i where i.table_id = t.id and (i.table_id, i.name) not in (select k.table_id, k.name from sys.keys k) order by t.schema_id, t.name, i.name;", "VIEW", true, "COMMIT", "WRITABLE", NULL ] @@ -480,7 +480,7 @@ select 'null in value_partitions.value', [ "sys._tables", "sys", "dump_comments", "create view sys.dump_comments as select 'COMMENT ON ' || c.tpe || ' ' || c.fqn || ' IS ' || sys.sq(c.rem) || ';' stmt from sys.describe_comments c;", "VIEW", true, "COMMIT", "WRITABLE", NULL ] [ "sys._tables", "sys", "dump_create_roles", "create view sys.dump_create_roles as select 'CREATE ROLE ' || sys.dq(name) || ';' stmt, name user_name from sys.auths where name not in (select name from sys.db_user_info) and grantor <> 0;", "VIEW", true, "COMMIT", "WRITABLE", NULL ] [ "sys._tables", "sys", "dump_create_schemas", "create view sys.dump_create_schemas as select 'CREATE SCHEMA ' || sys.dq(s.name) || ifthenelse(a.name <> 'sysadmin', ' AUTHORIZATION ' || sys.dq(a.name), ' ') || ';' stmt, s.name schema_name from sys.schemas s, sys.auths a where s.authorization = a.id and s.system = false;", "VIEW", true, "COMMIT", "WRITABLE", NULL ] -[ "sys._tables", "sys", "dump_create_users", "create view sys.dump_create_users as select 'CREATE USER ' || sys.dq(ui.name) || ' WITH ENCRYPTED PASSWORD ' || sys.sq(sys.password_hash(ui.name)) || ' NAME ' || sys.sq(ui.fullname) || ' SCHEMA ' || sys.sq(s.name) || ' SCHEMA PATH ' || sys.sq(ui.schema_path) || ';' stmt, ui.name user_name from sys.db_user_info ui, sys.schemas s where ui.default_schema = s.id and ui.name <> 'monetdb' and ui.name <> '.snapshot';", "VIEW", true, "COMMIT", "WRITABLE", NULL ] +[ "sys._tables", "sys", "dump_create_users", "create view sys.dump_create_users as select 'CREATE USER ' || sys.dq(ui.name) || ' WITH ENCRYPTED PASSWORD ' || sys.sq(sys.password_hash(ui.name)) || ' NAME ' || sys.sq(ui.fullname) || ' SCHEMA sys' || ifthenelse(ui.schema_path = '\"sys\"', '', ' SCHEMA PATH ' || sys.sq(ui.schema_path)) || ';' stmt, ui.name user_name from sys.db_user_info ui, sys.schemas s where ui.default_schema = s.id and ui.name <> 'monetdb' and ui.name <> '.snapshot';", "VIEW", true, "COMMIT", "WRITABLE", NULL ] [ "sys._tables", "sys", "dump_foreign_keys", "create view sys.dump_foreign_keys as select 'ALTER TABLE ' || sys.fqn(fk_s, fk_t) || ' ADD CONSTRAINT ' || sys.dq(fk) || ' ' || 'FOREIGN KEY(' || group_concat(sys.dq(fk_c), ',') ||') ' || 'REFERENCES ' || sys.fqn(pk_s, pk_t) || '(' || group_concat(sys.dq(pk_c), ',') || ') ' || 'ON DELETE ' || on_delete || ' ON UPDATE ' || on_update || ';' stmt, fk_s foreign_schema_name, fk_t foreign_table_name, pk_s primary_schema_name, pk_t primary_table_name, fk key_name from sys.describe_foreign_keys group by fk_s, fk_t, pk_s, pk_t, fk, on_delete, on_update;", "VIEW", true, "COMMIT", "WRITABLE", NULL ] [ "sys._tables", "sys", "dump_function_grants", "create view sys.dump_function_grants as with func_args_all(func_id, number, max_number, func_arg) as (select a.func_id, a.number, max(a.number) over (partition by a.func_id order by a.number desc), group_concat(sys.describe_type(a.type, a.type_digits, a.type_scale), ', ') over (partition by a.func_id order by a.number) from sys.args a where a.inout = 1), func_args(func_id, func_arg) as (select func_id, func_arg from func_args_all where number = max_number) select 'GRANT ' || pc.privilege_code_name || ' ON ' || ft.function_type_keyword || ' ' || sys.fqn(s.name, f.name) || '(' || coalesce(fa.func_arg, '') || ') TO ' || ifthenelse(a.name = 'public', 'PUBLIC', sys.dq(a.name)) || case when p.grantable = 1 then ' WITH GRANT OPTION' else '' end || ';' stmt, s.name schema_name, f.name function_name, a.name grantee from sys.schemas s, sys.functions f left outer join func_args fa on f.id = fa.func_id, sys.auths a, sys.privileges p, sys.auths g, sys.function_types ft, sys.privilege_codes pc where s.id = f.schema_id and f.id = p.obj_id and p.auth_id = a.id and p.grantor = g.id and p.privileges = pc.privilege_code_id and f.type = ft.function_type_id and not f.system order by s.name, f.name, a.name, g.name, p.grantable;", "VIEW", true, "COMMIT", "WRITABLE", NULL ] [ "sys._tables", "sys", "dump_functions", "create view sys.dump_functions as select f.o o, sys.schema_guard(f.sch, f.fun, f.def) stmt, f.sch schema_name, f.fun function_name from sys.describe_functions f;", "VIEW", true, "COMMIT", "WRITABLE", NULL ] @@ -527,7 +527,7 @@ select 'null in value_partitions.value', [ "sys._tables", "sys", "queue", "create view sys.queue as select * from sys.queue();", "VIEW", true, "COMMIT", "WRITABLE", NULL ] [ "sys._tables", "sys", "range_partitions", NULL, "TABLE", true, "COMMIT", "WRITABLE", NULL ] [ "sys._tables", "sys", "rejects", "create view sys.rejects as select * from sys.rejects();", "VIEW", true, "COMMIT", "WRITABLE", NULL ] -[ "sys._tables", "sys", "roles", "create view sys.roles as select id, name, grantor from sys.auths a where a.name not in (select u.name from sys.users u);", "VIEW", true, "COMMIT", "WRITABLE", NULL ] +[ "sys._tables", "sys", "roles", "create view sys.roles as select id, name, grantor from sys.auths a where a.name not in (select u.name from sys.db_user_info u);", "VIEW", true, "COMMIT", "WRITABLE", NULL ] [ "sys._tables", "sys", "schemas", NULL, "TABLE", true, "COMMIT", "WRITABLE", NULL ] [ "sys._tables", "sys", "schemastorage", "create view sys.\"schemastorage\" as select \"schema\", count(*) as \"storages\", sum(columnsize) as columnsize, sum(heapsize) as heapsize, sum(hashes) as hashsize, sum(\"imprints\") as imprintsize, sum(orderidx) as orderidxsize from sys.\"storage\" group by \"schema\" order by \"schema\";", "VIEW", true, "COMMIT", "WRITABLE", NULL ] [ "sys._tables", "sys", "sequences", NULL, "TABLE", true, "COMMIT", "WRITABLE", NULL ] @@ -546,7 +546,7 @@ select 'null in value_partitions.value', [ "sys._tables", "sys", "triggers", NULL, "TABLE", true, "COMMIT", "WRITABLE", NULL ] [ "sys._tables", "sys", "types", NULL, "TABLE", true, "COMMIT", "WRITABLE", NULL ] [ "sys._tables", "sys", "user_role", NULL, "TABLE", true, "COMMIT", "WRITABLE", NULL ] -[ "sys._tables", "sys", "users", "create view sys.users as select u.\"name\", u.\"fullname\", u.\"default_schema\", u.\"schema_path\", u.\"max_memory\", u.\"max_workers\", u.\"optimizer\", u.\"default_role\" from \"sys\".\"db_user_info\" as u;", "VIEW", true, "COMMIT", "WRITABLE", NULL ] +[ "sys._tables", "sys", "users", "create view sys.users as select name, fullname, default_schema, schema_path, max_memory, max_workers, optimizer, default_role from sys.db_user_info;", "VIEW", true, "COMMIT", "WRITABLE", NULL ] [ "sys._tables", "sys", "value_partitions", NULL, "TABLE", true, "COMMIT", "WRITABLE", NULL ] [ "sys._tables", "sys", "var_values", "create view sys.var_values (var_name, value) as select 'current_role', current_role union all select 'current_schema', current_schema union all select 'current_timezone', current_timezone union all select 'current_user', current_user union all select 'debug', debug union all select 'last_id', last_id union all select 'optimizer', optimizer union all select 'pi', pi() union all select 'rowcnt', rowcnt;", "VIEW", true, "COMMIT", "WRITABLE", NULL ] [ "sys._tables", "tmp", "_columns", NULL, "TABLE", true, "PRESERVE", "WRITABLE", NULL ] @@ -625,10 +625,10 @@ select 'null in value_partitions.value', [ "sys._columns", "db_user_info", "fullname", "varchar", 2048, 0, NULL, true, 1, NULL, NULL ] [ "sys._columns", "db_user_info", "default_schema", "int", 9, 0, NULL, true, 2, NULL, NULL ] [ "sys._columns", "db_user_info", "schema_path", "clob", 0, 0, NULL, true, 3, NULL, NULL ] -[ "sys._columns", "db_user_info", "max_memory", "bigint", 19, 0, NULL, true, 4, NULL, NULL ] -[ "sys._columns", "db_user_info", "max_workers", "int", 9, 0, NULL, true, 5, NULL, NULL ] +[ "sys._columns", "db_user_info", "max_memory", "bigint", 64, 0, NULL, true, 4, NULL, NULL ] +[ "sys._columns", "db_user_info", "max_workers", "int", 32, 0, NULL, true, 5, NULL, NULL ] [ "sys._columns", "db_user_info", "optimizer", "varchar", 1024, 0, NULL, true, 6, NULL, NULL ] -[ "sys._columns", "db_user_info", "default_role", "int", 9, 0, NULL, true, 7, NULL, NULL ] +[ "sys._columns", "db_user_info", "default_role", "int", 32, 0, NULL, true, 7, NULL, NULL ] [ "sys._columns", "db_user_info", "password", "varchar", 256, 0, NULL, true, 8, NULL, NULL ] [ "sys._columns", "dependencies", "id", "int", 32, 0, NULL, true, 0, NULL, NULL ] [ "sys._columns", "dependencies", "depend_id", "int", 32, 0, NULL, true, 1, NULL, NULL ] @@ -1313,14 +1313,14 @@ select 'null in value_partitions.value', [ "sys._columns", "types", "schema_id", "int", 32, 0, NULL, true, 7, NULL, NULL ] [ "sys._columns", "user_role", "login_id", "int", 32, 0, NULL, true, 0, NULL, NULL ] [ "sys._columns", "user_role", "role_id", "int", 32, 0, NULL, true, 1, NULL, NULL ] -[ "sys._columns", "users", "name", "varchar", 2048, 0, NULL, true, 0, NULL, NULL ] +[ "sys._columns", "users", "name", "varchar", 1024, 0, NULL, true, 0, NULL, NULL ] [ "sys._columns", "users", "fullname", "varchar", 2048, 0, NULL, true, 1, NULL, NULL ] [ "sys._columns", "users", "default_schema", "int", 9, 0, NULL, true, 2, NULL, NULL ] [ "sys._columns", "users", "schema_path", "clob", 0, 0, NULL, true, 3, NULL, NULL ] -[ "sys._columns", "users", "max_memory", "bigint", 19, 0, NULL, true, 4, NULL, NULL ] -[ "sys._columns", "users", "max_workers", "int", 9, 0, NULL, true, 5, NULL, NULL ] +[ "sys._columns", "users", "max_memory", "bigint", 64, 0, NULL, true, 4, NULL, NULL ] +[ "sys._columns", "users", "max_workers", "int", 32, 0, NULL, true, 5, NULL, NULL ] [ "sys._columns", "users", "optimizer", "varchar", 1024, 0, NULL, true, 6, NULL, NULL ] -[ "sys._columns", "users", "default_role", "int", 9, 0, NULL, true, 7, NULL, NULL ] +[ "sys._columns", "users", "default_role", "int", 32, 0, NULL, true, 7, NULL, NULL ] [ "sys._columns", "value_partitions", "table_id", "int", 32, 0, NULL, true, 0, NULL, NULL ] [ "sys._columns", "value_partitions", "partition_id", "int", 32, 0, NULL, true, 1, NULL, NULL ] [ "sys._columns", "value_partitions", "value", "varchar", 2048, 0, NULL, true, 2, NULL, NULL ] @@ -1554,7 +1554,7 @@ select 'null in value_partitions.value', [ "sys.functions", "sys", "dayofmonth", "SYSTEM", "day", "mtime", "Internal C", "Scalar function", false, false, false, false, NULL, "res_0", "int", 32, 0, "out", "arg_1", "date", 0, 0, "in", NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL ] [ "sys.functions", "sys", "dayofweek", "SYSTEM", "dayofweek", "mtime", "Internal C", "Scalar function", false, false, false, false, NULL, "res_0", "int", 32, 0, "out", "arg_1", "date", 0, 0, "in", NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL ] [ "sys.functions", "sys", "dayofyear", "SYSTEM", "dayofyear", "mtime", "Internal C", "Scalar function", false, false, false, false, NULL, "res_0", "int", 32, 0, "out", "arg_1", "date", 0, 0, "in", NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL ] -[ "sys.functions", "sys", "db_users", "SYSTEM", "CREATE FUNCTION db_users () RETURNS TABLE(name varchar(2048)) return select name from users;", "sql", "SQL", "Function returning a table", false, false, false, true, NULL, "name", "varchar", 2048, 0, "out", NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL ] +[ "sys.functions", "sys", "db_users", "SYSTEM", "create function sys.db_users() returns table(name varchar(2048)) return select name from sys.db_user_info;", "sql", "SQL", "Function returning a table", false, false, false, true, NULL, "name", "varchar", 2048, 0, "out", NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL ] [ "sys.functions", "sys", "debug", "SYSTEM", "create function sys.debug(debug int) returns integer external name mdb.\"setDebug\";", "mdb", "MAL", "Scalar function", false, false, false, true, NULL, "result", "int", 32, 0, "out", "debug", "int", 32, 0, "in", NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL ] [ "sys.functions", "sys", "debug", "SYSTEM", "create function sys.debug(flag string) returns integer external name mdb.\"setDebug\";", "mdb", "MAL", "Scalar function", false, false, false, true, NULL, "result", "int", 32, 0, "out", "flag", "clob", 0, 0, "in", NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL ] [ "sys.functions", "sys", "debugflags", "SYSTEM", "create function sys.debugflags() returns table(flag string, val bool) external name mdb.\"getDebugFlags\";", "mdb", "MAL", "Function returning a table", false, false, false, true, NULL, "flag", "clob", 0, 0, "out", "val", "boolean", 1, 0, "out", NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL ] @@ -2560,6 +2560,7 @@ select 'null in value_partitions.value', % %1, name, name, name, name, dependency_type_name # name % char, varchar, varchar, varchar, varchar, varchar # type % 22, 3, 26, 3, 37, 9 # length +[ "table used by function", "sys", "db_user_info", "sys", "db_users", "FUNCTION" ] [ "table used by function", "sys", "_columns", "sys", "describe_columns", "FUNCTION" ] [ "table used by function", "sys", "_tables", "sys", "describe_columns", "FUNCTION" ] [ "table used by function", "sys", "comments", "sys", "describe_columns", "FUNCTION" ] @@ -2616,6 +2617,7 @@ select 'null in value_partitions.value', % %1, name, name, name, name, name, dependency_type_name # name % char, varchar, varchar, varchar, varchar, varchar, varchar # type % 23, 3, 26, 21, 3, 37, 9 # length +[ "column used by function", "sys", "db_user_info", "name", "sys", "db_users", "FUNCTION" ] [ "column used by function", "sys", "_columns", "default", "sys", "describe_columns", "FUNCTION" ] [ "column used by function", "sys", "_columns", "id", "sys", "describe_columns", "FUNCTION" ] [ "column used by function", "sys", "_columns", "name", "sys", "describe_columns", "FUNCTION" ] @@ -2897,8 +2899,8 @@ select 'null in value_partitions.value', [ "table used by view", "sys", "keys", "sys", "dependency_keys_on_foreignkeys", "VIEW" ] [ "table used by view", "sys", "auths", "sys", "dependency_owners_on_schemas", "VIEW" ] [ "table used by view", "sys", "schemas", "sys", "dependency_owners_on_schemas", "VIEW" ] +[ "table used by view", "sys", "db_user_info", "sys", "dependency_schemas_on_users", "VIEW" ] [ "table used by view", "sys", "schemas", "sys", "dependency_schemas_on_users", "VIEW" ] -[ "table used by view", "sys", "users", "sys", "dependency_schemas_on_users", "VIEW" ] [ "table used by view", "sys", "keys", "sys", "dependency_tables_on_foreignkeys", "VIEW" ] [ "table used by view", "sys", "tables", "sys", "dependency_tables_on_foreignkeys", "VIEW" ] [ "table used by view", "sys", "dependencies", "sys", "dependency_tables_on_functions", "VIEW" ] @@ -3052,7 +3054,7 @@ select 'null in value_partitions.value', [ "table used by view", "tmp", "keys", "sys", "ids", "VIEW" ] [ "table used by view", "tmp", "triggers", "sys", "ids", "VIEW" ] [ "table used by view", "sys", "auths", "sys", "roles", "VIEW" ] -[ "table used by view", "sys", "users", "sys", "roles", "VIEW" ] +[ "table used by view", "sys", "db_user_info", "sys", "roles", "VIEW" ] [ "table used by view", "sys", "storage", "sys", "schemastorage", "VIEW" ] [ "table used by view", "sys", "schemas", "sys", "statistics", "VIEW" ] [ "table used by view", "sys", "tables", "sys", "statistics", "VIEW" ] @@ -3237,10 +3239,10 @@ select 'null in value_partitions.value', [ "column used by view", "sys", "schemas", "id", "sys", "dependency_owners_on_schemas", "VIEW" ] [ "column used by view", "sys", "schemas", "name", "sys", "dependency_owners_on_schemas", "VIEW" ] [ "column used by view", "sys", "schemas", "owner", "sys", "dependency_owners_on_schemas", "VIEW" ] +[ "column used by view", "sys", "db_user_info", "default_schema", "sys", "dependency_schemas_on_users", "VIEW" ] +[ "column used by view", "sys", "db_user_info", "name", "sys", "dependency_schemas_on_users", "VIEW" ] [ "column used by view", "sys", "schemas", "id", "sys", "dependency_schemas_on_users", "VIEW" ] [ "column used by view", "sys", "schemas", "name", "sys", "dependency_schemas_on_users", "VIEW" ] -[ "column used by view", "sys", "users", "default_schema", "sys", "dependency_schemas_on_users", "VIEW" ] -[ "column used by view", "sys", "users", "name", "sys", "dependency_schemas_on_users", "VIEW" ] [ "column used by view", "sys", "keys", "id", "sys", "dependency_tables_on_foreignkeys", "VIEW" ] [ "column used by view", "sys", "keys", "name", "sys", "dependency_tables_on_foreignkeys", "VIEW" ] [ "column used by view", "sys", "keys", "rkey", "sys", "dependency_tables_on_foreignkeys", "VIEW" ] @@ -3539,7 +3541,6 @@ select 'null in value_partitions.value', [ "column used by view", "sys", "db_user_info", "name", "sys", "dump_create_users", "VIEW" ] [ "column used by view", "sys", "db_user_info", "schema_path", "sys", "dump_create_users", "VIEW" ] [ "column used by view", "sys", "schemas", "id", "sys", "dump_create_users", "VIEW" ] -[ "column used by view", "sys", "schemas", "name", "sys", "dump_create_users", "VIEW" ] [ "column used by view", "sys", "describe_foreign_keys", "fk", "sys", "dump_foreign_keys", "VIEW" ] [ "column used by view", "sys", "describe_foreign_keys", "fk_c", "sys", "dump_foreign_keys", "VIEW" ] [ "column used by view", "sys", "describe_foreign_keys", "fk_s", "sys", "dump_foreign_keys", "VIEW" ] @@ -3763,7 +3764,7 @@ select 'null in value_partitions.value', [ "column used by view", "sys", "auths", "grantor", "sys", "roles", "VIEW" ] [ "column used by view", "sys", "auths", "id", "sys", "roles", "VIEW" ] [ "column used by view", "sys", "auths", "name", "sys", "roles", "VIEW" ] -[ "column used by view", "sys", "users", "name", "sys", "roles", "VIEW" ] +[ "column used by view", "sys", "db_user_info", "name", "sys", "roles", "VIEW" ] [ "column used by view", "sys", "storage", "columnsize", "sys", "schemastorage", "VIEW" ] [ "column used by view", "sys", "storage", "hashes", "sys", "schemastorage", "VIEW" ] [ "column used by view", "sys", "storage", "heapsize", "sys", "schemastorage", "VIEW" ] @@ -4102,7 +4103,7 @@ select 'null in value_partitions.value', [ "grant on table", "triggers", "public", "SELECT", NULL, 0 ] [ "grant on table", "types", "public", "SELECT", NULL, 0 ] [ "grant on table", "user_role", "public", "SELECT", NULL, 0 ] -[ "grant on table", "users", "public", "SELECT", NULL, 0 ] +[ "grant on table", "users", "public", "SELECT", "monetdb", 0 ] [ "grant on table", "value_partitions", "public", "SELECT", NULL, 0 ] [ "grant on table", "var_values", "public", "SELECT", "monetdb", 0 ] % .%1, .t, .c, sys.a, .pc, .g, .p # table_name
--- a/sql/test/testdb-previous-upgrade-chain/Tests/upgrade.stable.out +++ b/sql/test/testdb-previous-upgrade-chain/Tests/upgrade.stable.out @@ -3740,6 +3740,34 @@ update sys._tables set system = true whe update sys.functions set system = true where system <> true and schema_id = 2000 and name in ('analyze','statistics'); Running database upgrade commands: +alter table sys.db_user_info add column max_memory bigint; +alter table sys.db_user_info add column max_workers int; +alter table sys.db_user_info add column optimizer varchar(1024); +alter table sys.db_user_info add column default_role int; +alter table sys.db_user_info add column password varchar(256); +update sys.db_user_info u set max_memory = 0, max_workers = 0, optimizer = 'default_pipe', default_role = (select id from sys.auths a where a.name = u.name); +-- and copying passwords + +Running database upgrade commands: +drop view sys.dependency_schemas_on_users; +drop view sys.roles; +drop view sys.users; +drop function sys.db_users(); +CREATE VIEW sys.roles AS SELECT id, name, grantor FROM sys.auths a WHERE a.name NOT IN (SELECT u.name FROM sys.db_user_info u); +GRANT SELECT ON sys.roles TO PUBLIC; +CREATE VIEW sys.users AS SELECT name, fullname, default_schema, schema_path, max_memory, max_workers, optimizer, default_role FROM sys.db_user_info; +GRANT SELECT ON sys.users TO PUBLIC; +CREATE FUNCTION sys.db_users() RETURNS TABLE(name varchar(2048)) RETURN SELECT name FROM sys.db_user_info; +CREATE VIEW sys.dependency_schemas_on_users AS +SELECT s.id AS schema_id, s.name AS schema_name, u.name AS user_name, CAST(6 AS smallint) AS depend_type + FROM sys.db_user_info AS u, sys.schemas AS s + WHERE u.default_schema = s.id + ORDER BY s.name, u.name; +GRANT SELECT ON sys.dependency_schemas_on_users TO PUBLIC; +update sys._tables set system = true where name in ('users', 'roles', 'dependency_schemas_on_users') AND schema_id = 2000; +update sys.functions set system = true where system <> true and name in ('db_users') and schema_id = 2000 and type = 5; + +Running database upgrade commands: drop function sys.dump_database(boolean); drop procedure sys.dump_table_data(); drop procedure sys.dump_table_data(string, string); @@ -3749,6 +3777,7 @@ drop view sys.dump_sequences; drop view sys.dump_start_sequences; drop view sys.dump_tables; drop view sys.describe_tables; +drop view sys.dump_create_users; CREATE VIEW sys.describe_partition_tables AS SELECT m_sch, @@ -3893,6 +3922,16 @@ BEGIN END WHILE; END IF; END; +CREATE VIEW sys.dump_create_users AS + SELECT + 'CREATE USER ' || sys.dq(ui.name) || ' WITH ENCRYPTED PASSWORD ' || + sys.sq(sys.password_hash(ui.name)) || + ' NAME ' || sys.sq(ui.fullname) || ' SCHEMA sys' || ifthenelse(ui.schema_path = '"sys"', '', ' SCHEMA PATH ' || sys.sq(ui.schema_path)) || ';' stmt, + ui.name user_name + FROM sys.db_user_info ui, sys.schemas s + WHERE ui.default_schema = s.id + AND ui.name <> 'monetdb' + AND ui.name <> '.snapshot'; CREATE VIEW sys.describe_tables AS SELECT t.id o, @@ -3978,7 +4017,7 @@ BEGIN INSERT INTO sys.dump_statements VALUES ((SELECT COUNT(*) FROM sys.dump_statements) + 1, 'COMMIT;'); RETURN sys.dump_statements; END; -update sys._tables set system = true where name in ('describe_partition_tables', 'dump_partition_tables', 'dump_sequences', 'dump_start_sequences', 'describe_tables', 'dump_tables') AND schema_id = 2000; +update sys._tables set system = true where name in ('describe_partition_tables', 'dump_partition_tables', 'dump_sequences', 'dump_start_sequences', 'describe_tables', 'dump_tables', 'dump_create_users') AND schema_id = 2000; update sys.functions set system = true where system <> true and name in ('dump_table_data') and schema_id = 2000 and type = 2; update sys.functions set system = true where system <> true and name in ('dump_database') and schema_id = 2000 and type = 5; CREATE function sys.url_extract_host(url string, no_www bool) RETURNS STRING
--- a/sql/test/testdb-previous-upgrade-chain/Tests/upgrade.stable.out.32bit +++ b/sql/test/testdb-previous-upgrade-chain/Tests/upgrade.stable.out.32bit @@ -3740,6 +3740,34 @@ update sys._tables set system = true whe update sys.functions set system = true where system <> true and schema_id = 2000 and name in ('analyze','statistics'); Running database upgrade commands: +alter table sys.db_user_info add column max_memory bigint; +alter table sys.db_user_info add column max_workers int; +alter table sys.db_user_info add column optimizer varchar(1024); +alter table sys.db_user_info add column default_role int; +alter table sys.db_user_info add column password varchar(256); +update sys.db_user_info u set max_memory = 0, max_workers = 0, optimizer = 'default_pipe', default_role = (select id from sys.auths a where a.name = u.name); +-- and copying passwords + +Running database upgrade commands: +drop view sys.dependency_schemas_on_users; +drop view sys.roles; +drop view sys.users; +drop function sys.db_users(); +CREATE VIEW sys.roles AS SELECT id, name, grantor FROM sys.auths a WHERE a.name NOT IN (SELECT u.name FROM sys.db_user_info u); +GRANT SELECT ON sys.roles TO PUBLIC; +CREATE VIEW sys.users AS SELECT name, fullname, default_schema, schema_path, max_memory, max_workers, optimizer, default_role FROM sys.db_user_info; +GRANT SELECT ON sys.users TO PUBLIC; +CREATE FUNCTION sys.db_users() RETURNS TABLE(name varchar(2048)) RETURN SELECT name FROM sys.db_user_info; +CREATE VIEW sys.dependency_schemas_on_users AS +SELECT s.id AS schema_id, s.name AS schema_name, u.name AS user_name, CAST(6 AS smallint) AS depend_type + FROM sys.db_user_info AS u, sys.schemas AS s + WHERE u.default_schema = s.id + ORDER BY s.name, u.name; +GRANT SELECT ON sys.dependency_schemas_on_users TO PUBLIC; +update sys._tables set system = true where name in ('users', 'roles', 'dependency_schemas_on_users') AND schema_id = 2000; +update sys.functions set system = true where system <> true and name in ('db_users') and schema_id = 2000 and type = 5; + +Running database upgrade commands: drop function sys.dump_database(boolean); drop procedure sys.dump_table_data(); drop procedure sys.dump_table_data(string, string); @@ -3749,6 +3777,7 @@ drop view sys.dump_sequences; drop view sys.dump_start_sequences; drop view sys.dump_tables; drop view sys.describe_tables; +drop view sys.dump_create_users; CREATE VIEW sys.describe_partition_tables AS SELECT m_sch, @@ -3893,6 +3922,16 @@ BEGIN END WHILE; END IF; END; +CREATE VIEW sys.dump_create_users AS + SELECT + 'CREATE USER ' || sys.dq(ui.name) || ' WITH ENCRYPTED PASSWORD ' || + sys.sq(sys.password_hash(ui.name)) || + ' NAME ' || sys.sq(ui.fullname) || ' SCHEMA sys' || ifthenelse(ui.schema_path = '"sys"', '', ' SCHEMA PATH ' || sys.sq(ui.schema_path)) || ';' stmt, + ui.name user_name + FROM sys.db_user_info ui, sys.schemas s + WHERE ui.default_schema = s.id + AND ui.name <> 'monetdb' + AND ui.name <> '.snapshot'; CREATE VIEW sys.describe_tables AS SELECT t.id o, @@ -3978,7 +4017,7 @@ BEGIN INSERT INTO sys.dump_statements VALUES ((SELECT COUNT(*) FROM sys.dump_statements) + 1, 'COMMIT;'); RETURN sys.dump_statements; END; -update sys._tables set system = true where name in ('describe_partition_tables', 'dump_partition_tables', 'dump_sequences', 'dump_start_sequences', 'describe_tables', 'dump_tables') AND schema_id = 2000; +update sys._tables set system = true where name in ('describe_partition_tables', 'dump_partition_tables', 'dump_sequences', 'dump_start_sequences', 'describe_tables', 'dump_tables', 'dump_create_users') AND schema_id = 2000; update sys.functions set system = true where system <> true and name in ('dump_table_data') and schema_id = 2000 and type = 2; update sys.functions set system = true where system <> true and name in ('dump_database') and schema_id = 2000 and type = 5; CREATE function sys.url_extract_host(url string, no_www bool) RETURNS STRING
--- a/sql/test/testdb-previous-upgrade/Tests/upgrade.stable.out +++ b/sql/test/testdb-previous-upgrade/Tests/upgrade.stable.out @@ -3734,6 +3734,34 @@ update sys._tables set system = true whe update sys.functions set system = true where system <> true and schema_id = 2000 and name in ('analyze','statistics'); Running database upgrade commands: +alter table sys.db_user_info add column max_memory bigint; +alter table sys.db_user_info add column max_workers int; +alter table sys.db_user_info add column optimizer varchar(1024); +alter table sys.db_user_info add column default_role int; +alter table sys.db_user_info add column password varchar(256); +update sys.db_user_info u set max_memory = 0, max_workers = 0, optimizer = 'default_pipe', default_role = (select id from sys.auths a where a.name = u.name); +-- and copying passwords + +Running database upgrade commands: +drop view sys.dependency_schemas_on_users; +drop view sys.roles; +drop view sys.users; +drop function sys.db_users(); +CREATE VIEW sys.roles AS SELECT id, name, grantor FROM sys.auths a WHERE a.name NOT IN (SELECT u.name FROM sys.db_user_info u); +GRANT SELECT ON sys.roles TO PUBLIC; +CREATE VIEW sys.users AS SELECT name, fullname, default_schema, schema_path, max_memory, max_workers, optimizer, default_role FROM sys.db_user_info; +GRANT SELECT ON sys.users TO PUBLIC; +CREATE FUNCTION sys.db_users() RETURNS TABLE(name varchar(2048)) RETURN SELECT name FROM sys.db_user_info; +CREATE VIEW sys.dependency_schemas_on_users AS +SELECT s.id AS schema_id, s.name AS schema_name, u.name AS user_name, CAST(6 AS smallint) AS depend_type + FROM sys.db_user_info AS u, sys.schemas AS s + WHERE u.default_schema = s.id + ORDER BY s.name, u.name; +GRANT SELECT ON sys.dependency_schemas_on_users TO PUBLIC; +update sys._tables set system = true where name in ('users', 'roles', 'dependency_schemas_on_users') AND schema_id = 2000; +update sys.functions set system = true where system <> true and name in ('db_users') and schema_id = 2000 and type = 5; + +Running database upgrade commands: drop function sys.dump_database(boolean); drop procedure sys.dump_table_data(); drop procedure sys.dump_table_data(string, string); @@ -3743,6 +3771,7 @@ drop view sys.dump_sequences; drop view sys.dump_start_sequences; drop view sys.dump_tables; drop view sys.describe_tables; +drop view sys.dump_create_users; CREATE VIEW sys.describe_partition_tables AS SELECT m_sch, @@ -3887,6 +3916,16 @@ BEGIN END WHILE; END IF; END; +CREATE VIEW sys.dump_create_users AS + SELECT + 'CREATE USER ' || sys.dq(ui.name) || ' WITH ENCRYPTED PASSWORD ' || + sys.sq(sys.password_hash(ui.name)) || + ' NAME ' || sys.sq(ui.fullname) || ' SCHEMA sys' || ifthenelse(ui.schema_path = '"sys"', '', ' SCHEMA PATH ' || sys.sq(ui.schema_path)) || ';' stmt, + ui.name user_name + FROM sys.db_user_info ui, sys.schemas s + WHERE ui.default_schema = s.id + AND ui.name <> 'monetdb' + AND ui.name <> '.snapshot'; CREATE VIEW sys.describe_tables AS SELECT t.id o, @@ -3972,7 +4011,7 @@ BEGIN INSERT INTO sys.dump_statements VALUES ((SELECT COUNT(*) FROM sys.dump_statements) + 1, 'COMMIT;'); RETURN sys.dump_statements; END; -update sys._tables set system = true where name in ('describe_partition_tables', 'dump_partition_tables', 'dump_sequences', 'dump_start_sequences', 'describe_tables', 'dump_tables') AND schema_id = 2000; +update sys._tables set system = true where name in ('describe_partition_tables', 'dump_partition_tables', 'dump_sequences', 'dump_start_sequences', 'describe_tables', 'dump_tables', 'dump_create_users') AND schema_id = 2000; update sys.functions set system = true where system <> true and name in ('dump_table_data') and schema_id = 2000 and type = 2; update sys.functions set system = true where system <> true and name in ('dump_database') and schema_id = 2000 and type = 5; CREATE function sys.url_extract_host(url string, no_www bool) RETURNS STRING
--- a/sql/test/testdb-previous-upgrade/Tests/upgrade.stable.out.32bit +++ b/sql/test/testdb-previous-upgrade/Tests/upgrade.stable.out.32bit @@ -3734,6 +3734,34 @@ update sys._tables set system = true whe update sys.functions set system = true where system <> true and schema_id = 2000 and name in ('analyze','statistics'); Running database upgrade commands: +alter table sys.db_user_info add column max_memory bigint; +alter table sys.db_user_info add column max_workers int; +alter table sys.db_user_info add column optimizer varchar(1024); +alter table sys.db_user_info add column default_role int; +alter table sys.db_user_info add column password varchar(256); +update sys.db_user_info u set max_memory = 0, max_workers = 0, optimizer = 'default_pipe', default_role = (select id from sys.auths a where a.name = u.name); +-- and copying passwords + +Running database upgrade commands: +drop view sys.dependency_schemas_on_users; +drop view sys.roles; +drop view sys.users; +drop function sys.db_users(); +CREATE VIEW sys.roles AS SELECT id, name, grantor FROM sys.auths a WHERE a.name NOT IN (SELECT u.name FROM sys.db_user_info u); +GRANT SELECT ON sys.roles TO PUBLIC; +CREATE VIEW sys.users AS SELECT name, fullname, default_schema, schema_path, max_memory, max_workers, optimizer, default_role FROM sys.db_user_info; +GRANT SELECT ON sys.users TO PUBLIC; +CREATE FUNCTION sys.db_users() RETURNS TABLE(name varchar(2048)) RETURN SELECT name FROM sys.db_user_info; +CREATE VIEW sys.dependency_schemas_on_users AS +SELECT s.id AS schema_id, s.name AS schema_name, u.name AS user_name, CAST(6 AS smallint) AS depend_type + FROM sys.db_user_info AS u, sys.schemas AS s + WHERE u.default_schema = s.id + ORDER BY s.name, u.name; +GRANT SELECT ON sys.dependency_schemas_on_users TO PUBLIC; +update sys._tables set system = true where name in ('users', 'roles', 'dependency_schemas_on_users') AND schema_id = 2000; +update sys.functions set system = true where system <> true and name in ('db_users') and schema_id = 2000 and type = 5; + +Running database upgrade commands: drop function sys.dump_database(boolean); drop procedure sys.dump_table_data(); drop procedure sys.dump_table_data(string, string); @@ -3743,6 +3771,7 @@ drop view sys.dump_sequences; drop view sys.dump_start_sequences; drop view sys.dump_tables; drop view sys.describe_tables; +drop view sys.dump_create_users; CREATE VIEW sys.describe_partition_tables AS SELECT m_sch, @@ -3887,6 +3916,16 @@ BEGIN END WHILE; END IF; END; +CREATE VIEW sys.dump_create_users AS + SELECT + 'CREATE USER ' || sys.dq(ui.name) || ' WITH ENCRYPTED PASSWORD ' || + sys.sq(sys.password_hash(ui.name)) || + ' NAME ' || sys.sq(ui.fullname) || ' SCHEMA sys' || ifthenelse(ui.schema_path = '"sys"', '', ' SCHEMA PATH ' || sys.sq(ui.schema_path)) || ';' stmt, + ui.name user_name + FROM sys.db_user_info ui, sys.schemas s + WHERE ui.default_schema = s.id + AND ui.name <> 'monetdb' + AND ui.name <> '.snapshot'; CREATE VIEW sys.describe_tables AS SELECT t.id o, @@ -3972,7 +4011,7 @@ BEGIN INSERT INTO sys.dump_statements VALUES ((SELECT COUNT(*) FROM sys.dump_statements) + 1, 'COMMIT;'); RETURN sys.dump_statements; END; -update sys._tables set system = true where name in ('describe_partition_tables', 'dump_partition_tables', 'dump_sequences', 'dump_start_sequences', 'describe_tables', 'dump_tables') AND schema_id = 2000; +update sys._tables set system = true where name in ('describe_partition_tables', 'dump_partition_tables', 'dump_sequences', 'dump_start_sequences', 'describe_tables', 'dump_tables', 'dump_create_users') AND schema_id = 2000; update sys.functions set system = true where system <> true and name in ('dump_table_data') and schema_id = 2000 and type = 2; update sys.functions set system = true where system <> true and name in ('dump_database') and schema_id = 2000 and type = 5; CREATE function sys.url_extract_host(url string, no_www bool) RETURNS STRING
--- a/sql/test/testdb-upgrade-chain/Tests/upgrade.stable.out +++ b/sql/test/testdb-upgrade-chain/Tests/upgrade.stable.out @@ -2608,6 +2608,34 @@ create procedure SHPload(fid integer, fi update sys.functions set system = true where schema_id = 2000 and name in ('shpattach', 'shpload'); Running database upgrade commands: +alter table sys.db_user_info add column max_memory bigint; +alter table sys.db_user_info add column max_workers int; +alter table sys.db_user_info add column optimizer varchar(1024); +alter table sys.db_user_info add column default_role int; +alter table sys.db_user_info add column password varchar(256); +update sys.db_user_info u set max_memory = 0, max_workers = 0, optimizer = 'default_pipe', default_role = (select id from sys.auths a where a.name = u.name); +-- and copying passwords + +Running database upgrade commands: +drop view sys.dependency_schemas_on_users; +drop view sys.roles; +drop view sys.users; +drop function sys.db_users(); +CREATE VIEW sys.roles AS SELECT id, name, grantor FROM sys.auths a WHERE a.name NOT IN (SELECT u.name FROM sys.db_user_info u); +GRANT SELECT ON sys.roles TO PUBLIC; +CREATE VIEW sys.users AS SELECT name, fullname, default_schema, schema_path, max_memory, max_workers, optimizer, default_role FROM sys.db_user_info; +GRANT SELECT ON sys.users TO PUBLIC; +CREATE FUNCTION sys.db_users() RETURNS TABLE(name varchar(2048)) RETURN SELECT name FROM sys.db_user_info; +CREATE VIEW sys.dependency_schemas_on_users AS +SELECT s.id AS schema_id, s.name AS schema_name, u.name AS user_name, CAST(6 AS smallint) AS depend_type + FROM sys.db_user_info AS u, sys.schemas AS s + WHERE u.default_schema = s.id + ORDER BY s.name, u.name; +GRANT SELECT ON sys.dependency_schemas_on_users TO PUBLIC; +update sys._tables set system = true where name in ('users', 'roles', 'dependency_schemas_on_users') AND schema_id = 2000; +update sys.functions set system = true where system <> true and name in ('db_users') and schema_id = 2000 and type = 5; + +Running database upgrade commands: drop function sys.dump_database(boolean); drop procedure sys.dump_table_data(); drop procedure sys.dump_table_data(string, string); @@ -2617,6 +2645,7 @@ drop view sys.dump_sequences; drop view sys.dump_start_sequences; drop view sys.dump_tables; drop view sys.describe_tables; +drop view sys.dump_create_users; CREATE VIEW sys.describe_partition_tables AS SELECT m_sch, @@ -2761,6 +2790,16 @@ BEGIN END WHILE; END IF; END; +CREATE VIEW sys.dump_create_users AS + SELECT + 'CREATE USER ' || sys.dq(ui.name) || ' WITH ENCRYPTED PASSWORD ' || + sys.sq(sys.password_hash(ui.name)) || + ' NAME ' || sys.sq(ui.fullname) || ' SCHEMA sys' || ifthenelse(ui.schema_path = '"sys"', '', ' SCHEMA PATH ' || sys.sq(ui.schema_path)) || ';' stmt, + ui.name user_name + FROM sys.db_user_info ui, sys.schemas s + WHERE ui.default_schema = s.id + AND ui.name <> 'monetdb' + AND ui.name <> '.snapshot'; CREATE VIEW sys.describe_tables AS SELECT t.id o, @@ -2846,7 +2885,7 @@ BEGIN INSERT INTO sys.dump_statements VALUES ((SELECT COUNT(*) FROM sys.dump_statements) + 1, 'COMMIT;'); RETURN sys.dump_statements; END; -update sys._tables set system = true where name in ('describe_partition_tables', 'dump_partition_tables', 'dump_sequences', 'dump_start_sequences', 'describe_tables', 'dump_tables') AND schema_id = 2000; +update sys._tables set system = true where name in ('describe_partition_tables', 'dump_partition_tables', 'dump_sequences', 'dump_start_sequences', 'describe_tables', 'dump_tables', 'dump_create_users') AND schema_id = 2000; update sys.functions set system = true where system <> true and name in ('dump_table_data') and schema_id = 2000 and type = 2; update sys.functions set system = true where system <> true and name in ('dump_database') and schema_id = 2000 and type = 5; CREATE function sys.url_extract_host(url string, no_www bool) RETURNS STRING
--- a/sql/test/testdb-upgrade-chain/Tests/upgrade.stable.out.32bit +++ b/sql/test/testdb-upgrade-chain/Tests/upgrade.stable.out.32bit @@ -2608,6 +2608,34 @@ create procedure SHPload(fid integer, fi update sys.functions set system = true where schema_id = 2000 and name in ('shpattach', 'shpload'); Running database upgrade commands: +alter table sys.db_user_info add column max_memory bigint; +alter table sys.db_user_info add column max_workers int; +alter table sys.db_user_info add column optimizer varchar(1024); +alter table sys.db_user_info add column default_role int; +alter table sys.db_user_info add column password varchar(256); +update sys.db_user_info u set max_memory = 0, max_workers = 0, optimizer = 'default_pipe', default_role = (select id from sys.auths a where a.name = u.name); +-- and copying passwords + +Running database upgrade commands: +drop view sys.dependency_schemas_on_users; +drop view sys.roles; +drop view sys.users; +drop function sys.db_users(); +CREATE VIEW sys.roles AS SELECT id, name, grantor FROM sys.auths a WHERE a.name NOT IN (SELECT u.name FROM sys.db_user_info u); +GRANT SELECT ON sys.roles TO PUBLIC; +CREATE VIEW sys.users AS SELECT name, fullname, default_schema, schema_path, max_memory, max_workers, optimizer, default_role FROM sys.db_user_info; +GRANT SELECT ON sys.users TO PUBLIC; +CREATE FUNCTION sys.db_users() RETURNS TABLE(name varchar(2048)) RETURN SELECT name FROM sys.db_user_info; +CREATE VIEW sys.dependency_schemas_on_users AS +SELECT s.id AS schema_id, s.name AS schema_name, u.name AS user_name, CAST(6 AS smallint) AS depend_type + FROM sys.db_user_info AS u, sys.schemas AS s + WHERE u.default_schema = s.id + ORDER BY s.name, u.name; +GRANT SELECT ON sys.dependency_schemas_on_users TO PUBLIC; +update sys._tables set system = true where name in ('users', 'roles', 'dependency_schemas_on_users') AND schema_id = 2000; +update sys.functions set system = true where system <> true and name in ('db_users') and schema_id = 2000 and type = 5; + +Running database upgrade commands: drop function sys.dump_database(boolean); drop procedure sys.dump_table_data(); drop procedure sys.dump_table_data(string, string); @@ -2617,6 +2645,7 @@ drop view sys.dump_sequences; drop view sys.dump_start_sequences; drop view sys.dump_tables; drop view sys.describe_tables; +drop view sys.dump_create_users; CREATE VIEW sys.describe_partition_tables AS SELECT m_sch, @@ -2761,6 +2790,16 @@ BEGIN END WHILE; END IF; END; +CREATE VIEW sys.dump_create_users AS + SELECT + 'CREATE USER ' || sys.dq(ui.name) || ' WITH ENCRYPTED PASSWORD ' || + sys.sq(sys.password_hash(ui.name)) || + ' NAME ' || sys.sq(ui.fullname) || ' SCHEMA sys' || ifthenelse(ui.schema_path = '"sys"', '', ' SCHEMA PATH ' || sys.sq(ui.schema_path)) || ';' stmt, + ui.name user_name + FROM sys.db_user_info ui, sys.schemas s + WHERE ui.default_schema = s.id + AND ui.name <> 'monetdb' + AND ui.name <> '.snapshot'; CREATE VIEW sys.describe_tables AS SELECT t.id o, @@ -2846,7 +2885,7 @@ BEGIN INSERT INTO sys.dump_statements VALUES ((SELECT COUNT(*) FROM sys.dump_statements) + 1, 'COMMIT;'); RETURN sys.dump_statements; END; -update sys._tables set system = true where name in ('describe_partition_tables', 'dump_partition_tables', 'dump_sequences', 'dump_start_sequences', 'describe_tables', 'dump_tables') AND schema_id = 2000; +update sys._tables set system = true where name in ('describe_partition_tables', 'dump_partition_tables', 'dump_sequences', 'dump_start_sequences', 'describe_tables', 'dump_tables', 'dump_create_users') AND schema_id = 2000; update sys.functions set system = true where system <> true and name in ('dump_table_data') and schema_id = 2000 and type = 2; update sys.functions set system = true where system <> true and name in ('dump_database') and schema_id = 2000 and type = 5; CREATE function sys.url_extract_host(url string, no_www bool) RETURNS STRING
--- a/sql/test/testdb-upgrade/Tests/upgrade.stable.out +++ b/sql/test/testdb-upgrade/Tests/upgrade.stable.out @@ -2602,6 +2602,34 @@ insert into sys.functions values (904, ' insert into sys.functions values (905, 'sys_update_tables', 'update_tables', 'sql', 0, 2, true, false, false, 2000, true, true); Running database upgrade commands: +alter table sys.db_user_info add column max_memory bigint; +alter table sys.db_user_info add column max_workers int; +alter table sys.db_user_info add column optimizer varchar(1024); +alter table sys.db_user_info add column default_role int; +alter table sys.db_user_info add column password varchar(256); +update sys.db_user_info u set max_memory = 0, max_workers = 0, optimizer = 'default_pipe', default_role = (select id from sys.auths a where a.name = u.name); +-- and copying passwords + +Running database upgrade commands: +drop view sys.dependency_schemas_on_users; +drop view sys.roles; +drop view sys.users; +drop function sys.db_users(); +CREATE VIEW sys.roles AS SELECT id, name, grantor FROM sys.auths a WHERE a.name NOT IN (SELECT u.name FROM sys.db_user_info u); +GRANT SELECT ON sys.roles TO PUBLIC; +CREATE VIEW sys.users AS SELECT name, fullname, default_schema, schema_path, max_memory, max_workers, optimizer, default_role FROM sys.db_user_info; +GRANT SELECT ON sys.users TO PUBLIC; +CREATE FUNCTION sys.db_users() RETURNS TABLE(name varchar(2048)) RETURN SELECT name FROM sys.db_user_info; +CREATE VIEW sys.dependency_schemas_on_users AS +SELECT s.id AS schema_id, s.name AS schema_name, u.name AS user_name, CAST(6 AS smallint) AS depend_type + FROM sys.db_user_info AS u, sys.schemas AS s + WHERE u.default_schema = s.id + ORDER BY s.name, u.name; +GRANT SELECT ON sys.dependency_schemas_on_users TO PUBLIC; +update sys._tables set system = true where name in ('users', 'roles', 'dependency_schemas_on_users') AND schema_id = 2000; +update sys.functions set system = true where system <> true and name in ('db_users') and schema_id = 2000 and type = 5; + +Running database upgrade commands: drop function sys.dump_database(boolean); drop procedure sys.dump_table_data(); drop procedure sys.dump_table_data(string, string); @@ -2611,6 +2639,7 @@ drop view sys.dump_sequences; drop view sys.dump_start_sequences; drop view sys.dump_tables; drop view sys.describe_tables; +drop view sys.dump_create_users; CREATE VIEW sys.describe_partition_tables AS SELECT m_sch, @@ -2755,6 +2784,16 @@ BEGIN END WHILE; END IF; END; +CREATE VIEW sys.dump_create_users AS + SELECT + 'CREATE USER ' || sys.dq(ui.name) || ' WITH ENCRYPTED PASSWORD ' || + sys.sq(sys.password_hash(ui.name)) || + ' NAME ' || sys.sq(ui.fullname) || ' SCHEMA sys' || ifthenelse(ui.schema_path = '"sys"', '', ' SCHEMA PATH ' || sys.sq(ui.schema_path)) || ';' stmt, + ui.name user_name + FROM sys.db_user_info ui, sys.schemas s + WHERE ui.default_schema = s.id + AND ui.name <> 'monetdb' + AND ui.name <> '.snapshot'; CREATE VIEW sys.describe_tables AS SELECT t.id o, @@ -2840,7 +2879,7 @@ BEGIN INSERT INTO sys.dump_statements VALUES ((SELECT COUNT(*) FROM sys.dump_statements) + 1, 'COMMIT;'); RETURN sys.dump_statements; END; -update sys._tables set system = true where name in ('describe_partition_tables', 'dump_partition_tables', 'dump_sequences', 'dump_start_sequences', 'describe_tables', 'dump_tables') AND schema_id = 2000; +update sys._tables set system = true where name in ('describe_partition_tables', 'dump_partition_tables', 'dump_sequences', 'dump_start_sequences', 'describe_tables', 'dump_tables', 'dump_create_users') AND schema_id = 2000; update sys.functions set system = true where system <> true and name in ('dump_table_data') and schema_id = 2000 and type = 2; update sys.functions set system = true where system <> true and name in ('dump_database') and schema_id = 2000 and type = 5; CREATE function sys.url_extract_host(url string, no_www bool) RETURNS STRING
--- a/sql/test/testdb-upgrade/Tests/upgrade.stable.out.32bit +++ b/sql/test/testdb-upgrade/Tests/upgrade.stable.out.32bit @@ -2602,6 +2602,34 @@ insert into sys.functions values (904, ' insert into sys.functions values (905, 'sys_update_tables', 'update_tables', 'sql', 0, 2, true, false, false, 2000, true, true); Running database upgrade commands: +alter table sys.db_user_info add column max_memory bigint; +alter table sys.db_user_info add column max_workers int; +alter table sys.db_user_info add column optimizer varchar(1024); +alter table sys.db_user_info add column default_role int; +alter table sys.db_user_info add column password varchar(256); +update sys.db_user_info u set max_memory = 0, max_workers = 0, optimizer = 'default_pipe', default_role = (select id from sys.auths a where a.name = u.name); +-- and copying passwords + +Running database upgrade commands: +drop view sys.dependency_schemas_on_users; +drop view sys.roles; +drop view sys.users; +drop function sys.db_users(); +CREATE VIEW sys.roles AS SELECT id, name, grantor FROM sys.auths a WHERE a.name NOT IN (SELECT u.name FROM sys.db_user_info u); +GRANT SELECT ON sys.roles TO PUBLIC; +CREATE VIEW sys.users AS SELECT name, fullname, default_schema, schema_path, max_memory, max_workers, optimizer, default_role FROM sys.db_user_info; +GRANT SELECT ON sys.users TO PUBLIC; +CREATE FUNCTION sys.db_users() RETURNS TABLE(name varchar(2048)) RETURN SELECT name FROM sys.db_user_info; +CREATE VIEW sys.dependency_schemas_on_users AS +SELECT s.id AS schema_id, s.name AS schema_name, u.name AS user_name, CAST(6 AS smallint) AS depend_type + FROM sys.db_user_info AS u, sys.schemas AS s + WHERE u.default_schema = s.id + ORDER BY s.name, u.name; +GRANT SELECT ON sys.dependency_schemas_on_users TO PUBLIC; +update sys._tables set system = true where name in ('users', 'roles', 'dependency_schemas_on_users') AND schema_id = 2000; +update sys.functions set system = true where system <> true and name in ('db_users') and schema_id = 2000 and type = 5; + +Running database upgrade commands: drop function sys.dump_database(boolean); drop procedure sys.dump_table_data(); drop procedure sys.dump_table_data(string, string); @@ -2611,6 +2639,7 @@ drop view sys.dump_sequences; drop view sys.dump_start_sequences; drop view sys.dump_tables; drop view sys.describe_tables; +drop view sys.dump_create_users; CREATE VIEW sys.describe_partition_tables AS SELECT m_sch, @@ -2755,6 +2784,16 @@ BEGIN END WHILE; END IF; END; +CREATE VIEW sys.dump_create_users AS + SELECT + 'CREATE USER ' || sys.dq(ui.name) || ' WITH ENCRYPTED PASSWORD ' || + sys.sq(sys.password_hash(ui.name)) || + ' NAME ' || sys.sq(ui.fullname) || ' SCHEMA sys' || ifthenelse(ui.schema_path = '"sys"', '', ' SCHEMA PATH ' || sys.sq(ui.schema_path)) || ';' stmt, + ui.name user_name + FROM sys.db_user_info ui, sys.schemas s + WHERE ui.default_schema = s.id + AND ui.name <> 'monetdb' + AND ui.name <> '.snapshot'; CREATE VIEW sys.describe_tables AS SELECT t.id o, @@ -2840,7 +2879,7 @@ BEGIN INSERT INTO sys.dump_statements VALUES ((SELECT COUNT(*) FROM sys.dump_statements) + 1, 'COMMIT;'); RETURN sys.dump_statements; END; -update sys._tables set system = true where name in ('describe_partition_tables', 'dump_partition_tables', 'dump_sequences', 'dump_start_sequences', 'describe_tables', 'dump_tables') AND schema_id = 2000; +update sys._tables set system = true where name in ('describe_partition_tables', 'dump_partition_tables', 'dump_sequences', 'dump_start_sequences', 'describe_tables', 'dump_tables', 'dump_create_users') AND schema_id = 2000; update sys.functions set system = true where system <> true and name in ('dump_table_data') and schema_id = 2000 and type = 2; update sys.functions set system = true where system <> true and name in ('dump_database') and schema_id = 2000 and type = 5; CREATE function sys.url_extract_host(url string, no_www bool) RETURNS STRING