Dropping foreign key constraints from database

If you have explicitly stated the name of a constraint in a database, you can easily drop the constraint using the name specified. But if you haven't specified a name for a constraint at the time of the construction of the table, database engine will assign a name automatically and you will have to find the constraint name from the system tables of the database.

From this post we are going to see how to find the name of a foreign key constraint and drop it in a single script for different database systems.

1. H2

CREATE ALIAS IF NOT EXISTS DROP_FK AS $$ void executeSql(Connection conn, String sql)
throws SQLException { conn.createStatement().executeUpdate(sql); } $$;

call drop_fk('ALTER TABLE TABLE_NAME DROP CONSTRAINT ' ||
(SELECT CONSTRAINT_NAME FROM INFORMATION_SCHEMA.CONSTRAINTS 
WHERE TABLE_NAME = 'TABLE_NAME' AND COLUMN_LIST  = 'COLUMN_NAME'));

DROP ALIAS IF EXISTS DROP_FK;

2. Microsoft SQL Server

DECLARE @COMMAND NVARCHAR(200);

SELECT @COMMAND= 'ALTER TABLE TABLE_NAME DROP CONSTRAINT ' + RC.CONSTRAINT_NAME + ';'
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC
JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KF ON RC.CONSTRAINT_NAME = KF.CONSTRAINT_NAME
JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KP ON RC.UNIQUE_CONSTRAINT_NAME = KP.CONSTRAINT_NAME
WHERE KF.TABLE_NAME = 'TABLE_NAME';

EXEC (@COMMAND);

3. MySQL

SET @databasename = "sample";

SELECT CONCAT("ALTER TABLE TABLE_NAME DROP FOREIGN KEY ",constraint_name)
INTO @sqlst
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
where TABLE_SCHEMA = @databasename and TABLE_NAME = "TABLE_NAME"
and referenced_column_name is not NULL limit 1;

PREPARE stmt FROM @sqlst;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
SET @sqlstr = NULL;

4. Oracle

declare
  con_name varchar2(100);
  command varchar2(200);
  databasename VARCHAR2(100);
BEGIN
select constraint_name into con_name from all_constraints where table_name='TABLE_NAME' AND owner=databasename AND 
constraint_type = 'R';

    if TRIM(con_name) is not null
    then
      command := 'ALTER TABLE TABLE_NAME DROP CONSTRAINT ' || con_name;
      dbms_output.Put_line(command);
      execute immediate command;
    end if;

    exception
    when NO_DATA_FOUND
    then
    dbms_output.Put_line('Foreign key not found');
END;

5. PostgreSQL

DO $$ DECLARE con_name varchar(200); 
BEGIN 

SELECT 'ALTER TABLE table_name DROP CONSTRAINT ' || tc .constraint_name || ';' INTO con_name 
FROM information_schema.table_constraints AS tc 
JOIN information_schema.key_column_usage AS kcu ON tc.constraint_name = kcu.constraint_name 
JOIN information_schema.constraint_column_usage AS ccu ON ccu.constraint_name = tc.constraint_name 
WHERE constraint_type = 'FOREIGN KEY' AND tc.table_name = 'table_name' AND kcu.column_name = 'column_name';
 
EXECUTE con_name; 
END $$;

If you know the basic syntax of the sql, you can customize them according to your requirements.

Handling SAML metadata with opensaml - Part 1

SAML metadata specification describes SAML metadata as,

SAML profiles require agreements between system entities regarding identifiers, binding support and endpoints, certificates and keys, and so forth. A metadata specification is useful for describing this information in a standardized way. This specification defines an extensible metadata format for SAML system entities, organized by roles that reflect SAML profiles.

Opensaml library provides facilities to handle SAML metadata. From here you can add opensaml dependency to your project or download the jar.
http://mvnrepository.com/artifact/org.opensaml/opensaml/2.6.4

Bellow method can be used to create opensaml metadata objects.

1 2 3 4 5 6 7 8

private <T> T createSAMLObject(final Class<T> clazz) throws NoSuchFieldException, IllegalAccessException { XMLObjectBuilderFactory builderFactory = Configuration.getBuilderFactory(); QName defaultElementName = (QName)clazz.getDeclaredField("DEFAULT_ELEMENT_NAME").get(null); T object = (T)builderFactory.getBuilder(defaultElementName).buildObject(defaultElementName); return object; }

As an example if we want to create EntityDescriptor object, we can do it like this

EntityDescriptor entityDescriptor = createSAMLObject(EntityDescriptor.class);

Properties of the EntityDescriptor like EntityID can set simply like bellow.

entityDescriptor.setEntityID("EntityID");

EntityDescriptor contains one or more RoleDescriptors. We can create a role descriptor as bellow.

SPSSODescriptor spSSODescriptor = createSAMLObject(SPSSODescriptor.class);

SPSSODescriptor can be added to EntityDescriptor like bellow.

entityDescriptor.getRoleDescriptors().add(spSSODescriptor);

All the properties and elements of SAML Metadata spec are supported by opensaml library. So you can easily handle SAML metadata like this.

If we want to get the xml string of the metadata object we have created, we can use the bellow method

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

public String getMetadataString(EntityDescriptor entityDescriptor) { String metadataXML = ""; DocumentBuilder builder; DocumentBuilderFactory factory = DocumentBuilderFactory.newInstance(); try { builder = factory.newDocumentBuilder(); Document document = builder.newDocument(); Marshaller out = Configuration.getMarshallerFactory().getMarshaller(entityDescriptor); out.marshall(entityDescriptor, document); Transformer transformer = TransformerFactory.newInstance().newTransformer(); transformer.setOutputProperty(OutputKeys.INDENT, "yes"); transformer.setOutputProperty("{http://xml.apache.org/xslt}indent-amount", "2"); StringWriter stringWriter = new StringWriter(); StreamResult streamResult = new StreamResult(stringWriter); DOMSource source = new DOMSource(document); transformer.transform(source, streamResult); stringWriter.close(); metadataXML = stringWriter.toString(); } catch (ParserConfigurationException | IOException | MarshallingException | TransformerException e) { e.printStackTrace(); } return metadataXML; }

If we want to create a metadata object from a metadata xml file, we can use the bellow method.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18

public EntityDescriptor getMetadataObject(){ EntityDescriptor entityDescriptor = null; try { FilesystemMetadataProvider idpMetaDataProvider = new FilesystemMetadataProvider(new File("file_path")); idpMetaDataProvider.setRequireValidMetadata(true); idpMetaDataProvider.setParserPool(new BasicParserPool()); idpMetaDataProvider.initialize(); XMLObject xmlObject = idpMetaDataProvider.getMetadata(); entityDescriptor = (EntityDescriptor)xmlObject; } catch (MetadataProviderException e) { e.printStackTrace(); } return entityDescriptor; }

Before we start to do any of these things we need to bootstrap opensaml library. We can do it simply like this,

DefaultBootstrap.bootstrap();

In my next post I will focus on how to create saml metadata extensions.

Digital Signature Screening Scheme

To sign a message m ElGamal signature scheme, the signer performs the following steps.

The pair of (r,s) is the digital signature for message m. So generated the signature can be verified by using the below steps.

To speedup the verifying procedure, we can use batch verification scheme called screening. When verifying a batch of signed messages by a single signer, without calculating each and every g^H(m_i) separately, we calculate the sum of all H(m_i) values and raise it to the power of g , and verify all messages at once. That is calculating g^{H(m₁) + H(m₂) + .. + H(m_n)} and check the verifiability of all messages at same time.

If a single signature is tainted in the transmission, this procedure will capture it. But if two or more messages are tainted, error in each may cancel out. So we have to take random subsets when performing the test.

Lets see how we can do this using Java. First we generate keys and sign 10 messages.

// Key Generation
Random rand = new SecureRandom();
BigInteger secretKey = new BigInteger("12345678901234567890");
BigInteger q = BigInteger.probablePrime(64, rand); // select q, probably an integer
BigInteger g = new BigInteger("3"); // g integer
BigInteger Y = g.modPow(secretKey, q); // y = g^x mod q

//generate 10 messages
int range = 10;
Random rn = new Random();
String[] message = new String[range];
 
for(int i=0; i<range; i++){
 message[i] = rn.nextInt((int) Math.pow(2, 64))+"";
}
  
// Sign
BigInteger[] rs = new BigInteger[message.length];
BigInteger[] signs = new BigInteger[message.length];
BigInteger m, k;
for (int i = 0; i < message.length; i++) {
 m = new BigInteger(message[i]);
 k = BigInteger.probablePrime(64, rand);
 rs[i] = g.modPow(k, q);
 signs[i] = ((new BigInteger(m.hashCode() + "")).subtract(secretKey
     .multiply(rs[i]))).multiply(k.modInverse(q
     .subtract(new BigInteger("1"))));
}

Now we can see how the normal verification works.

// Normal Verification
BigInteger rhs, lhs;
for (int i = 0; i < message.length; i++) {
 rhs = g.modPow(
   new BigInteger((new BigInteger(message[i])).hashCode() + ""),
   q);
 lhs = Y.modPow(rs[i], q).multiply(rs[i].modPow(signs[i], q)).mod(q);
 if (rhs.equals(lhs)) {
   System.out.println("Signature " + (i+1) + " : verified");
 } else {
   System.out.println("Signature " + (i+1) + " : not verified");
 }
}

This is how the batch verification is done.

// Batch Verification
int randomNum = rn.nextInt(range);
  
BigInteger sumhm1 = new BigInteger("0");
BigInteger lhsMultiplication1 = new BigInteger("1");
 
BigInteger sumhm2 = new BigInteger("0");
BigInteger lhsMultiplication2 = new BigInteger("1");
 
//divide into 2 subsets
for (int i = 0; i < message.length; i++) {
 if (i < randomNum) {
  sumhm1 = sumhm1.add(new BigInteger((new BigInteger(message[i]))
   .hashCode() + ""));
  lhsMultiplication1 = lhsMultiplication1.multiply(Y
   .modPow(rs[i], q).multiply(rs[i].modPow(signs[i], q))
   .mod(q));
 }else{
  sumhm2 = sumhm2.add(new BigInteger((new BigInteger(message[i]))
   .hashCode() + ""));
  lhsMultiplication2 = lhsMultiplication2.multiply(Y
   .modPow(rs[i], q).multiply(rs[i].modPow(signs[i], q))
   .mod(q));
 }
}
  
//verify subset 1
BigInteger rhs1 = g.modPow(sumhm1, q);
BigInteger lhs1 = lhsMultiplication1.mod(q);

if (rhs1.equals(lhs1)) {
  System.out.println("Batch Screen subset 1 : verified");
} else {
  System.out.println("Batch Screen subset 1 : not verified");
}
  
//verify subset 2
BigInteger rhs2 = g.modPow(sumhm2, q);
BigInteger lhs2 = lhsMultiplication2.mod(q);

if (rhs2.equals(lhs2)) {
  System.out.println("Batch Screen subset 2 : verified");
} else {
  System.out.println("Batch Screen subset  :2 not verified");
}

Lets see results of the work. By verifying 10 messages 10 times using both procedures I got below results.

Average for single message verification = 8386173.8

Average for screening = 6563373.1

So from the results we can see that using batch verification scheme, a noticeable improvement of performance can be achieved.