User Privileges

Question

Why does a user needs privileges over his own schema to create packages and triggers?

Answer 1

I can't give you the "official" answer, but I can take a stab at the reasoning behind how it works. On the project I work on, Oracle Developers are the ones that deploy the triggers and packages into the database schema. But we have other teams of Java developers and testers, etc. Once the schema are migrated into the Test and then Production environments, we don't want the testers or the end user applications to be able to arbitrarily modify the triggers and packages associated with that schema, as that could invalidate the integrity of any testing they are doing (or cause even worse issues on the production system).

So within the test/production environments, you want the testers, the Java developers, and the end-user application to be able to read and write data to the schema, but not modify the triggers and packages which encompass the baselined code.

Answer 2

Are you asking why users need particular priviliges (i.e. CREATE TABLE, CREATE PROCEDURE, etc) in order to create particular types of objects in their own schema?

If so, the natural answer would be that good security begins with the principle of least privilege-- that is, a user should only have those privileges that they truly need to do their job and no more. DBAs frequently want to create read-only accounts for users in the production database (business analysts, for example, often need to do different sorts of ad hoc reporting, developers may need access to troubleshoot certain types of problems, etc). If a user were always able to create objects in their own schema, those read-only users would suddenly be able to deploy code to the production database without going through change control or even necessarily testing anything. And that generally leads to a proliferation in code doing basically the same thing (i.e. a dozen analysts each have a procedure to calculate sales tax in their own schemas) but each of which has its own unique signature, logic, requirements, assumptions, etc. And heaven forbid that one of those analysts get fired because the DBA would naturally delete their account, only to find out that some crucial report depended on code that existed only in that analyst's schema.

Answer 3

Why does a user needs privileges over his own schema to create packages and triggers They do not.

Their own schema implies the ownership (or namespace) defined by username.object All a user needs to create a trigger in their own schema is: a table in their own schema the create trigger privilege

All a user needs to create a package in their own schema is: the create package privilege

If the user wants to create objects in another schema or that references other objects, or that requires storage in a tablespace then they will need permissions on those foreign objects.