现有数据库账号:HEPSUSR:具有完整权限,增删改查。
需要创建一个数据库账号:HTREADER,对HEPSUSR账号下所有的表具有只读权限。
--创建只读账号 第一步 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;
获取原账号HEPSUSR用户的所有查询表权限 select 'grant select on '||owner||'.'||object_name||' to htreader;' from dba_objects where owner in ('HEPSUSR') and object_type='TABLE'; --查询结果为新账号的赋值语句,如下图
在原账号HEPSUSR下执行,将原账号的查询权限 赋值给新账号 ------- grant select on HEPSUSR.ENTRY_CERT to htreader; grant select on HEPSUSR.SUB_MESSAGE_INFO to htreader; grant select on HEPSUSR.ENTRY_CERT_RELATION to htreader; grant select on HEPSUSR.ENTRY_CERT_RELATION to htreader; grant select on HEPSUSR.ENTRY_DECL_TAX to htreader; grant select on HEPSUSR.ENTRY_DOCU to htreader; grant select on HEPSUSR.ENTRY_FEES to htreader; grant select on HEPSUSR.ENTRY_GOODS_TAX to htreader; grant select on HEPSUSR.ENTRY_HEAD to htreader; grant select on HEPSUSR.ENTRY_LIST to htreader; grant select on HEPSUSR.ENTRY_WORKFLOW to htreader; grant select on HEPSUSR.IQ_APPEND to htreader; grant select on HEPSUSR.IQ_CERT to htreader; grant select on HEPSUSR.SUB_SWAP to htreader; grant select on HEPSUSR.VIN_LIST to htreader;
因为新创建的只读账号,Tables栏中显示为空,我们需要在PL/SQL显示栏中为新账号登录界面添加显示同位元素,如下:
--在原账号HEPSUSR端执行,获取需要显示的表名称 select 'create or replace SYNONYM htreader.'||object_name|| ' for ' ||owner|| '.'||object_name||';' from dba_objects where owner in ('HEPSUSR') and object_type='TABLE'
在只读账号HTREADER端执行:添加显示各个表信息;在SYSNONYM目录下,tables目录下无显示 create or replace SYNONYM htreader.VIN_LIST for HEPSUSR.VIN_LIST; create or replace SYNONYM htreader.SUB_SWAP for HEPSUSR.SUB_SWAP; create or replace SYNONYM htreader.SUB_MESSAGE_INFO for HEPSUSR.SUB_MESSAGE_INFO; create or replace SYNONYM htreader.IQ_CERT for HEPSUSR.IQ_CERT; create or replace SYNONYM htreader.IQ_APPEND for HEPSUSR.IQ_APPEND; create or replace SYNONYM htreader.ENTRY_WORKFLOW for HEPSUSR.ENTRY_WORKFLOW; create or replace SYNONYM htreader.ENTRY_LIST for HEPSUSR.ENTRY_LIST; create or replace SYNONYM htreader.ENTRY_HEAD for HEPSUSR.ENTRY_HEAD; create or replace SYNONYM htreader.ENTRY_GOODS_TAX for HEPSUSR.ENTRY_GOODS_TAX; create or replace SYNONYM htreader.ENTRY_FEES for HEPSUSR.ENTRY_FEES; create or replace SYNONYM htreader.ENTRY_DOCU for HEPSUSR.ENTRY_DOCU; create or replace SYNONYM htreader.ENTRY_DECL_TAX for HEPSUSR.ENTRY_DECL_TAX; create or replace SYNONYM htreader.ENTRY_CONTAINER for HEPSUSR.ENTRY_CONTAINER; create or replace SYNONYM htreader.ENTRY_CERT_RELATION for HEPSUSR.ENTRY_CERT_RELATION; create or replace SYNONYM htreader.ENTRY_CERT for HEPSUSR.ENTRY_CERT;
新账号可以查询原账号的所有表结构,但是无法执行 增删改相关操作
1.查看所有用户: select * from dba_users; select * from all_users; select * from user_users; 2.查看用户或角色系统权限(直接赋值给用户或角色的系统权限): select * from dba_sys_privs; select * from user_sys_privs; 3.查看角色(只能查看登陆用户拥有的角色)所包含的权限 sql>select * from role_sys_privs; 4.查看用户对象权限: select * from dba_tab_privs; select * from all_tab_privs; select * from user_tab_privs; 5.查看所有角色: select * from dba_roles; 6.查看用户或角色所拥有的角色: select * from dba_role_privs; select * from user_role_privs;
免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:mmqy2019@163.com进行举报,并提供相关证据,查实之后,将立刻删除涉嫌侵权内容。