【实验】使用PRODUCT_USER_PROFILE禁止特定用户在SQL*Plus中使用delete语句

原创

小麦苗123 2021-04-18 10:26:40 ©著作权

文章标签 SQL*Plus 文章分类 数据库

©著作权归作者所有：来自51CTO博客作者小麦苗123的原创作品，请联系作者获取转载授权，否则将追究法律责任

【实验优先】
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/<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

看到了么？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”中描述了另外一个例子
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 --