创建只读账号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;

创建只读账号oracle

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

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

上一篇:计算excel列的名字


下一篇:百度EChart3初体验