【實驗優先】
1.在sys使用者下執行指令碼pupbld.sql使PRODUCT_USER_PROFILE可用
pupbld.sql指令碼所在目錄為$ORACLE_HOME/sqlplus/admin/pupbld.sql

sys@ora10g> conn / as sysdba
Connected.
sys@ora10g> @?/sqlplus/admin/pupbld.sql

2.向product_user_profile中出入如下限制資訊
sys@ora10g> insert into product_user_profile(PRODUCT,USERID,ATTRIBUTE,CHAR_VALUE) values ('SQL*Plus','SEC','DELETE','DISABLED');

1 row created.

sys@ora10g> commit;

Commit complete.

sys@ora10g> col PRODUCT    for a10
sys@ora10g> col USERID     for a10
sys@ora10g> col ATTRIBUTE for a10
sys@ora10g> col CHAR_VALUE for a10
sys@ora10g> select PRODUCT,USERID,ATTRIBUTE,CHAR_VALUE from product_user_profile;

PRODUCT    USERID     ATTRIBUTE CHAR_VALUE
---------- ---------- ---------- ----------
SQL*Plus   SEC        DELETE     DISABLED

3.驗證效果
sys@ora10g> conn sec/sec
Connected.

sec@ora10g> select * from t;

         A
----------
         1
         1
         1
         1
         1

sec@ora10g> delete from t;
SP2-0544: Command "delete" disabled in Product User Profile

這種限制方法已經生效，從此，sec使用者將不可以再執行刪除delete操作。
OK，這個小實驗到此先告一段落。

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

【知識擴充套件ing】
1.先看一下pupbld.sql指令碼都寫了什麼
ora10g@testdb /home/oracle$ cat $ORACLE_HOME/sqlplus/admin/pupbld.sql
--
-- Copyright (c) Oracle Corporation 1988, 2003. All Rights Reserved.
--
-- NAME
--   pupbld.sql
--
-- DESCRIPTION
--   Script. to install the SQL*Plus PRODUCT_USER_PROFILE tables. These
--   tables allow SQL*Plus to disable commands per user. The tables
--   are used only by SQL*Plus and do not affect other client tools
--   that access the database. Refer to the SQL*Plus manual for table
--   usage information.
--
--   This script. should be run on every database that SQL*Plus connects
--   to, even if the tables are not used to restrict commands.

-- USAGE
--   sqlplus system/ @pupbld
--
--   Connect as SYSTEM before running this script

-- If PRODUCT_USER_PROFILE exists, use its values and drop it

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 scratch

CREATE 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_profile

DROP TABLE PRODUCT_PROFILE;

-- Create the view PRODUCT_PRIVS and grant access to that

DROP 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;

-- End of pupbld.sql

看到了麼？PRODUCT_USER_PROFILE原來是SYSTEM.SQLPLUS_PRODUCT_PROFILE的一個同名，所以可以直接操作system使用者下的SQLPLUS_PRODUCT_PROFILE表，如下操作：
sys@ora10g> insert into product_user_profile(PRODUCT,USERID,ATTRIBUTE,CHAR_VALUE) values ('SQL*Plus','SEC','DELETE','DISABLED');
變成如下的形式效果是相同的
sys@ora10g> insert into SYSTEM.SQLPLUS_PRODUCT_PROFILE(PRODUCT,USERID,ATTRIBUTE,CHAR_VALUE) values ('SQL*Plus','SEC','DELETE','DISABLED');

2.參考一下這個文章
《安全保護專案》中的“2.4 使用產品配置檔案保護 SQL*Plus”中描述了另外一個例子

簡單摘錄兩條SQL語句
SQL> insert into system.SQLPLUS_PRODUCT_PROFILE
2 values ('SQL*Plus','%','HOST',null,null,'DISABLED',null,null)
3 /

insert into system.SQLPLUS_PRODUCT_PROFILE
values ('SQL*Plus','%','LOCK',null,null,'DISABLED',null,null)
/

3.該方法支援的其他限制內容如下
可以被禁用的 SQL 命令如下：
alter            drop          revoke
analyze          explain       rollback
associate        flashback     savepoint
audit            grant         select
call             insert        set constraints
comment          lock          set role
commit           merge         set transaction
create           noaudit       truncate
delete           purge         update
disassociate     rename        validate

可以被禁用的 SQL*Plus 專有命令（及其縮寫或簡寫形式）如下：
accept          edit          repheader
append          execute       run
archive log     exit          save
attribute       quit          set
break           get           show
btitle          help          shutdown
change          host          spool
clear           input         start
column          list          startup
compute         password      store
connect         pause         timing
copy            print         ttitle
define          prompt        undefine
del             recover       variable
describe        remark        whenever oserror
disconnect      repfooter     whenever sqlerror

4.最後，為加深記憶，對product_user_profile每一個欄位的含義做一下介紹
sys@ora10g> desc product_user_profile
Name             Null?    Type
---------------- -------- ------------------
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       ------ 產品名稱，如“SQL*Plus”
USERID        ------ 被禁止的使用者名稱
ATTRIBUTE     ------ 被禁止的命令，如上面列出的，如“delete”等
SCOPE         ------ null
NUMERIC_VALUE ------ null
CHAR_VALUE    ------ 禁用時值應為“DISABLED”
DATE_VALUE    ------ null
LONG_VALUE    ------ null

-- The End --

【實驗】使用PRODUCT_USER_PROFILE禁止特定使用者在SQL*Plus中使用delete語句

相關文章