3.2.6.2. OceanBase-Oracle

3.2.6.2.1. 创建用户

语法

CREATE USER username
    IDENTIFIED BY password
    [DEFAULT TABLESPACE tablespace_name]
    [TEMPORARY TABLESPACE temp_tablespace_name]

警告

用户名称不支持双引号格式
密码格式支持数字字母以及双引号对象其它格式不支持
创建用户的语法TEMPORARY TABLESPACE temp_tablespace_name为语法糖

示例

CREATE TABLESPACE my_tablespace DATAFILE '/home/oracle/tablespace' SIZE 1G;

-- 转换前Oracle SQL:
CREATE USER test_user IDENTIFIED BY test_password
DEFAULT TABLESPACE my_tablespace TEMPORARY TABLESPACE temp;

-- 转换后OceanBase-Oracle SQL:
CREATE USER test_user IDENTIFIED BY test_password
DEFAULT TABLESPACE my_tablespace;

3.2.6.2.2. 删除用户

语法

DROP USER username [CASCADE]

警告

用户名称不支持双引号格式

示例

-- 转换前Oracle SQL:
DROP USER test_user;

-- 转换后OceanBase-Oracle SQL:
DROP USER test_user CASCADE;

3.2.6.2.3. 权限赋值

语法

GRANT system_privilege [, system_privilege]...
TO user [, user]...;

system_privilege：
        CONNECT
        | RESOURCE
        | UNLIMITED TABLESPACE
        | CREATE ANY TABLE
        | SELECT ANY TABLE
        | DROP ANY TABLE
        | CREATE ANY INDEX
        | DELETE ANY TABLE
        | INSERT ANY TABLE
        | UPDATE ANY TABLE
        | CREATE ANY DIRECTORY

警告

用户名称不支持双引号格式，不支持单引号格式，不支持反引号格式
目标库oceanbase-oracle版本>=4.2.1

示例

-- 转换前Oracle SQL:
GRANT UNLIMITED TABLESPACE, CONNECT, RESOURCE TO test_user1, test_user2;

-- 转换后OceanBase-Oracle SQL:
GRANT ALL PRIVILEGES, CONNECT, RESOURCE TO test_user1, test_user2;

-- 转换前，权限赋值

-- 授予创建任何表的权限
GRANT CREATE ANY TABLE TO user_name;

-- 授予查询任何表的权限
GRANT select any table TO user_name;

-- 授予删除任何表（表定义和数据）的权限
grant DROP ANY TABLE TO user_name;

-- 授予创建任何索引的权限
GRANT CREATE any INDEX TO user_name;

-- 授予删除任何表中数据的权限
GRANT DELETE ANY table TO user_name;

-- 授予向任何表插入数据的权限
GRANT INSERT ANY TAble TO user_name;

-- 授予更新任何表中数据的权限
GRANT UPDATE ANY TABLE TO user_name;

-- 授予创建任何目录对象的权限
GRANT CREATE ANY DIRECTORY TO user_name;

-- 授予用户多个权限
GRANT CREATE ANY DIRECTORY,update any table TO user_name;

-- 授予多个用户多个权限
GRANT CREATE ANY DIRECTORY,update ANY TABLE TO user_name1,user_name2;

-- 转换后，权限赋值

GRANT CREATE ANY TABLE TO user_name

GRANT SELECT ANY TABLE TO user_name

GRANT DROP ANY TABLE TO user_name

GRANT CREATE ANY INDEX TO user_name

GRANT DELETE ANY TABLE TO user_name

GRANT INSERT ANY TABLE TO user_name

GRANT UPDATE ANY TABLE TO user_name

GRANT CREATE ANY DIRECTORY TO user_name

GRANT CREATE ANY DIRECTORY, UPDATE ANY TABLE TO user_name

GRANT CREATE ANY DIRECTORY, UPDATE ANY TABLE TO user_name1, user_name2

3.2.6.2.4. 权限移除

语法

REVOKE system_privilege [, system_privilege]...
FROM user [, user]...;

system_privilege：
        CONNECT
        | RESOURCE
        | UNLIMITED TABLESPACE
        | CREATE ANY TABLE
        | SELECT ANY TABLE
        | DROP ANY TABLE
        | CREATE ANY INDEX
        | DELETE ANY TABLE
        | INSERT ANY TABLE
        | UPDATE ANY TABLE
        | CREATE ANY DIRECTORY

警告

用户名称不支持双引号格式，不支持单引号格式，不支持反引号格式
目标库oceanbase-oracle版本>=4.2.1

示例

-- 转换前Oracle SQL:
REVOKE UNLIMITED TABLESPACE, CONNECT, RESOURCE FROM test_user1, test_user2;

-- 转换后OceanBase-Oracle SQL:
REVOKE ALL PRIVILEGES, CONNECT, RESOURCE FROM test_user1, test_user2;

-- 转换前，权限移除
-- 撤销创建任何表的权限
REVOKE CREATE ANY TABLE FROM user_name;

-- 撤销查询任何表的权限
REVOKE SELECT ANY TABLE FROM user_name;

-- 撤销删除任何表（表定义和数据）的权限
REVOKE DROP ANY TABLE FROM user_name;

-- 撤销创建任何索引的权限
REVOKE CREATE ANY INDEX FROM user_name;

-- 撤销删除任何表中数据的权限
REVOKE DELETE ANY TABLE FROM user_name;

-- 撤销向任何表插入数据的权限
REVOKE INSERT ANY TABLE FROM user_name;

-- 撤销更新任何表中数据的权限
REVOKE UPDATE ANY TABLE FROM user_name;

-- 撤销创建任何目录对象的权限
REVOKE CREATE ANY DIRECTORY FROM user_name;

-- 撤销用户多个权限
REVOKE CREATE ANY DIRECTORY, INSERT ANY TABLE FROM user_name;

-- 撤销多个用户多个权限
REVOKE create any directory, INSERT ANY TABLE FROM user_name1,user_name2;

-- 转换后，权限移除
REVOKE CREATE ANY TABLE FROM user_name

REVOKE SELECT ANY TABLE FROM user_name

REVOKE DROP ANY TABLE FROM user_name

REVOKE CREATE ANY INDEX FROM user_name

REVOKE DELETE ANY TABLE FROM user_name

REVOKE INSERT ANY TABLE FROM user_name

REVOKE UPDATE ANY TABLE FROM user_name

REVOKE CREATE ANY DIRECTORY FROM user_name

REVOKE CREATE ANY DIRECTORY, INSERT ANY TABLE FROM user_name

REVOKE CREATE ANY DIRECTORY, INSERT ANY TABLE FROM user_name1, user_name2