Hi, I want to disable via PRODUCT_USER_PROFILE the command HOST for some 'ROLES'. Does the PRODUCT_USER_PROFILE works with 'ROLES'?
insert INTO product_user_profile values('SQL*Plus', 'USERPROD', 'HOST', null, null, 'DISABLED', null, null); commit;User 'MAC' is in USERPROD role, but the command HOST still works... Why?
Sorry to break the bad news, but using PRODUCT_USER_PROFILE entries to disable certain commands and features does NOT work at the role level. All entries are user specific. So, if you want to disable the HOST command for everyone with a certain role, all of those users need to have an entry added to the PRODUCT_USER_PROFILE table.
There is a way you can make this process of adding all these individual userid rows easier on yourself. That would be to generate a script to create those entries by reading from the data dictionary DBA_ROLE_PRIVS view. Create a SQL*Plus script as follows:
-- Start of script SET HEADING OFF SET FEEDBACK OFF SET ECHO OFF SPOOL ins_pup_users_by_role.sql SELECT 'INSERT INTO product_user_profile VALUES (' || '''' || 'SQL*PLUS' || '''' || ', ' || '''' || grantee || '''' || ', ' || '''' || 'HOST' || '''' || ', ' || 'null, null, ' || '''' || 'DISABLED' || ', ' || 'null, null);' as insert_stmt FROM dba_role_privs WHERE granted_role = 'USERPROD' ; SPOOL OFF SET HEADING ON SET FEEDBACK ON SET ECHO ON @ins_pup_users_by_role -- End of scriptThe select statement would generate a script file named ins_pup_users_by_role.sql that contains command lines something like these:
INSERT INTO product_user_profile VALUES ('SQL*PLUS', 'MAC', 'HOST', null, null, 'DISABLED, null, null); INSERT INTO product_user_profile VALUES ('SQL*PLUS', 'SCOTT', 'HOST', null, null, 'DISABLED, null, null); INSERT INTO product_user_profile VALUES ('SQL*PLUS', 'KAREN', 'HOST', null, null, 'DISABLED, null, null); INSERT INTO product_user_profile VALUES ('SQL*PLUS', 'JOE', 'HOST', null, null, 'DISABLED, null, null);As you can see, you can quickly generate the insert statements you'll need to insert entries into product_user_profile for all users for a given role for any command.
Hope that helps!
For More Information
- Dozens more answers to tough Oracle questions from Karen Morton are available.
- The Best Oracle Web Links: tips, tutorials, scripts, and more.
- Have an Oracle or SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical Oracle and SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.
This was first published in July 2002