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

On Fri, 8 Jun 2001 08:37:20 -0700, "Randall Roberts" <randall_at_filer.org.nospam> wrote:

>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
>>
>

Randall,

I totally agree that user accounts are meant for identifying users, not 'roles' or a group of users. However, the reality tells a different story.

As far as internal users are concerned, if we create for each user an Oracle account, obviously we'd better integrate them with the primary OS user account system, such as a Windows NT/2K primary domain rather than create an independent set of user accounts. If we have many Oracle databases and applications, we'd better have one single set of Oracle user accounts rather than have 'many' sets of them, which, unfortunately, might not be possible for some existing or out-sourced systems. Furthermore, we'd better integrate user accounts of Windows NT/2K, Lotus Notes, Novell NetWare, and Unix's (IBM RS/6000, Sun Solaris, HP UX), etc., but that's not easy. Maybe directory service can solve some of the problems, but don't forget directory service itself has several versions, IBM, Microsoft, Oracle, Novell, etc.. For so many reasons, we are forced to give up the thinking of a unified set of user accounts. We then will go back down to the management issues of the outstanding applications. I created only two accounts, dm_admin, dm_user in an OLAP application, which, I think, is a good choice. Firstly, only two user accounts instead of many need to be created. Secondly, even if I can adopt the OS accounts (i.e., Windows NT/2K domain), I don't know how to adopt the OS groups as well, which implies the hustles of granting or revoking roles back and forth. Thirdly, Cognos BI (Business Intelligence) tools such as Impromptu has it's own security system, the users don't really see Oracle user accounts, they see only Cognos user classes, which will in turn log on to Oracle with pre-defined user accounts.

As far as the external users, typically random Internet users, I don't see why different Oracle user accounts are necessary. Even in the case of B to B partners, special authentication procedures should be provided, but not necessarily for Oracle user accounts. However, I don't have much idea in this aspect, I hope others can comment on this too.

Dino Received on Fri Jun 08 2001 - 21:34:22 CDT