先说一个我干了这么多年数据库经常被问的问题老大为什么我在A用户下能建表但表属主不是A或者为什么我明明授权了别人还是不能在我这个schema下建表这个问题的答案在Oracle、PostgreSQL下文统称PG、GaussDB这三个库里细节差异挺多而且坑也不少。今天我把这个事儿彻底拆开讲透包括三边的权限模型差异、实操语句、以及最容易踩的“属主陷阱”。不管你是做数据迁移、多租户隔离还是日常开发环境里各干各的schema这篇文章都值得你花十分钟看完。尤其是最后那个GaussDB的坑我敢说十个人里九个第一次都会掉进去。1. 先把概念盘清楚SCHEMA到底是什么属主到底属于谁要看懂后面的操作和差异得先把Schema和Owner这两个最基础的概念对齐。很多人在这一步就已经混了后面自然全是糊涂账。1.1 Schema是“命名空间”不是“用户”先说Schema。Oracle里Schema和用户是强绑定的创建用户会自动创建同名Schema两者基本可以划等号所以Oracle里很少单独谈“Schema”。但在PG和GaussDB里Schema是一个完全独立的命名空间对象它不属于某个用户“天生所有”而是可以显式创建、授权、修改属主。一个用户可以在自己的数据库里建好几个Schema也可以被授权去别人的Schema里干活。一句话总结Oracle用户 Schema或者是 Schema 的化身。PG / GaussDBSchema 是独立的容器用户是用户两者通过权限绑定。这个本质区别决定了后面授权方式完全不同。Oracle里常见的“在别人用户下建表”本质是“A用户能不能往B用户的地盘里扔东西”而PG/GaussDB里是“A用户对B拥有的那个schema容器有没有CREATE权限”。1.2 属主Owner决定了你能干什么、不能干什么再说Owner也就是“这个表到底是谁的”。如果一个表的Owner是用户甲那么甲拥有这个表的几乎所有权限ALTER、DROP、SELECT、INSERT、UPDATE、DELETE、TRUNCATE等默认自动拥有。如果Owner不是schema的属主那这个表就是“外来户”。将来schema属主想删这个表得看有没有被授权或者是不是超级用户。没有权限的话schema属主反而动不了这个“寄居”在自己地盘里的表。在Oracle里表的Owner永远是那个被指定的Schema用户。你就算是用DBA权限CREATE ANY TABLE在别人Schema下建了表表的Owner也自动是那个Schema的属主。这一点和PG系完全不一样是很多人从Oracle迁到PG后最先撞上的认知冲突。搞清楚这两个基本概念后面的所有SQL和授权操作才有意义。2. Oracle中的实现权限集中在“用户”身上建表靠CREATE ANY TABLE或对象授权在Oracle里用户甲要去用户乙的Schema下建一张表说白了只有两条路要么甲有全局的CREATE ANY TABLE权限要么乙显式把在自己Schema下建表的权限授给甲。2.1 方法一DBA直接赋权CREATE ANY TABLE这是最“暴力”也最常用的方式。DBA执行GRANT CREATE ANY TABLE TO user_a;执行完用户甲就可以指定表空间和Schema在任意用户下建表了。比如CREATE TABLE user_b.t_demo ( id NUMBER PRIMARY KEY, name VARCHAR2(100) );建完之后查一下属主SELECT owner, table_name FROM all_tables WHERE table_name T_DEMO;结果owner这一列是USER_B不是USER_A。这就是Oracle和PG系最大的不同虽然建表的人是甲但表的Owner自动归属于表所在的Schema即乙。甲只有自己权限能做的事比如查询、插入、更新等看后续授权情况但DML权限他天然有吗没有。他只是建了个表这个表从物权角度看是乙的。如果乙想把表的管理权拿回来一部分比如允许甲继续查询GRANT SELECT ON user_b.t_demo TO user_a;如果想让甲也能增删改GRANT INSERT, UPDATE, DELETE ON user_b.t_demo TO user_a;2.2 方法二乙授权甲“在自己名下建表”Oracle 12c之后权限模型细化了一些可以不用全局的CREATE ANY TABLE而是由乙直接授予甲权限。这个权限是对象级别的叫CREATE TABLE在很多版本里通过“对象权限”来给。例如乙执行GRANT CREATE TABLE TO user_a;等等这个语句在Oracle里其实是给甲一个系统权限允许甲在任何自己有“空间”的地方建表并不代表甲能在乙的schema下建表。那怎么真正实现“只在乙的schema下建表”Oracle里实践中更常见的是DBA给甲一个受限角色同时回收全局权限然后用“IN”子句指定Schema建表但是建表目标的限制本质上还得靠CREATE ANY TABLE没有更细的“在指定schema下建表”这种权限粒度。如果不用CREATE ANY TABLEOracle其实没有直接支持“仅允许A在B的schema下建表”的细粒度授权。所以在Oracle里实践中就是两条要么给CREATE ANY TABLE一给给全部。要么用存储过程以定义者权限AUTHID DEFINER封装建表逻辑由乙的schema下执行。这是很多规范化环境里的常用解法。举个例子乙用户下建一个存储过程CREATE OR REPLACE PROCEDURE user_b.prc_create_t_demo AS BEGIN EXECUTE IMMEDIATE CREATE TABLE t_demo (id NUMBER, name VARCHAR2(100)); END; /然后授权给甲执行GRANT EXECUTE ON user_b.prc_create_t_demo TO user_a;甲执行BEGIN user_b.prc_create_t_demo; END; /因为存储过程是以定义者乙身份运行的所以表建在乙的schema下Owner也是乙。这个方案更安全不需要给全局权限缺点是每建一张表都要写个过程灵活性差一些。2.3 Oracle里的属主和表空间陷阱在Oracle里有一个容易出事的点建表语句里如果不指定表空间Oracle会用用户的默认表空间如果用户乙的默认表空间配额是0甲在乙的schema下建表会报ORA-01950: no privileges on tablespace。所以做这种跨用户建表最好在建表SQL里显式指定表空间或者由DBA提前把配额给足GRANT UNLIMITED TABLESPACE TO user_b;或者指定到某个表空间ALTER USER user_b QUOTA 100M ON users;这个坑在Oracle DBA日常里非常常见尤其是用PDB/CDB架构之后默认表空间配置错乱的事情太多了。3. PG中的实现靠USAGE CREATE权限属主是“建表人”PG的权限模型和Oracle完全不同。在PG里用户甲要在用户乙的schema下建表核心就两句话甲需要有乙的schema的USAGE权限。甲需要有乙的schema的CREATE权限。有了这两个权限甲就能在乙的schema下建表了。但是表的Owner不是乙而是甲。我再说一遍这是PG系和Oracle系在“跨Schema建表”这件事上最核心的认知差异Oracle建出来的表归Schema属主PG建出来的表归建表人。3.1 具体授权操作假设PG库里有用户user_a和user_buser_b拥有schema b_schema。现在要让user_a能在b_schema下建表先由超级用户或b_schema的属主user_b执行GRANT USAGE ON SCHEMA b_schema TO user_a; GRANT CREATE ON SCHEMA b_schema TO user_a;此时user_a执行CREATE TABLE b_schema.t_demo ( id serial PRIMARY KEY, name text );验证一下表OwnerSELECT schemaname, tablename, tableowner FROM pg_tables WHERE tablename t_demo;返回结果里tableowner是user_a不是user_b。这就引申出了一个关键问题如果让乙来管这张表乙能删吗不能。乙虽然有schema的CREATE和USAGE权限但这张表的Owner是甲乙默认对这张表没有任何权限连SELECT都进不去除非甲授给他。这是PG设计上的一种“普遍所有权”模型谁建的表谁负责。Schema只是一个文件夹文件夹的属主可以决定谁能往里面放东西但文件夹里的文件是属于创造者的。3.2 让建出来的表归乙所有ALTER TABLE OWNER在实际业务里很多时候我们更希望表归schema属主所有尤其是做CDD数据模型统一管理的时候。PG里最常见的方案就是建完表以后改属主ALTER TABLE b_schema.t_demo OWNER TO user_b;或者如果甲和乙都有足够权限也可以由乙来执行ALTER TABLE b_schema.t_demo OWNER TO user_b;这条语句会把表的Owner改成user_b但有一个前提执行者必须是表的Owner、schema的Owner或者超级用户。也就是说user_a自己可以把自己的表让给user_b前提是user_a能连上b_schema且对b_schema有CREATE权限但user_b不能强行把user_a建的表拿过来除非有超级用户或者在schema权限模型上做了额外授权。这里有一个很容易踩的序列/序列权限坑表改Owner之后如果有serial或identity列对应生成的序列Owner可能还是user_a。虽然表Owner变了但序列还是甲的将来如果乙想TRUNCATE或者重置序列可能没有权限。所以改完表Owner建议顺手把序列也改掉ALTER SEQUENCE b_schema.t_demo_id_seq OWNER TO user_b;或者建表的时候直接用GENERATED ALWAYS AS IDENTITY配合PG 10的自动序列归属逻辑会更清晰。3.3 默认权限省去每次都授权的麻烦如果用户乙希望以后甲每次新建表都自动获得一系列权限可以用ALTER DEFAULT PRIVILEGES。比如乙执行ALTER DEFAULT PRIVILEGES FOR ROLE user_b IN SCHEMA b_schema GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO user_a;这条语句的作用就是以后只要在b_schema下新建的表默认给user_a查、增、改、删的权限不用每次新建后手动授权。这个功能在多人协作schema场景下非常实用。我们团队里有个项目就是大家共用一个大schema但每个人都有自己独立的子schema做开发然后通过ALTER DEFAULT PRIVILEGES互相同步权限。实测下来比手动授权靠谱得多新表一出来权限就齐了省了不少沟通成本。4. GaussDB中的实现PG内核的延续但有ORA兼容模式的干扰GaussDB的架构大家一般都比较清楚了内核是从PostgreSQL的代码演进来的所以权限模型在默认行为上跟PG非常像。但同时GaussDB兼容Oracle语法开了ORA兼容模式之后有些行为和Oracle更像恰恰是这种“像又不像”最容易让人栽跟头。4.1 和PG一致的常规授权方式在GaussDB的默认行为下非ORA兼容或者兼容模式但权限模型仍是PG风格实现方式和PG一模一样。用户乙把schema的使用和创建权限给甲GRANT USAGE ON SCHEMA b_schema TO user_a; GRANT CREATE ON SCHEMA b_schema TO user_a;甲执行CREATE TABLE b_schema.t_demo ( id serial PRIMARY KEY, name varchar(100) );表Owner也是user_a不是user_b。验证方法SELECT schemaname, tablename, tableowner FROM pg_tables WHERE tablename t_demo;在GaussDB的某些版本里查看Owner的视图可能叫pg_class也可以这样查SELECT relname, relowner::regrole FROM pg_class WHERE relname t_demo;结果里relowner就是user_a同样不会变成user_b。4.2 和Oracle不同不存在CREATE ANY TABLE的全局能力有一点和Oracle很大的区别即使在兼容模式下GaussDB也没有直接把“CREATE ANY TABLE”当作一个系统权限给用户用。想通过“我有全局建表能力”去别的schema下建表在GaussDB里并没有这么顺滑。很多从Oracle迁过来的人会习惯性地给开发账号发一个“CREATE ANY TABLE”但在GaussDB里这条路大概率走不通。正确的姿势仍然是回到PG的授权模型针对目标schema授权。或者用系统管理员SYSADMIN权限直接操作。不过在GaussDB里SYSADMIN也有边界不像Oracle的SYSDBA那么“万能”。这一点需要通过实际测试确认不能想当然。4.3 ORA兼容模式下的特殊表现GaussDB的ORA兼容模式主要是语法层面的兼容底层的权限模型依然是PG的那一套。但在长期实践中我发现有两个点值得留意第一默认schema解析行为。在ORA兼容模式下如果没有显式指定schemaGaussDB默认会解析到当前用户名对应的schema这和Oracle习惯一致。但如果你是在B账号下想跨到A的schema建表仍然得写全限定名CREATE TABLE user_a.t_demo (...)并且需要schema授权否则会报permission denied for schema。第二创建用户时的默认权限。GaussDB里普通用户默认对public schema有CREATE权限吗和PG一样默认public schema权限比较宽松任何用户都能在public下建表。但一旦涉及别人创建的schema就必须显式授权。所以我想强调一句GaussDB看起来是Oracle的皮PG的核。凡是遇到权限问题先按PG的思路排查再考虑ORA兼容模式的特殊语法。4.4 GaussDB实操完整流程演示为了让大家彻底明白这里给一套完整的GaussDB实操流程假设有用户u_dev和u_schema_owner目标是让u_dev在u_schema_owner拥有的schema dev_data下建表。第1步用u_schema_owner或系统管理员连接数据库创建schema并授权CREATE SCHEMA dev_data; GRANT USAGE ON SCHEMA dev_data TO u_dev; GRANT CREATE ON SCHEMA dev_data TO u_dev;第2步切到u_dev尝试建表SET ROLE u_dev; CREATE TABLE dev_data.t_order ( order_id bigserial PRIMARY KEY, user_id bigint NOT NULL, amount numeric(10,2) );第3步验证表OwnerSELECT schemaname, tablename, tableowner FROM pg_tables WHERE schemaname dev_data;你会发现tableowner是u_dev。如果想让这张表归u_schema_owner执行ALTER TABLE dev_data.t_order OWNER TO u_schema_owner;第4步同步序列ownerALTER SEQUENCE dev_data.t_order_order_id_seq OWNER TO u_schema_owner;这套流程跑完整张表的归属就干净了。如果想让u_schema_owner后续对u_dev默认创建的每张表都有权限可以执行ALTER DEFAULT PRIVILEGES FOR ROLE u_dev IN SCHEMA dev_data GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO u_schema_owner;注意这个语句的FOR ROLE子句要写成建表人u_dev而不是schema属主很多人在这一步写反导致权限不生效。5. 三库横向对比与避坑策略总结前面分别把每个库的细节讲完了最后用一个全局视角把差异和坑位整理出来。维度OraclePostgreSQLGaussDBSchema与用户关系用户Schema强绑定Schema独立权限控制Schema独立权限模型同PG跨Schema建表授权方式CREATE ANY TABLE / 定义者存储过程USAGE CREATE 权限授予USAGE CREATE 权限授予表Owner归属于Schema所在用户归属于建表人归属于建表人让表归Schema属主的方法默认就是ALTER TABLE OWNER TOALTER TABLE OWNER TO序列/自增列归属序列独立一般不跟表序列可单独改Owner序列可单独改Owner权限最小化实现难度较难需要存储过程较容易较容易再单独列几个最容易踩的坑都是我实际运维里亲眼见人踩过的坑一在Oracle里建表成功但报ORA-01950。原因就是目标用户没有目标表空间的配额。解决办法是在SQL里显式写USERS表空间或者给用户ALTER USER xxx QUOTA unlimited ON users。坑二在PG/GaussDB里授权了但建表时报permission denied for schema。大概率是只授了USAGE没授CREATE。USAGE只是让你能看到schema里的对象CREATE才允许你新建对象。坑三表建好了但表的Owner不是schema属主导致schema属主想改表结构、删表都做不了。解决办法就是ALTER TABLE OWNER但注意序列也要同步改。坑四在GaussDB兼容Oracle的数据库里用了Oracle的建表习惯比如CREATE TABLE user_b.t_demo但没先授权。在GaussDB里这样写没问题但如果不授权依旧会报permission denied。权限模型是PG的不是Oracle的别被兼容模式的语法迷惑。坑五使用ALTER DEFAULT PRIVILEGES时FOR ROLE写错了人。这是个特别容易出错的地方。你如果想要的是“某个人在某个schema下建表之后另一个人自动获得权限”FOR ROLE后面写的一定是建表的那个人不是schema属主。6. 我自己的几条实战建议做了这么多年数据库跨Schema建表这个场景我总结出几条建议算是给同行们的一个参考。第一如果业务上没有强制要求尽量避免让普通用户跨Schema建表。宁可在设计阶段就把表规划到正确的schema下然后让对应属主自己建表再授权给其他人。这是最干净、最好维护的方式。第二如果确实需要跨Schema建表优先用“建完改Owner”这个方案。不管是PG还是GaussDB让表归schema属主所有后续的权限管理会省心很多。否则一个schema里趴着几十张Owner各异的表哪天做数据清理的时候就会哭出来。第三Oracle环境里如果不想给那么大的全局权限可以考虑用存储过程封装建表操作。虽然麻烦一点但安全性和可控性都高得多尤其是生产环境安全团队一般不会让你随随便便把CREATE ANY TABLE发出去。第四定期巡检一下库里的表Owner。这个习惯特别好。我一般会写个简单查询把Owner不是预期的表都捞出来-- PG / GaussDB SELECT schemaname, tablename, tableowner FROM pg_tables WHERE schemaname NOT IN (pg_catalog, information_schema) AND tableowner schemaname::regrole::text;这条SQL能把那些不属于自己schema的表都列出来有异常就能第一时间发现。第五序列的Owner调整一定要和表一起做。本来表归乙了但序列还是甲的后面有人TRUNCATE表或者需要重置自增列就会遇到权限不足的诡异报错。调整一次很便宜不调整的代价很贵。最后再分享一个关于GaussDB的小技巧在用gsql连GaussDB排查权限问题时可以先看当前用户和search_pathSELECT current_user, current_schema; SHOW search_path;纯属我的个人经验很多GaussDB下的权限问题根源往往不是权限没给对而是search_path不对导致找表的时候找错了地方。把上下文理顺了再往上加权限问题基本都能迎刃而解。