Re: access to database

From: <jl34778_at_corp02.d51.lilly.com>
Date: 31 Aug 94 20:10:19 EST
Message-ID: <1994Aug31.201019.1_at_corp02.d51.lilly.com>

Granting 'select any table' would allow the user to see any table in the database, which is a bit of overkill. A much better way to do this in ORACLE7 is to use roles. You create the role once, grant access on the tables to the role, once, then grant the role to each user as you create it.

For example:

create role application_user;

grant select, update on tablea to application_user;
grant select, update on tableb to application_user;
grant select, update on tablec to application_user;

etc...

When you create a user:

create user fred identified by wilma

	default tablespace users
	temporary tablespace temp
	quota unlimited on users;

grant application_user to fred;

Note that this role only has object privileges. The user will also need system privileges such as CREATE SESSION. You may want to grant that privilege to APPLICATION_USER, or create a separate role.

In article <34320j$eu_at_pipe1.pipeline.com>, sri_at_pipeline.com (Srinivas Gurram) writes:
> if you are using oracle7 you can use grant select any table to
> userid;
> grant insert any table to userid;
> grant update any table to userid;
> and so on...
>
> and if you are using oracle6 do the following:
> get into sqlplus with the userid who owns the tables
> sqlplus tableowner/password
> set feedback off;
> spool temp;
> select 'grant all on ', table_name, ' to userid;'
> from user_tables;
> spool off;
> exit
> edit the temp file in any editor to delete any unnecessary text
> and get back in to sqlplus
> sqlplus tableowner/password
> _at_temp
>
> that should do it.
>
> joya_at_mhd1.moorhead.msus.edu (Joya M. Sharif) wrote:
>
>

>>Subject: access to database
>>From: joya_at_mhd1.moorhead.msus.edu (Joya M. Sharif)
>>Date: 24 Aug 94 07:58:00 -0500
>>Organization: Moorhead State University
>>
>>Hi EveryBody:
>>
>>I have a database(35 tables), and like to create a new 
>>user so that  he/she can access to database with their 
>>loginid, and password.
>>
>>They way I am doing is very lenthy that is access to 
>>each table. I am  sure there must be better way. Will 
>>appreciate any help.
>>
>>Thanks
>>joya (joya_at_mhd1.moorhead.msus.edu)
-- 
Bob Swisshelm                | swisshelm_at_Lilly.com     | 317 276 5472
Eli Lilly and Company        | Lilly Corporate Center  | Indianapolis, IN 46285

Received on Thu Sep 01 1994 - 03:10:19 CEST