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

標籤：style   blog   http   color   io   使用   ar   for   資料   

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

1）建立使用者：

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

（註：quota是為了限制使用者對錶空間的使用,比如你限制使用者Guotu在tablespace CYYD中的quota為10m，當使用者Guotu在tablespace CYYD中的資料量達到10m後，無論你的tablespace CYYD中有多少空間，Guotu都無法再使用tablespace CYYD了。）

2）使用者授權

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

GRANT CREATE SESSION TO NORTHBOUND;--登入許可權

GRANT SELECT ON "DNINMSV31"."V_DNDEVICE" TO NORTHBOUND;--查看視圖"DNINMSV31"."V_DNDEVICE"許可權
GRANT SELECT ON "DNINMSV31"."V_DNSUBNE" TO NORTHBOUND;--查看視圖"DNINMSV31"."V_DNSUBNE"許可權
GRANT SELECT ON "DNINMSV31"."V_DNPACKAGE" TO NORTHBOUND;--查看視圖"DNINMSV31"."V_DNPACKAGE"許可權
GRANT SELECT ON "DNINMSV31"."V_DNPORT" TO NORTHBOUND;--查看視圖"DNINMSV31"."V_DNPORT"許可權 

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

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

3）登入使用者查看

使用NORTHBOUND使用者建立同義字供對方公司系統訪問：

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中的視圖。這也是對系統安全的一種保護措施。第三方系統登入後，只能看到其同義字，其他的都不會訪問得到。

4）限制第三方使用者串連數

最後還要對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 [email protected]_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 [email protected]_QLZQWEB a

left   join [email protected]_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;

 

原文：http://blog.sina.com.cn/s/blog_4f925fc30102dtdb.html

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