WITH GRANT OPTION GRANTS: The transitive property permitting

The GRANT statement is used to allow generic or specific permissions on objects permissions to users in the database.

The same syntax is:

GRANT [privilegios_de_sistema | roles]
TO [user | role | PUBLIC] {} WITH GRANT OPTION

to grant generic licenses

GRANT [ALL PRIVILEGES} {| SELECT | INSERT | UPDATE | DELETE] ON object
TO [user | role | PUBLIC] {} WITH GRANT OPTION

to grant permissions on objects

The syntax is very simple, and privileges can grant the user the owner of the objects, or a user with privileges to grant permissions on objects that are not yours (DBA's).

What I wanted to comment on is the use of the WITH GRANT OPTION option, which allows the user to whom you have granted permissions can in turn grant other users.

Show the usefulness of this option with an example:
Imagine you have a user 'U_VISTA', which creates a view with a query that query information about an object from another user 'U_DATOS'.Up here is simple, as a user GRANT 'U_DATOS' user 'U_VISTA' on the track these objects is solved.
U_DATOS:

SQL> GRANT SELECT ON TABLE TO U_VISTA;

The problem would come if we have a third user 'U_CONSULTA', you have to use this view. You might think that to give access permissions to the user to the query by 'U_VISTA', and access permissions to objects that refer to the view from 'U_DATOS' and would be all right:
U_VISTA:

SQL> GRANT SELECT ON VIEW TO U_CONSULTA;

U_DATOS:

SQL> GRANT SELECT ON TABLE TO U_CONSULTA;

No, it is not sufficient because access to these data through the view must be the owner, who view the permissions granted to a third party.Let's say for the granting of privileges is not met the transitive property.

To 'U_CONSULTA' to work on Vista without the database will return an ORA-00942, the owner of the objects (or a DBA user) is to grant privileges on those objects to another user, but with permission to This could in turn give other users:
U_DATOS:

SQL> GRANT SELECT ON TABLE WITH GRANT TO U_VISTAOPTION;

U_VISTA:

SQL> GRANT SELECT ON TO U_CONSULTA U_DATOS.TABLA;
SQL> GRANT SELECT ON VIEW TO U_CONSULTA;

U_CONSULTA:

SQL> SELECT * FROM VIEW;

And that's it, you can check U_CONSULTA view data.

Comment it Forum