创建只读账号oracle

创建只读账号oracle

1.创建用户，指定哪个表空间
create user test2 identified by "123" default tablespace BDCDJ_XC temporary tablespace BDCDJ_XC_temp;

2.创建角色
CREATE ROLE SELECT_ROLE

3.给角色分配权限
grant connect to SELECT_ROLE;
grant create synonym to SELECT_ROLE;

4.分配某些表的select权限
如：
grant select on BDCDJ_XC.BDC_CF to SELECT_ROLE;

--可以通过语句生成：
select 'grant select on '||owner||'.'||object_name||' to test2;'
from dba_objects
where owner in ('BDCDJ_XC') and object_type='TABLE'

5.创建同义词给用户
如：
create or replace SYNONYM test2.BDC_CF FOR BDCDJ_XC.BDC_CF;

--可以通过语句生成所有表：
SELECT 'create or replace SYNONYM BDCDJ_XC.' || object_name|| ' FOR ' || owner || '.' || object_name|| ';'
from dba_objects
where owner in ('BDCDJ_XC') and object_type='TABLE';

 

6.把角色赋予指定账户
grant SELECT_ROLE to test2;

7.删除角色
drop role SELECT_ROLE;

8.检查角色的权限
select * from dba_sys_privs where grantee='SELECT_ROLE'
---------------------

这样其他人只有通过这个账号登录查询，表名不列出来，表空间不列出来。
可以创建database_link但是查询时报“表或视图不存在”
create database link to_bdcxc
connect to test2 identified by "123"
using '(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.xx.xxx)
(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = orcl) ))'

select* from bdc_cf@to_bdcxc;

 

也就无法通过create table ..select复制数据库。
create table bdc_cf as select * from bdc_cf@to_bdcxc;

这样办法可行否，给个意见。

posted on 2019-02-13 17:48 Anders' Yan 阅读( ...) 评论( ...) 编辑 收藏

转载于:https://www.cnblogs.com/yansc/p/10371154.html