Use Product_User_Profile to implement user permissions
Author: kamusmail: kamus@itpub.netdate: 2004-1
When we log in to SQL * Plus with ordinary users, we will encounter the following error tips: Error Accessing Product_User_ProfileWarning: Product User Profile Information Not loaded! You May Need to run puPBLD.SQL AS SYSTEM
In fact, this is just a warning, not a real mistake, encountered this prompt, does not affect our normal use of SQL * Plus, nor does it affect the database function.
If the database is created using DBCA, don't worry about this problem, usually we manually create a database, forgetting to implement some scripts, causing such a warning. Product_user_profile actually has a very powerful feature, this is a table in system mode, and the data exists in this table checks if the client program is logged in. What is the limit on the execution of the command. Basically we use it to limit SQL * Plus client programs (it seems to be only this program will only check this table: D)
If you don't want to see this warning, make the following: Log in to SQL * Plus with the System user, then execute PuPBLD.SQL, this file is usually in the $ Oracle_home / SQLPlus / Admin directory. SQL> @ $ oracle_home / sqlplus / admin / pupbld.sql
After the execution is completed, you can verify that the change form has been created and is familiar with the structure of the table below.
The above is not the focus of this small article. Here we need to use this table to limit the Scott user can't execute the DROP command, even if the Scott user has DROP TABLE. SQL> INSERT INTO PRODUCT_USER_PROFILE (2 Product, Userid, Attribute, Char_Value) 3 VALUES (4 'SQL * Plus', 'Scot%', 'Drop', 'Disabled');
1 row inserted
Executed in 0.01 seconds
SQL> commit;
Commit completion
Executed in 0 seconds
Then quit the System user, log in with Scott users, make a test: SQL> CREATE TABLE T_TEST_PROFILE (2 ID Number);
Table created.
Elapsed: 00: 00: 00100.10
SQL> Drop Table T_test_profle; SP2-0544: Invalid Command: Drop
This is the role of Product_user_profile, review: 1. If we change the DROP to create or Update, then these commands are prohibited. 2. From the INSERT statement from above, we can see that the user's restriction supports wildcard, all users who started with SCOT cannot perform the specified command. 3. Modified Product_User_profile content, the user must log in to SQL * Plus, and the modification will take effect, that is, SQL * Plus checks the contents of the product_user_profile table when logging in.
