Oracle建立使用者並給使用者授權查詢指定表或檢視的許可權

lygle發表於2014-05-12
Oracle

用DNINMSV31賬戶登入資料庫進行如下操作：

CREATE USER NORTHBOUND IDENTIFIED BY NORTHBOUND
 DEFAULT TABLESPACE "TBS_DNINMSV31"
 TEMPORARY TABLESPACE "TEMP2"
 QUOTA UNLIMITED ON "TBS_DNINMSV31";

GRANT "CONNECT" TO NORTHBOUND;
ALTER USER NORTHBOUND DEFAULT ROLE NONE;

GRANT CREATE SESSION TO NORTHBOUND;

GRANT SELECT ON "DNINMSV31"."V_DNDEVICE" TO NORTHBOUND;
GRANT SELECT ON "DNINMSV31"."V_DNSUBNE" TO NORTHBOUND;
GRANT SELECT ON "DNINMSV31"."V_DNPACKAGE" TO NORTHBOUND;
GRANT SELECT ON "DNINMSV31"."V_DNPORT" TO NORTHBOUND;


【注】：在用NORTHBOUND登入後查詢的時候要在檢視前加上DNINMSV31，否則會報表或檢視不存在。
        例如：
        select * from DNINMSV31.V_DNDEVICE;     --可以正常執行

        select * from DNINMSV31.TB_DEVICE where rownum<5;   --執行的時候會報表或檢視不存在


建立同義詞供對方公司系統訪問：

CREATE SYNONYM  V_DNDEVICE FOR  DNINMSV31.V_DNDEVICE; 

CREATE SYNONYM  V_DNSUBNE  FOR  DNINMSV31.V_DNSUBNE;

CREATE SYNONYM  V_DNPACKAGE  FOR  DNINMSV31.V_DNPACKAGE;

CREATE SYNONYM V_DNPORT  FOR  DNINMSV31.V_DNPORT;

第三方的系統直接通過這個同義詞就可以訪問到使用者DNINMSV31中的檢視。這也是對系統安全的一種保護措施。第三方系統登入後，只能看到其同義詞，其他的都不會訪問得到。


最後還要對NORTHBOUND使用者進行連線數的限制，以免第三方無限制的連線資料庫，造成資料庫SESSION暴漲：

conn / as sysdba

alter system set resource_limite=true scope=both sid='*';

新建profile, 初始限制為1 ，用於測試。

create profile third_user limit SESSIONS_PER_USER 1 FAILED_LOGIN_ATTEMPTS unlimited;

alter user NORTHBOUND profile third_user;

將會話數調整到30

alter profile third_user limit SESSIONS_PER_USER 30;

--------------------------------------------------------------------------------------------------

跟第三方做介面程式時給對方開放只能查詢某些檢視使用者的步驟如下：


我們有這樣一個需求：在資料庫中建立兩個使用者，使用者A 用於建立一些檢視，直接訪問自己資料庫中一個模式下的表，以及通過資料庫鏈路訪問其他資料庫中的表；另一個使用者B 能訪問到這個使用者A 中的檢視，並且只能訪問檢視，且訪問連線數有限制。

這個使用者B 是用來給其他系統訪問的，因此對許可權和資源使用都需要有限制條件。

這種需求在很多行業的應用中都很常見。假如這是一道面試題，您該如何去回答呢？

我採用下面的方法來回答這個問題。

 

第一步，建立新使用者A 和B 。

這裡使用者名稱稱分別為ryd_interface_src  和ryd_interface ，對這兩個使用者都授予非常有限的許可權。

drop user ryd_interface_src cascade;

create user ryd_interface_src identified by ryd_interface_src;

grant connect,create view to ryd_interface_src;

 

drop user ryd_interface cascade;

create user ryd_interface identified by ryd_interface;

grant connect,create synonym to ryd_interface;

 

第二步，登入資料庫一個模式中，授權給使用者A ，使得使用者A 能建立檢視

conn qlzqclient/qlzq+client8

grant select on INVEST_CLOCK  to ryd_interface_src with grant option;

grant select on INVEST_LOG   to ryd_interface_src with grant option;

這裡授權方法加了一個with grant option ，請注意。

 

第三步，登入資料庫使用者A 中，建立檢視

conn ryd_interface_src/ryd_interface_src

create or replace view run_views as

select id as doc_id, title,fbsj as upload_date,'' as branch_code from qlzq.runs_lantern@CLIENT_QLZQWEB

where EXT1='1' and sysdate >  START_TIME

and sysdate < SOLID_TIME

union

select a.doc_id,a.title,a.upload_date,a.branch_code  from qlzq.cms_doc_single_attr@CLIENT_QLZQWEB a

left   join qlzq.cms_doc_category_map@CLIENT_QLZQWEB b on a.doc_id=b.doc_id

where

a.state =1 and a.is_delete =0

and  a.upload_date> sysdate-90

and  b.cat_id=4;

 

create or replace view INVEST_CLOCK_VIEWS as

select  *  from  qlzqclient.INVEST_CLOCK;

 

create or replace view INVEST_LOG_VIEWS as

select  *  from  qlzqclient.INVEST_LOG;

 

第四步，在資料庫使用者A 中，將檢視查詢許可權授予給使用者B

因為在步驟二中，加了with grant option ，所以這裡檢視查詢許可權可以成功授予。

grant select on INVEST_CLOCK_views to ryd_interface;

grant select on INVEST_LOG_views to ryd_interface;

grant select on run_views to ryd_interface;

 

第五步，在資料庫使用者B 中，檢查檢視能否查詢得到，再建立同義詞。

conn ryd_interface/ryd_interface

select count(*) from ryd_interface_src.run_views;

select count(*) from ryd_interface_src.INVEST_CLOCK_views;

select count(*) from ryd_interface_src.INVEST_LOG_views;

 

create synonym  run_views for  ryd_interface_src.run_views;

create synonym  INVEST_CLOCK_views for  ryd_interface_src.INVEST_CLOCK_views;

create synonym  INVEST_log_views for  ryd_interface_src.INVEST_log_views;

第三方的系統直接通過這個同義詞就可以訪問到使用者A 中的檢視。這也是對系統安全的一種保護措施。第三方系統登入後，只能看到其同義詞，其他的都不會訪問得到。

 

第六步，限制資源使用

因為使用者B 是給第三方系統使用，我們無法控制第三方應用的質量，為了防止在應用出現異常連線時資料庫會話數暴漲導致資料庫整體服務出現故障，所以我們對該使用者的資料庫會

話數做一個限制。

這裡採用profile 的sessions_per_user 功能，實現單個使用者會話數的限制。

conn / as sysdba

alter system set resource_limite=true scope=both sid='*';

新建profile, 初始限制為1 ，用於測試。

create profile third_user limit SESSIONS_PER_USER 1 FAILED_LOGIN_ATTEMPTS unlimited;

alter user ryd_interface profile third_user;

將會話數調整到30

alter profile third_user limit SESSIONS_PER_USER 30;

 

通過以上操作，我們就可以實現這個需求了。 

--------------------------------------------------------------------------------------------------

GRANT debug any procedure, debug connect session TO hnckb;
grant select on sys.dba_pending_transactions to hnckb;
grant CREATE VIEW,CREATE JOB,CREATE SYNONYM to hnckb;
grant CONNECT,RESOURCE to hnckb;
1.標準角色
CONNECT
RESOURCE

2.系統許可權
CREATE VIEW
CREATE DATABASE LINK
CREATE JOB
CREATE SYNONYM
UNLIMITED TABLESPACE
更改定額
命令：ALTER USER 名稱 QUOTA 0 ON 表空間名
ALTER USER 名字 QUOTA （數值）K|M|UNLIMITED ON 表空間名；
使用方法：
A、控制使用者資料增長
B、當使用者擁有一定的資料，而管理員不想讓他在增加新的資料的時候。
C、當將使用者定額設為零的時候，使用者不能建立新的資料，但原有資料仍可訪問。

3.物件許可權
DBA_PENDING_TRANSACTIONS （SELECT）  — XA事務支援

grant select on sys.dba_pending_transactions to user;
4.建議開放
DEBUG CONNECT SESSION                — 除錯儲存過程
例如：
GRANT debug any procedure, debug connect session TO hr;

Oracle建立表空間、建立使用者、授權、授權物件的訪問以及檢視許可權
1.建立臨時表空間
Oracle臨時表空間主要用來做查詢和存放一些緩衝區資料。臨時表空間消耗的主要原因是需要對查詢的中間結

果進行排序。
臨時表空間的主要作用：
索引create或rebuild
Order by 或 group by
Distinct 操作
Union 或 intersect 或 minus
Sort-merge joins

CREATE TEMPORARY TABLESPACE “TEST_TEMP”
    TEMPFILE ‘D:\ORACLE\PRODUCT\10.2.0\ORADATA\GIS\TEST_DATA.ora’
    SIZE 20M
AUTOEXTEND ON
NEXT 32M MAXSIZE 2048M
EXTENT MANAGEMENT LOCAL;

2.建立使用者表空間
CREATE TABLESPACE “TEST_DATA”
    LOGGING
    DATAFILE ‘D:\ORACLE\PRODUCT\10.2.0\ORADATA\GIS\TEST_DATA.ora’
    SIZE 20M
AUTOEXTEND ON
NEXT 32M MAXSIZE 2048M
EXTENT MANAGEMENT LOCAL;

3.建立使用者並設定其表空間
username:usertest
password:userpwd

CREATE USER usertest IDENTIFIED BY userpwd
DEFAULT TABLESPACE TEST_DATA
TEMPORARY TABLESPACE TEST_TEMP;

4.給使用者授權
GRANT
　　CREATE SESSION, CREATE ANY TABLE, CREATE ANY VIEW ,CREATE ANY INDEX, CREATE ANY PROCEDURE,
　　ALTER ANY TABLE, ALTER ANY PROCEDURE,
　　DROP ANY TABLE, DROP ANY VIEW, DROP ANY INDEX, DROP ANY PROCEDURE,
　　SELECT ANY TABLE, INSERT ANY TABLE, UPDATE ANY TABLE, DELETE ANY TABLE
TO username;

首先授權使用者testuser兩個基本的角色許可權

CONNECT角色：    –是授予終端使用者的典型權利，最基本的      
   CREATE    SESSION    –建立會話 

RESOURCE角色：    –是授予開發人員的     
   CREATE    CLUSTER         –建立聚簇  
   CREATE    PROCEDURE    –建立過程  
   CREATE    SEQUENCE      –建立序列  
   CREATE    TABLE              –建表  
   CREATE    TRIGGER          –建立觸發器  
   CREATE    TYPE                –建立型別
   CREATE    OPERATOR       –建立操作者
   CREATE    INDEXTYPE      –建立索引型別
   CREATE    TABLE              –建立表

授權角色給使用者的sql語句：
GRANT role TO username;

 

5.檢視使用者許可權

檢視所有使用者
SELECT * FROM DBA_USERS;
SELECT * FROM ALL_USERS;
SELECT * FROM USER_USERS;

檢視使用者系統許可權
SELECT * FROM DBA_SYS_PRIVS;
SELECT * FROM USER_SYS_PRIVS;

檢視使用者物件或角色許可權
SELECT * FROM DBA_TAB_PRIVS;
SELECT * FROM ALL_TAB_PRIVS;
SELECT * FROM USER_TAB_PRIVS;

檢視所有角色
SELECT * FROM DBA_ROLES;

檢視使用者或角色所擁有的角色
SELECT * FROM DBA_ROLE_PRIVS;
SELECT * FROM USER_ROLE_PRIVS;

遇到no privileges on tablespace ‘tablespace ‘
alter user userquota 10M[unlimited] on tablespace;

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/28673746/viewspace-1159359/，如需轉載，請註明出處，否則將追究法律責任。

相關文章