Forum Stats

  • 3,875,191 Users
  • 2,266,843 Discussions
  • 7,912,104 Comments

Discussions

Design question: Where does the user belong?

nsher
nsher Member Posts: 6
edited May 16, 2014 3:59PM in Multitenant

I am testing Multitenant feature in 12c and in my multi-tenancy use case, a user can belong to more than one tenant. This is not a Oracle admin/local/common user, but a user in the application itself, existing in its own table.

So, here are the sequence of steps:

  1. user logs in using userId and password
  2. If user belongs to more than one tenant, ask which tenant he wants to work with now. Now change the PDB.
  3. If user belongs to only one tenant, switch to that PDB.

Question: Since I need the user info before getting into a PDB, can I create tables at container level? How would I do this? Or, do I need to have a separate PDB just for this?

Please suggest.

--Nik

Answers

  • Unknown
    edited Apr 22, 2014 6:54PM
    I am testing Multitenant feature in 12c and in my multi-tenancy use case, a user can belong to more than one tenant. This is not a Oracle admin/local/common user, but a user in the application itself, existing in its own table.
    So, here are the sequence of steps:
    
    user logs in using userId and password
    If user belongs to more than one tenant, ask which tenant he wants to work with now. Now change the PDB.
    If user belongs to only one tenant, switch to that PDB.
    
    
    Question: Since I need the user info before getting into a PDB, can I create tables at container level? How would I do this? Or, do I need to have a separate PDB just for this?
     

    Welcome to the Multitenant forum - you are one of the few that seem to have found it so far!

    And you are starting to discover that your options in 12c can be radically different than before.

    a user can belong to more than one tenant.

    Ok - but how is that any different that 'a user can belong to more than one database'? Just as you can have the same user account in more than one 11g database you can also have the same 'local' user account in more than one 12c PDB.

    Option 1 - tell the user to log into the database (PDB) that they want to use. Why do you need a menu that says 'pick one'?

    Assuming that you really need the 'login to one place' as you say then you need to review ALL of the documentation about 'common' and 'local' users.

    Users in the CDB MUST BE common users as defined by Oracle. See the Database Security Guide

    http://docs.oracle.com/cd/E16655_01/network.121/e17607/users.htm

    About Common Users

    In a multitenant environment, a common user is 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.
     

    Option 2 - use the CDB as the common connect point. This means that all of your users MUST BE common users (see above) and your code would then 'switch' them to the appropriate PDB. It also means that your application must now have tables/objects in the CDB even if the app is appropriate for only 'some' of the PDBs.

    That also means that your management of common user priviliges will be more complicated since, as that quote above says, those common users will, by default, exist in EVERY PDB that you create. Then you will need to manage the privileges properly to make sure that a common user can NOT access or otherwise interact with PDBs that they are not supposed to interact with.

    All of those 'privilege' and 'role' issues are discussed in the 'Configuring Privilege and Role Authorization' chapter of that security doc

    http://docs.oracle.com/cd/E16655_01/network.121/e17607/authorization.htm#CACIGEHG

    How the Oracle Multitenant Option Affects Privileges

    In a multitenant environment, all users, including common users, can exercise their privileges only within the current container. However, a user connected to the root can perform certain operations that affect other pluggable databases (PDBs). These operations include ALTER PLUGGABLE DATABASE, CREATE USER, CREATE ROLE, and ALTER USER. The common user must possess the common privilege grants that enable these operations. A common user connected to the root can see metadata pertaining to PDBs by way of the container data objects (for example, multitenant container database (CDB) views and V$ views) in the root, provided that the common user has been granted privileges required to access these views and his CONTAINER_DATA attribute has been set to allow seeing data about various PDBs. The common user cannot query tables or views in a PDB.
    
    Common users cannot exercise their privileges across other PDBs. They must first switch to the PDB that they want, and then exercise their privileges from there. To switch to a different container, the common user must have the SET CONTAINER privilege. Alternatively, the common user can start a new database session whose initial current container is the container this user wants, relying on the CREATE SESSION privilege in that PDB.

    Be aware that commonly granted privileges that have been made to common users may interfere with the security configured for individual PDBs.

    You will be charting new territory that does NOT yet have established 'best practices' if you go the option #2 route.

    The close you stay to the tried and true 'connect directly to the DB you want to use the fewer issues and problems you will have and the fewer issues you will have with architecture, implementation, testing and training. If you want to be on the 'bleeding edge' go right ahead.

    You haven't indicated at all what your app does or how users need to interact with it.

    There is a MAJOR difference between an architecture where users interact ONLY with an application (e.g APEX or a web or middle-tier application server) and an architecture where users interact directly with Oracle and the DB.

    You can easily use a middle-tier application to allow a universal log in point that then connects behind the scenes to the actual PDB/DB that the user has chosen from a pick list. That avoids having to deal with those pesky privilege issues in the backend but still allows the user to access based on choice.

  • Aman....
    Aman.... Member Posts: 22,919 Gold Crown

    Rp,

    How can one create use which is going to belonging to more than one tenant ? Since the OP mentioned that user is not a common/local user than is there any other way also to create users that they would be belonging to multiple PDB's ?

    Regards

    Aman....

  • How can one create use which is going to belonging to more than one tenant ? Since the OP mentioned that user is not a common/local user than is there any other way also to create users that they would be belonging to multiple PDB's ?

    Yes - like I said above you do it the old fashioned way:

    Just as you can have the same user account in more than one 11g database you can also have the same 'local' user account in more than one 12c PDB.

    Connect the PDB1 and create a local user, SCOTT. Then connect to PDB2 and create local user SCOTT. And so on. You manually create the users in any PDB that needs that user. You also have to manage those users separately and one of those connected users can NOT use ALTER SESSION SET CONTAINER since they are NOT common users.

    In other words the OP would create whatever user accounts are needed for each PDB and the users would need to login to the one they wanted. A 'choose a PDB' option would only be available if an application or midde-tier app provided it and then made a direct connection to the desired PDB on that user's behalf.

    When OP said this:

    This is not a Oracle admin/local/common user, but a user in the application itself, existing in its own table.

    I took that to mean that he was describing his application user. I didnt' interpret it as meaning that a solution could not use Oracle's 'common' users. But maybe I was wrong.

  • nsher
    nsher Member Posts: 6

    Thanks rp for the detailed reply. Except for the users, you did help in answering the question with answers. Here is some more detail on what I am looking for.

    The application I have is a J2EE MVC web application hosted on an application server. Right now there is one common user(say c##cadmin) with DBA rights on all the PDBs.

    here is an example: Suppose there are 3 PDBs. Every PDB has a table called MYAPP_USERS where userIds and pwds are stored. A user (user1/pwd1) can login to PDB1 and PDB3, and user2/pwd2 can login to PDB1 and PDB2. When a user tries to login, a connection is created using c##cadmin/pwd and MYAPP_USERS is queried to see if the user1/pwd1 is valid.

    So, what I am looking for is a way to have a mapping of users in MYAPP_USERS and their PDBs (tenants). I don't want a user to first try PDB1, next PDB2 and then PDB3. If a user belongs to more than one PDB, I want to show him the list of PDBs that he can go to. Then its upto him where he can go. So, I am thinking that all the users must also exist in a place outside of the PDBs.

    Option 1: Create a new PDB called USER_TENANT_PDB, for example, for user-tenant mapping. users in this table are also expected in other PDBs (only if they are allowed to login in that PDB). So, a c##cadmin will always first connect to USER_TENANT_PDB, and then switch the PDB based on user selection.

    Option 2: As you suggested, use the CDB as the connection point: I don't want the users to be common users. How about creating tables in CDB? I don't know if that is possible because I thought tables can only be created in PDBs. If there is a way, then I would like to know about it.

    --Nik

  • Aman....
    Aman.... Member Posts: 22,919 Gold Crown

    Excellent reply Rp, thanks a bunch! I did the same thing today but I didn't that "prompt" . Was silly of me to think it would appear in a linux terminal anyways .

    Regards

    Aman....

  • The application I have is a J2EE MVC web application hosted on an application server. Right now there is one common user(say c##cadmin) with DBA rights on all the PDBs.
    
    here is an example: Suppose there are 3 PDBs. Every PDB has a table called MYAPP_USERS where userIds and pwds are stored. A user (user1/pwd1) can login to PDB1 and PDB3, and user2/pwd2 can login to PDB1 and PDB2. When a user tries to login, a connection is created using c##cadmin/pwd and MYAPP_USERS is queried to see if the user1/pwd1 is valid.
    
    So, what I am looking for is a way to have a mapping of users in MYAPP_USERS and their PDBs (tenants). I don't want a user to first try PDB1, next PDB2 and then PDB3. If a user belongs to more than one PDB, I want to show him the list of PDBs that he can go to. Then its upto him where he can go. So, I am thinking that all the users must also exist in a place outside of the PDBs.
    

    I suggest you separate the 'physical' from the 'logical'. Users do NOT need to know the 'physical' (PDB). They should just know the 'logical' (e.g. BILLING, PAYROLL, etc). Users do NOT need to know what database they connect to or even have user names or passwords on ANY of the actual databases (especially the CDB).

    So rather that show a user a 'list of PDBs that he can go to' you provide a list of 'functional' areas to work with.

    Option 1: Create a new PDB called USER_TENANT_PDB, for example, for user-tenant mapping. users in this table are also expected in other PDBs (only if they are allowed to login in that PDB). So, a c##cadmin will always first connect to USER_TENANT_PDB, and then switch the PDB based on user selection.
    

    More typically that 'master user list' is maintained at the middle-tier as far as the users are concerned. The actual backup store might be an Oracle database but some middle-tier app servers have extensive functionality for creating GROUPS, USERS and PRIVILEGES.

    That way a user NEVER gets connected to a database except to do actual work. The authentication (including single signon) is handled in the middle-tier. That means that your users do NOT have a username or password on ANY PDB or database.

    If you did use the database for authentication then, yes, you would create an ultra-secure PDB that maintains the group, user and privilege lists. Each user would have credentials for authentication (user name/password/verification questions/etc) and then, based on their choice of work area, would be connected 'behind the scenes' using 'proxy users' to the proper PDB for doing their work.

    If their 'work' included a need to transparently switch 'containers' then their proxy user would need to be a 'common' user as defined by Oracle and you would have to manage those privileges properly as I previously mentioned.

    Option 2: As you suggested, use the CDB as the connection point: I don't want the users to be common users. How about creating tables in CDB? I don't know if that is possible because I thought tables can only be created in PDBs. If there is a way, then I would like to know about it.
    
    

    I already covered most of that above.

    The multitenant functionality is too new to have a developed set of 'best practices' so most of this discussion is my own opinion.

    The CDB should be used mainly for 'administration' purposes to 'administer' the various PDBs and the overall superstructure. The use case I mentioned above could utilize common users 'behind the scenes' as long as the proper privilege administration has been set up and that will be the tricky part.

    The key architectural issue for those multi-tenant use cases, IMHO, depends on YOUR definition of multi-tenant. Your use of that term seems to be more of a 'multi functional area' that a true multitenant. An example of what I mean by 'true' multitenant might be an architecture where each PDB belongs to a different company and the data and access need to be highly segregated.

  • Franck Pachot
    Franck Pachot Member Posts: 912 Bronze Trophy

    Hi,

    That kind of requirement is not specific to multitenant in my opinion. I've seen an application using hundreds of schemas (datamarts) on several databases. And the user was directed to one database/schema depending on a few parameters that were stored in a database.

    One option is to have one 'logon' PDB: everybody connects to it, check the user parameters and then redirects to the right PDB.

    Another option is to connect to any PDB and query that common 'logon' PDB through a database link.

    Another option that I mention just for the fun of it because it is not supported. It's what Oracle does when it need to share some data among several containers: metadata links + object links + common data views. An exemple here: Oracle 12c CDB - metadata and object links internals ... remember: forbidden in prod

    Regards,

    Franck.

This discussion has been closed.