How to restrict the commands that the Sqlplus tool can perform

In addition to providing various permissions at the database level to control security, Oracle provides a special control method for the Sqlplus tool.

When a general database is created, DBCA automatically loads the $oracle_home/sqlplus/admin/pupbld.sql script under the system user to create Sqlplus_product_profile tables, if the table is not created, The alarm will appear when the normal user logs in. This table can limit all ordinary users except the SYS, system, and sysdba/sysoper identity landings. This table allows you to limit most of the sqlplus commands, SQL commands, and Pl/sql commands.

A simple example:

Sql> CONN SYSTEM

Please enter password:

is connected.

Sql> DESC Sqlplus_product_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

Sql> INSERT into Sqlplus_product_profile

2 VALUES (' Sql*plus ', ' U1 ', ' DROP ', null, NULL, ' DISABLED ', null, NULL);

1 lines have been created.

Sql> COMMIT;

Submit completed.

Sql> CONN U1/U1

is connected.

Sql> SELECT * from TAB;

Tname Tabtype Clusterid

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

T TABLE

sql> DROP TABLE T;

sp2-0544: Invalid command: Drop

Sql> SELECT * from V$version;

BANNER

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

Oracle9i Enterprise Edition release9.2.0.4.0-64bitproduction

Pl/sql Release 9.2.0.4.0-production

CORE 9.2.0.3.0 Production

TNS for Linux:version 9.2.0.4.0-production

Nlsrtl Version 9.2.0.4.0-production

This article URL address: http://www.bianceng.cn/database/Oracle/201410/45454.htm

Because the Sqlplus_product_profile table is only specific to the Sqlplus tool, you need to enter ' Sql*plus ' for the PRODUCT column. The UserID column enters the user name you want to restrict, and if you enter a wildcard '% ', it takes effect for all users. Attribute enter the limit command, where the drop command is restricted. Char_value must enter ' DISABLED ', while the remaining columns are not currently in use, enter NULL.

As you can see, the error message is very ambiguous in 9i, and if the user encounters this error, it is not clear what causes the command to fail.

In 10g, the error message is much clearer:

Sql> Conn/as SYSDBA

Connected.

sql> INSERT INTO System.sqlplus_product_profile

2 values (' sql*plus ', '% ', ' SELECT ', null, NULL, ' DISABLED ', null, NULL);

1 row created.

Sql> commit;