Home » RDBMS Server » Server Administration » Force one TableSpace (oracle 10g)
Force one TableSpace [message #339429] Thu, 07 August 2008 08:45 Go to next message
jaume
Messages: 5
Registered: August 2008
Junior Member
Hi all,
I am using oracle in my enterprise and I need to know if it's possible to do when create a user and assign a tablespace if I can force the create user to use and only use one tablespace (for example USER).
This is because I created one user for the developers, and this user can create another users, but I need that this new user only works in the tablespace USER.
Thanks in advance.
Jaume
Re: Force one TableSpace [message #339430 is a reply to message #339429] Thu, 07 August 2008 08:51 Go to previous messageGo to next message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

Oracle Documentation

CREATE USER
Re: Force one TableSpace [message #339431 is a reply to message #339429] Thu, 07 August 2008 08:51 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quota are there to allow/forbid to use space only in some tablespaces.
Of course, you must not give them "unlimited tablespace" privilege or "resource" role.

Regards
Michel
Re: Force one TableSpace [message #339462 is a reply to message #339431] Thu, 07 August 2008 09:49 Go to previous messageGo to next message
jaume
Messages: 5
Registered: August 2008
Junior Member
Thank you very much for your answers but, I don't trust in my developers whose create the new users, and then I don't believe that these users do the things in the right manner.
I need a way to create one user and then give this new user that I created to my developers and the developers then with this user will create new users without my intervention. This new users should be only use the USER tablespace.
Can I tell to oracle: Give all the space in the tablespace USER to the users created by SuperuserOfDevelopers and no space for this users in the others tablespaces?, or better
Can I force all the users created by SuperuserOfDevelopers to use ONLY the USER tablespace?
Thank you very much
Re: Force one TableSpace [message #339469 is a reply to message #339462] Thu, 07 August 2008 10:02 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
the developers then with this user will create new users without my intervention.

Not if you don't grant them CREATE USER privilege (or DBA role).

Quote:
Can I tell to oracle: Give all the space in the tablespace USER to the users created by SuperuserOfDevelopers and no space for this users in the others tablespaces?,

Yes, use quota as I said and don't grant them DBA role (in addition to the previous things I said in my previous post).

Quote:
Can I force all the users created by SuperuserOfDevelopers to use ONLY the USER tablespace?

You (as a DBA) create a procedure to create a user as you want and grant execute privilege to this procedure to SuperuserOfDevelopers but does not grant him CREATE USER.

Regards
Michel
Re: Force one TableSpace [message #339495 is a reply to message #339469] Thu, 07 August 2008 11:31 Go to previous messageGo to next message
jaume
Messages: 5
Registered: August 2008
Junior Member
Ok thanks, I think that the best solution for my configuration is the last one, creating a procedure.
Thank you very much, I will create this and then I will put here for verification.
Re: Force one TableSpace [message #339794 is a reply to message #339495] Fri, 08 August 2008 09:10 Go to previous messageGo to next message
jaume
Messages: 5
Registered: August 2008
Junior Member
Ok, I did it. The solution for my company is the second option that Michel gives me:

1. Create a role with the permissions you want (except CREATE USER), in my example 'customizedperms'

2. Create a procedure with the user SYS with the following:

CREATE OR REPLACE PROCEDURE "SYS"."CREAUSUARIO" (
name_in IN varchar2, pwd_in IN varchar2
)
as
begin
EXECUTE IMMEDIATE 'CREATE USER '||name_in||' IDENTIFIED BY '||pwd_in||' DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP QUOTA UNLIMITED ON USERS';
EXECUTE IMMEDIATE 'GRANT customizedperms,connect to '||name_in;
end;

3. Then assign execution permissions to the user that is allowed to create new users so that he execute the procedure.

And that's all, so to execute the procedure:
# execute creausuario ('newuser','pwd');

Thank you very much
Re: Force one TableSpace [message #339805 is a reply to message #339794] Fri, 08 August 2008 10:20 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
In your procedure take care of SQL injection.
As you are in 10g you can use dbms_assert to verify the values that are passed.

Regards
Michel
Re: Force one TableSpace [message #340635 is a reply to message #339805] Wed, 13 August 2008 09:19 Go to previous messageGo to next message
jaume
Messages: 5
Registered: August 2008
Junior Member
Michel Cadot wrote on Fri, 08 August 2008 10:20
In your procedure take care of SQL injection.
As you are in 10g you can use dbms_assert to verify the values that are passed.


Thanks Michel I rebuild the procedure with dbms_assert and this is the result,

(
name_in IN varchar2, pwd_in IN varchar2
)
as
n1 VARCHAR2(50);
n2 VARCHAR2(50);
begin
n1 := name_in;
n2 := pwd_in;
EXECUTE IMMEDIATE 'CREATE USER '||
DBMS_ASSERT.SIMPLE_SQL_NAME (n1) ||' IDENTIFIED BY ' ||
DBMS_ASSERT.SIMPLE_SQL_NAME (n2) || ' DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP QUOTA UNLIMITED ON USERS';
EXECUTE IMMEDIATE 'GRANT scytl,connect to '||n1;
end;

Is this correct?, are the n1, n2 necessary?, Is the sql injection avoided?

Thank you very much
Re: Force one TableSpace [message #340641 is a reply to message #340635] Wed, 13 August 2008 10:28 Go to previous message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Is this correct?, are the n1, n2 necessary?, Is the sql injection avoided?

Yes, No, Yes.

Don't use CONNECT role, use CREATE SESSION privilege.

Regards
Michel
Previous Topic: Triggers not working
Next Topic: Problem creating database
Goto Forum:
  


Current Time: Tue Dec 06 12:39:20 CST 2016

Total time taken to generate the page: 0.15529 seconds