Configuration and use of Oracle pups

Recently in the official document of Oracle Sqlplus, the Sqlplus Security Section introduced the PUP mechanism. To take this, I will use the following:

PUP (Product_user_profile) Introduction

Product_user_profile is the next table in the system account, which provides user-level security restrictions.

The PUP setting is not valid for DBA authority users.

The pup only takes effect for the local database.

1. The SYSTEM user creates pups:

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_va  
      
LUE 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 varcha  
  R2 () not NULL, USERID VARCHAR2 (), ATTRIBUTE VARCHAR2 (+), SCOPE VARCHAR2 (240), Numeric_value DECIMAL (15,2), Char_value VARCHAR2 (), 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 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);

1 lines have been created.

System@orcl> commit;

Submit completed.

2, the 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

This column more highlights: http://www.bianceng.cnhttp://www.bianceng.cn/database/Oracle/

--product: Description of the program to be restricted

--userid: Users to restrict (uppercase)

--attribute: A 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, the HR login to carry out the drop Operation certification settings effective:

System@orcl> Conn Hr/hr

is connected.

Hr@orcl> CREATE table t (x int);

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

is connected.

System@orcl> Delete from Product_user_profile;

1 rows have been deleted.

System@orcl> commit;

Submit completed.

System@orcl> Conn HR/HR@ORCL

is connected.

hr@orcl> drop table t;

The table has been deleted.

4. Disabling roles

PRODUCT USERID ATTRIBUTE SCOPE numeric_value char_value date_value long_value

------- ------ --------- ----- -------- ------ ----- -----

Sql*plus HR ROLES ROLE1

Sql*plus Public ROLES ROLE2

Pup Row Records during user logon will be translated to the following command

During login, these table rows are translated into the command

SET role All EXCEPT ROLE1, ROLE2

Example:

sys@orcl> Create role r_t;

The role has been created.

Sys@orcl> Grant Select on T to r_t;

The authorization was successful.

Sys@orcl> Grant r_t to HR;

The authorization was successful.

Sys@orcl> Conn Hr/hr

is connected.

Hr@orcl> select * from SYS.T;

No rows selected

Hr@orcl> Conn System/oracle

is connected.

The session has changed.

system@orcl> INSERT into product_user_profile values (' Sql*plus ', ' HR ', ' ROLES ', null, NULL, ' r_t ', null, NULL);

1 lines have been created.

System@orcl> commit;

Submit completed.

System@orcl> Conn Hr/hr

is connected.

Hr@orcl> select * from SYS.T;

SELECT * FROM SYS.T

*

Line 1th 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

is connected.

The session has changed.

System@orcl> Delete from Product_user_profile;

1 rows have been deleted.

System@orcl> commit;

Submit completed.

System@orcl> Conn Hr/hr

is connected.

The session has changed.

Hr@orcl> select * from SYS.T;

No rows selected

------------------------‘

Dylan presents.