Oracle中sqlplus命令限制（使用自带脚本pupbld.sql）

有时出于安全考虑需要禁止一些业务系统的数据库用户执行sqlplus命令，方法很简单：在运行命令之前，将这些命令限制到一个由 SQL*Plus 引用的“特殊位置”。 此特殊位置是SYSTEM 模式中一个名为PRODUCT_USER_PROFILE 的表。 如果该表不存在，则您在每次启动 SQL*Plus 时将获得一个类似“Product User Profile Not Loaded”这样的警告。

为了创建这个表，需要运行pupbld.sql脚本。通常，这个脚本在$ORACLE_HOME/sqlplus/admin 路径中运行，具体的位置由系统决定。记住用system用户执行：

SQL> conn system/passwd

SQL> @?/sqlplus/admin/pupbld.sql

pupbld.sql脚本内容如下（红色的内容是我添加的，表示禁止WENDING结尾的数据库用户操作列出的那些sqlplus命令，可以根据实际情况增删改）：

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

-- wangnc added, 2008-9-25 16:47:11, insert data
-- 所有业务系统用户禁止执行sqlplus一些命令

delete PRODUCT_USER_PROFILE where product='SQL*Plus' and userid='%WENDING';

insert into PRODUCT_USER_PROFILE (product, userid, attribute, char_value)
values ('SQL*Plus','%WENDING','HOST','DISABLED');

insert into PRODUCT_USER_PROFILE (product, userid, attribute, char_value)
values ('SQL*Plus','%WENDING','ALTER','DISABLED');

insert into PRODUCT_USER_PROFILE (product, userid, attribute, char_value)
values ('SQL*Plus','%WENDING','AUDIT','DISABLED');

insert into PRODUCT_USER_PROFILE (product, userid, attribute, char_value)
values ('SQL*Plus','%WENDING','ANALYZE','DISABLED');

insert into PRODUCT_USER_PROFILE (product, userid, attribute, char_value)
values ('SQL*Plus','%WENDING','CREATE','DISABLED');

insert into PRODUCT_USER_PROFILE (product, userid, attribute, char_value)
values ('SQL*Plus','%WENDING','DELETE','DISABLED');

insert into PRODUCT_USER_PROFILE (product, userid, attribute, char_value)
values ('SQL*Plus','%WENDING','DROP','DISABLED');

insert into PRODUCT_USER_PROFILE (product, userid, attribute, char_value)
values ('SQL*Plus','%WENDING','LOCK','DISABLED');

insert into PRODUCT_USER_PROFILE (product, userid, attribute, char_value)
values ('SQL*Plus','%WENDING','NOAUDIT','DISABLED');

insert into PRODUCT_USER_PROFILE (product, userid, attribute, char_value)
values ('SQL*Plus','%WENDING','RENAME','DISABLED');

insert into PRODUCT_USER_PROFILE (product, userid, attribute, char_value)
values ('SQL*Plus','%WENDING','SELECT','DISABLED');

insert into PRODUCT_USER_PROFILE (product, userid, attribute, char_value)
values ('SQL*Plus','%WENDING','UPDATE','DISABLED');

insert into PRODUCT_USER_PROFILE (product, userid, attribute, char_value)
values ('SQL*Plus','%WENDING','VALIDATE','DISABLED');

insert into PRODUCT_USER_PROFILE (product, userid, attribute, char_value)
values ('SQL*Plus','%WENDING','TRUNCATE','DISABLED');

insert into PRODUCT_USER_PROFILE (product, userid, attribute, char_value)
values ('SQL*Plus','%WENDING','GRANT','DISABLED');

insert into PRODUCT_USER_PROFILE (product, userid, attribute, char_value)
values ('SQL*Plus','%WENDING','REVOKE','DISABLED');

insert into PRODUCT_USER_PROFILE (product, userid, attribute, char_value)
values ('SQL*Plus','%WENDING','SET ROLE','DISABLED');  

insert into PRODUCT_USER_PROFILE (product, userid, attribute, char_value)
values ('SQL*Plus','%WENDING','SET TRANSACTION','DISABLED');  

insert into PRODUCT_USER_PROFILE (product, userid, attribute, char_value)
values ('SQL*Plus','%WENDING','DECLARE','DISABLED');  

insert into PRODUCT_USER_PROFILE (product, userid, attribute, char_value)
values ('SQL*Plus','%WENDING','BEGIN','DISABLED');  

insert into PRODUCT_USER_PROFILE (product, userid, attribute, char_value)
values ('SQL*Plus','%WENDING','EXECUTE','DISABLED');

insert into PRODUCT_USER_PROFILE (product, userid, attribute, char_value)
values ('SQL*Plus','%WENDING','COPY','DISABLED');

--insert into PRODUCT_USER_PROFILE (product, userid, attribute, char_value)
--values ('SQL*Plus','%WENDING','SET','DISABLED');

insert into PRODUCT_USER_PROFILE (product, userid, attribute, char_value)
values ('SQL*Plus','%WENDING','EDIT','DISABLED');

insert into PRODUCT_USER_PROFILE (product, userid, attribute, char_value)
values ('SQL*Plus','%WENDING','PASSWORD','DISABLED');

insert into PRODUCT_USER_PROFILE (product, userid, attribute, char_value)
values ('SQL*Plus','%WENDING','SPOOL','DISABLED');

insert into PRODUCT_USER_PROFILE (product, userid, attribute, char_value)
values ('SQL*Plus','%WENDING','START','DISABLED');

--insert into PRODUCT_USER_PROFILE (product, userid, attribute, char_value)
--values ('SQL*Plus','%WENDING','QUIT','DISABLED');

--insert into PRODUCT_USER_PROFILE (product, userid, attribute, char_value)
--values ('SQL*Plus','%WENDING','EXIT','DISABLED');

insert into PRODUCT_USER_PROFILE (product, userid, attribute, char_value)
values ('SQL*Plus','%WENDING','RUN','DISABLED');

insert into PRODUCT_USER_PROFILE (product, userid, attribute, char_value)
values ('SQL*Plus','%WENDING','GET','DISABLED');

insert into PRODUCT_USER_PROFILE (product, userid, attribute, char_value)
values ('SQL*Plus','%WENDING','SAVE','DISABLED');

commit;

注意：

禁用SQL*Plus SET指令的同时也会禁用SQL SET ROLE和SET TRANSACTION命令。
禁用SQL*Plus START的同时也会禁用SQL*Plus @和@@命令。
禁用SQL*Plus HOST的同时也会禁用等同命令（如VMS上的$以及UNIX上的!）。

--End--