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 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.

share|improve this question

2 Answers 2

up vote 4 down vote accepted

The system privileges that relate to the object types (CREATE TABLE, VIEW, ...) can be divided in two groups:

  • Those that lack the keyword ANY are only effective on the user's own schema.
  • Those with the keyword ANY (DROP ANY TABLE, EXECUTE ANY PROCEDURE, ...) are effective on all schemas. Consequently, you should restrict the ANY privileges to administrative users.

If you grant an object type privilege (without ANY) such as CREATE 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.

share|improve this answer
    
Perfect. All of the examples of the syntax I was able to find contained the ON some_table portion of the command. I have successfully granted privileges to a role without specifying a target object with GRANT connect TO student_access; –  emhohensee Jun 20 '13 at 16:23
    
CONNECT is a built-in role; you didn't grant privileges to student_access as much as you granted a role through which student_access inherits connect's privileges. –  Adam Musch Jun 25 '13 at 13:50
    
CONNECT was a bad example on my part. I am trying to grant system privileges (I think that's right) to a role so that instead of typing GRANT CONNECT, CREATE TABLE, ALTER TABLE... to username; I can just type GRANT student_access TO username; Is that not what this is doing? –  emhohensee Jun 26 '13 at 1:33

Oracle, unlike DB2, doesn't support GRANT SELECT ON some_schema TO some_role or GRANT SELECT ON some_schema TO some_user. It's up to you to ensure that the privileges you want granted are granted; I find it a best practice to save the grants against schema objects with the DDL I used to define those objects.

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.