Home » RDBMS Server » Security » How to have essentially DML and no DDL for a user schema? (Oracle 10G, version 2, UNIX)
How to have essentially DML and no DDL for a user schema? [message #301476] Wed, 20 February 2008 16:53 Go to next message
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.
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 Go to previous messageGo to next message
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');
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 Go to previous message
Michel Cadot
Messages: 59822
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
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:
  


Current Time: Sat Nov 29 03:05:26 CST 2014

Total time taken to generate the page: 0.09430 seconds