| Force one TableSpace [message #339429] |
Thu, 07 August 2008 08:45  |
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 #339462 is a reply to message #339431] |
Thu, 07 August 2008 09:49  |
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 #339495 is a reply to message #339469] |
Thu, 07 August 2008 11:31 |
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 |
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 #340635 is a reply to message #339805] |
Wed, 13 August 2008 09:19 |
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 |
 |
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
|
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
