Re: How do I make a database accessible by other users?

there are a number of ways to accomplish what you need to accomplish and i will offer you one. i am sure someone here on the list will find several reasons why you should not do it...that's just the nature of list behaviour for some reason. that being said, here is what you can do.

1. oracle has the concept of ROLEs which is synonymous with GROUPs in most other system concepts. there is a predefined group called PUBLIC (any database user). you can also create your own, such as DBUser, or <App>User where <App> is the name or abbreviation of the specific application (i recommend the latter). suppose the app is called Xyzzy. in SQL*Plus or SQL*Worksheet, do the following:

    create role XyzzyUser;

2. A role can be granted privileges, such as SELECT, UPDATE, INSERT, DELETE (on tables and views) and EXECUTE on procuredures and such. for simplicity, assume users have only select and insert on all tables (they could have none, one, or more....any combination) and there are no stored procedures or packages to be concerned with. in SQL*Plus or SQL*Worksheet run the following SQL statement....which generates another SQL statement which you will want to save in a file for later use...while logged in as the owner of the existing database (no need to export/import or any such thing):

    select 'grant select, insert on '||rtrim(tablename)||' to XyzzyUser;'     from user_tables;

3. edit the file produced by the above SQL statement (which should be a grant statement for each table in the schema) to clean up the miscellanous lines created by the SQL utility. then run the SQL statements which will grant the stated privileges to the role XyzzyUser.

4. for each user that should use the application with the select and insert privileges on all tables, connect as dba and do the following:

    create user SomeUser identified by SomePassword     default tablespace users
    temporary tablespace temp;

    grant connect, XyzzyUser to SomeUser;

the "connect" privilege is required to connect to the database to do anything. the XyzzyUser is the role with the privileges select and insert on all tables.

another approach would be to create one role for each privilege on all tables, ie XyzzySelect, XyzzyInsert, XyzzyUpdate, XyzzyDelete and then grant each role as needed....again, these are applied to all tables in the schema which may not be appropriate.

thus, if you have the time, the patience, or the need, you can identify groups of users, ie, Accounting, HR, Payroll, Operations, etc, who each need different privileges on various subsets of tables. in this case, you can define a role with only those privileges on each table required by each group. then grant the role (group) to each user as appropriate. roles can be aggregated so, for example, the CFO might have Accounting, Payroll, and Operations roles which would give the CFO user cumulative privileges for all three roles.

now i am sure someone will mention synonyms. a synonym is just that....calling one thing something else. there are "private" synonyms (defined within each user's own area) and PUBLIC synonums, defined globally in the database. the advantage of a public synonym is in the case where your application, Xyzzy, is used by almost every user (or a good portion of them) and the object names in the Xyzzy schema do not conflict with object names in other schemas (which may need to be public). then, rather than referring to Xyzzy objects with a fully qualified name in all cases (which you really SHOULD do...), the synonym may be used in its place, ie the table Employee in Xyzzy would normally be referenced as Xyzzy.Employee. by creating a public synonym, all users may refer to the table using the public synonym Employee.

    create PUBLIC synonym Employee for Xyzzy.Employee;

like we did with the grant statement, you can generate a SQL script to create public synonyms for all objects:

    select 'create public synonym '||rtrim(object_name)||' for Xyzzy.'||rtrim(object_name)||';'

    from user_objects;

then run the (edited) result of the above as a SQL script.

HTH steve long
"The world's best technology strategist." (TM)

"MikeWJ" <mjenkins_at_jcn.net> wrote in message news:go1ndtg85p99uoinupdt34pddp9hitiptv_at_4ax.com...
> Last November I was handed the (many) Oracle CD's and told to install it
 on a
> server...and make it work. With NO previous experience at installing
 databases,
> or anything (for that matter) on a WnT server, I tried.
>
> Backups work.
> I can run "job" on the console.
> I had the existing dba transfer the databases and their structures from a
> VAXcluster (I'm the system manager) to the NT server. The old databases
> Ingres & Coda. He put all of the info under his username.
>
> Now I, somehow, have to re-put all of this data under a "general" user,
> called dbowner, so that all of our client users may have access to it.
>
> My progress, such as it is, so far?
>
> @#$%^& Only the fellow who created it, myself, and one other admin can
 access
> the database.
>
> Someone said to export then import the data. Import to what, dbowner?
 How?
> After I started re-reading the manuals, I got to be more confused. I
 tried to
> contact a dba at another site, but was greeted with "This doesn't look
 ANYTHING
> like what WE use! Why didn't you follow (our) standards?"
>
> Me> because YOU (or your boss) NEVER gave us a copy of them!
> dba> Oh....I'll send them to you.
>
> And, yes, I did fill the dba in on my background and general lack of
 experience
> with databases, especially Oracle.
>
> Now I'm turning to the one source that's always been helpful---the
 newsgroup.
> Any help that you can give is greatly appreciated.
>
> After 31 years in MIS, I have read the manuals AND I'm STILL confused.
>
> MikeWJ
Received on Mon Apr 16 2001 - 20:26:28 CDT