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

    Login

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

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top