PRODUCT_USER_PROFILE Table

最新推荐文章于 2024-09-13 21:40:38 发布

cneo2012

最新推荐文章于 2024-09-13 21:40:38 发布

阅读量145

点赞数

文章标签：数据库

SQL*Plus uses the PRODUCT_USER_PROFILE (PUP) table, a table in the SYSTEM
account, to provide product-level security that supplements the user-level
security provided by the SQL GRANT and REVOKE commands and user roles.

DBAs can use the PUP table to disable certain SQL and SQL*Plus commands in the
SQL*Plus environment on a per-user basis. SQL*Plus—not Oracle Database—
enforces this security. DBAs can even restrict access to the GRANT, REVOKE, and
SET ROLE commands to control users' ability to change their database privileges.

SQL*Plus reads restrictions from the PUP table when a user logs in to SQL*Plus
and maintains those restrictions for the duration of the session. Changes to the
PUP table will only take effect the next time the affected users log in to
SQL*Plus.

When SYSTEM, SYS, or a user authenticating with SYSDBA or SYSOPER privileges
connects or logs in, SQL*Plus does not read the PUP table. Therefore, no
restrictions apply to these users.

The PUP table applies only to the local database. If accessing objects on a
remote database through a database link, the PUP table for the remote database
does not apply. The remote database cannot extract the username and password
from the database link in order to determine that user's profile and privileges.

Example : Setting Restrictions in the PUP Table
This is an example of how to insert a row into the PUP table to restrict the
user HR from using the SELECT statement:
1.
Log in as SYSTEM with the command
SQLPLUS SYSTEM
2.
Insert a row into the PUP table with the command:
INSERT INTO PRODUCT_USER_PROFILE
VALUES ('SQL*Plus', 'HR', 'SELECT', NULL, NULL, 'DISABLED', NULL, NULL);
3.
Connect as HR and try to SELECT something:
CONNECT HR
SELECT * FROM EMP_DETAILS_VIEW;
This command causes the following error message:
SP2-0544: Command SELECT disabled in Product User Profile
4.
To delete this row and remove the restriction from the user HR, CONNECT
again as SYSTEM and enter:
DELETE FROM PRODUCT_USER_PROFILE WHERE USERID = 'HR';