Why should pupbld.sql be run as SYSTEM (and not as SYS)?

Posted on

Question :

--   Script to install the SQL*Plus PRODUCT_USER_PROFILE tables.  These
--   tables allow SQL*Plus to disable commands per user.  The tables
--   are used only by SQL*Plus and do not affect other client tools
--   that access the database.  Refer to the SQL*Plus manual for table
--   usage information.
--   This script should be run on every database that SQL*Plus connects
--   to, even if the tables are not used to restrict commands.

--   sqlplus system/<system_password> @pupbld
--   **Connect as SYSTEM before running this script**

-- If PRODUCT_USER_PROFILE exists, use its values and drop it

What are the specific reasons to run it as SYSTEM?

Answer :

The script creates certain objects, and it will put them in the current schema. Oracle wishes that these objects be created on SYSTEM schema, hence the requirement.

There are two Oracle schemas, SYS and SYSTEM, because they have two purposes. In short, SYS truly is the “special” user in respect to database internals, while SYSTEM works normally.

The SYS is the owner of the database, the only user with access to X$ tables, the only user which acts as SYSDBA (internally), that bypasses logon triggers, and that, for some obscure reason, cannot obtain read-consistent view of data. As a schema it holds crucial Oracle stuff, mainly the data dictionary.

The SYSTEM is fairly normal schema with DBA privilege (database administrator, not the same as SYSDBA), only that it is built-in and contains some additional (but also quite important) Oracle stuff. You shouldn’t put your own stuff in this schema, if not instructed so by Oracle.

The script assumes that the script is run as the SYSTEM user. For example:


Why even question what a core Oracle provided script instructs the end user to do?

Leave a Reply

Your email address will not be published. Required fields are marked *