Oracle創建隻讀賬號的詳細步驟
需求說明
現有數據庫賬號: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;
第七步:執行完成之後 登錄新賬號,查看結果
新賬號可以查詢原賬號的所有表結構,但是無法執行 增刪改相關操作
第八步:執行刪除、修改sql語句測試
附錄:Oracle查詢賬號及權限詳細語句
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;
以上就是Oracle創建隻讀賬號的詳細步驟的詳細內容,更多關於Oracle創建隻讀賬號的資料請關註WalkonNet其它相關文章!
推薦閱讀:
- openGauss的SCHEMA的原理及管理介紹
- Postgresql 數據庫權限功能的使用總結
- MySQL授權命令grant的使用方法小結
- postgreSQL數據庫默認用戶postgres常用命令分享
- Postgres 創建Role並賦予權限的操作