Oracle用户授权表

业务场景
合作平台方需要对接业务数据库，新建用户设置相关权限。

--创建用户。使用有管理员权限的用户创建一个新的用户：
Create User MLSJPD Identified By Ue4lqau7;
--连接权限:
Grant Connect To MLSJPD ;
--创建同义词权限:
Grant Create Synonym To MLSJPD;
--登录权限
Grant Create Session To MLSJPD;

--赋表权限
Grant Select On DLSJZS.PUB_GOODS To MLSJPD;
Grant Select,Delete,Insert,Update On DLSJZS.AA_HXJ_TMP2 To MLSJPD;
--回收权限
revoke delete,insert,update on DLSJZS.AA_HXJ_TMP2 from MLSJPD;

--批量转换 [SELECT,DELETE,INSERT,UPDATE] [TABLE，VIEW，SEQUENCE]
Select 'Grant Select On '||Owner||'.'||Object_name||' To MLSJPD;' 
From Dba_objects 
Where Owner In ('DLSJZS') 
And Object_type='TABLE';

--用新建的用户登录执行
Create Or Replace Synonym MLSJPD.PUB_GOODS FOR DLSJZS.PUB_GOODS;

--DLSJZS下查询 MLSJPD下执行给表权限
Select 'Create Or Replace Synonym MLSJPD.'|| Object_name||' For '|| Owner||'.'||Object_name||';'
From Dba_objects 
Where Owner In ('DLSJZS') 
And Object_type='TABLE';