oracle创建只读账户

为了数据库安全，我们通常会创建一个只读账户，与业务账号进行分开，那如何创建一个只读账户？

创建账号

create user readonly identified by 123456;
grant create session,create synonym to readonly;

授权

--授权所有表上的读权限
begin
for i in (select owner,table_name from dba_tables where owner='EBTRISK')
loop
execute immediate 'grant select on '||i.owner||'.'||i.table_name||' to ebview';
end loop;
end;
/

--创建表的同义词
begin
for i in (select owner,table_name from dba_tables where owner='ETUSP')
loop
execute immediate 'create or replace synonym etusp_read.'||i.table_name||' for '||i.owner||'.'||i.table_name;
end loop;
end;
/

--授权视图上的读权限
begin
for i in (select owner,VIEW_NAME from dba_views where owner='ETUSP')
loop
execute immediate 'grant select  on '||i.owner||'.'||i.VIEW_NAME||' to etusp_read';
end loop;
end;
/

--授权函数和存储过程的读权限
begin
for i in (select distinct owner,name from dba_source where owner='BIDW' and type in('PROCEDURE','FUNCTION','PACKAGE','PACKAGE BODY','TYPE BODY','TYPE'))
loop
execute immediate 'grant debug on '||i.owner||'.'||i.name||' to bidw_read';
end loop;
end;
/

--授权序列的读权限
begin
for i in (select sequence_owner,sequence_name from dba_sequences where sequence_owner='ETUSP')
loop
execute immediate 'grant select on '||i.sequence_owner||'.'||i.sequence_name||' to etusp_read';
end loop;
end;
/