Configure and use Oracle 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 AS  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 scratchCREATE 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_profileDROP TABLE PRODUCT_PROFILE;-- Create the view PRODUCT_PRIVS and grant access to thatDROP VIEW PRODUCT_PRIVS;CREATE VIEW PRODUCT_PRIVS AS  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.




4. disable a role
Product userid attribute scope NUMERIC_VALUE CHAR_VALUE DATE_VALUE LONG_VALUE
---------------------------------------------------
SQL * Plus HR ROLES ROLE1
SQL * Plus PUBLIC ROLES ROLE2


During User Login, PUP line records will be translated into the following commands
During login, these table rows are translated into the command
Set role all rule t ROLE1, ROLE2


Example:
SYS @ orcl> create role r_t;
The role has been created.
SYS @ orcl> grant select on t to r_t;
Authorization successful.
SYS @ orcl> grant r_t to hr;
Authorization successful.
SYS @ orcl> conn hr/hr
Connected.
HR @ orcl> select * from sys. t;


Unselected row
HR @ orcl> conn system/oracle
Connected.


The session has been changed.


SYSTEM @ orcl> insert into product_user_profile values ('SQL * Plus', 'hr ', 'roles', NULL, NULL, 'r _ t', NULL, NULL );


One row has been created.


SYSTEM @ orcl> commit;


Submitted.


SYSTEM @ orcl> conn hr/hr
Connected.
HR @ orcl> select * from sys. t;
Select * from sys. t
*
Row 3 has an error:
ORA-00942: Table or view does not exist


HR @ orcl> select username, granted_role
2 from user_role_privs
3 where granted_role = 'r _ T ';


USERNAME GRANTED_ROLE
------------------------------------------------------------
HR R_T


HR @ orcl> conn system/oracle
Connected.


The session has been changed.


SYSTEM @ orcl> delete from product_user_profile;


One row has been deleted.
SYSTEM @ orcl> commit;


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


The session has been changed.


HR @ orcl> select * from sys. t;


Unselected row






------------------------'
Dylan Presents.