Oracle PUP（PRODUCT_USER_PROFILE）配置和使用

最近在翻Oracle SQLPLUS官方文檔，在講SQLPLUS Security章節介紹了PUP這個機制。藉此，我來使用以下：

PUP(PRODUCT_USER_PROFILE)介紹
  PRODUCT_USER_PROFILE是SYSTEM賬戶下一個表，可提供使用者層級的安全限制。
  PUP設定對DBA許可權使用者無效。
  PUP只針對本機資料庫生效(Local Database)。




1、SYSTEM 使用者建立PUP：
SQLPLUS SYSTEM

@ D:\app\Administrator\product\11.2.0\dbhome_1\sqlplus\admin\pupbld.sql

指令碼內容：

DROP SYNONYM PRODUCT_USER_PROFILE;CREATE TABLE SQLPLUS_PRODUCT_PROFILE AS  SELECT PRODUCT, USERID, ATTRIBUTE, SCOPE, NUMERIC_VALUE, CHAR_VALUE,  DATE_VALUE FROM PRODUCT_USER_PROFILE;DROP TABLE PRODUCT_USER_PROFILE;ALTER TABLE SQLPLUS_PRODUCT_PROFILE ADD (LONG_VALUE LONG);-- Create SQLPLUS_PRODUCT_PROFILE from scratchCREATE TABLE SQLPLUS_PRODUCT_PROFILE(  PRODUCT        VARCHAR2 (30) NOT NULL,  USERID         VARCHAR2 (30),  ATTRIBUTE      VARCHAR2 (240),  SCOPE          VARCHAR2 (240),  NUMERIC_VALUE  DECIMAL (15,2),  CHAR_VALUE     VARCHAR2 (240),  DATE_VALUE     DATE,  LONG_VALUE     LONG);-- Remove SQL*Plus V3 name for sqlplus_product_profileDROP TABLE PRODUCT_PROFILE;-- Create the view PRODUCT_PRIVS and grant access to thatDROP VIEW PRODUCT_PRIVS;CREATE VIEW PRODUCT_PRIVS AS  SELECT PRODUCT, USERID, ATTRIBUTE, SCOPE,         NUMERIC_VALUE, CHAR_VALUE, DATE_VALUE, LONG_VALUE  FROM SQLPLUS_PRODUCT_PROFILE  WHERE USERID = 'PUBLIC' OR USER LIKE USERID;GRANT SELECT ON PRODUCT_PRIVS TO PUBLIC;DROP PUBLIC SYNONYM PRODUCT_PROFILE;CREATE PUBLIC SYNONYM PRODUCT_PROFILE FOR SYSTEM.PRODUCT_PRIVS;DROP SYNONYM PRODUCT_USER_PROFILE;CREATE SYNONYM PRODUCT_USER_PROFILE FOR SYSTEM.SQLPLUS_PRODUCT_PROFILE;DROP PUBLIC SYNONYM PRODUCT_USER_PROFILE;CREATE PUBLIC SYNONYM PRODUCT_USER_PROFILE FOR SYSTEM.PRODUCT_PRIVS;

--禁用HR使用者的DROP命令
SYSTEM@orcl> insert into product_user_profile values('SQL*Plus', 'HR', 'DROP', NULL, NULL, 'DISABLED', NULL, NULL);


已建立 1 行。


SYSTEM@orcl> commit;


提交完成。


2、PUP表結構概覽
SYSTEM@orcl> desc product_user_profile
 名稱                                     是否為空白? 類型
 ---------------------------------------- -------- ---------------------------
 PRODUCT                                  NOT NULL VARCHAR2(30)
 USERID                                            VARCHAR2(30)
 ATTRIBUTE                                         VARCHAR2(240)
 SCOPE                                             VARCHAR2(240)
 NUMERIC_VALUE                                     NUMBER(15,2)
 CHAR_VALUE                                        VARCHAR2(240)
 DATE_VALUE                                        DATE
 LONG_VALUE                                        LONG


 --PRODUCT : 說明要限制的程式 
 --USERID : 要限制的使用者（大寫）
 --ATTRIBUTE : 要限制的命令或角色
 --SCOPE : 不適用；放NULL
 --NUMERIC_VALUE : 不適用；放NULL
 --CHAR_VALUE ：DISABLED
 --DATE_VALUE ：不適用；放NULL
 --LONG_VALUE ：不適用；放NULL
 


3、HR登入進行DROP操作證明設定生效：
SYSTEM@orcl> conn hr/hr
已串連。


HR@orcl> create table t(x int);


表已建立。


HR@orcl> drop table t;
SP2-0544: 在產品使用者概要檔案中禁用命令 "drop"
HR@orcl> conn system/oracle@orcl
已串連。


SYSTEM@orcl> delete from product_user_profile;


已刪除 1 行。


SYSTEM@orcl> commit;


提交完成。


SYSTEM@orcl> conn hr/hr@orcl
已串連。


HR@orcl> drop table t;


表已刪除。




4、禁用角色
PRODUCT USERID ATTRIBUTE SCOPE NUMERIC_VALUE CHAR_VALUE DATE_VALUE LONG_VALUE
------- ------ --------- ----- -------- ------ ----- -----
SQL*Plus HR     ROLES                           ROLE1
SQL*Plus PUBLIC ROLES                          ROLE2


使用者登入期間PUP行記錄將翻譯為以下命令
During login, these table rows are translated into the command
SET ROLE ALL EXCEPT ROLE1, ROLE2


例子：
SYS@orcl> create role r_t;
角色已建立。
SYS@orcl> grant select on t to r_t;
授權成功。
SYS@orcl> grant r_t to hr;
授權成功。
SYS@orcl> conn hr/hr
已串連。
HR@orcl> select * from sys.t;


未選定行
HR@orcl> conn system/oracle
已串連。


會話已更改。


SYSTEM@orcl> insert into product_user_profile values('SQL*Plus', 'HR', 'ROLES', NULL, NULL, 'r_t', NULL, NULL);


已建立 1 行。


SYSTEM@orcl> commit;


提交完成。


SYSTEM@orcl> conn hr/hr
已串連。
HR@orcl> select * from sys.t;
select * from sys.t
                  *
第 1 行出現錯誤:
ORA-00942: 表或視圖不存在


HR@orcl> select username, granted_role
  2      from user_role_privs
  3     where granted_role='R_T';


USERNAME                       GRANTED_ROLE
------------------------------ ------------------------------
HR                             R_T


HR@orcl> conn system/oracle
已串連。


會話已更改。


SYSTEM@orcl> delete from product_user_profile;


已刪除 1 行。
SYSTEM@orcl> commit;


提交完成。
SYSTEM@orcl> conn hr/hr
已串連。


會話已更改。


HR@orcl> select * from sys.t;


未選定行






------------------------‘
Dylan    Presents.