| Lesson 10 | Product user profile |
| Objective | Describe the tables and views that are created as part of the product user profile. |
Running PUPBLD Script
In Oracle RDBMS, the Product User Profile (PUP) is an integral feature designed to control user access to specific SQL*Plus commands and options. This control is crucial for maintaining database security and ensuring that users adhere to their designated permissions.
The Product User Profile functionality is implemented through a set of tables and views in the Oracle database.
The central components of the Product User Profile in Oracle are as follows:
The Product User Profile is typically used in environments where there is a need to restrict certain SQL*Plus functionalities for specific users or roles. For instance, in a production environment, it might be necessary to prevent certain users from executing data definition language (DDL) commands or accessing certain sensitive SQL*Plus options. It is important to note that while the Product User Profile provides a layer of security within SQL*Plus, it does not replace or negate the need for proper database security practices and permissions management at the database level. Proper use of the Product User Profile should be part of a comprehensive security strategy.
- Table: PRODUCT_USER_PROFILE (PUP Table):
- This table is the core of the Product User Profile.
- It contains rows that define restrictions on SQL*Plus commands for each user.
- Each row specifies a command or a set of commands and the user or users for whom the command is restricted.
- The columns include fields like `PRODUCT`, `USERID`, `ATTRIBUTE`, `SCOPE`, `ACTION`, and `SQLERRM`, which collectively define the restrictions.
- Only users with appropriate privileges can insert, update, or delete rows in this table, ensuring that command restrictions are securely managed.
- Views:
- Oracle provides views on the PRODUCT_USER_PROFILE table, offering a secure and simplified interface for querying PUP information.
- These views filter and present data in a more readable and relevant format, tailored to specific use cases or user roles.
- Common views include those that display the current user's restrictions or provide summaries of restrictions applied across different users or commands.
- Synonyms:
- Oracle may create public synonyms for the PRODUCT_USER_PROFILE table and its associated views.
- These synonyms facilitate easier access to the table and views without requiring users to specify the full path or schema.
- Procedures and Packages for Management:- Oracle might also include stored procedures or packages that help in managing the entries in the PRODUCT_USER_PROFILE table.
- These procedures can automate common tasks such as adding, modifying, or removing restrictions.
The Product User Profile is typically used in environments where there is a need to restrict certain SQL*Plus functionalities for specific users or roles. For instance, in a production environment, it might be necessary to prevent certain users from executing data definition language (DDL) commands or accessing certain sensitive SQL*Plus options. It is important to note that while the Product User Profile provides a layer of security within SQL*Plus, it does not replace or negate the need for proper database security practices and permissions management at the database level. Proper use of the Product User Profile should be part of a comprehensive security strategy.
Results of running PUPBLD script
When you run the PUPBLD script to create the product user profile for an Oracle database,
you get one table, one view, two public synonyms, and one private synonym.
The following diagram discusses how these components interrelate and what they are for:
Creating Product User Profile
- product_user_profile arrow, pointing to the table (to the left): The system user gets a private synonym that points to the table, and not the view. This prevents the system user from being constrained by the view and allows him or her to see everything.
- product_user_profile arrow, pointing to the view (to the right): PUPBLD creates a public synonym named product_user_profile that points to the product_profile view. This is what SQL*Plus queries against when a user logs on.
- product_profile arrow: This is also a public synonym and also points to the product_profile view.
- sqlplus_product_profile table: This table contains command and role restrictions for SQL*Plus users. Prior to the release of Oracle8i (i.e., prior to 8.1.5), this table was named product_profile. Database users are not granted access to this table. Only the system user can see it.
- product_profile view: This view is written in such a way as to return only those rows from the sqlplus_product_profile table that apply to the user who queries it. SELECT access on this view is granted to public. This allows a product such as SQL*Plus to query the product user profile for a given user without having to have access to the underlying table.
If you look back at previous releases of Oracle (prior to 8.1.5), you will see that the PUPBLD script has changed slightly over time. Most notably, in the recent past, the table name was product_profile. Now it is sqlplus_product_profile.