Oracle 创建只读账号以及授权
创建只读账号 wms_reader
1
CREATE USER wms_reader IDENTIFIED BY wms_reader;
授予只读账号登录数据库权限
1
GRANT CONNECT TO WMS_READER;
授予只读账号创建同义词的权限
1
GRANT CREATE SYNONYM TO WMS_READER;
登录 qdmwms_pre 用户,将 sp_wm_audit_sealwrh 的相应权限授予只读账号
1
2
3
4
5GRANT EXECUTE,DEBUG ON sp_wm_audit_sealwrh TO WMS_READER;
-- 生成批量授权存储过程的语句
set pagesize 0;
select 'grant execute,debug on '||object_name||' to WMS_READER;' from user_procedures where OBJECT_TYPE = 'PROCEDURE';使用只读账号登录 Oracle,创建私有同义词
1
2
3
4
5
6
7CREATE SYNONYM sp_wm_audit_sealwrh FOR qdmwms_pre.sp_wm_audit_sealwrh;
-- 生成批量创建同义词的语句
select 'create or replace synonym '||TABLE_NAME||' for '||OWNER||'.'||TABLE_NAME||';'
from user_tab_privs
where owner = 'QDMWMS_PRE'
and privilege = 'DEBUG';使用只读账号登录Oracle,在 PL/SQL 对象窗口 Synonyms 中查看是否存在同义词对象 sp_wm_audit_sealwrh
授权所有debug 权限(可选)
1
GRANT debug any procedure, debug connect session TO WMS_READER;
同义词创建/删除方法
单个同义词创建并授权
创建同义词
1
create or replace synonym SP_WM_AUDIT_SEALWRH for QDMWMS.SP_WM_AUDIT_SEALWRH;
将同义词授权给只读账号
1
2
3grant DEBUG,EXECUTE on SP_WM_AUDIT_SEALWRH to WMS_READER;
-- 或者
grant DEBUG,EXECUTE on SP_WM_AUDIT_SEALWRH to PUBLIC;查看同义词
1
select object_name, object_type, owner from all_objects where object_name = upper('p_report_days_of_turnover');
或者
1
select * from user_synonyms;
批量生成同义词创建语句的方法
生成批量对表授权的语句
1
2
3
4select 'grant select on '||owner||'.'||object_name||' to WMS_READER;'
from dba_objects
where owner in ('QDMWMS')
and object_type='TABLE';生成表的同义词创建语句
1
2
3
4SELECT 'create or replace SYNONYM ' || object_name|| ' FOR ' || owner || '.' || object_name|| ';'
from dba_objects
where owner in ('QDMWMS')
and object_type='TABLE';生成对同义词的 select 授权语句
1
2
3select 'grant select on ' ||SYNONYM_NAME || ' to PUBLIC; '
from dba_synonyms
WHERE OWNER='WMS_READER';生成对存储过程进行授权的语句
1
2
3
4select 'grant execute,debug on '||owner||'.'||object_name||' to WMS_READER;'
from dba_objects
where owner in ('QDMWMS_PRE')
and object_type='PROCEDURE';
删除同义词
删除同义词的语句为:
1 | DROP [PUBLIC] SYNONYM [schema.]sysnonym_name; |
- 删除指定同义词
1 | DROP SYNONYM sp_wm_audit_sealwrh; |
查看权限语句
查看账号拥有的dba权限与角色
1
2
3
4select privilege from dba_sys_privs where grantee='WMS_READER'
union
select privilege from dba_sys_privs where grantee in
(select granted_role from dba_role_privs where grantee='WMS_READER');查看用户拥有的dba权限
1
select * from dba_sys_privs where grantee='WMS_READER';
查看用户拥有的角色
1
select * from dba_role_privs where grantee='WMS_READER';