【无标题】

需求说明

现有数据库账号：CCENSE:具有完整权限，增删改查。 需要创建一个数据库账号：ykt_test,对CCENSE账号下的某四张表赋予只读权限

第一步、创建只读账号

第一步、创建只读账号
CREATE USER htreader identified by 123456

第二步、赋予账号连接数据库等基本权限

赋予htreader连接等常规权限
grant connect to htreader;
grant create view to htreader;
grant create session to htreader;
grant create synonym to htreader;

第三步、获取原账号的查询权限

select ‘grant select on ‘|| owner || ‘.’||object_name||’ to ykt_test;’
from dba_objects
where owner in (‘CCENSE’) – 数据库/表空间
and object_type=‘TABLE’
and object_name in(‘BASE_CUSTOMERS’,‘BASE_CUSTDEPT’,‘BASE_DEPT’,‘REC_MAIN_CONSUME’);-- 相关表

-- 执行上面查询语句
grant select on CCENSE.BASE_CUSTDEPT to ykt_test;
grant select on CCENSE.BASE_CUSTOMERS to ykt_test;
grant select on CCENSE.BASE_DEPT to ykt_test;
grant select on CCENSE.REC_MAIN_CONSUME to ykt_test;

第四步：在原账号CCENSE端执行，获取需要显示的表名称

select ‘create or replace SYNONYM ykt_test.’|| object_name ||’ for ‘||owner|| ‘.’|| object_name||’;’
from dba_objects
where owner in (‘CCENSE’)
and object_type=‘TABLE’
and object_name in(‘BASE_CUSTOMERS’,‘BASE_CUSTDEPT’,‘BASE_DEPT’,‘REC_MAIN_CONSUME’);-- 相关表

执行上面查询语句
create or replace SYNONYM ykt_test.BASE_CUSTDEPT for CCENSE.BASE_CUSTDEPT;
create or replace SYNONYM ykt_test.BASE_CUSTOMERS for CCENSE.BASE_CUSTOMERS;
create or replace SYNONYM ykt_test.BASE_DEPT for CCENSE.BASE_DEPT;
create or replace SYNONYM ykt_test.REC_MAIN_CONSUME for CCENSE.REC_MAIN_CONSUME;

第五步：测试查询

select * from BASE_CUSTOMERS WHERE ROWNUM = 1;
select * from BASE_CUSTDEPT WHERE ROWNUM = 1;
select * from BASE_DEPT WHERE ROWNUM = 1;
select * from REC_MAIN_CONSUME WHERE ROWNUM = 1;

修改测试
update CCENSE.REC_MAIN_CONSUME  set dscrp = '餐费支出1' where  CSTACCFC = '14632377';