Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: How to suppress the schema for different user to run a SQL?

Re: How to suppress the schema for different user to run a SQL?

From: Randall Roberts <randall_at_filer.org.nospam>
Date: Fri, 8 Jun 2001 08:37:20 -0700
Message-ID: <3b20f15a_2@news.pcmagic.net>

I personally think that you are already coding it correctly. I am a great proponent of prefixing all references in your code to make it schema independent. I don't like synonyms. Synonyms create a new object in the user's schema so that the user can't have their own table of that name. If you use a public synonym, a user can have a table of that name but it overrides the synonym.

I also don't like creating a single account for all users. You say the account is query-oriented. Are the accounts that do DML Oracle accounts? I don't care for letting users in a shared account do DML. I realize that their are some cases where this is necessary, i.e. you are Amazon.com and don't want to create an account for every person you ever sell to. But Oracle has given us a built in system of accounts and roles to simplify security issues. By using one account you have to resort to identifying the user by their OS account, or building your own user table in your schema and coding your own password validation. I've seen cases where both approaches have been good ideas (I've even done one once), but I believe they are special cases. It never ceases to surprise me when someone tells me that they bought an Oracle application from a high dollar consulting firm and it uses one account for everything.

Best!

Randall

Dino Hsu <dino1_nospam_at_ms1.hinet.net> wrote in message news:o8h0itg7mem9l94u0sk0q1m07l2ckbp9jl_at_4ax.com...
> Dear all,
>
> We have an OLAP database, where all tables are created with
> 'dm_admin', an administrative user account, while are queried by
> 'dm_user', a query-oriented user account (shared by all query users).
> We have SQL's that are coded without the schema 'dm_admin' to be used
> by 'dm_user'. However, if the schema is not prefixed before the
> objects referenced, the objects will not be found by 'dm_user', and it
> is not appropriate for query users to use 'dm_admin'. What's your
> suggestions about this? Thanks in advance.
>
> Dino
>
Received on Fri Jun 08 2001 - 10:37:20 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US