OraclePUP (PRODUCT_USER_PROFILE) configuration and use

I recently reviewed the OracleSQLPLUS official document and introduced the PUP mechanism in the SQLPLUSSecurity chapter. Here, I will use the following: PUP (PRODUCT_USER_PROFILE)

I recently reviewed the official Oracle SQLPLUS documentation and introduced the PUP mechanism in the SQLPLUS Security chapter. Here, I will use the following: PUP (PRODUCT_USER_PROFILE)

I recently reviewed the official Oracle SQLPLUS documentation and introduced the PUP mechanism in the SQLPLUS Security chapter. Here, I will use the following:

Introduction to PUP (PRODUCT_USER_PROFILE)
PRODUCT_USER_PROFILE is the next table of the system account, which provides user-level security restrictions.
The PUP setting is invalid for DBA permission users.
PUP only applies to Local databases ).

1. Create a PUP as a SYSTEM user:
SQLPLUS SYSTEM

@ D: \ app \ Administrator \ product \ 11.2.0 \ dbhome_1 \ sqlplus \ admin \ pupbld. SQL

Script content:

Drop synonym PRODUCT_USER_PROFILE;

Create table SQLPLUS_PRODUCT_PROFILE
Select product, USERID, ATTRIBUTE, SCOPE, NUMERIC_VALUE, CHAR_VALUE,
DATE_VALUE FROM PRODUCT_USER_PROFILE;

Drop table PRODUCT_USER_PROFILE;
Alter table SQLPLUS_PRODUCT_PROFILE ADD (LONG_VALUE LONG );

-- Create SQLPLUS_PRODUCT_PROFILE from scratch

Create table SQLPLUS_PRODUCT_PROFILE
(
PRODUCT VARCHAR2 (30) not null,
USERID VARCHAR2 (30 ),
ATTRIBUTE VARCHAR2 (240 ),
SCOPE VARCHAR2 (240 ),
NUMERIC_VALUE DECIMAL (15, 2 ),
CHAR_VALUE VARCHAR2 (240 ),
DATE_VALUE DATE,
LONG_VALUE LONG
);

-- Remove SQL * Plus V3 name for sqlplus_product_profile

Drop table PRODUCT_PROFILE;

-- Create the view PRODUCT_PRIVS and grant access to that

Drop view PRODUCT_PRIVS;
Create view PRODUCT_PRIVS
Select product, USERID, ATTRIBUTE, SCOPE,
NUMERIC_VALUE, CHAR_VALUE, DATE_VALUE, LONG_VALUE
FROM SQLPLUS_PRODUCT_PROFILE
Where userid = 'public' or user like userid;

Grant select on PRODUCT_PRIVS to public;
Drop public synonym PRODUCT_PROFILE;
Create public synonym PRODUCT_PROFILE for system. PRODUCT_PRIVS;
Drop synonym PRODUCT_USER_PROFILE;
Create synonym PRODUCT_USER_PROFILE for system. SQLPLUS_PRODUCT_PROFILE;
Drop public synonym PRODUCT_USER_PROFILE;
Create public synonym PRODUCT_USER_PROFILE for system. PRODUCT_PRIVS;

-- Disable the DROP command for HR users
SYSTEM @ orcl> insert into product_user_profile values ('SQL * Plus', 'hr ', 'drop', NULL, NULL, 'Disabled', NULL, NULL );

One row has been created.

SYSTEM @ orcl> commit;

Submitted.

2. PUP table structure Overview
SYSTEM @ orcl> desc product_user_profile
Is the name empty? Type
---------------------------------------------------------------------------
Product not null VARCHAR2 (30)
USERID VARCHAR2 (30)
ATTRIBUTE VARCHAR2 (240)
SCOPE VARCHAR2 (240)
NUMERIC_VALUE NUMBER (15, 2)
CHAR_VALUE VARCHAR2 (240)
DATE_VALUE DATE
LONG_VALUE LONG

-- PRODUCT: Specifies the program to be restricted
-- USERID: the user to be restricted (uppercase)
-- ATTRIBUTE: Command or role to be restricted
-- SCOPE: not applicable; put NULL
-- NUMERIC_VALUE: not applicable; put NULL
-- CHAR_VALUE: DISABLED
-- DATE_VALUE: not applicable; put NULL
-- LONG_VALUE: not applicable; put NULL

3. log on to HR and perform the DROP operation to verify that the settings take effect:
SYSTEM @ orcl> conn hr/hr
Connected.

HR @ orcl> create table t (x int );

The table has been created.

HR @ orcl> drop table t;
SP2-0544: Disable the command "drop" in the product user profile"
HR @ orcl> conn system/oracle @ orcl
Connected.

SYSTEM @ orcl> delete from product_user_profile;

One row has been deleted.

SYSTEM @ orcl> commit;

Submitted.

SYSTEM @ orcl> conn hr/hr @ orcl
Connected.

HR @ orcl> drop table t;

The table has been deleted.