0

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.

Nitin Singhal
  • 215
  • 2
  • 11

1 Answers1

1

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.

Roberto Hernandez
  • 8,231
  • 3
  • 14
  • 43
  • Can you share the doc link where oracle states "Users are considered..." – Nitin Singhal Aug 05 '20 at 06:49
  • answer updated. Keep in mind that underscore parameters are treated by support documents or notes, not in the documentation, as Oracle strongly recommend not to use them unless it is indicated by them. – Roberto Hernandez Aug 05 '20 at 06:51
  • Can you share the document or website where it states that user will become Admin if we use ```_oracle_script" is set to TRUE.``` – Nitin Singhal Aug 05 '20 at 06:56