I'm getting to the stage where I'm starting to grant rights to our Analysts/Developers, and I'm already a bit confused. Here was our theoretical plan:
Create a Schema/User for each application (call them APP1, APP2, APP3), then create an analyst role for each schema (call them ANALYST_APP1, ANALYST_APP3, ANALYST_APP3) which would allow anyone who was a member of that role to create objects for that one schema. That way, people wouldn't have to log in as the schema owner to create objects, and we could prohibit unauthorized object creation/modification based upon role membership.
Is this even possible? I see the CREATE TABLE system priviledge for example. What I don't see is how you'd give ANALYST_APP1 the CREATE TABLE right for only the APP1 schema. Really, it looks like there's no relationship between schema and system priviledges unless you're logged in as the schema owner.
Where does the CREATE TABLE priviledge fit in, then? As opposed to the CREATE ANY TABLE?
If CREATE ANY TABLE allows a user to create a table in any schema, then I'm assuming that CREATE TABLE is specific to a single schema, but I don't see how you constrain the CREATE TABLE system priviledge to a single schema.