Skip to main content

Java DB Authorization using GRANT and REVOKE

Posted by davidvc on June 17, 2007 at 7:23 PM PDT

The default behavior of Java DB is that you have two level of access control: full access and read-only. Again, this is I believe due to the legacy of Cloudscape being originally an embedded-only database. In this environment, the embedding application is responsible for controlling access to the database, and always only logs in as one user, so there is no real need for finer-grained access control in the database

But that changes when you have a client/server environment. So as of 10.2, Java DB has support for the SQL standard GRANT/REVOKE. However, again for backward compatibility, you have to explicitly enable support for it, otherwise you get the default behavior that anybody who is able to log in has full access.

You enable GRANT/REVOKE access control by adding an entry in your derby.properties file:

derby.database.sqlAuthorization=true

Once you have done this, only the owner of a resource (such as a table, view, index, etc.) has any rights to view, modify, or delete the resource. The owner is the one who created the resource.

The owner can then grant and revoke specific rights for that resource to other users. The rights supported by Java DB are: DELETE, EXECUTE, INSERT, SELECT, REFERENCES, TRIGGER, UPDATE. For more information on SQL authorization using GRANT and REVOKE, you can check out the Derby documentation

So, for example, lets say you create a table named "races". You can then let the Marx Brothers see the races by executing the following command:

GRANT SELECT ON TABLE races TO groucho, harpo, chico