Product_profile y User_profile
Rasec
12 de Mayo del 2004
Hola a todos,
Alguien puede indicarme como utilizar las tablas PRODUCT_PROFILE y USER_PROFILE.
Saludos,.
Alguien puede indicarme como utilizar las tablas PRODUCT_PROFILE y USER_PROFILE.
Saludos,.
rgp
12 de Mayo del 2004
En metalink.oracle.com tienes esta nota (2181.1) que te indica como se usa.
Salu2.
Purpose
This article explains how to use product and user level security by disabling and re-enabling commands such as SPOOL or ALTER using the table PRODUCT_USER_PROFILE.
PRODUCT_USER_PROFILE
A table that resides in the SYSTEM account. It provides product level security that supplements the user level security provided by SQL commands GRANT and REVOKE, and can be used with one's own applications as well as with other ORACLE products. The table is created by running the command file PUPBLD.SQL under the schema SYSTEM.
points to remember:
1) DBAs can disable certain SQL and SQL*Plus commands on a per user basis.
2) Restrictions are applied from PRODUCT_USER_PROFILE when a user logs onto SQL*Plus and maintains those restrictions for the session. Changes made to PRODUCT_USER_PROFILE will take effect the next time the user logs onto SQL*Plus.
3) If one restricts a user's privileges through Oracle's GRANT and REVOKE commands, but do not restrict those privileges in PRODUCT_USER_PROFILE, the restrictions still apply.
4) If one denies a user access to commands through PRODUCT_USER_PROFILE, but do not place any restrictions with GRANT or REVOKE, access is still denied.
Usage:
To disable a SQL or SQL*Plus commands for a given user, insert a row in the PRODUCT_USER_PROFILE tables. User names can be appended to with the wildcard '%'. For example 'SC%', which will then disable the relevant command for all users whose name starts with 'SC'.
Note: The userid, attribute and char_value must be in uppercase.
For Example:
INSERT into product_user_profile (product,userid,attribute,char_value,date_value)
values('SQL*Plus','MBUI','SPOOL','DISABLED',NULL);
NUMERIC CHAR DATE
PRODUCT USERID ATTRIBUTE SCOPE VALUE VALUE VALUE
---------- -------- ---------- ---------- ------- ---------- ---------
SQL*PLUS SCOTT HOST DISABLED
SQL*PLUS MBUI SPOOL DISABLED
If for example user MBUI attempts a SPOOL, she would receive this error:
SP2-0544: invalid command: spool
To re-enable commands, delete the row containing the restriction
*** NOTE: For a table structure, description and use of columns, check the current SQL*Plus Reference Manual, appendix E.
--------------------------------------------------------------------------------
The following are SQL and SQL*Plus commands that one can disable through the PRODUCT_USER_PROFILE. For a full list please check your SQL*Plus User's Guide.
SQL*Plus:
CONNECT
EDIT
EXIT
GET
HOST (or your operating system's alias for HOST)
QUIT
RUN
SAVE
SPOOL
START
SQL:
ALTER
AUDIT
CREATE
DELETE
DROP
GRANT
INSERT
LOCK
NOAUDIT
RENAME
REVOKE
SELECT
UPDATE
VALIDATE
PL/SQL:
BEGIN
DECLARE
Salu2.
Purpose
This article explains how to use product and user level security by disabling and re-enabling commands such as SPOOL or ALTER using the table PRODUCT_USER_PROFILE.
PRODUCT_USER_PROFILE
A table that resides in the SYSTEM account. It provides product level security that supplements the user level security provided by SQL commands GRANT and REVOKE, and can be used with one's own applications as well as with other ORACLE products. The table is created by running the command file PUPBLD.SQL under the schema SYSTEM.
points to remember:
1) DBAs can disable certain SQL and SQL*Plus commands on a per user basis.
2) Restrictions are applied from PRODUCT_USER_PROFILE when a user logs onto SQL*Plus and maintains those restrictions for the session. Changes made to PRODUCT_USER_PROFILE will take effect the next time the user logs onto SQL*Plus.
3) If one restricts a user's privileges through Oracle's GRANT and REVOKE commands, but do not restrict those privileges in PRODUCT_USER_PROFILE, the restrictions still apply.
4) If one denies a user access to commands through PRODUCT_USER_PROFILE, but do not place any restrictions with GRANT or REVOKE, access is still denied.
Usage:
To disable a SQL or SQL*Plus commands for a given user, insert a row in the PRODUCT_USER_PROFILE tables. User names can be appended to with the wildcard '%'. For example 'SC%', which will then disable the relevant command for all users whose name starts with 'SC'.
Note: The userid, attribute and char_value must be in uppercase.
For Example:
INSERT into product_user_profile (product,userid,attribute,char_value,date_value)
values('SQL*Plus','MBUI','SPOOL','DISABLED',NULL);
NUMERIC CHAR DATE
PRODUCT USERID ATTRIBUTE SCOPE VALUE VALUE VALUE
---------- -------- ---------- ---------- ------- ---------- ---------
SQL*PLUS SCOTT HOST DISABLED
SQL*PLUS MBUI SPOOL DISABLED
If for example user MBUI attempts a SPOOL, she would receive this error:
SP2-0544: invalid command: spool
To re-enable commands, delete the row containing the restriction
*** NOTE: For a table structure, description and use of columns, check the current SQL*Plus Reference Manual, appendix E.
--------------------------------------------------------------------------------
The following are SQL and SQL*Plus commands that one can disable through the PRODUCT_USER_PROFILE. For a full list please check your SQL*Plus User's Guide.
SQL*Plus:
CONNECT
EDIT
EXIT
GET
HOST (or your operating system's alias for HOST)
QUIT
RUN
SAVE
SPOOL
START
SQL:
ALTER
AUDIT
CREATE
DELETE
DROP
GRANT
INSERT
LOCK
NOAUDIT
RENAME
REVOKE
SELECT
UPDATE
VALIDATE
PL/SQL:
BEGIN
DECLARE