Re: retroactive GRANT privledges to ROLES

DA Morgan <damorgan_at_exxesolutions.com> wrote in message news:<3E83A027.3456C980_at_exxesolutions.com>...
> mmeandro wrote:
>
> > My friend says the following, IN CHRONOLOGICAL ORDER:
> > 1. Make user SCOTT a ROLE of STUDENT
> > 2. GRANT New privledges to the ROLE STUDENT.
> > 3. User SCOTT doesnot automatically get the new privledges of STUDENT
> > you must REMOVE user SCOTT and then make him ROLE of STUDENT again to
> > pick up the new privledges.
> >
> > Is this true?
> > Is there some "flag" my friend forgot to set when setting up the
> > database so that ROLE changes are retroactive to the individual users.
> > This seems strange but according to him he has learned the "hardway"
> > this is true.
>
> No. Your friend knows little about Oracle. Do not take advice from this
> person. They may or may not be a friend but they are not a source of
> knowledge about Oracle.
>
> As soon as you grant the role to the schema owner the privileges
> immediately take effect. Dropping a user drops all tables, etc.
>
> Daniel Morgan

Privileges

Privileges
There are two kinds of privileges: system and object. System privileges give system-wide abilities or the ability to perform an action on an object type. Object privileges are the ability to perform an action on a particular object (table, view, package, etc.)

GRANT system privileges to a user, role, or PUBLIC (everyone):

GRANT { system_privilege | ALL PRIVILEGES | role } TO

{ user | role | PUBLIC } {WITH ADMIN OPTION} ;

The optional clause WITH ADMIN OPTION allows the recipient to grant his system privileges to others. Dictionary views DBA_SYS_PRIVS and USER_SYS_PRIVS track system privileges granted. There are over 90 different system privileges; be generally familiar with them.

Object privileges are rights on specific objects. You can grant them if you're the object owner (or have the system privilege GRANT ANY PRIVILEGE, or have been given an object privilege WITH GRANT OPTION so you can pass it on). Dictionary tables DBA_TAB_PRIVS, USER_TAB_PRIVS, and ALL_TAB_PRIVS track object privileges. The object privileges the object owner can grant include ALTER, DELETE, INDEX, INSERT, REFERENCES, SELECT, UPDATE, EXECUTE, and READ.

There are also privileges that can be granted only at the column level (for tables, views or sequences). These are INSERT, UPDATE, and REFERENCES only. Column privileges are tracked in DBA_COL_PRIVS, USER_COL_PRIVS_RECD and USER_COL_PRIVS_MADE.

Revoke (take away) grants through the REVOKE statement. Object privileges automatically cascade when revoked, while system privileges do not. So if you granted a system privilege WITH ADMIN OPTION, you still must individually REVOKE whatever system privileges were subsequently granted with that authority.

Example: I grant some table and system privileges to John (WITH GRANT OPTION and WITH ADMIN OPTION, respectively). I revoke both grants from John. All table privileges he has given out are rescinded, but any system privileges he gave out remain in force. Received on Sun Mar 30 2003 - 21:50:12 CST