【實驗優先】
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
sec@ora10g> select * from t;
A
----------
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/<system_password> @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;
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 SYSTEM.SQLPLUS_PRODUCT_PROFILE(PRODUCT,USERID,ATTRIBUTE,CHAR_VALUE) values ('SQL*Plus','SEC','DELETE','DISABLED');
2.參考一下這個文章
《安全保護項目》中的“2.4 使用産品配置檔案保護 SQL*Plus”中描述了另外一個例子
http://www.oracle.com/technology/global/cn/pub/articles/project_lockdown/phase2.html#2.4
簡單摘錄兩條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 --