[Registry-dev] svn commit r14559 - in
trunk/registry/modules/core/src/main:
java/org/wso2/registry/users/def
java/org/wso2/registry/users/def/util resources/database-scripts
svn at wso2.org
svn at wso2.org
Thu Mar 6 07:38:28 PST 2008
Author: dimuthul
Date: Thu Mar 6 07:38:13 2008
New Revision: 14559
Log:
Removing the UUID generated primary key.
Following the registry standards.
Modified:
trunk/registry/modules/core/src/main/java/org/wso2/registry/users/def/DefaultAccessControlAdmin.java
trunk/registry/modules/core/src/main/java/org/wso2/registry/users/def/DefaultDataSupplier.java
trunk/registry/modules/core/src/main/java/org/wso2/registry/users/def/DefaultRealmConstants.java
trunk/registry/modules/core/src/main/java/org/wso2/registry/users/def/DefaultUserStoreAdmin.java
trunk/registry/modules/core/src/main/java/org/wso2/registry/users/def/DefaultUserStoreReader.java
trunk/registry/modules/core/src/main/java/org/wso2/registry/users/def/util/DefaultDatabaseUtil.java
trunk/registry/modules/core/src/main/resources/database-scripts/derby-complete.sql
trunk/registry/modules/core/src/main/resources/database-scripts/hsql-registry.sql
Modified: trunk/registry/modules/core/src/main/java/org/wso2/registry/users/def/DefaultAccessControlAdmin.java
==============================================================================
--- trunk/registry/modules/core/src/main/java/org/wso2/registry/users/def/DefaultAccessControlAdmin.java (original)
+++ trunk/registry/modules/core/src/main/java/org/wso2/registry/users/def/DefaultAccessControlAdmin.java Thu Mar 6 07:38:13 2008
@@ -22,13 +22,12 @@
import javax.sql.DataSource;
-import org.apache.axiom.om.util.UUIDGenerator;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.wso2.registry.i18n.Messages;
import org.wso2.registry.users.AccessControlAdmin;
import org.wso2.registry.users.UserStoreException;
-
+import org.wso2.registry.users.def.util.DefaultDatabaseUtil;
/**
* @see org.wso2.usermanager.AccessControlAdmin
@@ -58,7 +57,8 @@
try {
dbConnection = dataSource.getConnection();
if (dbConnection == null) {
- throw new UserStoreException(Messages.getMessage("null_connection"));
+ throw new UserStoreException(Messages
+ .getMessage("null_connection"));
}
dbConnection.setAutoCommit(false);
PreparedStatement getPermission = dbConnection
@@ -68,190 +68,57 @@
getPermission.setString(2, action);
ResultSet rs = getPermission.executeQuery();
- String pid = null;
+ int pid = -1;
if (rs.next()) {
- pid = rs.getString(data
+ pid = rs.getInt(data
.getColumnName(DefaultRealmConstants.COLUMN_ID_ID));
} else {
throw new UserStoreException(Messages.getMessage("nullData"));
}
- String uid = data.getUserId(userName);
+ int uid = DefaultDatabaseUtil.getUserId(dbConnection, userName);
PreparedStatement clearUPStmt = dbConnection
.prepareStatement(data
.getAccessControlAdminSQL(DefaultRealmConstants.DELETE_USER_PERMISSION));
- clearUPStmt.setString(1, uid);
- clearUPStmt.setString(2, pid);
+ clearUPStmt.setInt(1, uid);
+ clearUPStmt.setInt(2, pid);
clearUPStmt.executeUpdate();
dbConnection.commit();
getPermission.close();
clearUPStmt.close();
} catch (SQLException e) {
log.debug(e);
- throw new UserStoreException(Messages.getMessage("errorModifyingUserStore"), e);
+ throw new UserStoreException(Messages
+ .getMessage("errorModifyingUserStore"), e);
} finally {
try {
if (dbConnection != null) {
dbConnection.close();
}
} catch (SQLException e) {
- throw new UserStoreException(Messages.getMessage("errorClosingConnection"), e);
+ throw new UserStoreException(Messages
+ .getMessage("errorClosingConnection"), e);
}
}
}
public void denyUser(String userName, String resourceId, String action)
throws UserStoreException {
- String permissionId = this.getOrAddPermissionId(resourceId, action);
-
- String userId = data.getUserId(userName);
-
- if (userId == null) {
- throw new UserStoreException(Messages.getMessage("nullUser"));
- }
-
- Connection dbConnection = null;
- String idUserPermission = UUIDGenerator.getUUID();
- short allow = 0;
- try {
- dbConnection = dataSource.getConnection();
- if (dbConnection == null) {
- throw new UserStoreException(Messages.getMessage("null_connection"));
- }
- dbConnection.setAutoCommit(false);
- PreparedStatement clearUPStmt = dbConnection
- .prepareStatement(data
- .getAccessControlAdminSQL(DefaultRealmConstants.DELETE_USER_PERMISSION));
- clearUPStmt.setString(1, userId);
- clearUPStmt.setString(2, permissionId);
- clearUPStmt.executeUpdate();
-
- PreparedStatement addUserPermissionStmt = dbConnection
- .prepareStatement(data
- .getAccessControlAdminSQL(DefaultRealmConstants.ADD_USER_PERMISSION));
- addUserPermissionStmt.setString(1, permissionId);
- addUserPermissionStmt.setShort(2, allow);
- addUserPermissionStmt.setString(3, userId);
- addUserPermissionStmt.setString(4, idUserPermission);
- addUserPermissionStmt.executeUpdate();
- dbConnection.commit();
- addUserPermissionStmt.close();
- clearUPStmt.close();
- } catch (SQLException e) {
- log.debug(e);
- throw new UserStoreException(Messages.getMessage("errorModifyingUserStore"), e);
- } finally {
- try {
- if (dbConnection != null) {
- dbConnection.close();
- }
- } catch (SQLException e) {
- throw new UserStoreException(Messages.getMessage("errorClosingConnection"), e);
- }
- }
-
+ short deny = 0;
+ addAllowDenyUserPermission(userName, resourceId, action, deny);
}
public void authorizeRole(String roleName, String resourceId, String action)
throws UserStoreException {
- String roleId = data.getRoleId(roleName);
-
- if (roleId == null) {
- throw new UserStoreException(Messages.getMessage("nullRole"));
- }
-
- String permissionId = this.getOrAddPermissionId(resourceId, action);
- Connection dbConnection = null;
- String idRolePermission = UUIDGenerator.getUUID();
short allow = 1;
- try {
- dbConnection = dataSource.getConnection();
- if (dbConnection == null) {
- throw new UserStoreException(Messages.getMessage("null_connection"));
- }
- dbConnection.setAutoCommit(false);
- PreparedStatement deleteRolesStmt = dbConnection
- .prepareStatement(data
- .getAccessControlAdminSQL(DefaultRealmConstants.DELETE_ROLE_PERMISSION));
- deleteRolesStmt.setString(1, roleId);
- deleteRolesStmt.setString(2, permissionId);
- deleteRolesStmt.executeUpdate();
-
- PreparedStatement addRolePermissionStmt = dbConnection
- .prepareStatement(data
- .getAccessControlAdminSQL(DefaultRealmConstants.ADD_ROLE_PERMISSION));
- addRolePermissionStmt.setString(1, permissionId);
- addRolePermissionStmt.setShort(2, allow);
- addRolePermissionStmt.setString(3, roleId);
- addRolePermissionStmt.setString(4, idRolePermission);
- addRolePermissionStmt.executeUpdate();
- dbConnection.commit();
- addRolePermissionStmt.close();
- deleteRolesStmt.close();
- } catch (SQLException e) {
- log.debug(e);
- throw new UserStoreException(Messages.getMessage("errorModifyingUserStore"), e);
- } finally {
- try {
- if (dbConnection != null) {
- dbConnection.close();
- }
- } catch (SQLException e) {
- throw new UserStoreException(Messages.getMessage("errorClosingConnection"), e);
- }
- }
+ addAllowDenyRolePermission(roleName, resourceId, action, allow);
+
}
public void denyRole(String roleName, String resourceId, String action)
throws UserStoreException {
-
- String roleId = data.getRoleId(roleName);
-
- if (roleId == null) {
- throw new UserStoreException(Messages.getMessage("nullRole"));
- }
-
- String permissionId = this.getOrAddPermissionId(resourceId, action);
- Connection dbConnection = null;
- String idRolePermission = UUIDGenerator.getUUID();
- short allow = 0;
- try {
- dbConnection = dataSource.getConnection();
- if (dbConnection == null) {
- throw new UserStoreException(Messages.getMessage("null_connection"));
- }
- dbConnection.setAutoCommit(false);
- // There can be only one permission for a role and a permission
- PreparedStatement deleteRolesStmt = dbConnection
- .prepareStatement(data
- .getAccessControlAdminSQL(DefaultRealmConstants.DELETE_ROLE_PERMISSION));
- deleteRolesStmt.setString(1, roleId);
- deleteRolesStmt.setString(2, permissionId);
- deleteRolesStmt.executeUpdate();
-
- PreparedStatement addRolePermissionStmt = dbConnection
- .prepareStatement(data
- .getAccessControlAdminSQL(DefaultRealmConstants.ADD_ROLE_PERMISSION));
- addRolePermissionStmt.setString(1, permissionId);
- addRolePermissionStmt.setShort(2, allow);
- addRolePermissionStmt.setString(3, roleId);
- addRolePermissionStmt.setString(4, idRolePermission);
- addRolePermissionStmt.executeUpdate();
- dbConnection.commit();
- addRolePermissionStmt.close();
- deleteRolesStmt.close();
- } catch (SQLException e) {
- log.debug(e);
- throw new UserStoreException(Messages.getMessage("errorModifyingUserStore"), e);
- } finally {
- try {
- if (dbConnection != null) {
- dbConnection.close();
- }
- } catch (SQLException e) {
- throw new UserStoreException(Messages.getMessage("errorClosingConnection"), e);
- }
- }
+ short deny = 0;
+ addAllowDenyRolePermission(roleName, resourceId, action, deny);
}
public void clearRoleAuthorization(String roleName, String resourceId,
@@ -260,7 +127,8 @@
try {
dbConnection = dataSource.getConnection();
if (dbConnection == null) {
- throw new UserStoreException(Messages.getMessage("null_connection"));
+ throw new UserStoreException(Messages
+ .getMessage("null_connection"));
}
dbConnection.setAutoCommit(false);
PreparedStatement getPermission = dbConnection
@@ -278,12 +146,12 @@
throw new UserStoreException(Messages.getMessage("nullData"));
}
- String rid = data.getRoleId(roleName);
+ int rid = DefaultDatabaseUtil.getRoleId(dbConnection, roleName);
PreparedStatement deleteRolesStmt = dbConnection
.prepareStatement(data
.getAccessControlAdminSQL(DefaultRealmConstants.DELETE_ROLE_PERMISSION));
- deleteRolesStmt.setString(1, rid);
+ deleteRolesStmt.setInt(1, rid);
deleteRolesStmt.setString(2, pid);
deleteRolesStmt.executeUpdate();
@@ -292,14 +160,16 @@
deleteRolesStmt.close();
} catch (SQLException e) {
log.debug(e);
- throw new UserStoreException(Messages.getMessage("errorModifyingUserStore"), e);
+ throw new UserStoreException(Messages
+ .getMessage("errorModifyingUserStore"), e);
} finally {
try {
if (dbConnection != null) {
dbConnection.close();
}
} catch (SQLException e) {
- throw new UserStoreException(Messages.getMessage("errorClosingConnection"), e);
+ throw new UserStoreException(Messages
+ .getMessage("errorClosingConnection"), e);
}
}
@@ -311,7 +181,8 @@
try {
dbConnection = dataSource.getConnection();
if (dbConnection == null) {
- throw new UserStoreException(Messages.getMessage("null_connection"));
+ throw new UserStoreException(Messages
+ .getMessage("null_connection"));
}
dbConnection.setAutoCommit(false);
PreparedStatement clearResourceAuthorizationsStmt = dbConnection
@@ -323,14 +194,16 @@
clearResourceAuthorizationsStmt.close();
} catch (SQLException e) {
log.debug(e);
- throw new UserStoreException(Messages.getMessage("errorModifyingUserStore"), e);
+ throw new UserStoreException(Messages
+ .getMessage("errorModifyingUserStore"), e);
} finally {
try {
if (dbConnection != null) {
dbConnection.close();
}
} catch (SQLException e) {
- throw new UserStoreException(Messages.getMessage("errorClosingConnection"), e);
+ throw new UserStoreException(Messages
+ .getMessage("errorClosingConnection"), e);
}
}
}
@@ -341,7 +214,8 @@
try {
dbConnection = dataSource.getConnection();
if (dbConnection == null) {
- throw new UserStoreException(Messages.getMessage("null_connection"));
+ throw new UserStoreException(Messages
+ .getMessage("null_connection"));
}
dbConnection.setAutoCommit(false);
PreparedStatement getResourcePermissionStmt = dbConnection
@@ -350,8 +224,8 @@
getResourcePermissionStmt.setString(1, toResourceId);
ResultSet rs = getResourcePermissionStmt.executeQuery();
if (rs.next()) {
- throw new UserStoreException(Messages.getMessage(
- "copyAuthorizationFailedPermissionExist"));
+ throw new UserStoreException(Messages
+ .getMessage("copyAuthorizationFailedPermissionExist"));
}
getResourcePermissionStmt.setString(1, fromResourceId);
@@ -364,12 +238,27 @@
.getColumnName(DefaultRealmConstants.COLUMN_ID_ACTION));
String oldPermissionId = rs.getString(data
.getColumnName(DefaultRealmConstants.COLUMN_ID_ID));
- String idPermission = UUIDGenerator.getUUID();
+
addPermissionStmt.setString(1, toResourceId);
addPermissionStmt.setString(2, action);
- addPermissionStmt.setString(3, idPermission);
addPermissionStmt.executeUpdate();
+ // ***** read the id
+ PreparedStatement getPermissionStmt = dbConnection
+ .prepareStatement(data
+ .getAuthorizerSQL(DefaultRealmConstants.GET_PERMISSION));
+ getPermissionStmt.setString(1, toResourceId);
+ getPermissionStmt.setString(2, action);
+
+ ResultSet rs2 = getPermissionStmt.executeQuery();
+
+ int idPermission = -1;
+ if (rs2.next()) {
+ idPermission = rs2.getInt(data
+ .getColumnName(DefaultRealmConstants.COLUMN_ID_ID));
+ }
+ // ***** end read id
+
PreparedStatement getRolePermissionStmt = dbConnection
.prepareStatement(data
.getAuthorizerSQL(DefaultRealmConstants.GET_ROLE_PERMISSION));
@@ -381,7 +270,7 @@
.getAccessControlAdminSQL(DefaultRealmConstants.ADD_ROLE_PERMISSION));
while (rsr.next()) {
- String idRolePermission = UUIDGenerator.getUUID();
+
String roleId = rsr
.getString(data
.getColumnName(DefaultRealmConstants.COLUMN_ID_ROLE_ID));
@@ -389,10 +278,9 @@
.getShort(data
.getColumnName(DefaultRealmConstants.COLUMN_ID_IS_ALLOWED));
- addRolePermissionStmt.setString(1, idPermission);
+ addRolePermissionStmt.setInt(1, idPermission);
addRolePermissionStmt.setShort(2, allow);
addRolePermissionStmt.setString(3, roleId);
- addRolePermissionStmt.setString(4, idRolePermission);
addRolePermissionStmt.executeUpdate();
}
@@ -408,7 +296,6 @@
.getAccessControlAdminSQL(DefaultRealmConstants.ADD_USER_PERMISSION));
while (rsu.next()) {
- String idUserPermission = UUIDGenerator.getUUID();
String userId = rsu
.getString(data
.getColumnName(DefaultRealmConstants.COLUMN_ID_USER_ID));
@@ -416,10 +303,9 @@
.getShort(data
.getColumnName(DefaultRealmConstants.COLUMN_ID_IS_ALLOWED));
- addUserPermissionStmt.setString(1, idPermission);
+ addUserPermissionStmt.setInt(1, idPermission);
addUserPermissionStmt.setShort(2, allow);
addUserPermissionStmt.setString(3, userId);
- addUserPermissionStmt.setString(4, idUserPermission);
addUserPermissionStmt.executeUpdate();
}
@@ -437,14 +323,16 @@
} catch (SQLException e) {
log.debug(e);
- throw new UserStoreException(Messages.getMessage("errorCopyingAuthorizations"), e);
+ throw new UserStoreException(Messages
+ .getMessage("errorCopyingAuthorizations"), e);
} finally {
try {
if (dbConnection != null) {
dbConnection.close();
}
} catch (SQLException e) {
- throw new UserStoreException(Messages.getMessage("errorClosingConnection"), e);
+ throw new UserStoreException(Messages
+ .getMessage("errorClosingConnection"), e);
}
}
@@ -452,115 +340,178 @@
public void authorizeUser(String userName, String resourceId, String action)
throws UserStoreException {
+ short allow = 1;
+ addAllowDenyUserPermission(userName, resourceId, action, allow);
+ }
+
+
- String userId = data.getUserId(userName);
+ /**
+ * Gets the permission if it exists, else create the permission and returns
+ * the id.
+ *
+ * @return
+ */
+ protected int getOrAddPermissionId(Connection dbConnection, String resourceId, String action)
+ throws UserStoreException {
+ int permissionId = -1;
+ try {
+ PreparedStatement getPermissionStmt = dbConnection
+ .prepareStatement(data
+ .getAuthorizerSQL(DefaultRealmConstants.GET_PERMISSION));
+ getPermissionStmt.setString(1, resourceId);
+ getPermissionStmt.setString(2, action);
- if (userId == null) {
- throw new UserStoreException(Messages.getMessage("nullUser"));
- }
+ ResultSet rs = getPermissionStmt.executeQuery();
+
+ if (rs.next()) {
+ permissionId = rs.getInt(data
+ .getColumnName(DefaultRealmConstants.COLUMN_ID_ID));
+ }
+
+ PreparedStatement addPermissionStmt = dbConnection
+ .prepareStatement(data
+ .getAccessControlAdminSQL(DefaultRealmConstants.ADD_PERMISSION));
+
+ if (permissionId == -1) {
+ // no permission so add it
+ addPermissionStmt.setString(1, resourceId);
+ addPermissionStmt.setString(2, action);
+ addPermissionStmt.executeUpdate();
+ dbConnection.commit();
+
+ // now read the id again
+ getPermissionStmt = dbConnection
+ .prepareStatement(data
+ .getAuthorizerSQL(DefaultRealmConstants.GET_PERMISSION));
+ getPermissionStmt.setString(1, resourceId);
+ getPermissionStmt.setString(2, action);
+
+ ResultSet rs2 = getPermissionStmt.executeQuery();
+
+ if (rs2.next()) {
+ permissionId = rs2.getInt(data
+ .getColumnName(DefaultRealmConstants.COLUMN_ID_ID));
+ }
+
+ dbConnection.commit();
+ }
+
+ } catch (SQLException e) {
+ log.debug(e);
+ throw new UserStoreException(Messages
+ .getMessage("errorModifyingUserStore"), e);
+ }
- String permissionId = this.getOrAddPermissionId(resourceId, action);
+ return permissionId;
+
+ }
+
+
+ protected void addAllowDenyUserPermission(String userName, String resourceId, String action, short allow)
+ throws UserStoreException{
Connection dbConnection = null;
- String idUserPermission = UUIDGenerator.getUUID();
- short allow = 1;
+
try {
dbConnection = dataSource.getConnection();
if (dbConnection == null) {
- throw new UserStoreException(Messages.getMessage("null_connection"));
+ throw new UserStoreException(Messages
+ .getMessage("null_connection"));
}
dbConnection.setAutoCommit(false);
+
+
+ int userId = DefaultDatabaseUtil.getUserId(dbConnection, userName);
+
+ if (userId == -1) {
+ throw new UserStoreException(Messages.getMessage("nullUser"));
+ }
+
+ int permissionId = this.getOrAddPermissionId(dbConnection, resourceId, action);
+
PreparedStatement clearUPStmt = dbConnection
.prepareStatement(data
.getAccessControlAdminSQL(DefaultRealmConstants.DELETE_USER_PERMISSION));
- clearUPStmt.setString(1, userId);
- clearUPStmt.setString(2, permissionId);
+ clearUPStmt.setInt(1, userId);
+ clearUPStmt.setInt(2, permissionId);
clearUPStmt.executeUpdate();
PreparedStatement addUserPermissionStmt = dbConnection
.prepareStatement(data
.getAccessControlAdminSQL(DefaultRealmConstants.ADD_USER_PERMISSION));
- addUserPermissionStmt.setString(1, permissionId);
+ addUserPermissionStmt.setInt(1, permissionId);
addUserPermissionStmt.setShort(2, allow);
- addUserPermissionStmt.setString(3, userId);
- addUserPermissionStmt.setString(4, idUserPermission);
+ addUserPermissionStmt.setInt(3, userId);
addUserPermissionStmt.executeUpdate();
dbConnection.commit();
addUserPermissionStmt.close();
clearUPStmt.close();
} catch (SQLException e) {
log.debug(e);
- throw new UserStoreException(Messages.getMessage("errorModifyingUserStore"), e);
+ throw new UserStoreException(Messages
+ .getMessage("errorModifyingUserStore"), e);
} finally {
try {
if (dbConnection != null) {
dbConnection.close();
}
} catch (SQLException e) {
- throw new UserStoreException(Messages.getMessage("errorClosingConnection"), e);
+ throw new UserStoreException(Messages
+ .getMessage("errorClosingConnection"), e);
}
}
}
- /**
- * Gets the permission if it exists, else create the permission and returns
- * the id.
- *
- * @return
- */
- protected String getOrAddPermissionId(String resourceId, String action)
- throws UserStoreException {
- String permissionId = null;
+ protected void addAllowDenyRolePermission(String roleName, String resourceId, String action, short allow)
+ throws UserStoreException{
Connection dbConnection = null;
try {
dbConnection = dataSource.getConnection();
if (dbConnection == null) {
- throw new UserStoreException(Messages.getMessage("null_connection"));
+ throw new UserStoreException(Messages
+ .getMessage("null_connection"));
}
dbConnection.setAutoCommit(false);
- PreparedStatement getPermissionStmt = dbConnection
- .prepareStatement(data
- .getAuthorizerSQL(DefaultRealmConstants.GET_PERMISSION));
- getPermissionStmt.setString(1, resourceId);
- getPermissionStmt.setString(2, action);
-
- ResultSet rs = getPermissionStmt.executeQuery();
-
- if (rs.next()) {
- permissionId = rs.getString(data
- .getColumnName(DefaultRealmConstants.COLUMN_ID_ID));
+ int roleId = DefaultDatabaseUtil.getRoleId(dbConnection, roleName);
+
+ if (roleId == -1) {
+ throw new UserStoreException(Messages.getMessage("nullRole"));
}
- PreparedStatement addPermissionStmt = dbConnection
+ int permissionId = this.getOrAddPermissionId(dbConnection, resourceId, action);
+
+ PreparedStatement deleteRolesStmt = dbConnection
.prepareStatement(data
- .getAccessControlAdminSQL(DefaultRealmConstants.ADD_PERMISSION));
-
- if (permissionId == null) {
- // no permission so add it
- String idPermission = UUIDGenerator.getUUID();
- addPermissionStmt.setString(1, resourceId);
- addPermissionStmt.setString(2, action);
- addPermissionStmt.setString(3, idPermission);
- addPermissionStmt.executeUpdate();
- dbConnection.commit();
- permissionId = idPermission;
- }
+ .getAccessControlAdminSQL(DefaultRealmConstants.DELETE_ROLE_PERMISSION));
+ deleteRolesStmt.setInt(1, roleId);
+ deleteRolesStmt.setInt(2, permissionId);
+ deleteRolesStmt.executeUpdate();
+ PreparedStatement addRolePermissionStmt = dbConnection
+ .prepareStatement(data
+ .getAccessControlAdminSQL(DefaultRealmConstants.ADD_ROLE_PERMISSION));
+ addRolePermissionStmt.setInt(1, permissionId);
+ addRolePermissionStmt.setShort(2, allow);
+ addRolePermissionStmt.setInt(3, roleId);
+ addRolePermissionStmt.executeUpdate();
+ dbConnection.commit();
+ addRolePermissionStmt.close();
+ deleteRolesStmt.close();
} catch (SQLException e) {
log.debug(e);
- throw new UserStoreException(Messages.getMessage("errorModifyingUserStore"), e);
+ throw new UserStoreException(Messages
+ .getMessage("errorModifyingUserStore"), e);
} finally {
try {
if (dbConnection != null) {
dbConnection.close();
}
} catch (SQLException e) {
- throw new UserStoreException(Messages.getMessage("errorClosingConnection"), e);
+ throw new UserStoreException(Messages
+ .getMessage("errorClosingConnection"), e);
}
}
-
- return permissionId;
-
}
-
+
}
Modified: trunk/registry/modules/core/src/main/java/org/wso2/registry/users/def/DefaultDataSupplier.java
==============================================================================
--- trunk/registry/modules/core/src/main/java/org/wso2/registry/users/def/DefaultDataSupplier.java (original)
+++ trunk/registry/modules/core/src/main/java/org/wso2/registry/users/def/DefaultDataSupplier.java Thu Mar 6 07:38:13 2008
@@ -57,16 +57,6 @@
public String getUserStoreReaderSQL(int sqlID);
/**
- * Retrieves the User ID
- */
- public String getUserId(String userName) throws UserStoreException;
-
- /**
- * Retrieves the Role ID
- */
- public String getRoleId(String roleName) throws UserStoreException;
-
- /**
* Retrieves the User Roles
*/
public String[] getUserRoles(String userName) throws UserStoreException;
Modified: trunk/registry/modules/core/src/main/java/org/wso2/registry/users/def/DefaultRealmConstants.java
==============================================================================
--- trunk/registry/modules/core/src/main/java/org/wso2/registry/users/def/DefaultRealmConstants.java (original)
+++ trunk/registry/modules/core/src/main/java/org/wso2/registry/users/def/DefaultRealmConstants.java Thu Mar 6 07:38:13 2008
@@ -75,14 +75,15 @@
public static final int COLUMN_ID_CREDENTIAL = 9;
public static final int COLUMN_ID_ACTION = 10;
- public static final String ADD_USER_SQL = "insert into um_users (user_name, password, id) values (?, ?, ?)";
- public static final String ADD_ROLE_SQL = "insert into um_roles (role_name, id) values (?, ?)";
- public static final String ADD_USER_ROLE_SQL = "insert into um_user_roles (id, user_id, role_id) values (?, ?, ?)";
- public static final String ADD_USER_ATTRIBUTE_SQL = "insert into um_user_attributes (attr_name, attr_value, user_id, id) values (?, ?, ?, ?)";
- public static final String ADD_ROLE_ATTRIBUTE_SQL = "insert into um_role_attributes (attr_name, attr_value, role_id, id) values (?, ?, ?, ?)";
- public static final String ADD_PERMISSION_SQL = "insert into um_permissions (resource_id, action, id) values (?, ?, ?)";
- public static final String ADD_ROLE_PERMISSION_SQL = "insert into um_role_permissions (permission_id, is_allowed, role_id, id) values (?, ?, ?, ?)";
- public static final String ADD_USER_PERMISSION_SQL = "insert into um_user_permissions (permission_id, is_allowed, user_id, id) values (?, ?, ?, ?)";
+ public static final String ADD_USER_SQL = "insert into um_users (user_name, password) values (?, ?)";
+ public static final String ADD_ROLE_SQL = "insert into um_roles (role_name) values (?)";
+ public static final String ADD_USER_ROLE_SQL = "insert into um_user_roles (user_id, role_id) values (?, ?)";
+ public static final String ADD_USER_ATTRIBUTE_SQL = "insert into um_user_attributes (attr_name, attr_value, user_id) values (?, ?, ?)";
+ public static final String ADD_ROLE_ATTRIBUTE_SQL = "insert into um_role_attributes (attr_name, attr_value, role_id) values (?, ?, ?)";
+ public static final String ADD_PERMISSION_SQL = "insert into um_permissions (resource_id, action) values (?, ?)";
+ public static final String ADD_ROLE_PERMISSION_SQL = "insert into um_role_permissions (permission_id, is_allowed, role_id) values (?, ?, ?)";
+ public static final String ADD_USER_PERMISSION_SQL = "insert into um_user_permissions (permission_id, is_allowed, user_id) values (?, ?, ?)";
+
public static final String UPDATE_USER_SQL = "update um_users set password= ? where user_name= ?";
public static final String DELETE_USER_SQL = "delete from um_users where user_name = ?";
public static final String DELETE_ROLE_SQL = "delete from um_roles where role_name = ?";
Modified: trunk/registry/modules/core/src/main/java/org/wso2/registry/users/def/DefaultUserStoreAdmin.java
==============================================================================
--- trunk/registry/modules/core/src/main/java/org/wso2/registry/users/def/DefaultUserStoreAdmin.java (original)
+++ trunk/registry/modules/core/src/main/java/org/wso2/registry/users/def/DefaultUserStoreAdmin.java Thu Mar 6 07:38:13 2008
@@ -28,12 +28,12 @@
import javax.sql.DataSource;
import org.apache.axiom.om.util.Base64;
-import org.apache.axiom.om.util.UUIDGenerator;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.wso2.registry.i18n.Messages;
import org.wso2.registry.users.UserStoreAdmin;
import org.wso2.registry.users.UserStoreException;
+import org.wso2.registry.users.def.util.DefaultDatabaseUtil;
/**
@@ -83,8 +83,6 @@
MessageDigest dgst = MessageDigest.getInstance("MD5");
dgst.update(((String) credential).getBytes());
addUserStmt.setString(2, Base64.encode(dgst.digest()));
- String idUser = UUIDGenerator.getUUID();
- addUserStmt.setString(3, idUser);
addUserStmt.executeUpdate();
dbConnection.commit();
addUserStmt.close();
@@ -230,10 +228,6 @@
public void setUserProperties(String userName, Map properties)
throws UserStoreException {
- String userid = data.getUserId(userName);
- if (userid == null) {
- throw new UserStoreException(Messages.getMessage("nullUser"));
- }
Connection dbConnection = null;
try {
@@ -242,10 +236,16 @@
throw new UserStoreException(Messages.getMessage("null_connection"));
}
dbConnection.setAutoCommit(false);
+
+ int userid = DefaultDatabaseUtil.getUserId(dbConnection, userName);
+ if (userid == -1) {
+ throw new UserStoreException(Messages.getMessage("nullUser"));
+ }
+
PreparedStatement deleteUserPropsStmt = dbConnection
.prepareStatement(data
.getUserStoreAdminSQL(DefaultRealmConstants.DELETE_USER_ATTRIBUTE));
- deleteUserPropsStmt.setString(1, userid);
+ deleteUserPropsStmt.setInt(1, userid);
deleteUserPropsStmt.executeUpdate();
PreparedStatement setUserPropertiesStmt = dbConnection
@@ -259,11 +259,9 @@
String key = (String) entry.getKey();
String value = (String) entry.getValue();
if (value != null) {
- String idUserAttribute = UUIDGenerator.getUUID();
setUserPropertiesStmt.setString(1, key);
setUserPropertiesStmt.setString(2, value);
- setUserPropertiesStmt.setString(3, userid);
- setUserPropertiesStmt.setString(4, idUserAttribute);
+ setUserPropertiesStmt.setInt(3, userid);
setUserPropertiesStmt.executeUpdate();
}
}
@@ -287,11 +285,6 @@
public void setRoleProperties(String roleName, Map properties)
throws UserStoreException {
-
- String roleid = data.getRoleId(roleName);
- if (roleid == null) {
- throw new UserStoreException(Messages.getMessage("nullRole"));
- }
Connection dbConnection = null;
try {
dbConnection = dataSource.getConnection();
@@ -299,10 +292,16 @@
throw new UserStoreException(Messages.getMessage("null_connection"));
}
dbConnection.setAutoCommit(false);
+
+ int roleid = DefaultDatabaseUtil.getRoleId(dbConnection, roleName);
+ if (roleid == -1) {
+ throw new UserStoreException(Messages.getMessage("nullRole"));
+ }
+
PreparedStatement deleteRolePropsStmt = dbConnection
.prepareStatement(data
.getUserStoreAdminSQL(DefaultRealmConstants.DELETE_ROLE_ATTRIBUTE));
- deleteRolePropsStmt.setString(1, roleid);
+ deleteRolePropsStmt.setInt(1, roleid);
deleteRolePropsStmt.executeUpdate();
PreparedStatement setRolePropertiesStmt = dbConnection
@@ -315,11 +314,9 @@
String key = (String) entry.getKey();
String value = (String) entry.getValue();
if (value != null) {
- String idRoleAttribute = UUIDGenerator.getUUID();
setRolePropertiesStmt.setString(1, key);
setRolePropertiesStmt.setString(2, value);
- setRolePropertiesStmt.setString(3, roleid);
- setRolePropertiesStmt.setString(4, idRoleAttribute);
+ setRolePropertiesStmt.setInt(3, roleid);
setRolePropertiesStmt.executeUpdate();
}
}
@@ -352,9 +349,7 @@
dbConnection.setAutoCommit(false);
PreparedStatement addRoleStmt = dbConnection.prepareStatement(data
.getUserStoreAdminSQL(DefaultRealmConstants.ADD_ROLE));
- String idRole = UUIDGenerator.getUUID();
addRoleStmt.setString(1, roleName);
- addRoleStmt.setString(2, idRole);
addRoleStmt.execute();
dbConnection.commit();
addRoleStmt.close();
@@ -404,12 +399,6 @@
public void addUserToRole(String userName, String roleName)
throws UserStoreException {
- String userid = data.getUserId(userName);
- String roleid = data.getRoleId(roleName);
-
- if (roleid == null || userid == null) {
- throw new UserStoreException(Messages.getMessage("nullData"));
- }
Connection dbConnection = null;
try {
dbConnection = dataSource.getConnection();
@@ -417,13 +406,19 @@
throw new UserStoreException(Messages.getMessage("null_connection"));
}
dbConnection.setAutoCommit(false);
+
+ int userid = DefaultDatabaseUtil.getUserId(dbConnection, userName);
+ int roleid = DefaultDatabaseUtil.getRoleId(dbConnection,roleName);
+
+ if (roleid == -1 || userid == -1) {
+ throw new UserStoreException(Messages.getMessage("nullData"));
+ }
+
PreparedStatement addUserToRoleStmt = dbConnection
.prepareStatement(data
.getUserStoreAdminSQL(DefaultRealmConstants.ADD_USER_ROLE));
- String id = UUIDGenerator.getUUID();
- addUserToRoleStmt.setString(1, id);
- addUserToRoleStmt.setString(2, userid);
- addUserToRoleStmt.setString(3, roleid);
+ addUserToRoleStmt.setInt(1, userid);
+ addUserToRoleStmt.setInt(2, roleid);
addUserToRoleStmt.executeUpdate();
dbConnection.commit();
addUserToRoleStmt.close();
Modified: trunk/registry/modules/core/src/main/java/org/wso2/registry/users/def/DefaultUserStoreReader.java
==============================================================================
--- trunk/registry/modules/core/src/main/java/org/wso2/registry/users/def/DefaultUserStoreReader.java (original)
+++ trunk/registry/modules/core/src/main/java/org/wso2/registry/users/def/DefaultUserStoreReader.java Thu Mar 6 07:38:13 2008
@@ -33,8 +33,7 @@
import org.wso2.registry.users.UserStoreException;
import org.wso2.registry.users.UserStoreReader;
import org.wso2.registry.users.def.util.DefaultDataSupplierImpl;
-
-
+import org.wso2.registry.users.def.util.DefaultDatabaseUtil;
/**
* Manipulates data in the User Store
@@ -53,7 +52,8 @@
this.data = new DefaultDataSupplierImpl(dataSource);
}
- public DefaultUserStoreReader(DataSource dataSource, DefaultDataSupplier store) {
+ public DefaultUserStoreReader(DataSource dataSource,
+ DefaultDataSupplier store) {
this.dataSource = dataSource;
if (store != null) {
this.data = store;
@@ -68,7 +68,8 @@
try {
dbConnection = dataSource.getConnection();
if (dbConnection == null) {
- throw new UserStoreException(Messages.getMessage("null_connection"));
+ throw new UserStoreException(Messages
+ .getMessage("null_connection"));
}
dbConnection.setAutoCommit(false);
PreparedStatement getAllRoleNamesStmt = dbConnection
@@ -87,14 +88,16 @@
getAllRoleNamesStmt.close();
} catch (SQLException e) {
log.debug(e);
- throw new UserStoreException(Messages.getMessage("errorReadingFromUserStore"), e);
+ throw new UserStoreException(Messages
+ .getMessage("errorReadingFromUserStore"), e);
} finally {
try {
if (dbConnection != null) {
dbConnection.close();
}
} catch (SQLException e) {
- throw new UserStoreException(Messages.getMessage("errorClosingConnection"), e);
+ throw new UserStoreException(Messages
+ .getMessage("errorClosingConnection"), e);
}
}
return names;
@@ -106,7 +109,8 @@
try {
dbConnection = dataSource.getConnection();
if (dbConnection == null) {
- throw new UserStoreException(Messages.getMessage("null_connection"));
+ throw new UserStoreException(Messages
+ .getMessage("null_connection"));
}
dbConnection.setAutoCommit(false);
PreparedStatement getAllUserNamesStmt = dbConnection
@@ -126,14 +130,16 @@
getAllUserNamesStmt.close();
} catch (SQLException e) {
log.debug(e);
- throw new UserStoreException(Messages.getMessage("errorReadingFromUserStore"), e);
+ throw new UserStoreException(Messages
+ .getMessage("errorReadingFromUserStore"), e);
} finally {
try {
if (dbConnection != null) {
dbConnection.close();
}
} catch (SQLException e) {
- throw new UserStoreException(Messages.getMessage("errorClosingConnection"), e);
+ throw new UserStoreException(Messages
+ .getMessage("errorClosingConnection"), e);
}
}
return names;
@@ -146,7 +152,8 @@
try {
dbConnection = dataSource.getConnection();
if (dbConnection == null) {
- throw new UserStoreException(Messages.getMessage("null_connection"));
+ throw new UserStoreException(Messages
+ .getMessage("null_connection"));
}
dbConnection.setAutoCommit(false);
PreparedStatement getRolePropertiesStmt = dbConnection
@@ -166,14 +173,16 @@
getRolePropertiesStmt.close();
} catch (SQLException e) {
log.debug(e);
- throw new UserStoreException(Messages.getMessage("errorReadingFromUserStore"), e);
+ throw new UserStoreException(Messages
+ .getMessage("errorReadingFromUserStore"), e);
} finally {
try {
if (dbConnection != null) {
dbConnection.close();
}
} catch (SQLException e) {
- throw new UserStoreException(Messages.getMessage("errorClosingConnection"), e);
+ throw new UserStoreException(Messages
+ .getMessage("errorClosingConnection"), e);
}
}
return props;
@@ -186,7 +195,8 @@
try {
dbConnection = dataSource.getConnection();
if (dbConnection == null) {
- throw new UserStoreException(Messages.getMessage("null_connection"));
+ throw new UserStoreException(Messages
+ .getMessage("null_connection"));
}
dbConnection.setAutoCommit(false);
PreparedStatement getUserPropertiesStmt = dbConnection
@@ -206,14 +216,16 @@
getUserPropertiesStmt.close();
} catch (SQLException e) {
log.debug(e);
- throw new UserStoreException(Messages.getMessage("errorReadingFromUserStore"), e);
+ throw new UserStoreException(Messages
+ .getMessage("errorReadingFromUserStore"), e);
} finally {
try {
if (dbConnection != null) {
dbConnection.close();
}
} catch (SQLException e) {
- throw new UserStoreException(Messages.getMessage("errorClosingConnection"), e);
+ throw new UserStoreException(Messages
+ .getMessage("errorClosingConnection"), e);
}
}
return props;
@@ -226,7 +238,8 @@
try {
dbConnection = dataSource.getConnection();
if (dbConnection == null) {
- throw new UserStoreException(Messages.getMessage("null_connection"));
+ throw new UserStoreException(Messages
+ .getMessage("null_connection"));
}
dbConnection.setAutoCommit(false);
PreparedStatement getUserAttributeNamesStmt = dbConnection
@@ -243,14 +256,16 @@
getUserAttributeNamesStmt.close();
} catch (SQLException e) {
log.debug(e);
- throw new UserStoreException(Messages.getMessage("errorReadingFromUserStore"), e);
+ throw new UserStoreException(Messages
+ .getMessage("errorReadingFromUserStore"), e);
} finally {
try {
if (dbConnection != null) {
dbConnection.close();
}
} catch (SQLException e) {
- throw new UserStoreException(Messages.getMessage("errorClosingConnection"), e);
+ throw new UserStoreException(Messages
+ .getMessage("errorClosingConnection"), e);
}
}
return propNames;
@@ -262,17 +277,62 @@
public boolean isExistingUser(String userName) throws UserStoreException {
boolean isExisting = false;
- if (data.getUserId(userName) != null) {
- isExisting = true;
+ Connection dbConnection = null;
+ try {
+ dbConnection = dataSource.getConnection();
+ if (dbConnection == null) {
+ throw new UserStoreException(Messages
+ .getMessage("null_connection"));
+ }
+ dbConnection.setAutoCommit(false);
+ if (DefaultDatabaseUtil.getUserId(dbConnection, userName) != -1) {
+ isExisting = true;
+ }
+ } catch (SQLException e) {
+ log.debug(e);
+ throw new UserStoreException(Messages
+ .getMessage("errorReadingFromUserStore"), e);
+ } finally {
+ try {
+ if (dbConnection != null) {
+ dbConnection.close();
+ }
+ } catch (SQLException e) {
+ throw new UserStoreException(Messages
+ .getMessage("errorClosingConnection"), e);
+ }
}
return isExisting;
}
-
- public boolean isExistingRole(String roleName) throws UserStoreException{
+
+ public boolean isExistingRole(String roleName) throws UserStoreException {
boolean isExisting = false;
- if (data.getRoleId(roleName) != null) {
- isExisting = true;
+ Connection dbConnection = null;
+ try {
+ dbConnection = dataSource.getConnection();
+ if (dbConnection == null) {
+ throw new UserStoreException(Messages
+ .getMessage("null_connection"));
+ }
+ dbConnection.setAutoCommit(false);
+ if (DefaultDatabaseUtil.getRoleId(dbConnection, roleName) != -1) {
+ isExisting = true;
+ }
+ } catch (SQLException e) {
+ log.debug(e);
+ throw new UserStoreException(Messages
+ .getMessage("errorReadingFromUserStore"), e);
+ } finally {
+ try {
+ if (dbConnection != null) {
+ dbConnection.close();
+ }
+ } catch (SQLException e) {
+ throw new UserStoreException(Messages
+ .getMessage("errorClosingConnection"), e);
+ }
}
+
return isExisting;
}
@@ -283,7 +343,8 @@
try {
dbConnection = dataSource.getConnection();
if (dbConnection == null) {
- throw new UserStoreException(Messages.getMessage("null_connection"));
+ throw new UserStoreException(Messages
+ .getMessage("null_connection"));
}
dbConnection.setAutoCommit(false);
PreparedStatement getPropValue = null;
@@ -312,14 +373,16 @@
getPropValue.close();
} catch (SQLException e) {
log.debug(e);
- throw new UserStoreException(Messages.getMessage("errorReadingFromUserStore"), e);
+ throw new UserStoreException(Messages
+ .getMessage("errorReadingFromUserStore"), e);
} finally {
try {
if (dbConnection != null) {
dbConnection.close();
}
} catch (SQLException e) {
- throw new UserStoreException(Messages.getMessage("errorClosingConnection"), e);
+ throw new UserStoreException(Messages
+ .getMessage("errorClosingConnection"), e);
}
}
return names;
@@ -332,7 +395,8 @@
try {
dbConnection = dataSource.getConnection();
if (dbConnection == null) {
- throw new UserStoreException(Messages.getMessage("null_connection"));
+ throw new UserStoreException(Messages
+ .getMessage("null_connection"));
}
dbConnection.setAutoCommit(false);
PreparedStatement getUserInRole = dbConnection
@@ -350,14 +414,16 @@
getUserInRole.close();
} catch (SQLException e) {
log.debug(e);
- throw new UserStoreException(Messages.getMessage("errorReadingFromUserStore"), e);
+ throw new UserStoreException(Messages
+ .getMessage("errorReadingFromUserStore"), e);
} finally {
try {
if (dbConnection != null) {
dbConnection.close();
}
} catch (SQLException e) {
- throw new UserStoreException(Messages.getMessage("errorClosingConnection"), e);
+ throw new UserStoreException(Messages
+ .getMessage("errorClosingConnection"), e);
}
}
return names;
@@ -368,5 +434,4 @@
return null;
}
-
}
Modified: trunk/registry/modules/core/src/main/java/org/wso2/registry/users/def/util/DefaultDatabaseUtil.java
==============================================================================
--- trunk/registry/modules/core/src/main/java/org/wso2/registry/users/def/util/DefaultDatabaseUtil.java (original)
+++ trunk/registry/modules/core/src/main/java/org/wso2/registry/users/def/util/DefaultDatabaseUtil.java Thu Mar 6 07:38:13 2008
@@ -16,63 +16,84 @@
package org.wso2.registry.users.def.util;
import java.sql.Connection;
+import java.sql.PreparedStatement;
+import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
+import java.util.LinkedList;
+import java.util.List;
import org.wso2.registry.users.UserStoreException;
+import org.wso2.usermanager.UserManagerException;
+import org.wso2.usermanager.readwrite.DefaultRealmConstants;
public class DefaultDatabaseUtil {
+// TODO Start -delete
+ public static String userTable = "CREATE TABLE UM_USERS ("
+ +"ID INTEGER GENERATED BY DEFAULT AS IDENTITY,"
+ +"USER_NAME VARCHAR(255) NOT NULL,"
+ +"PASSWORD VARCHAR(255) NOT NULL, "
+ +"PRIMARY KEY (ID), "
+ +"UNIQUE(USER_NAME))";
+
+ public static String userAttributeTable="CREATE TABLE UM_USER_ATTRIBUTES ("
+ +"ID INTEGER GENERATED BY DEFAULT AS IDENTITY,"
+ +"ATTR_NAME VARCHAR(255) NOT NULL,"
+ +"ATTR_VALUE VARCHAR(255), "
+ +"USER_ID INTEGER, "
+ +"FOREIGN KEY (USER_ID) REFERENCES UM_USERS(ID) ON DELETE CASCADE,"
+ +"PRIMARY KEY (ID))";
+
+ public static String roleTable="CREATE TABLE UM_ROLES ("
+ +"ID INTEGER GENERATED BY DEFAULT AS IDENTITY, "
+ +"ROLE_NAME VARCHAR(255) NOT NULL, "
+ +"PRIMARY KEY (ID), "
+ +"UNIQUE(ROLE_NAME))";
+
+ public static String roleAttributeTable="CREATE TABLE UM_ROLE_ATTRIBUTES ("
+ +"ID INTEGER GENERATED BY DEFAULT AS IDENTITY, "
+ +"ATTR_NAME VARCHAR(255) NOT NULL, "
+ +"ATTR_VALUE VARCHAR(255), "
+ +"ROLE_ID INTEGER, "
+ +"FOREIGN KEY (ROLE_ID) REFERENCES UM_ROLES(ID) ON DELETE CASCADE,"
+ +"PRIMARY KEY (ID))";
+
+ public static String permissionTable="CREATE TABLE UM_PERMISSIONS ("
+ +"ID INTEGER GENERATED BY DEFAULT AS IDENTITY, "
+ +"RESOURCE_ID VARCHAR(255) NOT NULL, "
+ +"ACTION VARCHAR(255) NOT NULL, "
+ +"PRIMARY KEY (ID))";
+
+ public static String rolePermissionTable="CREATE TABLE UM_ROLE_PERMISSIONS ( "
+ +"ID INTEGER GENERATED BY DEFAULT AS IDENTITY, "
+ +"PERMISSION_ID INTEGER NOT NULL, "
+ +"ROLE_ID INTEGER NOT NULL, "
+ +"IS_ALLOWED SMALLINT NOT NULL, "
+ +"UNIQUE (PERMISSION_ID, ROLE_ID), "
+ +"FOREIGN KEY (PERMISSION_ID) REFERENCES UM_PERMISSIONS(ID) ON DELETE CASCADE, "
+ +"FOREIGN KEY (ROLE_ID) REFERENCES UM_ROLES(ID) ON DELETE CASCADE, "
+ +"PRIMARY KEY (ID))";
+
+ public static String userPermissionTable="CREATE TABLE UM_USER_PERMISSIONS ("
+ +"ID INTEGER GENERATED BY DEFAULT AS IDENTITY, "
+ +"PERMISSION_ID INTEGER NOT NULL, "
+ +"USER_ID INTEGER NOT NULL, "
+ +"IS_ALLOWED SMALLINT NOT NULL, "
+ +"UNIQUE (PERMISSION_ID, USER_ID), "
+ +"FOREIGN KEY (PERMISSION_ID) REFERENCES UM_PERMISSIONS(ID) ON DELETE CASCADE, "
+ +"FOREIGN KEY (USER_ID) REFERENCES UM_USERS(ID) ON DELETE CASCADE, "
+ +"PRIMARY KEY (ID))";
+
+ public static String userRolesTable="CREATE TABLE UM_USER_ROLES ("
+ +"ID INTEGER GENERATED BY DEFAULT AS IDENTITY, "
+ +"ROLE_ID INTEGER NOT NULL, "
+ +"USER_ID INTEGER NOT NULL, "
+ +"UNIQUE (USER_ID, ROLE_ID), "
+ +"FOREIGN KEY (ROLE_ID) REFERENCES UM_ROLES(ID) ON DELETE CASCADE, "
+ +"FOREIGN KEY (USER_ID) REFERENCES UM_USERS(ID) ON DELETE CASCADE, "
+ +"PRIMARY KEY (ID))";
- public static String userTable = "CREATE TABLE UM_USERS (ID VARCHAR(255),"
- + " USER_NAME VARCHAR(255) NOT NULL,"
- + " PASSWORD VARCHAR(255) NOT NULL," + " UNIQUE (USER_NAME),"
- + " PRIMARY KEY (ID))";
-
- public static String userAttributeTable = "CREATE TABLE UM_USER_ATTRIBUTES (ID VARCHAR(255),"
- + " ATTR_NAME VARCHAR(255) NOT NULL,"
- + " ATTR_VALUE VARCHAR(255),"
- + " USER_ID VARCHAR(255),"
- + " FOREIGN KEY (USER_ID) REFERENCES UM_USERS(ID) ON DELETE CASCADE,"
- + " PRIMARY KEY (ID))";
-
- public static String roleTable = "CREATE TABLE UM_ROLES (ID VARCHAR(255),"
- + " ROLE_NAME VARCHAR(255) NOT NULL," + " UNIQUE (ROLE_NAME),"
- + " PRIMARY KEY (ID))";
-
- public static String roleAttributeTable = "CREATE TABLE UM_ROLE_ATTRIBUTES (ID VARCHAR(255),"
- + " ATTR_NAME VARCHAR(255) NOT NULL,"
- + " ATTR_VALUE VARCHAR(255),"
- + " ROLE_ID VARCHAR(255),"
- + " FOREIGN KEY (ROLE_ID) REFERENCES UM_ROLES(ID) ON DELETE CASCADE,"
- + " PRIMARY KEY (ID))";
-
- public static String permissionTable = "CREATE TABLE UM_PERMISSIONS (ID VARCHAR(255),"
- + " RESOURCE_ID VARCHAR(255) NOT NULL,"
- + " ACTION VARCHAR(255) NOT NULL," + " PRIMARY KEY (ID))";
-
- public static String rolePermissionTable = "CREATE TABLE UM_ROLE_PERMISSIONS (ID VARCHAR(255),"
- + " PERMISSION_ID VARCHAR(255) NOT NULL,"
- + " ROLE_ID VARCHAR(255) NOT NULL,"
- + " IS_ALLOWED SMALLINT NOT NULL,"
- + " FOREIGN KEY (PERMISSION_ID) REFERENCES UM_PERMISSIONS(ID) ON DELETE CASCADE,"
- + " FOREIGN KEY (ROLE_ID) REFERENCES UM_ROLES(ID) ON DELETE CASCADE,"
- + " UNIQUE (PERMISSION_ID, ROLE_ID)," + " PRIMARY KEY (ID))";
-
- public static String userPermissionTable = "CREATE TABLE UM_USER_PERMISSIONS (ID VARCHAR(255),"
- + " IS_ALLOWED SMALLINT NOT NULL,"
- + " PERMISSION_ID VARCHAR(255) NOT NULL,"
- + " USER_ID VARCHAR(255) NOT NULL,"
- + " FOREIGN KEY (PERMISSION_ID) REFERENCES UM_PERMISSIONS(ID) ON DELETE CASCADE,"
- + " FOREIGN KEY (USER_ID) REFERENCES UM_USERS(ID) ON DELETE CASCADE,"
- + " UNIQUE (PERMISSION_ID, USER_ID)," + " PRIMARY KEY (ID))";
-
- public static String userRolesTable = "CREATE TABLE UM_USER_ROLES (ID VARCHAR(255),"
- + " ROLE_ID VARCHAR(255) NOT NULL,"
- + " USER_ID VARCHAR(255) NOT NULL,"
- + " FOREIGN KEY (ROLE_ID) REFERENCES UM_ROLES(ID) ON DELETE CASCADE,"
- + " FOREIGN KEY (USER_ID) REFERENCES UM_USERS(ID) ON DELETE CASCADE,"
- + " UNIQUE (USER_ID, ROLE_ID)," + " PRIMARY KEY (ID))";
public static void createDatabase(Connection conn) throws SQLException,
@@ -90,4 +111,43 @@
conn.commit();
}
+ // TODO End -delete
+
+ public static int getRoleId(Connection dbConnection, String roleName) throws UserStoreException {
+ int id = -1;
+ try {
+ PreparedStatement getRolIdStmt = dbConnection.prepareStatement(DefaultRealmConstants.GET_ROLE_ID_SQL);
+ getRolIdStmt.setString(1, roleName);
+ ResultSet rs = getRolIdStmt.executeQuery();
+ if (rs.next()) {
+ id = rs.getInt(DefaultRealmConstants.COLUMN_NAME_ID);
+ }
+
+ getRolIdStmt.close();
+ } catch (SQLException e) {
+ throw new UserStoreException("errorReadingFromUserStore", e);
+ }
+ return id;
+ }
+
+ public static int getUserId(Connection dbConnection, String userName) throws UserStoreException {
+ int id = -1;
+ try {
+ PreparedStatement getUserIdStmt = dbConnection
+ .prepareStatement(DefaultRealmConstants.GET_USER_ID_SQL);
+ getUserIdStmt.setString(1, userName);
+ ResultSet rs = getUserIdStmt.executeQuery();
+ if (rs.next()) {
+ id = rs.getInt(DefaultRealmConstants.COLUMN_NAME_ID);
+ }
+ getUserIdStmt.close();
+ } catch (SQLException e) {
+ throw new UserStoreException("errorReadingFromUserStore", e);
+ }
+ return id;
+ }
+
+
+
+
}
Modified: trunk/registry/modules/core/src/main/resources/database-scripts/derby-complete.sql
==============================================================================
--- trunk/registry/modules/core/src/main/resources/database-scripts/derby-complete.sql (original)
+++ trunk/registry/modules/core/src/main/resources/database-scripts/derby-complete.sql Thu Mar 6 07:38:13 2008
@@ -87,11 +87,66 @@
FOREIGN KEY (DAID) REFERENCES ARTIFACTS (AID),
FOREIGN KEY (AID) REFERENCES ARTIFACTS (AID));
-CREATE TABLE UM_USERS (ID VARCHAR(255), USER_NAME VARCHAR(255) NOT NULL UNIQUE, PASSWORD VARCHAR(255) NOT NULL, PRIMARY KEY (ID));
-CREATE TABLE UM_USER_ATTRIBUTES (ID VARCHAR(255), ATTR_NAME VARCHAR(255) NOT NULL, ATTR_VALUE VARCHAR(255), USER_ID VARCHAR(255), FOREIGN KEY (USER_ID) REFERENCES UM_USERS(ID) ON DELETE CASCADE, PRIMARY KEY (ID));
-CREATE TABLE UM_ROLES (ID VARCHAR(255), ROLE_NAME VARCHAR(255) NOT NULL UNIQUE, PRIMARY KEY (ID));
-CREATE TABLE UM_ROLE_ATTRIBUTES (ID VARCHAR(255), ATTR_NAME VARCHAR(255) NOT NULL, ATTR_VALUE VARCHAR(255), ROLE_ID VARCHAR(255), FOREIGN KEY (ROLE_ID) REFERENCES UM_ROLES(ID) ON DELETE CASCADE, PRIMARY KEY (ID));
-CREATE TABLE UM_PERMISSIONS (ID VARCHAR(255), RESOURCE_ID VARCHAR(255) NOT NULL, ACTION VARCHAR(255) NOT NULL, PRIMARY KEY (ID));
-CREATE TABLE UM_ROLE_PERMISSIONS (ID VARCHAR(255), PERMISSION_ID VARCHAR(255), ROLE_ID VARCHAR(255), IS_ALLOWED SMALLINT NOT NULL, FOREIGN KEY (PERMISSION_ID) REFERENCES UM_PERMISSIONS(ID) ON DELETE CASCADE, FOREIGN KEY (ROLE_ID) REFERENCES UM_ROLES(ID) ON DELETE CASCADE, PRIMARY KEY (ID));
-CREATE TABLE UM_USER_PERMISSIONS (ID VARCHAR(255), IS_ALLOWED SMALLINT NOT NULL, PERMISSION_ID VARCHAR(255), USER_ID VARCHAR(255), FOREIGN KEY (PERMISSION_ID) REFERENCES UM_PERMISSIONS(ID) ON DELETE CASCADE, FOREIGN KEY (USER_ID) REFERENCES UM_USERS(ID) ON DELETE CASCADE, PRIMARY KEY (ID));
-CREATE TABLE UM_USER_ROLES (ID VARCHAR(255), ROLE_ID VARCHAR(255), USER_ID VARCHAR(255), FOREIGN KEY (ROLE_ID) REFERENCES UM_ROLES(ID) ON DELETE CASCADE, FOREIGN KEY (USER_ID) REFERENCES UM_USERS(ID) ON DELETE CASCADE, PRIMARY KEY (ID));
+CREATE TABLE UM_USERS (
+ ID INTEGER GENERATED ALWAYS AS IDENTITY,
+ USER_NAME VARCHAR(255) NOT NULL,
+ PASSWORD VARCHAR(255) NOT NULL,
+ PRIMARY KEY (ID),
+ UNIQUE(USER_NAME));
+
+CREATE TABLE UM_USER_ATTRIBUTES (
+ ID INTEGER GENERATED ALWAYS AS IDENTITY,
+ ATTR_NAME VARCHAR(255) NOT NULL,
+ ATTR_VALUE VARCHAR(255),
+ USER_ID INTEGER,
+ FOREIGN KEY (USER_ID) REFERENCES UM_USERS(ID) ON DELETE CASCADE,
+ PRIMARY KEY (ID));
+
+CREATE TABLE UM_ROLES (
+ ID INTEGER GENERATED ALWAYS AS IDENTITY,
+ ROLE_NAME VARCHAR(255) NOT NULL,
+ PRIMARY KEY (ID),
+ UNIQUE(ROLE_NAME));
+
+CREATE TABLE UM_ROLE_ATTRIBUTES (
+ ID INTEGER GENERATED ALWAYS AS IDENTITY,
+ ATTR_NAME VARCHAR(255) NOT NULL,
+ ATTR_VALUE VARCHAR(255),
+ ROLE_ID INTEGER,
+ FOREIGN KEY (ROLE_ID) REFERENCES UM_ROLES(ID) ON DELETE CASCADE,
+ PRIMARY KEY (ID));
+
+CREATE TABLE UM_PERMISSIONS (
+ ID INTEGER GENERATED ALWAYS AS IDENTITY,
+ RESOURCE_ID VARCHAR(255) NOT NULL,
+ ACTION VARCHAR(255) NOT NULL,
+ PRIMARY KEY (ID));
+
+CREATE TABLE UM_ROLE_PERMISSIONS (
+ ID INTEGER GENERATED ALWAYS AS IDENTITY,
+ PERMISSION_ID INTEGER NOT NULL,
+ ROLE_ID INTEGER NOT NULL,
+ IS_ALLOWED SMALLINT NOT NULL,
+ UNIQUE (PERMISSION_ID, ROLE_ID),
+ FOREIGN KEY (PERMISSION_ID) REFERENCES UM_PERMISSIONS(ID) ON DELETE CASCADE,
+ FOREIGN KEY (ROLE_ID) REFERENCES UM_ROLES(ID) ON DELETE CASCADE,
+ PRIMARY KEY (ID));
+
+CREATE TABLE UM_USER_PERMISSIONS (
+ ID INTEGER GENERATED ALWAYS AS IDENTITY,
+ PERMISSION_ID INTEGER NOT NULL,
+ USER_ID INTEGER NOT NULL,
+ IS_ALLOWED SMALLINT NOT NULL,
+ UNIQUE (PERMISSION_ID, USER_ID),
+ FOREIGN KEY (PERMISSION_ID) REFERENCES UM_PERMISSIONS(ID) ON DELETE CASCADE,
+ FOREIGN KEY (USER_ID) REFERENCES UM_USERS(ID) ON DELETE CASCADE,
+ PRIMARY KEY (ID));
+
+CREATE TABLE UM_USER_ROLES (
+ ID INTEGER GENERATED ALWAYS AS IDENTITY,
+ ROLE_ID INTEGER NOT NULL,
+ USER_ID INTEGER NOT NULL,
+ UNIQUE (USER_ID, ROLE_ID),
+ FOREIGN KEY (ROLE_ID) REFERENCES UM_ROLES(ID) ON DELETE CASCADE,
+ FOREIGN KEY (USER_ID) REFERENCES UM_USERS(ID) ON DELETE CASCADE,
+ PRIMARY KEY (ID));
Modified: trunk/registry/modules/core/src/main/resources/database-scripts/hsql-registry.sql
==============================================================================
--- trunk/registry/modules/core/src/main/resources/database-scripts/hsql-registry.sql (original)
+++ trunk/registry/modules/core/src/main/resources/database-scripts/hsql-registry.sql Thu Mar 6 07:38:13 2008
@@ -75,4 +75,68 @@
DVN INTEGER NOT NULL,
UNIQUE (AID,VN,DAID,DVN),
FOREIGN KEY (DAID) REFERENCES ARTIFACTS (AID),
- FOREIGN KEY (AID) REFERENCES ARTIFACTS (AID));
\ No newline at end of file
+ FOREIGN KEY (AID) REFERENCES ARTIFACTS (AID));
+
+CREATE TABLE UM_USERS (
+ ID INTEGER GENERATED BY DEFAULT AS IDENTITY,
+ USER_NAME VARCHAR(255) NOT NULL,
+ PASSWORD VARCHAR(255) NOT NULL,
+ PRIMARY KEY (ID),
+ UNIQUE(USER_NAME));
+
+CREATE TABLE UM_USER_ATTRIBUTES (
+ ID INTEGER GENERATED BY DEFAULT AS IDENTITY,
+ ATTR_NAME VARCHAR(255) NOT NULL,
+ ATTR_VALUE VARCHAR(255),
+ USER_ID INTEGER,
+ FOREIGN KEY (USER_ID) REFERENCES UM_USERS(ID) ON DELETE CASCADE,
+ PRIMARY KEY (ID));
+
+CREATE TABLE UM_ROLES (
+ ID INTEGER GENERATED BY DEFAULT AS IDENTITY,
+ ROLE_NAME VARCHAR(255) NOT NULL,
+ PRIMARY KEY (ID),
+ UNIQUE(ROLE_NAME));
+
+CREATE TABLE UM_ROLE_ATTRIBUTES (
+ ID INTEGER GENERATED BY DEFAULT AS IDENTITY,
+ ATTR_NAME VARCHAR(255) NOT NULL,
+ ATTR_VALUE VARCHAR(255),
+ ROLE_ID INTEGER,
+ FOREIGN KEY (ROLE_ID) REFERENCES UM_ROLES(ID) ON DELETE CASCADE,
+ PRIMARY KEY (ID));
+
+CREATE TABLE UM_PERMISSIONS (
+ ID INTEGER GENERATED BY DEFAULT AS IDENTITY,
+ RESOURCE_ID VARCHAR(255) NOT NULL,
+ ACTION VARCHAR(255) NOT NULL,
+ PRIMARY KEY (ID));
+
+CREATE TABLE UM_ROLE_PERMISSIONS (
+ ID INTEGER GENERATED BY DEFAULT AS IDENTITY,
+ PERMISSION_ID INTEGER NOT NULL,
+ ROLE_ID INTEGER NOT NULL,
+ IS_ALLOWED SMALLINT NOT NULL,
+ UNIQUE (PERMISSION_ID, ROLE_ID),
+ FOREIGN KEY (PERMISSION_ID) REFERENCES UM_PERMISSIONS(ID) ON DELETE CASCADE,
+ FOREIGN KEY (ROLE_ID) REFERENCES UM_ROLES(ID) ON DELETE CASCADE,
+ PRIMARY KEY (ID));
+
+CREATE TABLE UM_USER_PERMISSIONS (
+ ID INTEGER GENERATED BY DEFAULT AS IDENTITY,
+ PERMISSION_ID INTEGER NOT NULL,
+ USER_ID INTEGER NOT NULL,
+ IS_ALLOWED SMALLINT NOT NULL,
+ UNIQUE (PERMISSION_ID, USER_ID),
+ FOREIGN KEY (PERMISSION_ID) REFERENCES UM_PERMISSIONS(ID) ON DELETE CASCADE,
+ FOREIGN KEY (USER_ID) REFERENCES UM_USERS(ID) ON DELETE CASCADE,
+ PRIMARY KEY (ID));
+
+CREATE TABLE UM_USER_ROLES (
+ ID INTEGER GENERATED BY DEFAULT AS IDENTITY,
+ ROLE_ID INTEGER NOT NULL,
+ USER_ID INTEGER NOT NULL,
+ UNIQUE (USER_ID, ROLE_ID),
+ FOREIGN KEY (ROLE_ID) REFERENCES UM_ROLES(ID) ON DELETE CASCADE,
+ FOREIGN KEY (USER_ID) REFERENCES UM_USERS(ID) ON DELETE CASCADE,
+ PRIMARY KEY (ID));
More information about the Registry-dev
mailing list