User Profiles  «Prev 

Creating the PRODUCT_PROFILE Table in SQL*Plus

To create the PRODUCT_PROFILE table in SQL*Plus, you can use the CREATE TABLE statement along with the appropriate column definitions. The following example provides a script to create the PRODUCT_PROFILE table with the necessary structure for managing product profiles in your Oracle database environment. 

CREATE TABLE PRODUCT_PROFILE (
  PRODUCT      VARCHAR2(30) NOT NULL,
  USERID       VARCHAR2(30) NOT NULL,
  ATTRIBUTE    VARCHAR2(240) NOT NULL,
  SCOPE        VARCHAR2(240),
  NUMERIC_VALUE NUMBER,
  CHAR_VALUE    VARCHAR2(240),
  CONSTRAINT PK_PRODUCT_PROFILE PRIMARY KEY (PRODUCT, USERID, ATTRIBUTE)
);

This script creates the PRODUCT_PROFILE table with the following columns:
  1. PRODUCT: A VARCHAR2(30) column to store the name of the Oracle tool (e.g., 'SQL*Plus').
  2. USERID: A VARCHAR2(30) column to store the user or role for which the product profile setting is applied.
  3. ATTRIBUTE: A VARCHAR2(240) column to store the attribute that defines the restriction or setting (e.g., 'ACCESS').
  4. SCOPE: A VARCHAR2(240) column to store the specific scope of the attribute, if applicable.
  5. NUMERIC_VALUE: A NUMBER column to store a numeric value for the attribute, if applicable.
  6. CHAR_VALUE: A VARCHAR2(240) column to store a character value for the attribute, if applicable.

Additionally, the script creates a primary key constraint named PK_PRODUCT_PROFILE on the PRODUCT, USERID, and ATTRIBUTE columns. To create the PRODUCT_PROFILE table in SQL*Plus, follow these steps:
  1. Launch SQL*Plus and connect to your Oracle database as a user with the necessary privileges to create tables (e.g., a user with the DBA role).
  2. Copy the CREATE TABLE script provided above and paste it into the SQL*Plus command prompt.
  3. Press Enter to execute the script. The PRODUCT_PROFILE table will be created in your database.

After creating the PRODUCT_PROFILE table, you can start managing product profile restrictions for SQL*Plus and other Oracle tools by inserting, updating, or deleting records in the table, as described in the previous answers.
You must set up the PRODUCT_PROFILE table to allow product profiles to be implemented. Your Oracle database comes with a SQL script called PUPBLD.SQL that will create the PRODUCT_PROFILE table and the appropriate synonyms and views that go along with it. You have to be logged on as user SYSTEM when you run this script from SQL*Plus.

Profiles

Database resources are not unlimited; therefore, a DBA must manage and allocate resources among all database users. Some examples of database resources are CPU time, concurrent sessions, logical reads, and connect time.
A database profile is a named set of resource limits that you can assigned to a user. After Oracle is installed, the DEFAULT profile exists and is assigned to any user not explicitly assigned a profile. The DBA can add new profiles or change the DEFAULT profile to suit the needs of the enterprise. The initial values for the DEFAULT profile allow for unlimited use of all database resources.