1 2 Previous Next 17 Replies Latest reply: Jan 4, 2013 4:53 PM by jgarry RSS

    Interesting Oracle Question

    Trith
      Hi Gurus,

      Happy New Year to Everyone!
      A very simple question related to Oracle!
      This thought comes out while working on Database Level Masking for Sensitive Data/Information!

      Can two different Oracle tables (residing in same or different schema) have the same or similar columns (containing same or similar data) ?

      For example:
      Column Name = PRINTER_NAME
      Table Name = FND_PRINTER and FND_PRINTER_TL

      I know Oracle is relational DBMS but any feedback from the Gurus would be really helpful!

      Thanks,
      Trith
        • 1. Re: Interesting Oracle Question
          Brian Bontrager
          Certainly.
          create table MY_USER_OBJECTS as select * from USER_OBJECTS;
          You now have 2 different tables with the same columns and the same data.
          The data in each table can have a life of its own and may or may not be related.
          • 2. Re: Interesting Oracle Question
            jgarry
            You might google for the word: normalization. You may find that not only may the column names be the same, but it actually is very useful. You don't want to copy table information willy-nilly, because then you will have synchronization problems. But if you are asking about creating tables from other tables with sensitive information masked, there are a number of ways to do that, including products you can buy.
            • 3. Re: Interesting Oracle Question
              Trith
              Thanks Brian, in your example we are creating our table.
              What I wanted to know is that does Oracle really have multiple tables (similar to my example, Standard Oracle Tables) with the same columns, the life of which (data) is the same ......
              In other words, If I restrict access to column X in table Y in schema Z, then is it possible for someone else to access the same column X from some other table A in any other schema S.

              Thanks again!
              • 4. Re: Interesting Oracle Question
                Brian Bontrager
                In my example, yes, they are multiple separate tables, with their own security. You would have to mask or prevent access to each version of the data in each schema/table it appears.

                (I'm not sure what you mean by "Standard Oracle Tables", so I can't say more on that.)
                • 5. Re: Interesting Oracle Question
                  Srini Chavali-Oracle
                  These are seeded tables shipped with E-Business Suite - the ER diagram is available at https://etrm.oracle.com - there is a method to the madness ;-)

                  HTH
                  Srini
                  • 6. Re: Interesting Oracle Question
                    EdStevens
                    Trith wrote:
                    Thanks Brian, in your example we are creating our table.
                    What I wanted to know is that does Oracle really have multiple tables (similar to my example, Standard Oracle Tables) with the same columns, the life of which (data) is the same ......
                    In other words, If I restrict access to column X in table Y in schema Z, then is it possible for someone else to access the same column X from some other table A in any other schema S.

                    Thanks again!
                    If column X is in "some other table A in any other scheama" then it is not the same column as column X in table Y in schema Z. Regardless of what they are named, how the data is typed, how the tables and columns are structured, the tables in schema A are totally separate objects from tables in schema Z. The possible fact that they may have the same names and data types is - as far as Oracle is concerned - mere coincidence.
                    • 7. Re: Interesting Oracle Question
                      Dave Rabone
                      ... and is the EBS physical data model denormalised? Almost certainly ... but I'm so far away from an EBS machine right now I can't give you an example (sigh)
                      • 8. Re: Interesting Oracle Question
                        jgarry
                        The 4GL I work on implicitly creates domains based on identical column names between tables. Welcome to the future, 1992 style.
                        • 9. Re: Interesting Oracle Question
                          Mark Malakanov (user11181920)
                          This thought comes out while working on Database Level Masking for Sensitive Data/Information!
                          Can two different Oracle tables (residing in same or different schema) have the same or similar columns (containing same or similar data) ?
                          Simple answer is Yes.

                          But how it may relate to "Database Level Masking for Sensitive Data/Information"?

                          BTW, you may be interested to look into following Oracle security/encryption features:
                          - Virtual Private Database
                          - Label Security Option
                          - Transparent Data Encryption
                          - DBMS_CRYPTO
                          - Database Vault
                          • 10. Re: Interesting Oracle Question
                            EdStevens
                            jgarry wrote:
                            The 4GL I work on implicitly creates domains based on identical column names between tables. Welcome to the future, 1992 style.
                            ???
                            • 11. Re: Interesting Oracle Question
                              jgarry
                              EdStevens wrote:
                              jgarry wrote:
                              The 4GL I work on implicitly creates domains based on identical column names between tables. Welcome to the future, 1992 style.
                              ???
                              Yes, it made more sense 20 years ago, when all database engines were going to be standard and relational. In our terms, it's automatically creating a foreign key just 'cause the columns have the same name as a primary identifier of another table (implementing the meaning of domain in relational theory). So for example, if you have company_code at the beginning of all the tables, when you insert a row in any table the company_code has to exist. And if you define indices, the first one alphabetically is the "primary identifier". Except, there are no constraint_type 'P' when looking at dba_constraints (except for tables internal to the app). The optimizer and things like MV love that. The words mean exactly what someone else wants them to mean. Wheeee!

                              Note to OP: This is not how Oracle works, just showing that apps can implement things in bizzaro world. Oracle objects have numeric ID's to distinguish themselves from each other.
                              • 12. Re: Interesting Oracle Question
                                Mark Malakanov (user11181920)
                                Oracle objects have numeric ID's to distinguish themselves from each other.
                                Not exactly. Internally - yes. But from user perspective Oracle distinguishes objects by name. It is impossible to create two objects of same name in one namespace.
                                • 13. Re: Interesting Oracle Question
                                  jgarry
                                  It depends on the namespace. In the schema namespace, you can have different types of objects with the same name. In various hashing schemes used by oracle internally to name things, there [url https://forums.oracle.com/forums/message.jspa?messageID=10446701#10446701]can be collisions. I've seen some more obscure things, but can't remember what just now.

                                  But the internal representation is the answer to the OP question (at least, one interpretation of the question). And it is visible to users, think of the recycle bin and describe user_objects. A user unfamiliar with the difference between views and tables might get completely confused (it had occurred to me that might possibly be the real question, asked incorrectly).
                                  • 14. Re: Interesting Oracle Question
                                    Mark Malakanov (user11181920)
                                    It depends on the namespace.
                                    That is what I said.
                                    In the schema namespace, you can have different types of objects with the same name. In various hashing schemes used by oracle internally to name things, there can be collisions.
                                    Could you give an example?

                                    There is no pure "schema" namespace. Inside a schema you can create a table and an index or a constraint with same name as the table. Same for supplemental log group. It is because they are associated with the table. However they are in a separate namespace than columns. you can create a constraint with same name as a column of the table.

                                    For PL/SQL name is not only differentiator. Here you can use overloading - a signature, that is a combination of name and set of positions and types of parameters must be unique for different PL/SQLs.
                                    But also there must not be other objects like tables with same name as the proc.
                                    So namespaces may overlap.
                                    I agree it is a bit confusing. :)

                                    But still, users/programmers should use and rely on object names, not IDs.
                                    1 2 Previous Next