This discussion is archived
5 Replies Latest reply: Jan 28, 2013 4:12 PM by Srini Chavali-Oracle RSS

schema issue

986173 Newbie
Currently Being Moderated
I thought it was such a little thing: using a power user to create a view, then grant select to a read only user to use the the view. Then, an Oracle expert in our community told me it's dangerous to do that because that may mess up with the database schema and further may even destroy the database.

Please shed light on this issue. Thanks.
  • 1. Re: schema issue
    Justin Cave Oracle ACE
    Currently Being Moderated
    Did you ask this expert to explain exactly what he believed was dangerous and why? It doesn't make sense to talk about "missing up a database schema" or "destroying a database"-- neither of those are technical terms so they can mean whatever you want them to mean.

    It's perfectly reasonable, in general, for a user to create a view and to grant access to that view to other users. That's generally a very sensible way to structure your security. Of course, there are times that this is unwise-- exposing a view that contains sensitive personal information to users that don't really need it, for example. If the expert you're quoting was concerned about a specific implementation detail, that's a very different thing than being concerned about the general principle.

    Justin
  • 2. Re: schema issue
    986173 Newbie
    Currently Being Moderated
    He says the following:

    Views should be created somewhere else, not in the production database.
    * Less risk of accidentally damaging real production db objects & data if working in Oracle as a user with these rights
    * Keep site-defined objects separate from the vendor-defined objects

    When asked "how do you use the views in other database, say, the training database, to work on the base tables which are on the
    production?" He gave the following suggestion. I am not sure if the traindb user can execute the view in another.

    As the my_schema user, create the view in your custom schema:
    create view my_schema.my_view as select ... from mydb.table1 etc.; Have the my_schema user grant access on the view:
    grant select on my_schema.my_view to my_traindb_user;
  • 3. Re: schema issue
    Justin Cave Oracle ACE
    Currently Being Moderated
    983170 wrote:
    Views should be created somewhere else, not in the production database.
    Are you sure that he said "database" and not "schema"?
    * Less risk of accidentally damaging real production db objects & data if working in Oracle as a user with these rights
    I'm not sure that I understand. What "damage" is being referred to?
    * Keep site-defined objects separate from the vendor-defined objects
    If we're talking about a packaged application, it makes good sense that you shouldn't be creating objects that are owned by schemas that are delivered by the vendor. In the same way that you shouldn't create objects that are owned by schemas that Oracle delivers (i.e. SYS and SYSTEM), it generally makes sense to create any custom objects in a separate schema so that you don't create problems in the future when the vendor delivers an upgrade script. If the vendor decides that it wants to create a view in the future that happens to use whatever name you choose, you don't want the upgrade script to fail because your view already exists in the same schema.
    When asked "how do you use the views in other database, say, the training database, to work on the base tables which are on the
    production?" He gave the following suggestion. I am not sure if the traindb user can execute the view in another.

    As the my_schema user, create the view in your custom schema:
    create view my_schema.my_view as select ... from mydb.table1 etc.; Have the my_schema user grant access on the view:
    grant select on my_schema.my_view to my_traindb_user;
    If you create a database link in database A that points to database B, you can create a view in A that references objects in B. It makes no sense to create a view in the training database that points to the production database. If this is a production object, it should exist in the production database. Probably in a schema in the production database other than the one that the vendor uses to own the application's objects.

    Justin
  • 4. Re: schema issue
    986173 Newbie
    Currently Being Moderated
    +983170 wrote:+
    Views should be created somewhere else, not in the production database.

    Are you sure that he said "database" and not "schema"?

    Exact quote.
  • 5. Re: schema issue
    Srini Chavali-Oracle Oracle ACE Director
    Currently Being Moderated
    Your "expert" is being unreasonable and paranoid. All production objects should reside in the production database and be subject to change control. Moving "view" objects to a non-production database does not solve anything or serve any purpose, IMHO.

    HTH
    Srini

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points