oracle怎么禁止用户sqlplus,【实验】使用PRODUCT_USER_PROFILE禁止特定用户在SQL*Plus中使用delete语句...

【实验优先】

1.在sys用户下执行脚本pupbld.sql使PRODUCT_USER_PROFILE可用pupbld.sql脚本所在目录为$ORACLE_HOME/sqlplus/admin/pupbld.sqlsys@ora10g> conn / as sysdbaConnected.sys@ora10g> @?/sqlplus/admin/pupbld.sql2.向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 a10sys@ora10g> col USERID     for a10sys@ora10g> col ATTRIBUTE  for a10sys@ora10g> col CHAR_VALUE for a10sys@ora10g> select PRODUCT,USERID,ATTRIBUTE,CHAR_VALUE from product_user_profile;PRODUCT    USERID     ATTRIBUTE  CHAR_VALUE---------- ---------- ---------- ----------SQL*Plus   SEC        DELETE     DISABLED3.验证效果sys@ora10g> conn sec/secConnected.sec@ora10g> select * from t;A----------11111sec@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 itDROP SYNONYM PRODUCT_USER_PROFILE;CREATE TABLE SQLPLUS_PRODUCT_PROFILE ASSELECT 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 ASSELECT PRODUCT, USERID, ATTRIBUTE, SCOPE,NUMERIC_VALUE, CHAR_VALUE, DATE_VALUE, LONG_VALUEFROM SQLPLUS_PRODUCT_PROFILEWHERE 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_PROFILE2  values ('SQL*Plus','%','HOST',null,null,'DISABLED',null,null)3  /insert into system.SQLPLUS_PRODUCT_PROFILEvalues ('SQL*Plus','%','LOCK',null,null,'DISABLED',null,null)/3.该方法支持的其他限制内容如下可以被禁用的 SQL 命令如下：alter            drop          revokeanalyze          explain       rollbackassociate        flashback     savepointaudit            grant         selectcall             insert        set constraintscomment          lock          set rolecommit           merge         set transactioncreate           noaudit       truncatedelete           purge         updatedisassociate     rename        validate可以被禁用的 SQL*Plus 专有命令(及其缩写或简写形式)如下：accept          edit          repheaderappend          execute       runarchive log     exit          saveattribute       quit          setbreak           get           showbtitle          help          shutdownchange          host          spoolclear           input         startcolumn          list          startupcompute         password      storeconnect         pause         timingcopy            print         ttitledefine          prompt        undefinedel             recover       variabledescribe        remark        whenever oserrordisconnect      repfooter     whenever sqlerror4.最后，为加深记忆，对product_user_profile每一个字段的含义做一下介绍sys@ora10g> desc product_user_profileName             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                DATELONG_VALUE                LONGPRODUCT       ------ 产品名称，如“SQL*Plus”USERID        ------ 被禁止的用户名ATTRIBUTE     ------ 被禁止的命令，如上面列出的，如“delete”等SCOPE         ------ nullNUMERIC_VALUE ------ nullCHAR_VALUE    ------ 禁用时值应为“DISABLED”DATE_VALUE    ------ nullLONG_VALUE    ------ null-- The End --