Bug 6952 - Add support for altering the authorization and/or owner of a schema
Summary: Add support for altering the authorization and/or owner of a schema
Status: NEW
Alias: None
Product: SQL
Classification: Unclassified
Component: all (show other bugs)
Version: 11.37.7 (Jun2020)
Hardware: All All
: Normal enhancement
Assignee: SQL devs
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2020-08-18 18:35 CEST by Martin van Dinther
Modified: 2020-08-18 18:35 CEST (History)
0 users



Attachments

Note You need to log in before you can comment on or make changes to this bug.
Description Martin van Dinther cwiconfidential 2020-08-18 18:35:11 CEST
User-Agent:       Mozilla/5.0 (Windows NT 6.1; rv:79.0) Gecko/20100101 Firefox/79.0
Build Identifier: 

There is currently no command to change the authorization or owner value of an existing schema.
Request to add support for 2 new SQL commands:
ALTER SCHEMA name AUTHORIZATION TO newauth;
ALTER SCHEMA name OWNER TO newowner;


Reproducible: Always

Steps to Reproduce:
CREATE or replace VIEW schema_names as
select s.name as schema_nm, auth.name as authorization_nm, ownr.name as owner_nm
 from schemas s
 join auths auth on s.authorization = auth.id
 join auths ownr on s.owner = ownr.id
where not s.system
order by s.name;

select * from schema_names;
CREATE USER kxn_user WITH UNENCRYPTED PASSWORD 'monetdb' NAME 'kxn' SCHEMA sys;
CREATE SCHEMA kxn_schema AUTHORIZATION kxn_user;
select * from schema_names;
CREATE SCHEMA AUTHORIZATION kxn_user;
select * from schema_names;
ALTER SCHEMA kxn_schema AUTHORIZATION TO monetdb;
ALTER SCHEMA kxn_user AUTHORIZATION TO monetdb;
select * from schema_names;
ALTER SCHEMA kxn_schema AUTHORIZATION TO kxn_user;
ALTER SCHEMA kxn_user AUTHORIZATION TO kxn_user;
ALTER SCHEMA kxn_schema OWNER TO kxn_user;
ALTER SCHEMA kxn_user OWNER TO kxn_user;
select * from schema_names;
DROP SCHEMA kxn_user;
DROP SCHEMA kxn_schema;
DROP USER kxn_user;
select * from schema_names;

DROP VIEW schema_names;

Actual Results:  
sql>CREATE or replace VIEW schema_names as
more>select s.name as schema_nm, auth.name as authorization_nm, ownr.name as owner_nm
more> from schemas s
more> join auths auth on s.authorization = auth.id
more> join auths ownr on s.owner = ownr.id
more>where not s.system
more>order by s.name;
operation successful
sql>
sql>select * from schema_names;
+-----------+------------------+----------+
| schema_nm | authorization_nm | owner_nm |
+===========+==================+==========+
+-----------+------------------+----------+
0 tuples
sql>CREATE USER kxn_user WITH UNENCRYPTED PASSWORD 'monetdb' NAME 'kxn' SCHEMA sys;
operation successful
sql>CREATE SCHEMA kxn_schema AUTHORIZATION kxn_user;
operation successful
sql>select * from schema_names;
+------------+------------------+----------+
| schema_nm  | authorization_nm | owner_nm |
+============+==================+==========+
| kxn_schema | kxn_user         | monetdb  |
+------------+------------------+----------+
1 tuple
sql>CREATE SCHEMA AUTHORIZATION kxn_user;
operation successful
sql>select * from schema_names;
+------------+------------------+----------+
| schema_nm  | authorization_nm | owner_nm |
+============+==================+==========+
| kxn_schema | kxn_user         | monetdb  |
| kxn_user   | kxn_user         | monetdb  |
+------------+------------------+----------+
2 tuples
sql>ALTER SCHEMA kxn_schema AUTHORIZATION TO monetdb;
syntax error, unexpected AUTHORIZATION, expecting RENAME in: "alter schema kxn_schema authorization"
sql>ALTER SCHEMA kxn_user AUTHORIZATION TO monetdb;
syntax error, unexpected AUTHORIZATION, expecting RENAME in: "alter schema kxn_user authorization"
sql>select * from schema_names;
+------------+------------------+----------+
| schema_nm  | authorization_nm | owner_nm |
+============+==================+==========+
| kxn_schema | kxn_user         | monetdb  |
| kxn_user   | kxn_user         | monetdb  |
+------------+------------------+----------+
2 tuples
sql>ALTER SCHEMA kxn_schema AUTHORIZATION TO kxn_user;
syntax error, unexpected AUTHORIZATION, expecting RENAME in: "alter schema kxn_schema authorization"
sql>ALTER SCHEMA kxn_user AUTHORIZATION TO kxn_user;
syntax error, unexpected AUTHORIZATION, expecting RENAME in: "alter schema kxn_user authorization"
sql>ALTER SCHEMA kxn_schema OWNER TO kxn_user;
syntax error, unexpected IDENT, expecting RENAME in: "alter schema kxn_schema owner"
sql>ALTER SCHEMA kxn_user OWNER TO kxn_user;
syntax error, unexpected IDENT, expecting RENAME in: "alter schema kxn_user owner"
sql>select * from schema_names;
+------------+------------------+----------+
| schema_nm  | authorization_nm | owner_nm |
+============+==================+==========+
| kxn_schema | kxn_user         | monetdb  |
| kxn_user   | kxn_user         | monetdb  |
+------------+------------------+----------+
2 tuples
sql>DROP SCHEMA kxn_user;
operation successful
sql>DROP SCHEMA kxn_schema;
operation successful
sql>DROP USER kxn_user;
operation successful
sql>select * from schema_names;
+-----------+------------------+----------+
| schema_nm | authorization_nm | owner_nm |
+===========+==================+==========+
+-----------+------------------+----------+
0 tuples
sql>
sql>DROP VIEW schema_names;
operation successful
sql>


Expected Results:  
sql>CREATE or replace VIEW schema_names as
more>select s.name as schema_nm, auth.name as authorization_nm, ownr.name as owner_nm
more> from schemas s
more> join auths auth on s.authorization = auth.id
more> join auths ownr on s.owner = ownr.id
more>where not s.system
more>order by s.name;
operation successful
sql>
sql>select * from schema_names;
+-----------+------------------+----------+
| schema_nm | authorization_nm | owner_nm |
+===========+==================+==========+
+-----------+------------------+----------+
0 tuples
sql>CREATE USER kxn_user WITH UNENCRYPTED PASSWORD 'monetdb' NAME 'kxn' SCHEMA sys;
operation successful
sql>CREATE SCHEMA kxn_schema AUTHORIZATION kxn_user;
operation successful
sql>select * from schema_names;
+------------+------------------+----------+
| schema_nm  | authorization_nm | owner_nm |
+============+==================+==========+
| kxn_schema | kxn_user         | monetdb  |
+------------+------------------+----------+
1 tuple
sql>CREATE SCHEMA AUTHORIZATION kxn_user;
operation successful
sql>select * from schema_names;
+------------+------------------+----------+
| schema_nm  | authorization_nm | owner_nm |
+============+==================+==========+
| kxn_schema | kxn_user         | monetdb  |
| kxn_user   | kxn_user         | monetdb  |
+------------+------------------+----------+
2 tuples
sql>ALTER SCHEMA kxn_schema AUTHORIZATION TO monetdb;
operation successful
sql>ALTER SCHEMA kxn_user AUTHORIZATION TO monetdb;
operation successful
sql>select * from schema_names;
+------------+------------------+----------+
| schema_nm  | authorization_nm | owner_nm |
+============+==================+==========+
| kxn_schema | monetdb          | monetdb  |
| kxn_user   | monetdb          | monetdb  |
+------------+------------------+----------+
2 tuples
sql>ALTER SCHEMA kxn_schema AUTHORIZATION TO kxn_user;
operation successful
sql>ALTER SCHEMA kxn_user AUTHORIZATION TO kxn_user;
operation successful
sql>ALTER SCHEMA kxn_schema OWNER TO kxn_user;
operation successful
sql>ALTER SCHEMA kxn_user OWNER TO kxn_user;
operation successful
sql>select * from schema_names;
+------------+------------------+----------+
| schema_nm  | authorization_nm | owner_nm |
+============+==================+==========+
| kxn_schema | kxn_user         | kxn_user |
| kxn_user   | kxn_user         | kxn_user |
+------------+------------------+----------+
2 tuples
sql>DROP SCHEMA kxn_user;
operation successful
sql>DROP SCHEMA kxn_schema;
operation successful
sql>DROP USER kxn_user;
operation successful
sql>select * from schema_names;
+-----------+------------------+----------+
| schema_nm | authorization_nm | owner_nm |
+===========+==================+==========+
+-----------+------------------+----------+
0 tuples
sql>
sql>DROP VIEW schema_names;
operation successful
sql>


https://www.postgresql.org/docs/12/sql-alterschema.html