在oracle中，我们一般通过授权和角色来控制用户的权限，但是某些情况下，比如想限制以dev开头的所有用户不能在sql*plus中执行alter命令，则通过grant授权的方式无法实现。这里要介绍另外一种能简单的实现上述需求的方式，就是PRODUCT_USER_PROFILE表，通过在该表中插入相应的记录，可以限制某些用户(可以使用通配符)在SQL*Plus中使用某些命令，但这只是SQL*Plus提供的特性，而不是数据库本身的控制，所以在其他连接到数据库的客户端中该限制将不可用。
在oracle9i开始提供该特性，如果创建的数据库中没有默认创建PRODUCT_USER_PROFILE表，也可以通过执行以下脚本手工创建(以system用户)：
SYS@ning>conn system/password
SYSTEM@NinGoo>@$ORACLE_HOME/sqlplus/admin/pupbld.sql
SYSTEM@ning>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
SYSTEM@NinGoo>@$ORACLE_HOME/sqlplus/admin/pupbld.sql
SYSTEM@ning>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
简单介绍一下这个表中的列：
- PRODULE:要限制的命令类型，比如SQL*Plus表示不能SQL*Plus命令如spool，SQL则表示SQL命令如alert，PL/SQL表示PL/SQL命令如BEGIN。不能使用通配符或者NULL。
- USERID:要限制的用户名，可以使用通配符如DEV%
- ATTRIBUTE：要限制的命令，必须是完整的SQL*Plus、SQL或者PL/SQL命令，不能使用通配符。如果是禁用某个角色，则必须为ROLES
- SCOPE：未使用，设为NULL
- NUMERIC_VALUE：未使用，设为NULL
- CHAR_VALUE:如果禁用的是SQL*Plus、SQL或者PL/SQL命令，必须为DISABLED。如果是禁用某个角色，则为角色的名字
- DATE_VALUE：未使用，设为NULL
- LONG_VALUE：未使用，设为NULL
假如我们要限制用户NinGoo不能执行SQL*Plus命令spool，则：
SYSTEM@ning>insert into product_user_profile(product,userid,attribute,char_value)
2 values('SQL*Plus','NINGOO','SPOOL','DISABLED');
1 row created.
2 values('SQL*Plus','NINGOO','SPOOL','DISABLED');
1 row created.
然后以NinGoo用户登陆执行SPOOL命令，发现报错：
SYSTEM@ning>conn NinGoo/NinGoo
Connected.
NINGOO@ning>spool
SP2-0544: Command "spool" disabled in Product User Profile
Connected.
NINGOO@ning>spool
SP2-0544: Command "spool" disabled in Product User Profile
假如限制使用test角色：
SYSTEM@ning>create role test;
Role created.
SYSTEM@ning>grant select on ning.test to test;
Grant succeeded.
SYSTEM@ning>insert into product_user_profile(product,userid,attribute,char_value)
2 values('SQL*Plus','NINGOO','ROLES','TEST');
1 row created.
Role created.
SYSTEM@ning>grant select on ning.test to test;
Grant succeeded.
SYSTEM@ning>insert into product_user_profile(product,userid,attribute,char_value)
2 values('SQL*Plus','NINGOO','ROLES','TEST');
1 row created.
这条记录的意思是，用户NinGoo登陆到SQL*Plus时会自动执行以下命令：
set roles all except test;
所以，如果原来没有将角色授予NinGoo，则登陆的时候会报错:
SYSTEM@ning>conn NinGoo/NinGoo
ERROR:
ORA-01919: role 'TEST' does not exist
SP2-0557: Error in disabling roles in product user profile.
Connected.
ERROR:
ORA-01919: role 'TEST' does not exist
SP2-0557: Error in disabling roles in product user profile.
Connected.
但是这里由于没有限制用户执行set role命令，所以实际上用户可以通过set role启用该角色
NINGOO@ning>conn system/password
Connected.
SYSTEM@ning>grant test to NinGoo;
Grant succeeded.
SYSTEM@ning>conn NinGoo/NinGoo
Connected.
NINGOO@ning>select * from ning.test;
select * from ning.test
*
ERROR at line 1:
ORA-00942: table or view does not exist
NINGOO@ning>set role test;
Role set.
NINGOO@ning>select * from ning.test;
I
----------
1
Connected.
SYSTEM@ning>grant test to NinGoo;
Grant succeeded.
SYSTEM@ning>conn NinGoo/NinGoo
Connected.
NINGOO@ning>select * from ning.test;
select * from ning.test
*
ERROR at line 1:
ORA-00942: table or view does not exist
NINGOO@ning>set role test;
Role set.
NINGOO@ning>select * from ning.test;
I
----------
1
要彻底对该用户禁用test角色，则可以在PRODUCT_USER_PROFILE中禁用SET ROLE命令。如果要去除限制，则删除表中相应的记录即可。
可以禁用的SQL*Plus命令：
ACCEPT DEFINE PASSWORD SHUTDOWN
APPEND DEL PAUSE SPOOL
ARCHIVELOG DESCRIBE PRINT START (@, @@)
ATTRIBUTE DISCONNECT PROMPT STARTUP
BREAK EDIT RECOVER STORE
BTITLE EXECUTE REMARK TIMING
CHANGE EXIT/QUIT REPFOOTER TTITLE
CLEAR GET REPHEADER UNDEFINE
COLUMN HELP (?) RUN VARIABLE
COMPUTE HOST SAVE WHENEVER OSERROR
CONNECT INPUT SET WHENEVER SQLERROR
COPY LIST (;) SHOW XQUERY
APPEND DEL PAUSE SPOOL
ARCHIVELOG DESCRIBE PRINT START (@, @@)
ATTRIBUTE DISCONNECT PROMPT STARTUP
BREAK EDIT RECOVER STORE
BTITLE EXECUTE REMARK TIMING
CHANGE EXIT/QUIT REPFOOTER TTITLE
CLEAR GET REPHEADER UNDEFINE
COLUMN HELP (?) RUN VARIABLE
COMPUTE HOST SAVE WHENEVER OSERROR
CONNECT INPUT SET WHENEVER SQLERROR
COPY LIST (;) SHOW XQUERY
可以禁用的SQL命令：
ALTER DELETE MERGE SET CONSTRAINTS
ANALYZE DISASSOCIATE NOAUDIT SET ROLE
ASSOCIATE DROP PURGE SET TRANSACTION
AUDIT EXPLAIN RENAME TRUNCATE
CALL FLASHBACK REVOKE UPDATE
COMMENT GRANT ROLLBACK VALIDATE
COMMIT INSERT SAVEPOINT
CREATE LOCK SELECT
ANALYZE DISASSOCIATE NOAUDIT SET ROLE
ASSOCIATE DROP PURGE SET TRANSACTION
AUDIT EXPLAIN RENAME TRUNCATE
CALL FLASHBACK REVOKE UPDATE
COMMENT GRANT ROLLBACK VALIDATE
COMMIT INSERT SAVEPOINT
CREATE LOCK SELECT
可以禁用的PL/SQL命令：
BEGIN DECLARE
不错的技巧
方法不错,但是不知道在一个并发超过4k每个节点的系统,如果作这样的操作,是否会给系统带来额外的开销?导致系统性能下降,有否注意其在高并发系统上的负面影响?
这个东西影响的是SQL*Plus的操作，对于应用应该是没有什么影响的，你的高并发不会都是直接操作SQL*Plus的吧？