OraFAQ Forum: Security » How to have essentially DML and no DDL for a user schema?

Home » RDBMS Server » Security » How to have essentially DML and no DDL for a user schema? (Oracle 10G, version 2, UNIX)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

How to have essentially DML and no DDL for a user schema? [message #301476]

Wed, 20 February 2008 16:53

stevekerver
Messages: 19
Registered: January 2008

Junior Member

Ok- this may seem like an overly pedestrian question, but...

How would I ensure that a user has ONLY the permissions to SELECT, INSERT, UPDATE, DELETE and EXECUTE PACKAGES/PROCEDURES/FUNCTIONS in their OWN schema, and no more?

So, I don't want them to be able to create or drop tables, indexes, functions, packages, etc.- even in their own schema.
But, they can connect, and use the objects that are created for them (by SYS), and no more.

...Assume this functionality would be implemented using a role, which would be customized, and then granted to the user.
Assume the user would have unlimited (DEFAULT) resources.
Also assume that the best method of implementation would be one where the role is created just once, and then does not require modification if SYS adds a new table / trigger / procedure / function / view / package, etc to said schema...

...The idea behind this is to have a main user that our application server connects to (and who owns the database objects for our application), but to which our developers (or hackers) can't tinker with in order to play at being DBA.

Any help would be greatly appreciated.

Report message to a moderator

Re: How to have essentially DML and no DDL for a user schema? [message #301510 is a reply to message #301476]

Wed, 20 February 2008 22:09

borg
Messages: 5
Registered: February 2008
Location: Indonesia

Junior Member

have u try with Product_user_profile table ?

SQL> connect system/oracle

SQL> insert into product_user_profile (product,userid,attribute,char_value)
values('SQL*Plus','TEST,'DROP','DISABLED');

Report message to a moderator

Re: How to have essentially DML and no DDL for a user schema? [message #301582 is a reply to message #301476]

Thu, 21 February 2008 01:27

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Quote:

How would I ensure that a user has ONLY the permissions to SELECT, INSERT, UPDATE, DELETE and EXECUTE PACKAGES/PROCEDURES/FUNCTIONS in their OWN schema, and no more?

Don't give them other privileges.

Quote:

I don't want them to be able to create or drop tables, indexes, functions, packages, etc.- even in their own schem

Don't grant them roles or privileges to create objects.
Don't give them quota on any tablespace.

Quote:

The idea behind this is to have a main user that our application server connects to (and who owns the database objects for our application),

This is a bad idea.
Each user must have their account with only the privileges they need to do their job.
Owner schema should be locked and never use (or maybe during application upgrade).

Regards
Michel

Report message to a moderator

Previous Topic:	What view in the data dictionary shows grants for procedures
Next Topic:	Required slight modification to oracle supplied Password Verification Function

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 19 11:29:16 CDT 2024