Technical Blogs By Mohit Dagar: December 2016

Thursday 22 December 2016

Implement Kentor auth service in asp.net MVC

It's very simple to Implement Kentor auth service.

For example point of view i am taking Kentor.AuthServices.StubIdp as my IDP(identity provider)

Install Kentor AuthServices for Nuget to your application

Install-Package Kentor.AuthServices

Your need two certificates service Certificates and signing Certificate. for data exchange and Encryption. Download certificate http://stubidp.kentor.se/
Add the following code to your web config file.

<configSections>
    
    <section name="system.identityModel" type="System.IdentityModel.Configuration.SystemIdentityModelSection, System.IdentityModel, Version=4.0.0.0, Culture=neutral, PublicKeyToken=B77A5C561934E089" />
    <section name="system.identityModel.services" type="System.IdentityModel.Services.Configuration.SystemIdentityModelServicesSection, System.IdentityModel.Services, Version=4.0.0.0, Culture=neutral, PublicKeyToken=B77A5C561934E089" />
    <section name="kentor.authServices" type="Kentor.AuthServices.Configuration.KentorAuthServicesSection, Kentor.AuthServices"/>
    <!--End of section-- </configSections>
<authentication mode="Forms">
<forms loginUrl="~/AuthServices/SignIn" />
</authentication>
<kentor.authServices entityId="http://localhost:61548/Home/Index"
                       returnUrl="http://localhost:61548/Home/Index"
                       authenticateRequestSigningBehavior="Never">
    <nameIdPolicy allowCreate="true"
                  format="Persistent"/>
    <metadata cacheDuration="0:0:42"
              validDuration="7.12:00:00"
              wantAssertionsSigned="true">
      <organization name="Kentor IT AB"
                    displayName="Kentor"
                    url="http://www.kentor.se"
                    language="sv" />
      <contactPerson type="Other" email="info@kentor.se" />
      
    </metadata>
    <identityProviders>
      <add entityId="http://stubidp.kentor.se/Metadata"
           signOnUrl="http://stubidp.kentor.se"
           allowUnsolicitedAuthnResponse="true"
           binding="HttpRedirect"
           wantAuthnRequestsSigned="true">
        <signingCertificate fileName="~/App_Data/Kentor.AuthServices.StubIdp.cer" />
      </add>

    </identityProviders>
    
    <serviceCertificates>
      <add fileName="~/App_Data/Kentor.AuthServices.Tests.pfx" />
    </serviceCertificates>
</kentor.authServices>

To get authencated user details user this code to your action method

[Authorize]
public ActionResult Index()
{
var identity = System.Web.HttpContext.Current.User.Identity as ClaimsIdentity;
return View(identity.Claims);
}

Your View Should have following code to print values that received for idp

@model System.Collections.Generic.IEnumerable<System.Security.Claims.Claim>
<p>This is a secure page that only works when logged in.</p>
<p>Claims:</p>
<ul>
@foreach (var claim in Model)
{
<li>@claim.Type - @claim.Value</li>
}
</ul>

For more detail configuration click Knetor auth service

Basic tips for writing best SQL Query.

1) It is good to use columns names in SELECT statement instead of than '*'.

For Example: Write the query as

 
SELECT id, Name, age, Contact, Address FROM Customers;

Instead of:


  SELECT * FROM

Customers;

2) Minimize the number of sub query block in your query.

For Example: Write the query as


SELECT name, age, salary FROM employee 

WHERE (salary, age ) = (SELECT MAX (salary), MAX (age)   

                   FROM employee_details)  AND dept = 'Electronics';

Instead of:


SELECT

name, age, salary FROM employee

WHERE salary = (SELECT MAX(salary) FROM employee_details) 

AND age = (SELECT MAX(age) FROM employee_details) 

AND emp_dept = 'Electronics';

3) HAVING clause is used to filter the rows after all the rows are selected. It is just like a filter.

For Example: Write the query as


SELECT Designation, count(

Designation) FROM Employee
WHERE

Designation!= 'Manager'  
GROUP BY

Designation;

Instead of:


SELECT

Designation, count(Designation) FROM Employee GROUP BY

Designation
HAVING

Designation!= 'Manager';

4) Use operator EXISTS, IN and table joins appropriately in your query.

Usually IN has the slowest performance.
IN is efficient when most of the filter criteria is in the sub-query.
EXISTS is efficient when most of the filter criteria is in the main query.

For Example: Write the query as


Select p.Name, P.Price, p.Quantity from product p  where EXISTS 
(select

o.Name, o.Price, o.Quantity from orders o 

where o.product_id = p.product_id)

Instead of:


Select

p.Name, P.Price, p.Quantity from product p where product_id IN 

(select product_id from order_items)

5) Use EXISTS instead of DISTINCT when using joins which involves tables having one-to-many relationship.
For Example: Write the query as


SELECT d.dept_id, d.dept FROM dept d  

WHERE EXISTS ( SELECT 'X' 
                      FROM employee e 
                      WHERE e.dept = d.dept);

Instead of:


SELECT DISTINCT d.dept_id, d.dept FROM dept d,employee e 

WHERE e.dept = e.dept;

6) Try to use UNION ALL in place of UNION.

For Example: Write the query as


SELECT id, first_name  FROM student_details_class10   

UNION ALL   SELECT id, first_name    
FROM sports_team;

Instead of:


SELECT id, first_name, subject  

FROM student_details_class10  

UNION 

SELECT id, first_name   

FROM sports_team;

7) Be careful while using conditions in where clause.

For Example: Write the query as


SELECT id, first_name, age FROM student_details WHERE age > 10;

Instead of:


SELECT id, first_name, age FROM student_details WHERE age != 10;

Write the query as


SELECT id, first_name, age  FROM student_details 

WHERE first_name LIKE 'Chan%';

Instead of:


SELECT id, first_name, age  FROM student_details 

WHERE SUBSTR(first_name,1,3) = 'Cha';

Write the query as


SELECT id, first_name, age  FROM student_details 

WHERE first_name LIKE NVL ( :name, '%');

Instead of:


SELECT id, first_name, age  FROM student_details 

WHERE first_name = NVL ( :name, first_name);

Write the query as


SELECT product_id, product_name FROM product  

WHERE unit_price BETWEEN MAX(unit_price) and MIN(unit_price)

Instead of:


SELECT product_id, product_name FROM product 

WHERE unit_price >= MAX(unit_price)  

and unit_price <= MIN(unit_price)

Write the query as


SELECT id, name, salary  FROM employee WHERE dept = 'Electronics'  

AND location = 'Bangalore';

Instead of:


SELECT id, name, salary  FROM employee 

WHERE dept || location= 'ElectronicsBangalore';

8) Use DECODE to avoid the scanning of same rows or joining the same table repetitively. DECODE can also be made used in place of GROUP BY or ORDER BY clause.

For Example: Write the query as


SELECT id 
FROM employee WHERE name LIKE 'Ramesh%' 
 
and location = 'Bangalore';

Instead of:


SELECT DECODE(location,'Bangalore',id,NULL) id
FROM employee 

WHERE name LIKE 'Ramesh%';

9) Use of valid Schema for SQL Query.

For Example: Write the query as


SELECT id,Name,salary 
FROM [dbo].[employee]

Instead of:


SELECT id,Name,salary 
FROM employee

Coding standard for SQL

1. Formatting Standards

a. Table Rules :

i. Pascal notation; end with an ‘s’ Like Products, Customers

ii. Group related table names

b. Stored Procedure Rules:

i. Name : USP_<App Name>_ [<Group Name >_] <Action><table/logical instance>

ii. Example : USP_Orders_GetNewOrders, usp_Products_UpdateProduct

c. Triggers Rule :

i. Name : TR_<Table Name>_<action>

ii. Examples: TR_Orders_UpdateProducts

d. Indexes Rules :

i. IX_<TableName>_<Columns >

ii. IX_Products_ProductID

e. Primary Keys :

i. Key Name : PK_<TableName>

ii. Example : PK_products

f. Foreign Key :

i. FK_<PrimaryKeyTable>_<ForeignKeyTable>

ii. Example : FK_Products_Orders

g. Default vales :

i. DF_<TableName>_<ColumnName>

ii. Example : DF_Products_Quantity

2. General Rules :

a. Use upper case for all SQL keywords

i. SELECT, INSERT, UPDATE, WHERE, AND, OR, LIKE, etc.

b. Do not use spaces in the name of database objects

i. not use SQL keywords as the name of database objects

1. In cases where this is necessary surround the object name with bracket, such as [Year]

c. Do not prefix stored procedures with sp_

i. Because it shows System stored procedures can conflict with other procedures

d. Indent code to improve readability

e. Comment code blocks that are not easily understandable

i. Use single-line comment markers(–)

ii. Reserve multi-line comments (/*.. ..*/) for blocking out sections of code

f. Use parentheses to increase readability

i. Example : WHERE (color=’red’ AND(size = 1 OR size = 2))

g. Use BEGIN..END blocks only when multiple statements are present within a conditional code segment.

h. Use one blank line to separate code sections

i. Use spaces so that expressions read like sentences.

i. fillfactor = 25, not fillfactor=25

j. Format JOIN operations using indents

k. Format JOIN operations using indents

l. Use ANSI Joins instead of old style joins

3. Structure :

a. Each table must have a primary key

i. In most cases it should be an IDENTITY column named ID

b. Normalize data to third normal form

i. Do not compromise on performance to reach third normal form. Sometimes, a little de-normalization results in better performance.

c. Do not use TEXT as a data type; use the maximum allowed characters of VARCHAR instead

d. In VARCHAR data columns

i. do not default to NULL

ii. use an empty string instead

e. Columns with default values should not allow NULLs

f. As much as possible, create stored procedures on the same database as the main tables they will be accessing

4. Coding guidelines :

a. Optimize queries using the tools

b. Do not use SELECT * (Use name of columns)

c. Place SET statements before any executing code in the procedure

d. Return multiple result sets from one stored procedure to avoid trips from the application server to SQL server

e. Avoid unnecessary use of temporary tables

i. Use ‘Derived tables’ or CTE (Common Table Expressions) wherever possible, as they perform better⁶

f. Avoid using <> as a comparison operator

i. Use ID IN(1,3,4,5) instead of ID <> 2

g. Use SET NOCOUNT ON at the beginning of stored procedures

h. Fully qualify tables and column names in JOINs

i. Fully qualify all stored procedure and table references in stored procedures.

j. Do not define default values for parameters.

k. If a default is needed, the front end will supply the value.

l. Do not use the RECOMPILE option for stored procedures.

m. Place all DECLARE statements before any other code in the procedure.

n. Do not use column numbers in the ORDER BY clause.

o. Do not use GOTO.

p. use TRY/CATCH

q. Do basic validations in the front-end itself during data entry

r. Off-load tasks, like string manipulations, concatenations, row numbering, case conversions, type conversions etc., to the front-end applications if these operations are going to consume more CPU cycles on the database server

s. Always use a column list in your INSERT statements.

i. This helps avoid problems when the table structure changes (like adding or dropping a column).

t. Minimize the use of NULLs, as they often confuse front-end applications, unless the applications are coded intelligently to eliminate NULLs or convert the NULLs into some other form.

i. Any expression that deals with NULL results in a NULL output.

ii. The ISNULL and COALESCE functions are helpful in dealing with NULL values.

u. Avoid the use of cross joins, if possible.

v. When executing an UPDATE or DELETE statement, use the primary key in the WHERE condition, if possible. This reduces error possibilities.

w. Avoid using TEXT or NTEXT datatypes for storing large textual data.⁹

i. Use the maximum allowed characters of VARCHAR instead

x. Avoid dynamic SQL statements as much as possible.

y. Avoid wild-card characters at the beginning of a word while searching using the LIKE keyword, as these results in an index scan, which defeats the purpose of an index.

z. Do not use white space in identifiers.

5. Show some smartness when writing a code :

a. Let’s understand it by some examples

b. Consider the following query to find the second highest offer price from the Items table

i. SELECT MAX(Price) FROM Products WHERE ID

( SELECT TOP 2 ID FROM Products ORDER BY Price DESC )

c. Same code can be written as

i. SELECT MAX(Price) FROM (SELECT TOP 2 Price FROM Products ORDER BY Price DESC)

d. Use code Like this

i. IF EXISTS( SELECT 1 FROM Products WHERE ID = 50)

ii. Instead of : IF EXISTS(SELECT COUNT(ID) FROM Products WHERE ID = 50)

6. Security of Database Server :

a. Disable unnecessary features and services.

b. Install only required components.

c. Enforce strong password policy (There should a strong password for every database user)

d. Disable the SA or (any default) account or rename it.

i. Do not use this account for SQL server management

e. Remove BUILDIN\Administrators group from the SQL Server Logins

f. Enable logging SQL Server login attempts (failed & successful).

g. Defining Database Ownership to Increase Security

i. Database Server instance can contain many databases which were created by users who are database owners - DBO (by default).

ii. Thus several best practices should be implemented regarding these special ownerships

1. Minimize the number accounts/users that have the db_owner role for each database.

2. Have distinct owners for databases; not all databases should be owned by SA or by any other user in sysadmin server role.

h. Hardening the network connectivity

i. A default installation of SQL Server use TCP port 1433 for client requests and communications. These ports are well known and are common target for hackers. Therefore it is recommended to change default ports associated with the SQL Server installation. It is same for My SQL.

i. SSL Configuration in Database Server

i. Database Server can use Secure Sockets Layer (SSL) to encrypt data that is transmitted across a network between an instance of SQL Server and a client application. Enabling SSL encryption increases the security of data transmitted across networks between instances of Database Server and applications

j. Auditing Mechanism in Database Server (Specially for SQL server)

i. Database Server security auditing monitors and tracks activity to log files that can be viewed through Windows application logs or SQL Server Management Studio. SQL Server offers the following four security levels with regards to security: