New tablespace, new user with full access to tablespace?

New tablespace, new user with full access to tablespace?

Post by danny de bi » Thu, 11 Apr 1996 04:00:00



Hi,

I'd like to create a new tablespace and a single new user.  I want
to limit the new user's access to the single new tablespace, and I
want him to be able to do anything in this tablespace.

This may be a FAQ, or just a TSQ (Terminally Stupid Question), but
I can not find any doc on how to accomplish this.

Any suggestions?  Many thanks in advance!

- Danny

 
 
 

New tablespace, new user with full access to tablespace?

Post by Mark Styl » Thu, 11 Apr 1996 04:00:00



Quote:>I'd like to create a new tablespace and a single new user.  I want
>to limit the new user's access to the single new tablespace, and I
>want him to be able to do anything in this tablespace.

>This may be a FAQ, or just a TSQ (Terminally Stupid Question), but
>I can not find any doc on how to accomplish this.

>Any suggestions?  Many thanks in advance!

This is assuming you are using Oracle 7, stuff in <>'s is for you
to replace with what you want:

CREATE TABLESPACE <tablespace_name>
DATAFILE <datafile_path> SIZE <size>
<any storage parameters etc>;

CREATE USER <username>
IDENTIFIED BY <password>
DEFAULT TABLEPACE <tablespace_name>
QUOTA UNLIMITED ON <tablespace_name>
<any other user stuff you want>;

You then need to grant the privs you require to the user, such as:

GRANT CREATE SESSION TO <username>;
GRANT CREATE TABLE   TO <username>;
etc.

--
** Mark Styles aka Small       -- Opinions expressed here are my own --   **

**           This whole world's wild at heart and weird on top            **

 
 
 

New tablespace, new user with full access to tablespace?

Post by mla.. » Thu, 11 Apr 1996 04:00:00



Quote:>Hi,

>I'd like to create a new tablespace and a single new user.  I want
>to limit the new user's access to the single new tablespace, and I
>want him to be able to do anything in this tablespace.

>This may be a FAQ, or just a TSQ (Terminally Stupid Question), but
>I can not find any doc on how to accomplish this.

>Any suggestions?  Many thanks in advance!

>- Danny

1) Tablespace example:

CREATE TABLESPACE user1
DATAFILE '\your_path\user1.ora' SIZE xxM
DEFAULT STORAGE (INITIAL xxK NEXT xxK MINEXTENTS x MAXEXTENTS xxx);

fill in the xx part with your default size and storage parameters.

2) Create the user, make tablespace user1 default, give the user a profile,
   grant the user resource on tablespace user1.

GRANT CONNECT TO TEST IDENTIFIED BY EXAMPLE;
ALTER USER TEST DEFAULT TABLESPACE user1 TEMPORARY TABLESPACE TEMP_TS;
ALTER USER TEST PROFILE <your profile>;
GRANT RESOURCE TO TEST;

<your profile> should be replaced with a profile you created or set to
Default.

See the Administrator's Guide for more info.

M.Landa

 
 
 

New tablespace, new user with full access to tablespace?

Post by Chuck Hamilt » Thu, 11 Apr 1996 04:00:00



Quote:>Hi,

>I'd like to create a new tablespace and a single new user.  I want
>to limit the new user's access to the single new tablespace, and I
>want him to be able to do anything in this tablespace.

>This may be a FAQ, or just a TSQ (Terminally Stupid Question), but
>I can not find any doc on how to accomplish this.

>Any suggestions?  Many thanks in advance!

>- Danny

Just create the tablespace, the user, and grant privs to the user.

create tablespace joes_ts datafile 'joes_file' size 10m;
create user joe identified by joes_pswd
  default tablespace joes_ts
  quota unlimited on joes_ts;
grant create table to joe;
--
Chuck Hamilton

Never share a foxhole with anyone braver than yourself!

 
 
 

New tablespace, new user with full access to tablespace?

Post by Sean Michael Dillo » Thu, 11 Apr 1996 04:00:00




> >Hi,

> >I'd like to create a new tablespace and a single new user.  I want
> >to limit the new user's access to the single new tablespace, and I
> >want him to be able to do anything in this tablespace.

> >This may be a FAQ, or just a TSQ (Terminally Stupid Question), but
> >I can not find any doc on how to accomplish this.

> >Any suggestions?  Many thanks in advance!

> >- Danny

> 1) Tablespace example:

> CREATE TABLESPACE user1
> DATAFILE '\your_path\user1.ora' SIZE xxM
> DEFAULT STORAGE (INITIAL xxK NEXT xxK MINEXTENTS x MAXEXTENTS xxx);

> fill in the xx part with your default size and storage parameters.

> 2) Create the user, make tablespace user1 default, give the user a profile,
>    grant the user resource on tablespace user1.

> GRANT CONNECT TO TEST IDENTIFIED BY EXAMPLE;
> ALTER USER TEST DEFAULT TABLESPACE user1 TEMPORARY TABLESPACE TEMP_TS;
> ALTER USER TEST PROFILE <your profile>;
> GRANT RESOURCE TO TEST;

> <your profile> should be replaced with a profile you created or set to
> Default.

> See the Administrator's Guide for more info.

> M.Landa

Doesn't 'GRANT RESOURCE TO TEST' then give that user RESOURCE authority
on any tablespace.  I wasn't aware the RESOURCE privelege was restricted
to a user's default tablespace.

Please correct me if I'm wrong, and where can I find it in the Administrator's
Guide?

r/s

Sean Michael Dillon
DBA / Technical Team Leader
ATLASS Branch, United States Marine Corps

 
 
 

New tablespace, new user with full access to tablespace?

Post by Mark Styl » Fri, 12 Apr 1996 04:00:00




>> GRANT CONNECT TO TEST IDENTIFIED BY EXAMPLE;
>> ALTER USER TEST DEFAULT TABLESPACE user1 TEMPORARY TABLESPACE TEMP_TS;
>> ALTER USER TEST PROFILE <your profile>;
>> GRANT RESOURCE TO TEST;

>Doesn't 'GRANT RESOURCE TO TEST' then give that user RESOURCE authority
>on any tablespace.  I wasn't aware the RESOURCE privelege was restricted
>to a user's default tablespace.

Thats correct, RESOURCE is actually a role, which gives access to
all tablespaces, and also grants CREATE CLUSTER, CREATE PROCEDURE,
CREATE SEQUENCE, CREATE TABLE, and CREATE TRIGGER to the user.

The ALTER USER <username> QUOTA UNLIMITED ON <tablespace>; command
should be used (replacing UNLIMITED with a value if you want to
restrict the space allowed to the user)

--
** Mark Styles aka Small       -- Opinions expressed here are my own --   **

**           This whole world's wild at heart and weird on top            **

 
 
 

New tablespace, new user with full access to tablespace?

Post by Vijay Band » Tue, 16 Apr 1996 04:00:00


create your tablespace first using your create tablespace command
eg:- create tablespace my_test
     DATAFILE 'filename'
     DEFAULT STORAGE 'storage_clause';

then Do your create user command like this

Create user dummy identified dummy
DEFAULT TABLESPACE my_test
quota unlimited;

for more info look for the syntax of create user commannd..

 
 
 

New tablespace, new user with full access to tablespace?

Post by Vijay Band » Tue, 16 Apr 1996 04:00:00


create your tablespace first using your create tablespace command
eg:- create tablespace my_test
     DATAFILE 'filename'
     DEFAULT STORAGE 'storage_clause';

then Do your create user command like this

Create user dummy identified dummy
DEFAULT TABLESPACE my_test
quota unlimited;

for more info look for the syntax of create user commannd..

 
 
 

1. new tablespace Vs new database

Hi,
I am in the process of creating an application which will be used
(atleast now) only to make changes to some tables and save (not update)
the changes as newer versions. The total space used by a single version
of all the records will be about 150Kb. My question is; what are
the adv./diasadvantages of having the tables in a separate tablespace
Vs. having them in a separate database.

Thanks.

2. scan range error

3. Problems Copying User Tables To New Tablespace

4. WWDC pass for Students

5. force index into new tablespace

6. Appoligy

7. revoking users access to system tablespace?

8. How to move tcpip directory to another disk

9. 8i Tablespace removal if someone has deleted the .dbf instead of dropping the tablespace

10. moving from tablespace a to tablespace b

11. Moving a table from one tablespace to another tablespace

12. Help: Tablespace full after creating 2 tables and no records.

13. Tablespace is full ... now what??