Home » SQL & PL/SQL » SQL & PL/SQL » Grant Privileges on schema
Grant Privileges on schema [message #232830] Mon, 23 April 2007 08:49 Go to next message
virjil
Messages: 8
Registered: April 2007
Junior Member
Hi,
I'm trying to create a user with grants on particular schema, not on objects on schema.

I want to allow this user to create new tables, drop tables, and manage objects on another schema without give a DBA role to it.

Do u know if it's possible to do it?

I tried to give (for example) create any table grant, but in this way the user is able to create a table also in system schema... but i don't want to.

Thanks in advance

Alex
Re: Grant Privileges on schema [message #232832 is a reply to message #232830] Mon, 23 April 2007 08:56 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can't do that.
What is your business need?
Why do you want to do that?
What is your Oracle version?

Regards
Michel
Re: Grant Privileges on schema [message #232833 is a reply to message #232830] Mon, 23 April 2007 09:04 Go to previous messageGo to next message
virjil
Messages: 8
Registered: April 2007
Junior Member
My Oracle Version is 9.2.0.7

My need's to have a User (see it like a High level User) that can manage only user schemas.

He's not a DBA, cause he don't need to.

For example, if I have 2 schemas (WEB and SECURITY) a want to have a user that can write on both schemas, but only on'em.

He can do:
create table web.mytabel (...etc)
and
create table security.mytabel (...etc)

but he can't do:
create table system.mytable (...etc) or xdb.mytable (...etc)

Re: Grant Privileges on schema [message #232834 is a reply to message #232833] Mon, 23 April 2007 09:06 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
But why does he need to create a table from time to time to another schema?
Tables are fixed objects. There are created with a release of your application. They don't need to be created on the fly.

Regards
Michel
Re: Grant Privileges on schema [message #232838 is a reply to message #232830] Mon, 23 April 2007 09:26 Go to previous messageGo to next message
virjil
Messages: 8
Registered: April 2007
Junior Member
I tell you that he need to Smile it's not important why !

So... the answer is that it's not possible or that also u don't know how to do it? Razz
Re: Grant Privileges on schema [message #232840 is a reply to message #232838] Mon, 23 April 2007 09:32 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I try to help you. You don't want to answer it's up to you, you can't take advantage of my help.

I already said that you can't do what you want.
A user may have 3 options and only 3:
- he can't create table
- he can create table only in his schema
- he can create table in ALL schemas

Regards
Michel
Re: Grant Privileges on schema [message #232841 is a reply to message #232840] Mon, 23 April 2007 09:39 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
I'm thinking in the direction of DDL triggers but I'm not a huge fan of this approach...

MHE
Re: Grant Privileges on schema [message #232842 is a reply to message #232830] Mon, 23 April 2007 09:39 Go to previous messageGo to next message
virjil
Messages: 8
Registered: April 2007
Junior Member
Ok... I got it.

Thanks
Re: Grant Privileges on schema [message #232843 is a reply to message #232841] Mon, 23 April 2007 09:50 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes, it can be done granting all privileges to a user and controling the scope with a DDL trigger.
You can also create a procedure in the target schema to allow the creation of objects.
But all these are just workarounds to force an implementation which contains a flaw in its logic.
Oracle is not designed to create objects on the fly and does not need it.
Trying to code in Oracle that way is like driving a nail with a screwdriver. It can be done... Thumbs Down

Regards
Michel
Re: Grant Privileges on schema [message #232920 is a reply to message #232843] Tue, 24 April 2007 01:27 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
I agree with Michel: it is not a very elegant solution but if you really insist, have a look at DDL triggers. They are discussed in the SQL Reference. Without checking, I think something like this would do:
CREATE OR REPLACE TRIGGER no_sys_objects
BEFORE CREATE
ON SCHEMA

BEGIN
  IF ora_dict_obj_owner IN ('SYS','SYSTEM')
  THEN
    raise_application_error(-20001,'You cannot create SYS/SYSTEM objects');
  END IF;
END no_sys_objects;
/


Again: WARNING, unchecked code!

MHE
Re: Grant Privileges on schema [message #232939 is a reply to message #232830] Tue, 24 April 2007 02:56 Go to previous message
virjil
Messages: 8
Registered: April 2007
Junior Member
I agree with you... this is not an elegant solution to use DDL triggers as workaround.

I think that a middle level between user and DBA it's useful... but maybe it's only my opinion.

I'll check for another solution, and if I found it, I'll post here.

Thanks
Alex
Previous Topic: number datatype
Next Topic: ORA-00942: table or view does not exist: v$locked_object used in Fucntion
Goto Forum:
  


Current Time: Fri Dec 09 05:52:59 CST 2016

Total time taken to generate the page: 0.09538 seconds