Grant two table spaces to a user / Role grants

Grant two table spaces to a user / Role grants

Post by Jawahar Ra » Mon, 16 Aug 2004 04:30:47

Is it possible to grant access to two tablespaces to a user when they are
We have a set of table in tablespace1 and the Audit tables in tablespace
two. Some users need access to both areas
Can we do this

CREATE user user1 identified by XXXX default tablespace1, tablespace2.

if not how can I grant access to the second table to user1

When creating a role to assign to a user, is it better to grant schema
privileges to the role than to the User.

I am creating a role role1 with Connect and resource privileges. but I want
User1 to have SELECT, INSERT and UPDATE on schema1 owned by owner1

So is it better to do this

Create role role1
grant select, insert update on Schema1 objects to role1

creat user1
grant role1 to user1

User1 will be used in a connection string to have database access from a VB

Any help is appreciated.


Grant two table spaces to a user / Role grants

Post by Hans Forbr » Mon, 16 Aug 2004 04:40:02

Kind-of-a funny statement, don't you think? ... 'default' is the one used
when nothing else is specified, so how would it be able to decide between
the two?

What you want is to Grant a specific quota (or UNLIMITED use) in a
TABLESPACE to the user.

Check out the GRANT statement in the SQL Language Reference Manual, or under
the SQL Syntax section at You might also check out
the 'CREATE USER' syntax while you are there.

Or, if you can't be bothered with documentation, fire up the Oracle
Enterprise Manager and point-n-click your way to happiness.



Grant two table spaces to a user / Role grants

Post by Daniel Mor » Mon, 16 Aug 2004 05:37:53

There is, in the sense I think you assume, a relationship between tables
and tablespaces. The table owner needs quota on the tablespace ...
not someone accessing the table.

That said:
1. Create a role with the permissions required
2. Assign the role to the user

Daniel A. Morgan
University of Washington
(replace 'x' with 'u' to respond)