Oracle PUP (product_user_profile) configuration and use

Recently in the Oracle Sqlplus Official document, the Sqlplus Security section describes the PUP mechanism. With this, I'll use the following:

PUP (Product_user_profile) Introduction
Product_user_profile is the next table in the system account to provide user-level security restrictions.
The PUP setting is not valid for DBA authority users.
Pups are only valid for local databases.




1. SYSTEM User created Pup:
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 SQLP Lus_product_profile (PRODUCT VARCHAR2 (+) not NULL, USERID VARCHAR2 (+), ATTRIBUTE VARCHAR2 (240) , SCOPE VARCHAR2, Numeric_value DECIMAL (15,2), Char_value VARCHAR2 (+), Date_value DATE, L Ong_value LONG);--Remove sql*plus V3 name for Sqlplus_product_profiledrop TABLE product_profile;--Create the View PR Oduct_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, LON G_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 the HR user
[Email protected]> INSERT INTO product_user_profile values (' Sql*plus ', ' HR ', ' DROP ', null, NULL, ' DISABLED ', NULL, NULL );


1 rows have been created.


[Email protected]> commit;


Submit complete.


2, PUP Table structure overview
[Email protected]> 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: Description of the program to be restricted
--userid: The user to restrict (uppercase)
--attribute: The command or role to restrict
--scope: not applicable; null
--numeric_value: not applicable; null
--char_value:disabled
--date_value: not applicable; null
--long_value: not applicable; null



3, HR Login for drop operation certificate set to take effect:
[Email protected]> Conn HR/HR
is connected.


[Email protected]> CREATE TABLE t (x int);


The table is created.


[Email protected]> drop table t;
sp2-0544: Disable the command "Drop" in the product user profile
[EMAIL protected]> conn System/[email protected]
is connected.


[Email protected]> Delete from product_user_profile;


1 rows have been deleted.


[Email protected]> commit;


Submit complete.


[EMAIL protected]> conn Hr/[email protected]
is connected.


[Email protected]> drop table t;


The table has been deleted.




4. Disable roles
PRODUCT USERID ATTRIBUTE SCOPE numeric_value char_value date_value long_value
------- ------ --------- ----- -------- ------ ----- -----
Sql*plus HR ROLES ROLE1
Sql*plus Public ROLES ROLE2


The Pup row record is translated to the following command during user logon
During login, these table rows is translated into the command
SET ROLE all EXCEPT ROLE1, ROLE2


Example:
[Email protected]> create role r_t;
The role has been created.
[Email protected]> grant Select on T to r_t;
Authorization is successful.
[Email protected]> grant r_t to HR;
Authorization is successful.
[Email protected]> Conn HR/HR
is connected.
[Email protected]> select * from SYS.T;


Row not selected
[Email protected]> Conn System/oracle
is connected.


The session has changed.


[Email protected]> INSERT INTO product_user_profile values (' Sql*plus ', ' HR ', ' ROLES ', null, NULL, ' r_t ', null, NULL);


1 rows have been created.


[Email protected]> commit;


Submit complete.


[Email protected]> Conn HR/HR
is connected.
[Email protected]> select * from SYS.T;
SELECT * FROM SYS.T
*
An error occurred on line 1th:
ORA-00942: Table or view does not exist


[Email protected]> Select username, granted_role
2 from User_role_privs
3 where granted_role= ' r_t ';


USERNAME Granted_role
------------------------------ ------------------------------
HR r_t


[Email protected]> Conn System/oracle
is connected.


The session has changed.


[Email protected]> Delete from product_user_profile;


1 rows have been deleted.
[Email protected]> commit;


Submit complete.
[Email protected]> Conn HR/HR
is connected.


The session has changed.


[Email protected]> select * from SYS.T;


Row not selected






------------------------‘
Dylan presents.