~Note~

Please note that you can always click on an image in my postings and it will render a clear full sized version in a separate browser page! Also please note that this blog is best viewed with Firefox and Chrome
Google
 

Wednesday, October 29, 2008

Grants, Grants, and More Grants - WITH GRANT OPTION

ORA 01720 grant option does not exist for....

Simple mistake, but easy to fall into.

User_A creates a view called AV. This view accesses some of User_A's objects but also accesses one of User_B's tables too (table name is BT). No problem, User_A had been granted select on that table (BT) from User_B:

(as User_B) GRANT SELECT ON USER_B.BT TO USER_A

Now, however, User_A wants to grant select on that view, AV to a third user...User_C.
Even if User_C had also been granted select on User_B.BT table, USER_A would not be able to simply say:

(as User_A) GRANT SELECT ON USER_A.AV TO USER_C

instead you'll see:

ORA 01720 grant option does not exist

What this means is that USER_A tried to grant access to one of USER_B's tables to USER_C through the view AV. Even though USER_C had been directly granted select on USER_B.BT from USER_B itself, you'll still see the above error message.

Only the schema that owns the object can grant privileges to that object unless the 'with grant option' is included in the command. The 'with grant option' allows you to give the user that receives the grant (with the grant option) the ability to grant that same privilege to other users too. In a way, it is like giving a bit of your authority on one of your own objects to another trusted schema. Here is an example of the use of the with grant option:

(As USER_B) GRANT SELECT ON USER_B.BT TO USER_A WITH GRANT OPTION

What this did was it allowed USER_A to then grant USER_C select access to USER_B.BT table. It ALSO indirectly allows USER_A to grant select on USER_B.BT table through the view, USER_A.AV.

You can now perform the following with no problems:

(as User_A) GRANT SELECT ON USER_A.AV TO USER_C

Wednesday, October 1, 2008

Oracle Enterprise Linux, Ubuntu Linux, and more Linux


So I've been building a few Virtual Machines using Oracle VM as well as VMWare using different distros of Linux to see the major fuss between them all. Seems everyone has a favorite.

At least from an Install point of view, Ubuntu was easily smoother, however OEL offered many more installation and configuring options to start right away. This I liked. I have to be honest, I could care less how pretty and customizable Ubntu seems to be if OEL can run Oracle software better in any way. I'll keep posting anything that I find to be a deal breaker for me. I am just looking for the best of breed to build out my many testbeds for all the configurations of the Oracle stack I'll need. I am sure many of you out there are in the same boat!