I am trying to create a role to simplify granting a set of privileges that I often give to users. I am wondering if there is any way to grant these privileges on only the objects owned by their schema without knowing what the schema name will be ahead of time?
For instance, I want to assign the role student_access
to the users JOE
and MARY
without having to specify what tables or procedures they have these privileges on. They should have all privileges that the role grants on all objects owned by their schema.
Thanks.
Best Answer
The system privileges that relate to the object types (
CREATE TABLE
,VIEW
, ...) can be divided in two groups:ANY
are only effective on the user's own schema.ANY
(DROP ANY TABLE
,EXECUTE ANY PROCEDURE
, ...) are effective on all schemas. Consequently, you should restrict theANY
privileges to administrative users.If you grant an object type privilege (without
ANY
) such asCREATE TABLE
to a role, all users of this role will be able to assert this privilege on their own schema. Consequently you can grant these privileges to a role and the users will inherit such rights (on their own schema) when they are granted the role.