oracle给用户授权所有权限,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';