Monday Apr 27, 2009

Derby 10.5 preview: SQL roles

Derby has supported SQL authorization with GRANT/REVOKE since version 10.2. In the upcoming 10.5 release, this is taken one step further with the addition of the concept of roles defined by the SQL:2003 standard.

Before, with SQL authorization enabled, you'd need to grant each required privilege explicitly to each user. With roles, this can be simplified by creating different roles which are granted sets of privileges, and granting roles instead of privileges to the users.

So instead of granting the same four privileges to three different users, you grant the privileges to a role and grant the role to those users.

ij> create role ordinary_user;
0 rows inserted/updated/deleted
ij> grant select on t1 to ordinary_user;
0 rows inserted/updated/deleted
ij> grant select on t2 to ordinary_user;
0 rows inserted/updated/deleted
ij> grant update on t2 to ordinary_user;
0 rows inserted/updated/deleted
ij> grant execute on procedure p1 to ordinary_user;
0 rows inserted/updated/deleted
ij> grant ordinary_user to username1, username2, username3;
0 rows inserted/updated/deleted

Later, if you want to take back the update privilege on T2 from those users, you simply revoke the privilege from the role, which is a simpler and less error-prone process than revoking the privilege from each single user.

ij> revoke update on t2 from ordinary_user;
0 rows inserted/updated/deleted

A role can also inherit privileges from another role. To create a superuser role which has all the privileges of the ordinary_user role, plus the privilege to insert rows into T1, do this:

ij> create role superuser;
0 rows inserted/updated/deleted
ij> grant ordinary_user to superuser;
0 rows inserted/updated/deleted
ij> grant insert on t1 to superuser;
0 rows inserted/updated/deleted

More detailed information about how to use SQL roles in Derby can be found in this section of Derby's reference manual.

Tuesday Dec 18, 2007

SQL syntax highlighting in OpenGrok

Perhaps not the most frequently requested feature, but since much of the OpenGrok development happens at Sun's Database Technology Group these days (unofficially, that is, so please don't tell our boss that's what's keeping us so busy!), it's kind of embarrassing that OpenGrok doesn't understand SQL scripts. Now that's finally about to change. Yesterday, I checked in some basic support for SQL syntax highlighting, and we'll hopefully also be able to support search for symbols and definitions in SQL scripts in the next release. So then there should be one less embarrassment to worry about...
About

kah

Search

Categories
Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today