Re: Create User

A better approach would be to create a role -which is, in fact, what the original poster was asking for.

So here goes for nothing:

create user fred identified by ginger temporary tablespace TEMP;

create role readtables;

grant select on ted.table1 to readtables;
grant select on ted.table2 to readtables;
grant select on ted.table(n) to readtables;  (repeat as necessary)

grant create session to readtables;

grant readtables to fred;

The benefit of doing it this way is that the (potential) multiplicity of grants only affects the role. Performance for Fred at parse time will improve somewhat when only the single rolw has to be checked for access rights.

The other benefits of roles also kick in: a subsequent grant of a select on newtable to readtables; will immediately be picked up by Fred. A direct grant of that permission would require a logoff and re-login. You also have the option of creating a number of "readtables"-like roles, and then enabling some and disabling others. Selective availability of read privileges.... nice.

Incidentally, I agree with Daniel: anyone advocating granting 'connect' to a user as a means of permitting them to connect to the Instance deserves to be strung up by the tenderest portions of their anatomy.

Regards
HJR "Daniel Morgan" <dmorgan_at_exesolutions.com> wrote in message news:3D0E0975.5C012562_at_exesolutions.com...
> Thedevastator wrote:
>
> > Can anyone help me creating a user from the SQLPLUS console ?
> >
> > create user user1 idientified by user1
> >
> > i know very little about oracle command line paremeters to create a
> > user.. with only readonly access..
> >
> > ive read something about roles .. could someone give me a command line
> > example so the new user has only Read/only access to all my tables ??
> >
> > any help appriciated
> >
> > thanks
> >
> > --
> > Posted via dBforums
> > http://dbforums.com
>
> Well you have received some answers that send shivers up my spine.
>
> The first grants permissions irrelevant to the requirement. The second
> doesn't even allow the user to connect to the database.
>
> To create a user with read-only access:
>
> CREATE USER <user_name>
> IDENTIFIED BY <password>
> TEMPORARY TABLESPACE <temp_tablespace_name>;
>
> To allow that user to connect to the database DO NOT grant them the
> CONNECT role which has nothing to do with connecting to the database ...
> rather:
>
> GRANT CREATE SESSION TO <user_name>;
>
> And if you want that user to be able to create synonyms to make it easier
> to access objects:
>
> GRANT CREATE SYNONYM TO <user_name>;
>
> though this is entirely optional.
>
> Finally, log on as any schema owner with objects for the new schema to
> query and:
>
> GRANT SELECT ON <object_name> TO <the_new_schema_name>;
>
> and repeat once for each object they need to access.
>
> Daniel Morgan
>
Received on Mon Jun 17 2002 - 18:52:37 CDT