6 Replies Latest reply on Jul 23, 2013 5:52 AM by Anju Garg

    12c : privileges required to create a pdb

    Anju Garg

      I am trying to craete a PDB as a non-sys user

       

      CDB1>sho con_name

       

      CON_NAME

      ------------------------------

      CDB$ROOT

      CDB1>select con_id, name, open_mode from v$pdbs;

       

       

          CON_ID NAME                                               OPEN_MODE

       

      ---------- -------------------------------------------------- ----------

       

               2 PDB$SEED                                           READ ONLY

       

               4 PDB3_COPY                                          READ ONLY

       

       

      CDB1>create user c##sys identified by oracle;

       

      User created.

       

      CDB1>grant connect, resource to c##sys;

       

      Grant succeeded.

       

      CDB1>grant create pluggable database to c##sys;

       

      CDB1>grant dba to c##sys;



      CDB1>conn c##sys/oracle@cdb1

      Connected.

      CDB1>sho user

      USER is "C##SYS"

      CDB1>create pluggable database pdb1 from pdb3_copy;

      create pluggable database pdb1 from pdb3_copy

      *

      ERROR at line 1:

      ORA-01031: insufficient privileges

       

      What all minimum privileges are required to create a PDB?

       

      Regards

        • 1. Re: 12c : privileges required to create a pdb
          rp0428

          user12288492 wrote:

           

          I am trying to craete a PDB as a non-sys user

           

          CDB1>sho con_name

           

          CON_NAME

          ------------------------------

          CDB$ROOT

          CDB1>select con_id, name, open_mode from v$pdbs;

           

           

              CON_ID NAME                                               OPEN_MODE

           

          ---------- -------------------------------------------------- ----------

           

                   2 PDB$SEED                                           READ ONLY

           

                   4 PDB3_COPY                                          READ ONLY

           

           

          CDB1>create user c##sys identified by oracle;

           

          User created.

           

          CDB1>grant connect, resource to c##sys;

           

          Grant succeeded.

           

          CDB1>grant create pluggable database to c##sys;

           

          CDB1>grant dba to c##sys;


           

          CDB1>conn c##sys/oracle@cdb1

          Connected.

          CDB1>sho user

          USER is "C##SYS"

          CDB1>create pluggable database pdb1 from pdb3_copy;

          create pluggable database pdb1 from pdb3_copy

          *

          ERROR at line 1:

          ORA-01031: insufficient privileges

           

          What all minimum privileges are required to create a PDB?

           

          Regards

          You can connect as SYSDBA to make it work but based on what you posted you haven't granted SYSDBA to user C##SYS.

          If you run your query now as user c##sys:

          >

          select con_id, name, open_mode from v$pdbs;

          >

          you should find that it returns no rows. That is also why your CREATE statement fails. Grant SYSDBA to the user and then run that query and you should now see the same PDBs you saw when you connected as SYS.

           

          Also, be careful when you make grants. By default the grant ONLY applies to the current container.

           

          So your 'grant connect to c##sys' only allows that user to connect to the ROOT. The 12C GRANT statement uses a CONTAINER clause whose default is CURRENT. If you want c##sys to be be able to connect to the new PDB either use CONTAINER=ALL or switch to the new PDB and, as SYS, do the grant again for that specific PDB.

           

          You don't need to grant the DBA role to make this work by the way.

           

          May as well mention also that if you use a tool like Toad that isn't 12c aware you are going to see a mess when you look at things like grants since there may appear to be multiple grants of exactly the same type. For instance your CONNECT grant could appear 5 times if the user has the grant for 5 different containers. An older tool won't show you which container the grants are for since they won't be 'container aware'.

           

          See CREATE PLUGGABLE DATABASE in the SQL Reference doc

          http://docs.oracle.com/cd/E16655_01/server.121/e17209/statements_6009.htm

           

          That doc lists the prerequisites

          >

          Prerequisites

          You must be connected to a CDB and the current container must be the root.

          You must have the CREATE PLUGGABLE DATABASE system privilege.

          The CDB in which the PDB is being created must be in READ WRITE mode.

          To specify the create_pdb_clone clause:

          •   If src_pdb_name refers to a PDB in the same CDB, then you must have the CREATE PLUGGABLE DATABASE system privilege in the root of the CDB in which the new PDB will be created and in the PDB being cloned.
          •   If src_pdb_name refers to a PDB in a remote database, then you must have the CREATE PLUGGABLE DATABASE system privilege in the root of the CDB in which the new PDB will be created and the remote user must have the CREATE PLUGGABLE DATABASE system privilege in the PDB to which src_pdb_name refers.

          >

          Did you notice that part about having CREATE PLUGGABLE DATABASE in 'the PDB being cloned'? You haven't done that.

           

          The doc also has an example of cloning:

          >

          Cloning a PDB From an Existing PDB: Example The following statement creates a PDB newpdb by cloning PDB salespdb. PDBs salespdb and newpdb are in the same CDB. Because no storage limits are explicitly specified, there is no limit on the amount of storage for newpdb. The files are copied from /disk1/oracle/salespdb/ to /disk2/oracle/newpdb/. The location of all directory object paths and paths contained in certain parameters associated with newpdb are restricted to the directory /disk2/oracle/newpdb/.

          CREATE PLUGGABLE DATABASE newpdb FROM salespdb  FILE_NAME_CONVERT = ('/disk1/oracle/dbs/salespdb/', '/disk1/oracle/dbs/newpdb/')  PATH_PREFIX = '/disk1/oracle/dbs/newpdb';  

          >

          Note in particular the use of the FILE_NAME_CONVERT clause to tell Oracle how to name and where to put the new files.

           

          The common reasons for your use case

          1. the CDB is not open in READ WRITE

          2. the CREATE PLUGGABLE DATABASE role was not created in the PDB being cloned or that role is not a DEFAULT role for your user.

          3. the PDB you are cloning has not been opened READ ONLY

          4. the user is not connected as SYSDBA (only needed if the grants are not done individually and manually)

           

          NOTES: you opened the source DB in READ ONLY mode BEFORE you created the common user c##sys and granted the privileges. That prevented Oracle from creating that user in the source DB. When you create a common user Oracle will also create that user automatically in any PDB that is already opened READ WRITE.

           

          The same goes for the grants when you use CONTAINER = ALL; that only creates the grants if the PDB is writeable. You need to be VERY CAREFUL when you create common users and issue grants to them to make sure you know which containers (PDBs) are open or you may have similar issues in the future. The only way you can correct those problems later is to connect to reissue the grants from the ROOT after the previously closed PDBs are open or to connect to the PDB and manually create the user and grants.

           

          Also your c##sys user won't be able to drop that PDB because you didn't grant that privilege. And if you do drop the PDB and use INCLUDING DATAFILES the directory itself won't be deleted.

           

          You can get a mess on your hands real quick if you don't create an architecture document ahead of time that identifies what the structure should be for the different containers.

           

          And just a reminder - we can't tell you HOW things are supposed to work in 12c. At best we can try to tell you how they DO work. So I have NO IDEA if Oracle intended PDB creation to require being connected as SYSDBA rather than just having the DBA role but that is how it works for me if you don't grant CREATE PLUGGABLE DATABASE in the PDB being cloned.

          • 2. Re: 12c : privileges required to create a pdb
            Anju Garg

            Sir,

               Thanks for your detailed reply.

            As per your suggestion, I

            - opened the source PDB in read write mode

            - REcreated he user C##sys

            - Granted connect,  resource, create pluggable database privileges to C##sys with clause container=all

            - verified that the user c##sys is listed within PDB also

            - Opened source PDB PDB3_copy in read only mode

            - Connected to CDB1 as c##sys

            - Tried to create pluggable database from pdb3_copy

            - Failed with error

            ORA-19502: write error on file

            "/u01/app/oracle/oradata/CDB1/E21FEB10D466049CE0438602A8C08192/datafile/o1_mf_sy

            stem_8yv2yqn1_.dbf", block number  (block size=)

             

            -- The alert  log  showed

             

            create pluggable database pdb3 from pdb3_copy

            ORA-604 signalled during: create pluggable database pdb3 from pdb3_copy...

             

            -- Here are the commands I issued :

            CDB1>alter pluggable database pdb3_copy close;

             

            Pluggable database altered.

             

            CDB1>alter pluggable database pdb3_copy open ;

             

            Pluggable database altered.

             

            CDB1>select con_id, name, open_mode from v$pdbs;

             

                CON_ID NAME                                               OPEN_MODE

            ---------- -------------------------------------------------- ----------

                     2 PDB$SEED                                           READ ONLY

                     4 PDB3_COPY                                          READ WRITE

             

            CDB1>sho user

            USER is "SYS"

            CDB1>create user c##sys identified by oracle;

             

            User created.

             

            CDB1>grant connect, resource, create pluggable database to c##sys container=all;

             

            Grant succeeded.

             

            CDB1>conn / as sysdba
            Connected.
            CDB1>alter pluggable database pdb3_copy close;

             

            Pluggable database altered.

             

            CDB1>alter pluggable database pdb3_copy open read only;

             

            Pluggable database altered.

             

            CDB1>select name, open_mode from v$pdbs;

             

            NAME                                               OPEN_MODE
            -------------------------------------------------- ----------
            PDB$SEED                                           READ ONLY
            PDB3_COPY                                          READ ONLY

             

            CDB1>conn c##sys/oracle@cdb1
            Connected.
            CDB1>sho con_name

             

            CON_NAME
            ------------------------------
            CDB$ROOT
            CDB1>sho user
            USER is "C##SYS"
            CDB1>create pluggable database pdb3 from pdb3_copy;
            create pluggable database pdb3 from pdb3_copy
            *
            ERROR at line 1:
            ORA-00604: error occurred at recursive SQL level
            ORA-19502: write error on file
            "/u01/app/oracle/oradata/CDB1/E21FEB10D466049CE0438602A8C08192/datafile/o1_mf_sy
            stem_8yv2yqn1_.dbf", block number  (block size=)

             

            -- alert log --


            create pluggable database pdb3 from pdb3_copy
            ORA-604 signalled during: create pluggable database pdb3 from pdb3_copy...

            • 3. Re: 12c : privileges required to create a pdb
              rp0428

              You likely don't have permissions to create or write to that file location.

               

              Perhaps you missed this part of my reply above?

              >

              Note in particular the use of the FILE_NAME_CONVERT clause to tell Oracle how to name and where to put the new files.

              >

              See the DOC example I posted above. Better yet see the doc at the link I provided

              >

              CREATE PLUGGABLE DATABASE newpdb FROM salespdb  FILE_NAME_CONVERT = ('/disk1/oracle/dbs/salespdb/', '/disk1/oracle/dbs/newpdb/')  PATH_PREFIX = '/disk1/oracle/dbs/newpdb';

              >

              • 4. Re: 12c : privileges required to create a pdb
                Aman....

                Anju,

                 

                It seems that you are trying to creating a PDB by cloning from an existing one. It's very very important to read docs carefully if you are playing with 12c. For the task that you are doing , read the below link,

                Creating and Removing PDBs with SQL*Plus

                 

                2nd thing, why you made a user with the name as c#sys as a common user? Not that it's technically wrong but it's a really bad example to show since you are making a common user mentioned as C#SYS but it would be confusing to use such name as the user SYS is already going to be a common user for both CDB and PDB.

                 

                Aman....

                1 person found this helpful
                • 5. Re: 12c : privileges required to create a pdb
                  Anju Garg

                  Sir,

                   

                      Thanks for your guidance. I have been able to clone an existing PDB as non-sys user. I have documented the procedure in my blog post at the following link:

                  http://oracleinaction.com/clone-pdbas-non-sys-user/

                   

                  Thanks and Regards

                  • 6. Re: 12c : privileges required to create a pdb
                    Anju Garg

                    Hi Aman

                       Thanks for your guidance. This time I have created userC##sys. In future I will take care to use some other name.

                     

                    Regards

                    Anju