How to restrict the commands that the Sqlplus tool can perform

Source: Internet
Author: User
Tags commit sqlplus

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;

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.