Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am configuring a 10g Express database on Windows 2003 that will be the back end for a public web server. There are many other things to do to harden the database but a good place to start is user privileges. I know the user needs Connect and Create Session privileges. All the tables, procedures and packages are owned by them.

Are there any other privileges required for basic CRUD operations on their own schema?

share|improve this question

1 Answer 1

up vote 3 down vote accepted

There is no need to grant the CONNECT role. In 10.2, Oracle finally reduced the set of privileges assigned to that role to just CREATE SESSION but in previous versions, that role has many more privileges than the name would imply.

It would be more secure to create one users-- one that owns the tables, procedures, etc. but that does not have CREATE SESSION privileges and one that has appropriate privileges on the various objects that the application can use to connect to the database. That allows you to do things like prevent the application user from dropping tables or from deleting data from logging tables.

Additionally, the owner of the tables is going to need to be granted quota on whatever tablespace or tablespaces that user's tables are going to be created in. You could grant the owner the UNLIMITED TABLESPACE privilege but it is more secure to grant them a smaller quota on whatever tablespaces they actually need to use.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.