I’ve recently been asked to grant a user permission to read a package body owned by another user. It’s surprisingly difficult to do.

There’s no object privilege that you can grant that will do this, instead you have to grant read access on DBA_SOURCE to the second user for them to see the first user’s source code. One problem with this is that you’ll grant them the right to read everybody’s code. If this is an issue (and it may well be) you’ll have to create a view which limits the rows that can be returned:

CREATE VIEW some_source AS
SELECT *
FROM   sys.dba_source
WHERE  owner = 'X'
AND    name = 'Y'

Remember to follow my earlier tip with regard to granting permissions if you take this approach.

If you’re happy to grant the straight DBA view to the other user, and they want to view the package source using TOAD, there’s a bit more to do. Firstly, they’ll need access to DBA_OBJECTS as well. Secondly, they’ll need to tweak their TOAD setup as follows:

1. Select View/Options from the menu.
2. Select “StartUp” from the tree view on the left.
3. Tick the “Check for access to DBA views” checkbox.

Next time they log on they’ll be able to see package bodies.

Incidentally, there’s an important moral to this story: Comment your packages in the specification, not the body (well, OK, you should comment the body too). Most of the time you won’t want to do the above hacking, so people need to be able to work out what a packaged program unit does from looking at the part of the package they can see!

I just found a great article on what you should, and shouldn’t, put in images’ alt attributes. It’s on a site by an outfit called WebAIM, an accessibilty think tank based at Utah State University. There look to be plenty more good articles on the site for web developers who care about their work being accessible to all.

Here’s an unexpected piece of Oracle behaviour that had me tearing my hair out last night until I figured out what was going on. It’s to do with object permissions as applied to views.

Suppose you have an oracle user called TABLE_OWNER who grants select access on his table to a role which all users have. One of those user, let’s call him VIEW_OWNER, creates a view based on that table and also grants select access on it to everybody.

Now, what happens when a third user attempts to select from the view? They get an “ORA-1031: Insufficient Privileges” error. Remember, they have select permission on both the view and the underlying table, so what’s happening?

The answer is that it’s Oracle security going a bit overboard. VIEW_OWNER has permission to see TABLE_OWNER’s table, but by creating a view of it and granting other users permission to view it, he’s effectively granting them select permission on the table. You need permission to do that, and VIEW_OWNER doesn’t have it. Curiously, this check is made when others try to select the view, rather than when the grant is made.

The solution is for TABLE_OWNER to do this:

GRANT SELECT ON my_table TO view_owner WITH GRANT OPTION;

That gives VIEW_OWNER permission to pass on the privilege, and thus other users permission to select from the view.

Here’s a handy tool for making sure that your site stays within the straight and narrow. Give it the URL of your home page and click the “Index & Validate” button, and it will spider your whole site, submit each page to the W3C validator, and tell you which pages (if any) fail the test.

It’s sobering to see how many errors there are in a site that you thought you’d coded with great care to meet all the rules - but that’s what makes this tool so valuable.

Now, I’m off to fix some validation errors…

This is a new design for the Extra Connections website, brought to you with the power of Wordpress. There’s still some finishing touches needed here and there, but take a look around and tell me what you think.

Watch this space for news, views and tips from the world of Oracle and web development.