1. 支持identified by创建用户
Oracle创建用户示例:
CREATE USER HS_SES
IDENTIFIED BY hundsun
DEFAULT TABLESPACE HS_SES_DATA
TEMPORARY TABLESPACE TEMP;
其中 IDENTIFIED BY后面跟的是用户的密码,原生LightDB后面的密码需要用单引号括起来,而Oracle对于纯字母的密码不用引号,或者用双引号,如 IDENTIFIED BY "hundsun"。具体规则如下:
* 1: Pure digital password with short length
* e.g. 123456
* 2: Pure digital password with long length
* e.g. 12345678901234567890
* 3: Alphabetic password
* e.g. abc123
* 4: Alphabetic password with Special symbols
* e.g. abc@@@
* Must be in double quotes
* 5. Password that does not start with a letter
* e.g. 123abc @abc123 ...
* Must be in double quotes
其中 DEFAULT TABLESPACE仅做语法支持。
其中 TEMPORARY TABLESPACE仅做语法支持。
LightDB实现的效果如下:
lightdb@lt_test=# CREATE USER HS_SES
lightdb@lt_test-# IDENTIFIED BY hundsun
lightdb@lt_test-# DEFAULT TABLESPACE HS_SES_DATA
lightdb@lt_test-# TEMPORARY TABLESPACE TEMP;
CREATE ROLE
2. 支持级联删除用户
Oracle支持级联删除用户,如下:
drop user u1 cascade;
LightDB原生不支持级联删除用户,只能普通删除,当用户有依赖时,删除用户直接报错:
lightdb@lt_test=# drop user u1;
ERROR: role "u1" cannot be dropped because some objects depend on it
DETAIL: owner of table t1
owner of table bmsql_config
owner of table bmsql_warehouse
owner of table bmsql_district
owner of table bmsql_customer
owner of sequence bmsql_hist_id_seq
owner of table bmsql_history
owner of table bmsql_new_order
owner of table bmsql_oorder
owner of table bmsql_order_line
owner of table bmsql_item
owner of table bmsql_stock
owner of function bmsql_cid_from_clast(integer,integer,character varying)
owner of function bmsql_proc_new_order(integer,integer,integer,integer[],integer[],integer[])
owner of function bmsql_proc_payment(integer,integer,integer,integer,integer,character varying,numeric)
owner of function bmsql_proc_stock_level(integer,integer,integer)
所以为了兼容Oracle,LightDB需要支持级联删除用户,如下:
lightdb@lt_test=# drop user u1 cascade;
DROP ROLE
这里有个注意点,当被删除的用户如果还未断开连接,级联删除用户会报错:
lightdb@lt_test=# drop user u1 cascade;
ERROR: user 'u1' cannot be dropped cascade because there are users already connected.
怎么看该用户有没有连接,有个简单的方法:
select * from pg_stat_activity where usename = 'u1';
其中pg_stat_activity是一个系统视图,是实时更新的。
3. 支持3种Oracle授权语句
1)connect
Oracle使用方法如下:
GRANT CONNECT TO HS_SES;
2)resource
Oracle使用方法如下:
GRANT RESOURCE TO HS_SES;
3)unlimited tablespace
GRANT UNLIMITED TABLESPACE TO HS_SES;
这三种语法仅做语法支持,效果如下:
lightdb@lt_test=# GRANT CONNECT TO HS_SES;
GRANT ROLE
lightdb@lt_test=# GRANT RESOURCE TO HS_SES;
GRANT ROLE
lightdb@lt_test=# GRANT UNLIMITED TABLESPACE TO HS_SES;
GRANT ROLE
这里有一个注意点,原生LightDB,对于grant u1 to u2语句是用来将用户u1的权限给用户u2,这就与Oracle语法冲突了,为了解决该冲突,LightDB不允许创建名称为'connect'和名称为'resource'的用户。效果如下:
lightdb@lt_test=# create user connect identified by 123456;
ERROR: The user is not allowed to create a user with the name connect.
lightdb@lt_test=# create user resource identified by 123456;
ERROR: The user is not allowed to create a user with the name resource.
详细语法可参考LightDB官网查看https://www.hs.net/lightdb
更多请登录LightDB官网进行查看:https://www.hs.net/lightdb