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

From: Bhooshan S. Prabhu <bhooshan.prabhu_at_citicorp.com>
Date: 8 Jun 2001 02:06:05 -0700
Message-ID: <2cf4efe2.0106080106.36b94d9b@posting.google.com>

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

Hello Dino,

Option A :
1. Create Public Synonyms for all tables of schema dm_admin 2. Grant SELECT privilege for all tables of schema dm_admin to dm_user.

Option B :

1. Grant 'CREATE SYNONYM' privilege to dm_user
2. Grant SELECT privilege for all tables of schema dm_admin to dm_user.
3. Create synonyms in schema dm_user for all tables of schema dm_admin

I'm just mentioning SELECT here but, if your SQL does any other DML operations you shall have to grant INSERT / UPDATE / DELETE privileges appropriately.

Regards
Bhooshan Received on Fri Jun 08 2001 - 04:06:05 CDT