0

This is my query :

CREATE PROFILE user_prof
LIMIT password_life_time unlimited;

I am getting ORA-65040: operation not allowed from within a pluggable database.

How to resolve this?

I create a user with name pp

CREATE USER pp IDENTIFIED BY ppop
PROFILE user_prof

When i tried to drop this user using :

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

I am getting :

ERROR at line 1:
ORA-28014: cannot drop administrative users
Nitin Singhal
  • 215
  • 2
  • 11

1 Answers1

1

To create a profile in a container database ( affects all pluggable databases in the container )

SQL> CREATE PROFILE xxxxx LIMIT PASSWORD_REUSE_MAX 10
    PASSWORD_REUSE_TIME 30 CONTAINER=ALL;

If you want to create a profile only for your pluggable database

SQL> CREATE PROFILE xxxxx LIMIT PASSWORD_REUSE_MAX 10
    PASSWORD_REUSE_TIME 30 CONTAINER=CURRENT;

Keep in mind that in order to specify the CONTAINER clause, you must be connected to a multitenant container database (CDB). To specify CONTAINER = ALL, the current container must be the root. To specify CONTAINER = CURRENT, the current container must be a pluggable database (PDB).

Update

To drop the administrative user

SQL> alter session set "_oracle_script"=true;
SQL> drop user pp cascade;

Note

Be careful when dropping users this way. Some of the users might have been indeed created by scripts supplied by Oracle and might be needed for the components running inside that pluggable database.

Roberto Hernandez
  • 8,231
  • 3
  • 14
  • 43
  • But when i am dropping the user with that profile i am getting ```ORA-28014: cannot drop administrative users``` – Nitin Singhal Jul 06 '20 at 10:19
  • which user are you trying to drop ?? – Roberto Hernandez Jul 06 '20 at 10:21
  • better, because the profile has nothing to do with drop an administrative user – Roberto Hernandez Jul 06 '20 at 10:27
  • Thanks, but I am not getting why the user ```pp``` got administrative rights in case of pluggable db? – Nitin Singhal Jul 06 '20 at 10:42
  • The profile belongs to the root container, but you switch to the plugabble database when you run the creating user. I think you should avoid this. If you want specific users for plugabble databases use the CONTAINER clause in the create user statement as well as you should do for the CREATE PROFILE statement. – Roberto Hernandez Jul 06 '20 at 10:53
  • Please, if the answer solves your problem, accept it. – Roberto Hernandez Jul 06 '20 at 10:54
  • Did you mean to say if i use ```container clause``` in both user creation and profile creation. This will solve my problem? – Nitin Singhal Jul 06 '20 at 10:59
  • I think so. The first command should be to define whether you go to the container root (CDB) or you want to go to one of the pluggable databases. However, before creating profiles and users in your pluggable database I should consider whether you need that or not. – Roberto Hernandez Jul 06 '20 at 11:01
  • I need to that user and profile only for pluggable db. – Nitin Singhal Jul 06 '20 at 11:02
  • In that case, you can use the alter session set container. However, over the years I realize than sometimes is better in order to avoid problems to add the clause CONTAINER=xxxx in your profile and create user statements. If you are using 12c, which was the first multitenant version, I would use always the container clause. for 18c or 19c, it should be the same. – Roberto Hernandez Jul 06 '20 at 11:05
  • Thanks for your help. Now, i understood what went wrong previously. – Nitin Singhal Jul 06 '20 at 11:07