oracle创建用户并授权只读权限


创建用户并设置密码

create user test identified by test123654;

授权用户连接权限
grant connect, resource to test;

授权用户查询权限
grant select on abc.ARTICLERES to test;
grant select on abc.PAGE to test;
grant select on abc.ISSUE to test;
grant select on abc.PAPER to test;
grant select on abc.ARTICLE to test;

授权test用户创建同义词权限

grant create synonym to test;

使用test登陆创建同义词

create synonym ARTICLE for abc.ARTICLE;
create synonym ARTICLERES for abc.ARTICLERES;
create synonym PAGE for abc.PAGE;
create synonym ISSUE for abc.ISSUE;
create synonym PAPER for abc.PAPER;

备注:

1、批量执行授权多表查询权限的查询语句

select 'grant select on '||owner||'.'||object_name||' to t1;'
from dba_objects
where owner in ('test')
and object_type='TABLE';

2、批量执行创建同义词查询语句

SELECT 'create or replace SYNONYM MACS.' || object_name|| ' FOR ' || owner || '.' || object_name|| ';'
from dba_objects
where owner in ('test')
and object_type='TABLE';


Whatever is worth doing is worth doing well.