ALTER SESSION SET CONTAINER can only be executed as sysdba ? - Multitenant

    Hi I createded a common user c##oracle ( identified externally.Igranted it dba,cdb_dba,set container  - but still I cannot set the container to my pluggable database using that user : sqlplus /SQL*Plus: Release 12.1.0.1.0 Production on Fri Jul 19 15:03:21 2013Copyright (c) 1982, 2013, Oracle.  All rights reserved.Last Successful login time: Fri Jul 19 2013 14:58:54 +00:00Connected to:Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit ProductionWith the Partitioning, OLAP, Advanced Analytics and Real Application Testing optionsSQL> ALTER SESSION SET CONTAINER = CDB$ROOT;Session altered.SQL> alter session set container=edwt;ERROR:ORA-01031: insufficient privileges only a sysdba it works : sqlplus "/as sysdba"SQL*Plus: Release 12.1.0.1.0 Production on Fri Jul 19 15:11:03 2013Copyright (c) 1982, 2013, Oracle.  All rights reserved.Connected to:Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit ProductionWith the Partitioning, OLAP, Advanced Analytics and Real Application Testing optionsSQL> alter session set container=edwt;Session altered.SQL>  What privilege do I need to give my common user so I can set hte container to a pluggable database ?

I want to add that even with a regular common user ( not identified externally ) I get the same error

Found the problem. I needed to grant the dba role as 'common ' grant dba to c##oracle container=all; then the alter session works from c##oracle

Hi Orna, Yes, you've found that you need to have the correct privilege in every container you wish to start a session in. In Oracle Database 12c we introduce a new privilege called "set container". This is probably part of the dba role, which is why you found that by granting dba to your common user you succeeded. I hope this helps. For more information please consult the Multitenant White Paper, available here. http://www.oracle.com/technetwork/database/multitenant-wp-12c-1949736.pdf Regards, Patrick

Actually , my user always had the DBA role.the confusing thing was that the way I granted the role : grant dba to c##oracle was NOT correct. Only when I granted it that way : grant dba to c##oracle container=all; the alter session was successfull.

You didn't say exactly what you wanted to achieve. To show you what I mean, suppose that I wanted to create a brand new common user that could connect ONLY to pdb1 and, from there, set container ONLY to pdb2. Assmuing that I already created a suitably privilaged admin user  c##Admin (see below on "with admin option"), then here's how I do it. I'm using Easy Connect where my CDB is called cdb1 and the listener is on a node called HoL (listening on port 1521). 
------------------------------------------------------------
CONNECT c##Admin/oracle#HoL/cdb1
create user c##Test identified by p container = all
/
alter session set container = pdb1
/
grant Create Session to c##Test
/
alter session set container = pdb2
/
grant Set Container to c##Test
/
CONNECT c##Test/p#HoL/pdb1
alter session set container = pdb2
/
------------------------------------------------------------
 Notice that I don't have to grant either the Create Session or the Set Container privileges commonly. However, if I want to do the grant commonly (i.e. use the "container = all" syntax) then I must issue the "grant" statement when my current container is CDB$Root. (It's illegal anywhere else.) All the usual rules about "with admin option" for system privileges and "with grant option" for bject privileges apply here too. All that's new is the "container = all" syntax and the mandate to have CDB$Root as rthe current container if you want to use it.

Hi, While giving grand , you should provide container=ALL. regards,manoj.V

Related

How to change Pluggable DB's SYS password ?

DB version: 12.1.0.2OS : Oracle Linux 6.6 I created a PDB from Seed. But, I get the following error when I try to change the password of SYS user in PDB. Any idea why ? SQL> create pluggable database PDB3admin user pdb_admin identified by oracle123roles = (DBA)CREATE_FILE_DEST='/oradata/CDB1/PDB3'  ; Pluggable database created. SQL> alter pluggable database PDB3 open; Pluggable database altered. $ sqlplus pdb_admin/oracle123#10.16.135.185:1521/PDB3 SQL*Plus: Release 12.1.0.2.0 Production on Fri Aug 28 11:48:44 2015 Copyright (c) 1982, 2014, Oracle.  All rights reserved.  Connected to:Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit ProductionWith the Partitioning, OLAP, Advanced Analytics and Real Application Testing options SQL> show con_name CON_NAME------------------------------PDB3 -- CREATED column is reflecting Seed PDB's creation date SQL> select username, account_Status, created from dba_users where username like 'SYS%'; USERNAME     ACCOUNT_STATUS                   CREATED------------ -------------------------------- ---------SYS          OPEN                             07-JUL-14SYSTEM       OPEN                             07-JUL-14SYSBACKUP    EXPIRED & LOCKED                 07-JUL-14SYSKM        EXPIRED & LOCKED                 07-JUL-14SYSDG        EXPIRED & LOCKED                 07-JUL-14 SQL> alter user sys identified by pdbsys123;alter user sys identified by pdbsys123*ERROR at line 1:ORA-65066: The specified changes must apply to all containers Apparently, I can do something like below . But, I don't want to set a common password for SYS and SYSTEM users across all PDBs alter user SYSTEM identified by oracle container=all;
T. Boyd, It is quite clear from Googling for the error, this is how it is supposed to work.You would need to file an enhancement request in My Oracle Support. ------------- Sybrand BakkerSenior Oracle DBA
In a multitenant environment, a common useris a database user whose identity and password are known in the root and in every existing and future pluggable database (PDB). Common users can connect to the root and perform operations. If the common user has the appropriate privileges, then this user can perform operations in PDBs as well, such as granting privileges to local users.
Doc says/provides the information. - Pavan Kumar N
I created a PDB from Seed. But, I get the following error when I try to change the password of SYS user in PDB. Any idea why ?
Unfortunately the dreaded RTFM answer is pretty much mandatory, IMHO, for 12c. Pavan's quote appears to come from this doc/linkhttp://docs.oracle.com/database/121/DBSEG/users.htm#DBSEG573 I would add quotes of two paragraphs that closely follow the one already provided:
All Oracle-supplied administrative user accounts, such as SYS and SYSTEM, are common users and can navigate across the CDB. Common users can have different privileges in different PDBs. For example, the common user SYSTEM can switch between PDBs and use the privileges that are granted to SYSTEM in the current PDB.
. . .
If you plug a PDB that contains a common user into a CDB, then the following actions take place:
The common user accounts in this PDB lose commonly granted privileges that they may have had, including the SET CONTAINER privilege.
If the target CDB has a common user with the same name as a common user in a newly plugged-in PDB, then the new common user is merged with the target CDB common user. The password of the target CDB common user takes precedence.
See that last sentence? As others, and the doc, say a common user is COMMON. The standard users SYS and SYSTEM are common users so only have one password.
Oh ok. Got it. Any COMMON user created must have the password same across all containers.I created a COMMON user and tried to change its password while logged into a container. It failed  SQL> select con_id, username, common common_user , account_Status from cdb_users where common = 'YES' and username = 'SYS';     CON_ID USERNAME        COM ACCOUNT_STATUS---------- --------------- --- --------------------------------         3 SYS             YES OPEN         1 SYS             YES OPEN SQL> show con_name CON_NAME------------------------------CDB$ROOTSQL>SQL> create user c##SCOTT identified by tiger container = all; User created. SQL> select con_id, username, common common_user , account_Status from cdb_users where common = 'YES' and username = 'C##SCOTT';     CON_ID USERNAME             COM ACCOUNT_STATUS---------- -------------------- --- --------------------------------         1 C##SCOTT             YES OPEN         3 C##SCOTT             YES OPEN SQL> alter session set container = PDB3; Session altered. SQL> SHOW CON_NAME CON_NAME------------------------------PDB3SQL>SQL> alter user c##SCOTT identified by newpass;alter user c##SCOTT identified by newpass*ERROR at line 1:ORA-65066: The specified changes must apply to all containers SQL> alter user c##SCOTT identified by  newpass container=current;alter user c##SCOTT identified by  newpass container=current*ERROR at line 1:ORA-65066: The specified changes must apply to all containers

How to query user data files as a pdb admin user

Hello experts,                       I have logged in as a pdb admin user with pdb_dba role but I can't query any dba_tablespace or v$tablespace to check as those are in cdb level.Please tell me how do I query those  as a pdb admin? RegardsDhritiman Deb
You can't. You'll have to log into CDB$ROOT to see that CDB-level information.  Cheers,Brian
Okay let me clarify my question.Let's say I am a PDB administrator and I want to allocate some space for tablespaces allocated for PDB.How do I query and allocate space? Assume I don't have cdb level access?
Sorry for the typo but it does not make any sense to connect as sys in pdb.If I have sys credential I can do whatever I want.What I want is as local pdb admin how do I do that? oracle#ccbqdb02:...12102/one_off_patches/21101873$ sqlplus salesadm#salespdbSQL*Plus: Release 12.1.0.2.0 Production on Mon Jun 5 14:56:17 2017Copyright (c) 1982, 2014, Oracle.  All rights reserved.Enter password:Last Successful login time: Mon Jun 05 2017 10:17:47 -07:00Connected to:Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit ProductionWith the Partitioning, OLAP, Advanced Analytics and Real Application Testing optionsSQL> show con_idCON_ID------------------------------3SQL> desc dba_tablespacesERROR:ORA-04043: object "SYS"."DBA_TABLESPACES" does not existSQL> desc v$tablespacesERROR:ORA-04043: object v$tablespaces does not exist
Hi, Dhritiman. From your original post, you said you granted the role pdb_dba to salesadm in salespdb. From the Oracle Database Security Guide: Configuring Privilege and Role Authorization  "PDB_DBA: Granted automatically to the local user that is created when you create a new PDB from the seed PDB. No privileges are provided with this role." Connect to salespdb and execute the following query: SELECT role, table_name, privilege FROM role_tab_privs WHERE role = 'PDB_DBA'; Regards, Geoff
It makes sense to avoid using SYS and prefer using a "local" DBA / Administrator account.As Geoff points out, your account might not have had privileges granted to it yet.  Connect as SYSTEM and grant the privileges or roles that are required. Hemant K Chitale
My assumption was incorrect.I thought PDB_DBA is a role equivalent to dba for pdb admin.I have given dba role to pdb admin salesadm and now I can see necessary data dictionaries. export TNS_ADMIN=/export/home/oracle sqlplus salesadm#salespdbSQL*Plus: Release 12.1.0.2.0 Production on Tue Jun 6 07:01:46 2017Copyright (c) 1982, 2014, Oracle.  All rights reserved.Enter password:Last Successful login time: Mon Jun 05 2017 14:56:27 -07:00Connected to:Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit ProductionWith the Partitioning, OLAP, Advanced Analytics and Real Application Testing optionsSQL> SELECT role, table_name, privilege FROM role_tab_privs WHERE role = 'PDB_DBA';ROLE                 TABLE_NAME                     PRIVILEGE-------------------- ------------------------------ ----------------------------------------PDB_DBA              PDB_ALERTS                     SELECTPDB_DBA              PDB_PLUG_IN_VIOLATIONS         SELECTSQL> select grantee,granted_role,default_role from cdb_role_privs where CON_ID=3 and common='NO';GRANTEE                        GRANTED_ROLE                   DEF------------------------------ ------------------------------ ---SALESADM                       PDB_DBA                        YESTEST_USER3                     DBA                            YESSALESADM                       DBA                            YESTEST1                          PDB_DBA                        YESSQL> conn salesadm#salespdbEnter password:Connected.SQL> show userUSER is "SALESADM"SQL> desc dba_data_files Name                                                                                                              Null?    Type ----------------------------------------------------------------------------------------------------------------- -------- ---------------------------------------------------------------------------- FILE_NAME                                                                                                                  VARCHAR2(513) FILE_ID                                                                                                                    NUMBER TABLESPACE_NAME                                                                                                            VARCHAR2(30) BYTES                                                                                                                      NUMBER BLOCKS                                                                                                                     NUMBER STATUS                                                                                                                     VARCHAR2(9) RELATIVE_FNO                                                                                                               NUMBER AUTOEXTENSIBLE                                                                                                             VARCHAR2(3) MAXBYTES                                                                                                                   NUMBER MAXBLOCKS                                                                                                                  NUMBER INCREMENT_BY                                                                                                               NUMBER USER_BYTES                                                                                                                 NUMBER USER_BLOCKS                                                                                                                NUMBER ONLINE_STATUS                                                                                                              VARCHAR2(7)
And now at this point, managing the application tablespaces becomes no different in a PDB than in a non-CDB database. Just make sure you are connected to the PDB first.  Cheers,Brian

Cannot drop PDB - insufficient privileges

I created a PDB, and then tried to drop it. I cannot drop it, I get "insufficient privileges" error.  Here is the SQL session : ==============================================-bash-4.1$ ./sqlplus system/manager1 SQL*Plus: Release 12.1.0.1.0 Production on Thu Sep 18 07:28:53 2014Copyright (c) 1982, 2013, Oracle.  All rights reserved.Last Successful login time: Thu Sep 18 2014 07:25:13 -07:00 Connected to:Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit ProductionWith the Partitioning, OLAP, Advanced Analytics and Real Application Testing options SQL> create pluggable database test002 admin user test2 identified by test2 file_name_convert=('/u01/app/testuser/oradata/orcl/pdbseed', '/u01/app/testuser/oradata/orcl/test002'); Pluggable database created. SQL> drop pluggable database test002 including datafiles;drop pluggable database test002 including datafiles*ERROR at line 1:ORA-01031: insufficient privileges============================================== I gave sysdba privileges to user "system", as follows : SQL> grant sysdba to system;Grant succeeded. but still get the same error. However, if I login as "sysdba", I can drop the PDB. I cannot use "sysdba" user for a number of reasons. I need to login as some other user. Any ideas? 
Creating and Removing PDBs with SQL*Plus
The current user must have SYSDBA or SYSOPER administrative privilege, and the privilege must be either commonly granted or locally granted in the PDB. The user must exercise the privilege using AS SYSDBA or AS SYSOPER at connect time.
2639137 wrote:
 
I created a PDB, and then tried to drop it. I cannot drop it, I get "insufficient privileges" error.  Here is the SQL session :
. . .
 
I gave sysdba privileges to user "system", as follows :
 
SQL> grant sysdba to system;
Grant succeeded.
 
but still get the same error.
Yes - as the exception says user 'system' does NOT have sufficient privileges to drop a PDB. That grant of sysdba is ONLY for the current container which is the root. Oracle 12c is NOT Oracle 11g - the multitenant architecture has SIGNIFICANT differences from previous versions in MANY areas especially in creating/managing users and granting privileges. The default container for grants is whatever the current container is. If you want the grant to cover ALL containers then you need to specify that. See the CONTAINER clause for the GRANT statement in the docsGRANT
CONTAINER Clause
If the current container is a pluggable database (PDB):
  Specify CONTAINER = CURRENT to locally grant a system privilege, object privilege, or role to a user or role. The privilege or role is granted to the user or role only in the current PDB.
If the current container is the root:
  Specify CONTAINER = CURRENTto locally grant a system privilege, object privilege, or role to a common user or common role. The privilege or role is granted to the user or role only in the root.
  Specify CONTAINER = ALL to commonly grant a system privilege, object privilege on a common object, or role, to a common user or common role.
If you omit this clause, then CONTAINER = CURRENT is the default.
See that last statement?

ORA-01017: invalid username/password; logon denied in 12c database

Hi,I have created the new user in 12c pdb and granted previliges also, but i coudnt able to connect that user. Kindly suggest me. Steps which i have done. SQL> alter session set container=TESTPDB1; Session altered. SQL> create user test1 identified by welcome; User created. SQL> grant connect,resource to test1; Grant succeeded. SQL> commit  2  ;Commit complete.SQL> conn test1/welcome;ERROR:ORA-01017: invalid username/password; logon deniedWarning: You are no longer connected to ORACLE.RegardsKumar V
Hi,  To connect to the PDB you need to make sure you use the service name.  Check the Listener status (e.g. lsnrctl status) and find out the service name registered for PDB. Now create a tnsnames.ora entry or use easy connect method;  example ;  connect test1/welcome#ServerName:PORT_NUMBER/PDB_SERVICE_NAME For more help you can check this note - point 2 http://www.oracle.com/webfolder/technetwork/tutorials/obe/db/12c/r1/pdb/pdb_basics/pdb_basics.html HTH,  Pradeep
I have created the new user in 12c pdb and granted previliges also,
Yes you create a LOCAL user in the PDB
but i coudnt able to connect that user.
SQL> conn test1/welcome;
ERROR:
ORA-01017: invalid username/password; logon denied
Warning: You are no longer connected to ORACLE.
Correct - there is NO SUCH USER 'TEST1' in the root/CDB. The user you created is in the PDB and is a LOCAL user. You tried to connect as that user to the CDB. You did NOT specify a SERVICE_NAME so you are connecting to the ONLY database that the OS environment variables know about which is the root. Many things, even basic things like creating users, are done differently in 12c. In 12c there are common users and local users. Even for EXPERT Oracle DBAs and developers it is MANDATORY to read and understand chapters 17 and 18 of the Concepts doc about the new multitenant architecture of 12c.http://docs.oracle.com/cd/E16655_01/server.121/e17633/part_consol.htm#CHDGDBHJ You, and others, will continue to face similar problems working with multitenant if you don't first read those chapters. Users, grants, privileges are ALL done differently in 12c multitenant. That PDB won't even be open when you restart the database unless you either open it manually or create an AFTER STARTUP trigger to open it for you. See my replies in this thread and others about this same issue.Re: 12c: ORA-65049: creation of local user or role is not allowed in CDB$ROOT
Dear All, The issue has been fixed now. in 12c database we couldnt able to connect the local user in pluggable database. We have to connect the users like below, [oracle#dg admin]$ sqlplus sys/welcome#PDBDEV as sysdba SQL*Plus: Release 12.1.0.2.0 Production on Thu Apr 16 12:56:38 2015 Copyright (c) 1982, 2014, Oracle.  All rights reserved.Connected to:Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit ProductionWith the Partitioning, OLAP, Advanced Analytics and Real Application Testing options SQL> show con_name; CON_NAME------------------------------PDBDEVSQL> create user kumar identified by kumar;User created. SQL> grant connect,resource to kumar;Grant succeeded. SQL> commit;Commit complete. SQL> conn kumar/kumar;ERROR:ORA-01017: invalid username/password; logon denied Warning: You are no longer connected to ORACLE.SQL> conn sys as sysdbaEnter password: Connected.SQL> show con_name;CON_NAME------------------------------CDB$ROOTSQL> conn kumar/kumar#PDBDEVConnected. SQL> show con_name; CON_NAME------------------------------PDBDEVSQL> show user;USER is "KUMAR" Thanks for the suggestions. RegardsKumar 
Which is EXACTLY what I told you in my reply. Glad I was able to help.

Insufficient privileges while create user on 12c

Hi. I have installed 12c grid RAC and 12c database RAC and i could not create common or local user on 12c. I shared steps about creating common or local user like below.Could you please give me your opinion what is the problem ? su - oracle $ iduid=1002(oracle) gid=1001(oinstall) groups=1001(oinstall),54322(dba),54325(asmdba) $ sqlplus / as sysdba SQL*Plus: Release 12.1.0.2.0 Production on Wed Sep 16 09:25:49 2015Copyright (c) 1982, 2014, Oracle.  All rights reserved.Connected to:Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit ProductionWith the Partitioning, Real Application Clusters, Automatic Storage Management, Oracle Label Security,OLAP, Advanced Analytics, Oracle Database Vault and Real Application Testing options SQL> show con_name; CON_NAME------------------------------CDB$ROOT SQL> create user c##test identified by test container=all;create user c##test identified by test container=all                                  *ERROR at line 1:ORA-01031: insufficient privileges SQL> alter session set container=fasdb; Session altered. SQL> show con_name; CON_NAME------------------------------FASDBSQL> create user test identified by test container=current;create user test identified by test container=current                               *ERROR at line 1:ORA-01031: insufficient privileges  SQL> exit
because of Oracle Database Vault enabled
First of all thanks for your reply.I will disable and inform you about latest situation.

Categories

Resources