oracle-databaseadministratorpluggable

Why pluggable db user became administrator?


I have following commands in a script :

ALTER SESSION SET CONTAINER = orclpdb
ALTER session set "_ORACLE_SCRIPT"=true;
CREATE PROFILE test_profile LIMIT password_life_time unlimited;

CREATE USER test IDENTIFIED BY test123
PROFILE test_profile

while dropping user using:

ALTER SESSION SET CONTAINER = orclpdb;
DROP USER test cascade;

I am getting :

ora-28014 cannot drop administrative users

My first concern is how this test user is getting administrative privilege. Secondly is there anyway better way to do this.


Solution

  • The reason why the user is becoming ADMIN is due that the fact that you are using the underscore parameter _oracle_script=true

    ORA-28014: Cannot Drop Administrative Users (Doc ID 1566042.1)

    https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=267287602351459&id=1566042.1&_afrWindowMode=0&_adf.ctrl-state=w6ehg2ftt_53

    As Oracle states in that document:

    Users are considered administrative users when are created using the script catcon.pl, or in that session the parameter "_oracle_script" is set to TRUE.

    You should not use it when you are creating users or anything else for that matter, unless you need to drop an administrative user, which in that case you have to.

    To avoid this, when creating users avoid the use of the _oracle_script parameter altogether.