数据安全越来越被重视，不仅仅是防范硬件故障和黑客攻击，现在防范管理人员误操作和维护/开发人员越级操作越来越重要。之前就有很多客户咨询，如何防止DBA通过sqlplus修改数据，也有客户通过数据库的审计来审计DBA的操作，靠谱吗？我本身就是DBA，你让我来限制我的操作，我要是想操作还能让你知道吗！不过还真有方法可以限制对oracle不是很熟悉的人通过sqlplus乱改数据的。

可以通过ORACLE的system用户下的PRODUCT_USER_PROFILE表来限制通过sqlplus连接数据库的用户操作，即使用户有权限，也是很被紧张的。下面是PRODUCT_USER_PROFILE的介绍。

SQL*Plus uses the PRODUCT_USER_PROFILE (PUP) table, a table in the SYSTEM
account, to provide product-level security that supplements the user-level
security provided by the SQL GRANT and REVOKE commands and user roles.

DBAs can use the PUP table to disable certain SQL and SQL*Plus commands in the
SQL*Plus environment on a per-user basis. SQL*Plus—not Oracle Database—
enforces this security. DBAs can even restrict access to the GRANT, REVOKE, and
SET ROLE commands to control users' ability to change their database privileges.

SQL*Plus reads restrictions from the PUP table when a user logs in to SQL*Plus
and maintains those restrictions for the duration of the session. Changes to the
PUP table will only take effect the next time the affected users log in to
SQL*Plus.

When SYSTEM, SYS, or a user authenticating with SYSDBA or SYSOPER privileges
connects or logs in, SQL*Plus does not read the PUP table. Therefore, no
restrictions apply to these users.

The PUP table applies only to the local database. If accessing objects on a
remote database through a database link, the PUP table for the remote database
does not apply. The remote database cannot extract the username and password
from the database link in order to determine that user's profile and privileges.

举个栗子，我的数据库中有dbdream用户，拥有connect和resource角色，那么对dbdream用户自己建的表，默认是有全部权限的(比如删除表和删除数据)，下面就来演示下怎么限制不让dbdream用户做这些他可以做的事情。

SQL> CREATE TABLE XXX AS SELECT * FROM USER_TABLES;

Table created.

SQL>  drop table xxx purge;

Table dropped.

默认情况下，dbdream用户对自己创建的表是有drop权限的，那么怎么限制不让他做这个呢？只需要在 system用户的PRODUCT_USER_PROFILE表里插一条记录，普通用户在通过sqlplus登录数据库时，需要读取PRODUCT_USER_PROFILE表的数据，查看是否对用户的操作有限制。

SQL> INSERT INTO SYSTEM.PRODUCT_USER_PROFILE VALUES ('SQL*Plus', 'DBDREAM', 'DROP', NULL, NULL, 'DISABLED', NULL, NULL);

1 row created.

SQL> commit;

上面的insert语句就是将dbdream用户通过sqlplus发出drop操作全部拒绝，当然，dbdream需要重新通过sqlplus登录才会生效。

[oracle@secdb1 ~]$ sqlplus dbdream/dbdream

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Jul 22 10:55:41 2014

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options

SQL> CREATE TABLE XXX AS SELECT * FROM USER_TABLES;

Table created.

SQL>  DROP TABLE XXX PURGE;
SP2-0544: Command "drop" disabled in Product User Profile

可见dbdream虽然有删除自己表的权限，但是还是被限制了，下面再看看delete操作。

SQL> select count(*) from xxx;

  COUNT(*)
----------
         3

SQL> delete from xxx where rownum=1;

1 row deleted.

SQL> commit;

Commit complete.

正常情况用户对自己的表可以删除数据，这没问题，下面看看限制用户删除数据。

SQL> INSERT INTO PRODUCT_USER_PROFILE VALUES ('SQL*Plus', 'DBDREAM', 'DELETE', NULL, NULL, 'DISABLED', NULL, NULL);

1 row created.

SQL> commit;

Commit complete.

如果用户没有重新使用sqlplus登录数据库，限制是不生效的，如下：

SQL>  delete from xxx where rownum=1;

1 row deleted.

SQL> commit;

Commit complete.

SQL> select count(*) from xxx;

  COUNT(*)
----------
         1

用户重新登录后，限制生效，无法删除数据。

[oracle@secdb1 ~]$ sqlplus dbdream/dbdream

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Jul 22 11:06:12 2014

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options

SQL> delete from xxx where rownum=1;
SP2-0544: Command "delete" disabled in Product User Profile

此方法基本所以操作都可以限制，对ORACLE懂的不是很多的人，可能会被限制住，如果懂的人，只需要将system用户下面的PRODUCT_USER_PROFILE表中相应的记录删掉或修改掉就可以跳过这个问题了，希望被限制的人不要搜到这篇文章，突破限制修改数据后果自负。

——————————end——————————–

本文固定链接: https://www.dbdream.com.cn/2014/07/%e5%88%a9%e7%94%a8product_user_profile%e8%a1%a8%e9%99%90%e5%88%b6%e7%94%a8%e6%88%b7%e7%9a%84%e6%9d%83%e9%99%90/ | 信春哥，系统稳，闭眼上线不回滚！