Ask the Expert

Using PRODUCT_USER_PROFILE to disable commands for some roles

Hi, I want to disable via PRODUCT_USER_PROFILE the command HOST for some 'ROLES'. Does the PRODUCT_USER_PROFILE works with 'ROLES'?

For example:

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?


    Requires Free Membership to View

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 script
The 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


This was first published in July 2002

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: