{"id":498,"date":"2013-11-10T22:56:20","date_gmt":"2013-11-10T20:56:20","guid":{"rendered":"http:\/\/www.ludovicocaldara.net\/dba\/?p=498"},"modified":"2020-08-18T16:46:22","modified_gmt":"2020-08-18T14:46:22","slug":"playing-with-oracle-12c-users-and-roles","status":"publish","type":"post","link":"https:\/\/www.ludovicocaldara.net\/dba\/playing-with-oracle-12c-users-and-roles\/","title":{"rendered":"Playing with Oracle 12c Multitenant Users and Roles"},"content":{"rendered":"

I’ve realized these days that the great list of articles by Oracle Alchemist<\/a> does not contain any articles describing a little more in depth common roles and Users.<\/p>\n

I’ve found these ones by Pete Finnigan and Bobby Curtis:<\/p>\n

http:\/\/www.petefinnigan.com\/weblog\/archives\/00001366.htm<\/a><\/p>\n

http:\/\/dbasolved.com\/2013\/06\/29\/common-user-vs-local-user-12c-edition\/<\/a><\/p>\n

http:\/\/www.petefinnigan.com\/weblog\/archives\/00001370.htm<\/a><\/p>\n

But I would like to investigate\u00a0a little more.<\/p>\n

My test environment<\/strong><\/p>\n

Just to give you an idea, I have two PDBs (HR and HR2), each containing an HR schema.<\/p>\n

SQL> alter session set container=CDB$ROOT;\r\n\r\nSession altered.\r\n\r\nSQL> select NAME, CON_ID from v$containers;\r\n\r\nNAME                               CON_ID\r\n------------------------------ ----------\r\nCDB$ROOT                                1\r\nPDB$SEED                                2\r\nHR                                      3\r\nHR2                                     4\r\n\r\n4 rows selected.\r\n\r\nSQL> r\r\n  1* select username, con_id from cdb_users where username ='HR'\r\n\r\nUSERNAME                           CON_ID\r\n------------------------------ ----------\r\nHR                                      4\r\nHR                                      3\r\n\r\n2 rows selected.<\/pre>\n
Creating the common user<\/strong><\/p>\n
As already pointed by the existing articles, I can’t create a common user<\/strong> into the root container without the c## prefix, unless I’m altering the hidden parameter\u00a0_common_user_prefix.<\/p>\n
SQL> create user goofy identified by pippo;\r\ncreate user goofy identified by pippo\r\n*\r\nERROR at line 1:\r\nORA-65096: invalid common user or role name<\/pre>\n
so I specify the correct prefix, and it works:<\/p>\n
SQL> create user C##GOOFY identified by pippo;\r\n\r\nUser created.\r\n\r\nSQL> grant create session, alter session to c##goofy;\r\n\r\nGrant succeeded.<\/pre>\n
The user is common<\/strong>, so it appears in all the containers<\/strong>, I can check it by querying CDB_USERS from the root container.<\/p>\n
SQL> select username, con_id from cdb_users where username like '%GOOFY%';\r\n\r\nUSERNAME                                               CON_ID\r\n-------------------------------------------------- ----------\r\nC##GOOFY                                                    1\r\nC##GOOFY                                                    3\r\nC##GOOFY                                                    4<\/pre>\n
Creating the local user<\/strong><\/p>\n
Then I create also a\u00a0local user<\/strong> into the HR PDB.<\/p>\n
SQL> alter session set container=HR;\r\n\r\nSession altered.\r\n\r\nSQL> create user goofy identified by pippo;\r\n\r\nUser created.\r\n\r\nSQL> grant create session, alter session to goofy;\r\n\r\nGrant succeeded.<\/pre>\n
 <\/p>\n
CDB_USERS scope<\/strong><\/p>\n
From the PDB I see only the users in the PDB scope:<\/p>\n
SQL> select username, con_id from cdb_users where username like '%GOOFY%';\r\n\r\nUSERNAME                                               CON_ID\r\n-------------------------------------------------- ----------\r\nC##GOOFY                                                    3\r\nGOOFY                                                       3<\/pre>\n
If I change to the root, I see the users valid into all the containers:<\/p>\n
SQL> alter session set container=CDB$ROOT;\r\n\r\nSession altered.\r\n\r\nSQL> select username, con_id from cdb_users where username like '%GOOFY%';\r\n\r\nUSERNAME                                               CON_ID\r\n-------------------------------------------------- ----------\r\nC##GOOFY                                                    1\r\nC##GOOFY                                                    3\r\nC##GOOFY                                                    4\r\nGOOFY                                                       3<\/pre>\n
Creating a common role<\/strong><\/p>\n
Do the roles obey to the same rules valid for the users?<\/p>\n
SQL> alter session set container=CDB$ROOT;\r\n\r\nSession altered.\r\n\r\nSQL> create role country_reader;\r\ncreate role country_reader\r\n*\r\nERROR at line 1:\r\nORA-65096: invalid common user or role name<\/pre>\n
Yes, they do! So, let’s create a common role with the C## prefix:<\/p>\n
SQL> create role C##COUNTRY_ROLE container=ALL;\r\n\r\nRole created.<\/pre>\n
It works, but if I try to create a common role into the root container only, I get an error:<\/p>\n
SQL> create role C##REGIONS_ROLE container=current;\r\ncreate role C##REGIONS_ROLE container=current\r\n*\r\nERROR at line 1:\r\nORA-65094: invalid local user or role name<\/pre>\n
And also if I try to create a local role into the root, I can’t:<\/p>\n
SQL> create role REGIONS_ROLE container=current;\r\ncreate role REGIONS_ROLE container=current\r\n*\r\nERROR at line 1:\r\nORA-65049: creation of local user or role is not allowed in CDB$ROOT<\/pre>\n
Note that the error ORA-65049 is different from the ORA-65096 that I’ve got before.<\/p>\n
My conclusion is that\u00a0the clause\u00a0container<\/strong>\u00a0of the create role<\/em> and create user<\/em> statements\u00a0doesn’t make sense<\/strong> as you can ONLY create common users and roles into the root container and only local ones into the PDBs.<\/p>\n
\u00a0Creating a local role<\/strong><\/p>\n
Just as experiment, I’ve tried to see if I can create a local role with container=ALL. It doesn’t work:<\/p>\n
SQL> alter session set container=hr;\r\n\r\nSession altered.\r\n\r\nSQL> create role REGION_ROLE container=ALL;\r\ncreate role REGION_ROLE container=ALL\r\n*\r\nERROR at line 1:\r\nORA-65050: Common DDLs only allowed in CDB$ROOT<\/pre>\n
So I create the local role with container=current:<\/p>\n
SQL> create role REGION_ROLE container=CURRENT;\r\n\r\nRole created.<\/pre>\n
Now, from the PDB I can see the two roles I can access, whereas from the root I can see all the roles I’ve defined so far: the common role is available from all the PDBs, the local role only from the db where it has been defined, just like the users.<\/p>\n
SQL>  select role, con_id from cdb_roles where   role like '%COUNTRY%' or role like '%REGION%';\r\n\r\nROLE                                                CON_ID\r\n----------------------------------------------- ----------\r\nREGION_ROLE                                              3\r\nC##COUNTRY_ROLE                                          3\r\n\r\nSQL> alter session set container=CDB$ROOT;\r\n\r\nSession altered.\r\n\r\nSQL>  select role, con_id from cdb_roles where  oracle_maintained='N';\r\n\r\nROLE                                                                                                                           CON_ID\r\n----------------------------------------- ----------\r\nC##COUNTRY_ROLE                                    1\r\nC##COUNTRY_ROLE                                    4\r\nC##COUNTRY_ROLE                                    3\r\nREGION_ROLE                                        3\r\n\r\n4 rows selected.<\/pre>\n
\u00a0Granting object privileges to the roles<\/strong><\/p>\n
From the root I can’t give grants on objects that reside in a PDB since I cannot see them: I need to connect to the PDB and give the grants from there:<\/p>\n
SQL> grant select on hr.countries to C##country_role;\r\ngrant select on hr.countries to C##country_role\r\n*\r\nERROR at line 1:\r\nORA-00942: table or view does not exist\r\n\r\nSQL> alter session set container=hr;\r\n\r\nSession altered.\r\n\r\nSQL> grant select on hr.countries to C##COUNTRY_ROLE;\r\n\r\nGrant succeeded.\r\n\r\nSQL> grant select on hr.regions to REGION_ROLE;\r\n\r\nGrant succeeded.<\/pre>\n
Now, if I query CDB_TAB_PRIVS from the root, I see that the grants are given at a local level (con_id=3 and common=N):<\/p>\n
SQL> alter session set container=cdb$root;\r\n\r\nSession altered.\r\n\r\nSQL> select GRANTEE, OWNER, TABLE_NAME, PRIVILEGE, COMMON, CON_ID from  cdb_tab_privs where owner='HR';\r\n\r\nGRANTEE              OWNER    TABLE_NAME       PRIVILEGE     COM     CON_ID\r\n-------------------- -------- ---------------- ------------- --- ----------\r\nREGION_ROLE          HR       REGIONS          SELECT        NO           3\r\nC##COUNTRY_ROLE      HR       COUNTRIES        SELECT        NO           3\r\n\r\n2 rows selected.<\/pre>\n
\u00a0Granting common and local roles to commond and local users<\/strong><\/p>\n
From a PDB, I can grant local roles to local users or common users:<\/p>\n
SQL> grant REGION_ROLE to goofy;\r\n\r\nGrant succeeded.\r\n\r\nSQL> grant REGION_ROLE to c##goofy;\r\n\r\nGrant succeeded.<\/pre>\n
But I can’t grant a common role to a common user with container=all if I’m in a PDB:<\/p>\n
SQL> grant C##COUNTRY_ROLE to c##goofy container=all;\r\ngrant C##COUNTRY_ROLE to c##goofy container=all\r\n*\r\nERROR at line 1:\r\nORA-65050: Common DDLs only allowed in CDB$ROOT<\/pre>\n
To grant the a common role to a common user I can decide either to:<\/p>\n
    \n
  • \u00a0give the grant locally while connected to the PDB:<\/li>\n<\/ul>\n
    SQL> grant C##COUNTRY_ROLE to c##goofy container=current;\r\n\r\nGrant succeeded.\r\n\r\nSQL><\/pre>\n
      \n
    • \u00a0give the grant commonly while connected to the root:<\/li>\n<\/ul>\n
      SQL> alter session set container=cdb$root;\r\n\r\nSession altered.\r\n\r\nSQL> grant C##COUNTRY_ROLE to c##goofy container=all;\r\n\r\nGrant succeeded.<\/pre>\n
      I can also grant locally both roles and system privileges to common users while connected to the root container: in this case the privileges are applied to the root container only. Finally having the clause container finally starts to make sense:<\/p>\n
      SQL> grant C##COUNTRY_ROLE to c##goofy container=current;\r\n\r\nGrant succeeded.\r\n\r\nSQL> grant select any table to c##goofy container=current;\r\n\r\nGrant succeeded.<\/pre>\n
      Verifying the grants<\/strong><\/p>\n
      Ok, I’ve given the grants and I’ve never verified if they work, so far.<\/p>\n
      Let’s try with the select any table<\/em> privilege<\/strong> I’ve given in the last snippet. I expect C##GOOFY to select any table from the root container and only HR.COUNTRIES and HR.REGIONS on the HR PDB bacause they have been granted through the two roles.<\/p>\n
      SQL> connect c##goofy\/pippo;\r\nConnected.\r\nSQL> select count(*) from APEX_040200.WWV_FLOW_TABS;\r\n\r\nCOUNT(*)\r\n----------\r\n609\r\n\r\n1 row selected.\r\n\r\nSQL> connect c##goofy\/pippo@node4:1521\/hr\r\nERROR:\r\nORA-01045: user C##GOOFY lacks CREATE SESSION privilege; logon denied\r\n\r\nWarning: You are no longer connected to ORACLE.<\/pre>\n
      What’s the mess? When I’ve created the user c##goofy, I’ve granted create and alter session without the container=all<\/strong>:<\/p>\n
      SQL> grant create session, alter session to c##goofy;<\/pre>\n
      According to the documentation, the grant command uses container=current by default<\/strong> (common=N):<\/p>\n
      SQL> select * from dba_sys_privs where grantee='C##GOOFY';\r\n\r\nGRANTEE                        PRIVILEGE                                ADM COM\r\n------------------------------ ---------------------------------------- --- ---\r\nC##GOOFY                       SELECT ANY TABLE                         NO  NO\r\nC##GOOFY                       ALTER SESSION                            NO  NO\r\nC##GOOFY                       CREATE SESSION                           NO  NO\r\n\r\n3 rows selected.<\/pre>\n
      So, I need to give the grants commonly to let c##goofy connect to all the PDBs:<\/p>\n
      SQL> grant create session, alter session to c##goofy container=all;\r\n\r\nGrant succeeded.\r\n\r\nSQL>  select * from dba_sys_privs where grantee='C##GOOFY';\r\n\r\nGRANTEE                        PRIVILEGE                                ADM COM\r\n------------------------------ ---------------------------------------- --- ---\r\nC##GOOFY                       SELECT ANY TABLE                         NO  NO\r\nC##GOOFY                       ALTER SESSION                            NO  NO\r\nC##GOOFY                       CREATE SESSION                           NO  NO\r\nC##GOOFY                       ALTER SESSION                            NO  YES\r\nC##GOOFY                       CREATE SESSION                           NO  YES\r\n\r\n5 rows selected.<\/pre>\n
      Now I see that the grants give\u00a0two distinct permissions<\/strong> : one local and the other common.<\/p>\n
      If I revoke the grants without container clause, actually only the local one is revoked<\/strong> and the user can continue to login. To revoke the grants I would need to check and revoke both local and common privileges.<\/p>\n
      SQL> revoke create session, alter session from c##goofy;\r\n\r\nRevoke succeeded.\r\n\r\nSQL>  select * from dba_sys_privs where grantee='C##GOOFY';\r\n\r\nGRANTEE                        PRIVILEGE                                ADM COM\r\n------------------------------ ---------------------------------------- --- ---\r\nC##GOOFY                       SELECT ANY TABLE                         NO  NO\r\nC##GOOFY                       ALTER SESSION                            NO  YES\r\nC##GOOFY                       CREATE SESSION                           NO  YES\r\n\r\n3 rows selected.\r\n\r\nSQL> connect C##GOOFY\/pippo;\r\nConnected.<\/pre>\n
      After the first revoke statement, I can still connect to HR and verify that my select any table privilege<\/em> doesn’t apply to the PDB as it’s local to the root container:<\/p>\n
      SQL> connect C##GOOFY\/pippo@node4:1521\/hr\r\nConnected.\r\nSQL> select count(*) from hr.DEPARTMENTS;\r\nselect count(*) from hr.DEPARTMENTS\r\n*\r\nERROR at line 1:\r\nORA-00942: table or view does not exist<\/pre>\n
      After that, I want to check the privileges given through the local and common roles.<\/p>\n
      I expect both users to select from hr.countries and hr.regions since they have been granted indirectly by the roles.<\/p>\n
      Let’s try the local user first:<\/p>\n
      SQL> connect goofy\/pippo@node4:1521\/hr\r\nConnected.\r\nSQL> select count(*) from hr.regions;\r\n\r\n  COUNT(*)\r\n----------\r\n         4\r\n\r\n1 row selected.\r\n\r\nSQL> select count(*) from hr.countries;\r\n\r\n  COUNT(*)\r\n----------\r\n        25\r\n\r\n1 row selected.<\/pre>\n
      Yeah, it works as expected.<\/p>\n
      Now let’s try the common user:<\/p>\n
      SQL> connect c##goofy\/pippo@node4:1521\/hr\r\nConnected.\r\nSQL> select count(*) from hr.regions;\r\n\r\n  COUNT(*)\r\n----------\r\n         4\r\n\r\n1 row selected.\r\n\r\nSQL> select count(*) from hr.countries;\r\n\r\n  COUNT(*)\r\n----------\r\n        25\r\n\r\n1 row selected.<\/pre>\n
      It also work, so everything is ok.<\/p>\n
      Common and local grants, why you must pay attention<\/strong><\/p>\n
      During the example, I’ve granted the C##COUNTRY_ROLE many times: locally to PDB, locally to the ROOT, commonly. The result is that I’ve flooded the grant table with many entries:<\/p>\n
      SQL> alter session set container=CDB$ROOT;\r\n\r\nSession altered.\r\n\r\nSQL> select grantee, GRANTED_ROLE, COMMON, CON_ID from cdb_role_privs where grantee like '%GOOFY' order by GRANTEE, GRANTED_ROLE;\r\n\r\nGRANTEE                        GRANTED_ROLE                   COM     CON_ID\r\n------------------------------ ------------------------------ --- ----------\r\nC##GOOFY                       C##COUNTRY_ROLE                YES          4\r\nC##GOOFY                       C##COUNTRY_ROLE                YES          3\r\nC##GOOFY                       C##COUNTRY_ROLE                YES          1\r\nC##GOOFY                       C##COUNTRY_ROLE                NO           1\r\nC##GOOFY                       C##COUNTRY_ROLE                NO           3\r\nC##GOOFY                       REGION_ROLE                    NO           3\r\nGOOFY                          C##COUNTRY_ROLE                NO           3\r\nGOOFY                          REGION_ROLE                    NO           3\r\n\r\n8 rows selected.<\/pre>\n
      Let’s try to clean things: for sure I don’t need the grant local to the root:<\/p>\n
      SQL> revoke C##COUNTRY_ROLE from C##GOOFY container=current;\r\n\r\nRevoke succeeded.<\/pre>\n
      Then I can choose between revoking the common privilege or the local one. Let’s try to remove the local one:<\/p>\n
      SQL> alter session set container=hr;\r\n\r\nSession altered.\r\n\r\nSQL>  revoke C##COUNTRY_ROLE from C##GOOFY container=current;\r\n\r\nRevoke succeeded.<\/pre>\n
      I’ve removed the local one, but I have still the common one (I’m connected to the PDB so the entries from the other containers are not displayed):<\/p>\n
      SQL> select grantee, GRANTED_ROLE, COMMON, CON_ID from cdb_role_privs where grantee like '%GOOFY' order by GRANTEE, GRANTED_ROLE;\r\n\r\nGRANTEE                        GRANTED_ROLE                   COM     CON_ID\r\n------------------------------ ------------------------------ --- ----------\r\nC##GOOFY                       C##COUNTRY_ROLE                YES          3\r\nC##GOOFY                       REGION_ROLE                    NO           3\r\nGOOFY                          C##COUNTRY_ROLE                NO           3\r\nGOOFY                          REGION_ROLE                    NO           3\r\n\r\n4 rows selected.<\/pre>\n
      I still have access to the tables as expected:<\/p>\n
      SQL> connect C##GOOFY\/pippo@node4:1521\/hr\r\nConnected.\r\nSQL>  select count(*) from hr.countries;\r\n\r\n  COUNT(*)\r\n----------\r\n        25\r\n\r\n1 row selected.\r\n\r\nSQL>  select count(*) from hr.regions;\r\n\r\n  COUNT(*)\r\n----------\r\n         4\r\n\r\n1 row selected.<\/pre>\n
      So, you must pay attention to a couple of things:<\/p>\n