热门搜索
分布式
数据库一体机
云数据库
建设银行
保险核心
密集交易
注册
ALTER USER 中 ON SCHEMA 子句的作用
技术分享/ 文章详情 /

ALTER USER 中 ON SCHEMA 子句的作用

苏童 2024/12/09 476 2 0

ALTER USER 中 ON SCHEMA 子句的作用

系统管理员手册中只有一句话，用于设置用户的缺省模式。

个人感觉有点费解，它的作用到底是什么呢？

这里的缺省模式是什么意思？ON SCHEMA 执行完会对数据库有什么影响？它的影响范围是多大？是只影响当前会话吗？

下边我们做一些测试来搞清楚ON SCHEMA ：

修改用户的缺省模式

使用sysdba用户登录

--创建用户
CREATE USER "TEST1" IDENTIFIED BY "TEST123456" HASH WITH SHA512 NO SALT PASSWORD_POLICY 2 ENCRYPT BY "TEST123456" 
 LIMIT FAILED_LOGIN_ATTEMPS 3, PASSWORD_LOCK_TIME 1, PASSWORD_GRACE_TIME 10 DEFAULT TABLESPACE "MAIN";
--授权
grant "RESOURCE","PUBLIC","SOI" to "TEST1";
grant CREATE TABLE,CREATE SESSION to "TEST1";

--创建新的schema,结尾必须使用/执行
CREATE SCHEMA TEST2 AUTHORIZATION TEST1;
/
--修改用户缺省schema
ALTER USER TEST1 ON SCHEMA "TEST2";

使用TEST1用户登录

disql TEST1/TEST123456
--不指定schema创建表:
SQL> create TABLE TEST_T_1(C1 INT);
操作已执行
已用时间: 3.129(毫秒). 执行号:1301.
--查看表所属schema
SQL> SELECT A.NAME AS SCH_NAME
     FROM SYSOBJECTS A
LEFT JOIN SYSOBJECTS B --用于确定表名
       ON A.ID = B.SCHID
LEFT JOIN SYSOBJECTS C --用于确定用户名
       ON A.PID = C.ID
    WHERE B.TYPE$ = 'SCHOBJ'
      AND B.SUBTYPE$ = 'UTAB'
      AND B.NAME = 'TEST_T_1'
      AND C.TYPE$ = 'UR'
      AND C.SUBTYPE$='USER'
      AND C.NAME = 'TEST1';

行号     SCH_NAME
---------- --------
1          TEST2

已用时间: 1.777(毫秒). 执行号:1302.

可见，如果创建对象是不指定schema，则会创建在缺省schema下。

搞清楚ON SCHEMA的影响范围

--SYSDBA会话中执行：
SQL> ALTER USER TEST1 ON SCHEMA TEST2;
操作已执行
已用时间: 2.413(毫秒). 执行号:1013.

##重启数据库,使用操作系统的root用户执行
systemctl restart DmService*

--TEST1用户登录,再次创建表：
SQL> create TABLE TEST_T_3(C1 INT);
操作已执行
已用时间: 2.905(毫秒). 执行号:1701.
SQL> SELECT A.NAME AS SCH_NAME
     FROM SYSOBJECTS A
LEFT JOIN SYSOBJECTS B --用于确定表名
       ON A.ID = B.SCHID
LEFT JOIN SYSOBJECTS C --用于确定用户名
       ON A.PID = C.ID
    WHERE B.TYPE$ = 'SCHOBJ'
      AND B.SUBTYPE$ = 'UTAB'
      AND B.NAME = 'TEST_T_3'
      AND C.TYPE$ = 'UR'
      AND C.SUBTYPE$='USER'
      AND C.NAME = 'TEST1'; 
行号     SCH_NAME
---------- --------
1          TEST2

已用时间: 2.388(毫秒). 执行号:1702.
SQL> select ut.TABLE_NAME,ut.TABLESPACE_NAME from USER_TABLES ut WHERE ut.TABLE_NAME ='TEST_T_3';

行号     TABLE_NAME TABLESPACE_NAME
---------- ---------- ---------------
1          TEST_T_3   MAIN

已用时间: 17.434(毫秒). 执行号:1703.

--查看当前使用的schema
SQL> SELECT SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA') FROM DUAL;

行号     SYS_CONTEXT('USERENV','CURRENT_SCHEMA')
---------- ---------------------------------------
1          TEST2

已用时间: 1.016(毫秒). 执行号:1704.

可见ALTER USER TEST1 ON SCHEMA TEST2 并不会因为重启失效，所以这个操作属于是数据库级别。

与之相对应的 SET SCHEMA TEST2 ,则是会话级别（等价于 ALTER SESSION SET CURRENT_SCHEMA=TEST2）

这里有个问题：重启不会失效的东西，一定要有持久化存储的位置；而最有可能的存放这个信息的无疑是DBA_USERS，但：

SQL> SET LINESIZE 100 
SQL> select * from SYS.DBA_USERS where username='TEST1';

行号     USERNAME USER_ID     PASSWORD ACCOUNT_STATUS LOCK_DATE EXPIRY_DATE DEFAULT_TABLESPACE
---------- -------- ----------- -------- -------------- --------- ----------- ------------------
           DEFAULT_INDEX_TABLESPACE TEMPORARY_TABLESPACE CREATED                    PROFILE
           ------------------------ -------------------- -------------------------- -------
           INITIAL_RSRC_CONSUMER_GROUP EXTERNAL_NAME PASSWORD_VERSIONS EDITIONS_ENABLED
           --------------------------- ------------- ----------------- ----------------
           AUTHENTICATION_TYPE NOWDATE            
           ------------------- -------------------
1          TEST1    50331756    NULL     OPEN           NULL      NULL        MAIN
           SYSTEM                   TEMP                 2024-12-02 18:40:01.957334 NULL
           NULL                        NULL          2                 Y
           NDCT_DB_AUTHENT     2024-12-04 23:37:40
已用时间: 11.860(毫秒). 执行号:509.
--显然这个系统视图中并没有关于schema的信息。而数据库中也没有查到以schema命名的字典表。
--使用10002event,搞清楚这个信息到底记录在哪里！
SQL> alter session set events '10002 trace name context forever';
SQL> ALTER USER TEST1 ON SCHEMA TEST1;
SQL> alter session set events '10002 trace name context off';
Tips:
整数值常量，表示只在对应的错误码或事件号触发时执行。其值取负数就对应系统的错误码；
取10000~10999之间的值则专指内部事件号，目前只能取值10001、10002、10003、10046或10053。
10001指操作符的监控事件；
10002指DDL跟踪的事件；
10003指针对全表扫描、嵌套循环连接等特定操作符的监控事件；
10046指SQL语句分析和执行过程监控事件；
10053指优化器的调试追踪事件。
--trace日志中看到如下内容
*** Start trace 10002 event [SESSION ID:139686915374240] ***
[DDL SQL]:
ALTER USER TEST1 ON SCHEMA TEST1;

[EQUIVALENT DML SQL]:
{
SP_LLOG_REPORT_DDL(65540, -1, -1, 'ALTER USER TEST1 ON SCHEMA TEST1;');
{
        INT V_DC_FLAG_LEVEL;
        INT ISO_LEVEL = -1;
try{
        V_DC_FLAG_LEVEL = SF_RESET_DDLCOMMIT_FLAG();

{
SP_VM_SET_SQL('ALTER USER TEST1 ON SCHEMA TEST1;');
        SF_LOCK_DICT(1056964608, 3, 0);
                UPDATE SYS.SYSOBJECTS SET INFO2 = 0, INFO3 = 4, INFO4 = 281470832739315, INFO5 = 0x000A000048, VERSION = VERSION + 1 WHERE NAME='TEST1' AND TYPE$='UR' AND SUBTYPE$='USER';
                UPDATE SYS.SYSUSER$ SET PASSWORD = 'F597936C419D1C0BC05E186B0249D9864C47D2B8E1DACA773DDFB0080E5930F4F72F52ADD60394CB1A801FF97AE7F606FC7877A81E192C1EC870922EE4B68D05',AUTHENT_TYPE = 1,SESS_PER_USER = 0,CONN_IDLE_TIME = 0,FAILED_NUM = 3,LIFE_TIME = 0   ,REUSE_TIME = 0,REUSE_MAX = 0,LOCK_TIME = 1,GRACE_TIME = 10, PWD_POLICY = 2,RN_FLAG = 0WHERE ID = 50331756;
SP_VM_SET_SQL(NULL);
        {
        SF_REMOVE_DICT_OBJ( 0, 50331756, 'UR', 'USER', 0);
        SP_RESET_SESSION_USER(50331756);
        }
        SF_LOCK_APPLY_ID();
        COMMIT;
}
        SP_RESTORE_DDLCOMMIT_FLAG(V_DC_FLAG_LEVEL);
}
catch (exception ex){
        SP_RESTORE_DDLCOMMIT_FLAG(V_DC_FLAG_LEVEL);
        SF_SET_GEN_INS_UREC_FLAG(1);
        if (ISO_LEVEL != -1)
        SF_SET_TRX_ISO_LEVEL(ISO_LEVEL);
        throw;
}
}
}

--多次修改用户的缺省模式之后发现，INFO4表示的就是用户的缺省模式
UPDATE 
       SYS.SYSOBJECTS 
   SET INFO2 = 0, 
       INFO3 = 4, 
       INFO4 = 281470832739316, 
       INFO5 = 0x000A000048, 
       VERSION = VERSION + 1 
 WHERE NAME='TEST1' 
   AND TYPE$='UR' 
   AND SUBTYPE$='USER';

那281470832739316是什么意思呢？

DBA如何查询用户的缺省模式？

作为DBA，如何快速查询所有用户的缺省模式是什么？（我们总不能挨个登录用户去查上下文吧😂）

通过对SYSOBJECTS中数据的分析，我得出这个结论：

--查询用户信息及其缺省模式：
   SELECT A.NAME AS USER_NAME,
          A.ID AS USER_ID,
          B.NAME AS SHCEMA_NAME,
          B.ID AS SHCEMA_ID,
          SF_GET_REAL_ROWID(B.ROWID) as REALID,
          CASE 
           WHEN A.INFO4 is null
          THEN A.NAME
          WHEN (A.INFO4 = 281474976710655) 
          THEN A.NAME 
          WHEN (A.INFO4 = B.ID + 281470681743360)
          THEN B.NAME
          END AS DEFAULT_SHCEMA
     FROM SYSOBJECTS A --用于确定用户名
 left  JOIN SYSOBJECTS B --SCHEMA_NAME
       ON A.ID = B.PID 
    WHERE A.TYPE$ = 'UR' AND A.SUBTYPE$='USER' AND B.TYPE$='SCH'
    order by B.ROWID
    
--其中：
--281474976710655 是INFO4的默认值： (2^48-1)(十六进制:0xFFFFFFFFFFFF) (同时也是rowid的最大值) ， 约为 256 TB。
--修改缺省模式后：info4=schema_id + 281470681743360
--281470681743360 (十六进制：0x4000000000000) 这个数字在DM中有什么特殊含义我也无从得知...

思考： schema_id与rowid之间有何关联？数据库中对象的数量能否超过2^48个？

用户的默认schema可以修改吗？

--尝试drop schema
[dmdba@localhost ~]$ disql TEST1/TEST123456
服务器[LOCALHOST:5236]:处于普通打开状态
登录使用时间 : 2.636(ms)
disql V8
--这里切换模式，避免资源占用
SQL> set schema "TEST2";
操作已执行
已用时间: 0.426(毫秒). 执行号:0.

SQL> drop schema "TEST1" restrict;
drop schema "TEST1" restrict;
第1 行附近出现错误[-2506]:试图删除用户[TEST1]的默认模式.
已用时间: 0.339(毫秒). 执行号:0.

SQL> select * fromWHERE CODE = -2506;
行号     CODE        ERRINFO                              
---------- ----------- -------------------------------------
1          -2506       试图删除用户[%s]的默认模式
已用时间: 2.190(毫秒). 执行号:3601.

由此可见，用户TEST1的默认SCHEMA仍然是TEST1。ALTER USER TEST1 ON SCHEMA “TEST2” 只是影响了SQL的执行。

从这些现象来看，用户的默认SCHEMA并不能被修改。其实也可以理解，毕竟从人类的思维习惯来将，表之类的模式对象是属于用户的。

不允许删除用户同名模式可以在一定程度上避免给用户带来困惑。

但，ERR-2101-无效的用户名，可以考虑增加一些详细信息输出。这个错误发生在创建与已有schema同名的用户时。

一个有趣的现象

--此处使用SYSDBA登录
[dmdba@localhost DAMENG]$ disql
disql V8
用户名:
密码:
服务器[LOCALHOST:5236]:处于普通打开状态
登录使用时间 : 3.877(ms)
SQL> set schema TEST1;
操作已执行
已用时间: 0.367(毫秒). 执行号:0.
SQL> SELECT SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA') FROM DUAL;
行号     SYS_CONTEXT('USERENV','CURRENT_SCHEMA')
---------- ---------------------------------------
1          TEST1
已用时间: 1.002(毫秒). 执行号:3302.
--确认当前登录用户是SYSDBA
SQL> SELECT SYS_CONTEXT('USERENV', 'CURRENT_USER') FROM DUAL;
行号     SYS_CONTEXT('USERENV','CURRENT_USER')
---------- -------------------------------------
1          SYSDBA

已用时间: 1.225(毫秒). 执行号:3303.
--不指定SCHEMA创建表
SQL> create TABLE TEST_T_4(C1 INT);
操作已执行
已用时间: 17.093(毫秒). 执行号:3306.
--查看表所属用户和SCHEMA
SQL>  SELECT 
		C.NAME AS USER_NAME,
		A.NAME AS SCH_NAME
     FROM SYSOBJECTS A
LEFT JOIN SYSOBJECTS B --用于确定表名
       ON A.ID = B.SCHID
LEFT JOIN SYSOBJECTS C --用于确定用户名
       ON A.PID = C.ID
    WHERE B.TYPE$ = 'SCHOBJ'
      AND B.SUBTYPE$ = 'UTAB'
      AND B.NAME = 'TEST_T_4'
      AND C.TYPE$ = 'UR'
      AND C.SUBTYPE$='USER'; 
      
行号     USER_NAME SCH_NAME
---------- --------- --------
1          TEST1     TEST1

可见使用SYSDBA用户连接数据库时，更改会话的schema后，创建的对象会属于该schema，而该对象所属用户则是拥有该schema的用户。

应用程序在连接数据库时如果指定了SCHEMA，则创建的对象属于该SCHEMA，而不会属于建立连接的用户。[1]

大家在使用数据库的过程中一定要注意用户与模式的区别，在语义分析的过程中，schema作为结构元数据，而用户则是作为权限元数据

模式的概念

类型 描述 示例
Schema对象 属于某个schema的对象，通常用于存储和管理用户的数据、视图、索引等。 表、视图、索引、存储过程、触发器、约束等
非Schema对象 不属于任何特定schema，通常用于数据库的管理、存储、访问控制等。 数据库、表空间、用户、权限、数据库链接等

达梦管理工具指定schema方式如下：

image20241203152053629.png
点击高级，输入schem名
image20241203152120643.png

JDBC驱动指定schema的方式如下：

spring.datasource.url=jdbc:dm://192.168.131.135:5236?schema=TEST

  1. SELECT * FROM TEST1.TEST_T_1中的TEST1是什么？ DBA_TABLES中的OWNER是什么？ ↩︎

上一篇：
三步生成亿级仿真数据集，支持达梦、Oracle、MySQL
评论
后发表回复

作者

文章

阅读量

获赞

扫一扫
联系客服
logo
logo
logo
鄂Copyright © 达梦国产化应用创新实验室
达梦在线服务平台社区用户管理规范

鄂ICP备18017926号-1