Q

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?


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

Dig deeper on Oracle and SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

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:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close