Hello,

I would like to create a database user in ASA8, that is allowed to
create, alter and drop tables and do everything like select, delete and
update within hits own tables. Also, this user shall have read access to
certain tables.

What about the namespaces? Is it possible that the users sees tables
created by DBA without using dba.tablename to access it? I would prefer
to have both users (the new and DBA) to share the same namespaces, so
noone can create tables that already exist at the other user name and
each can access tables from the other user. Which commands do I have to use?

Regards
Marten

Marten,

see my notes inline.

HTH
Volker

Marten Lehmann wrote:
grant connect to MyUser identified by abc;
grant resource to MyUser;

For all tables:
grant insert,update,... to MyTable for MyUser;


1. Generally, a user can access objects from different owners when he
has appropriate rights to do so. Usually "grant insert, update,... to
MyTable..." is necessary - see above.
2. If 1. is fulfilled, a user can access objects from different owners
without qualifying the owner name only when he is in the owner's group.
Therefore

grant group to "DBA";
grant membership in group "DBA" to MyUser;
(untested syntax!)

So you could evenly turn MyUser in a group, too, and make DBA a member
of that group.

However, it's still possible for each user to create its own objects
without interfering with the other namespace. AFAIK, it's not possible
that both users create objects in the same namespace as long as they use
ordinary DDL statements.

One way to accomplish that would be to create SPs to create/alter/drop
tables and the like. In the SP code, you could of course check if an
object for the "other" user is already existing, and then do not create
it. (You could also make MyUser to be able to create DBA objects by this
without giving MyUser RESOURCE permissions - i.e. he could ONLY use DDL
via these SPs. Then DBA and MyUser would both only use the DBA namespace.)

However, at least a DBA would always be able to use dirsct DDL statements.