User Identification and Authentication with Transact-SQL

Doing user authentication in SQL Server can be customized; you can use all kinds of data from a database to authenticate users. Every application needs to deal with security, making sure that sensitive data cannot be accessed by the wrong users.
You can write your own custom logic to verify user names and passwords and make sure the information is stored.

Fig 1. The database diagram.

In the database under a secure account with a password that couldn’t easily be guessed by a user. The easiest way to accomplish this is to one-way encrypt user passwords on store procedure.

A simple but fully functional example is shown below.

Fig 2. The T-SQL code.

This code will insert one row, corresponding to the new user, in the users table.

The SQL Server way to store passwords is by wrapping them in a built-in encrypting function called HASHBYTES .

Download example source code.

Oracle Recipe #5 How to execute Oracle parameterized commands with ODP.NET

SQL statements can receive input-only parameters, output-only parameters, and bidirectional parameters.
You can use a OracleCommand object to execute parameterized SQL statements.
To execute a parameterized SQL statement use the following steps:

  1. Open a database connection,use OracleConnection.
  2. Create and initialize an OracleCommand object.
  3. Create a OracleParameter object, for each input parameter required by the SQL statement. Specify the name, type size, and value for each parameter, and add it to the parameters collection of the command object.
  4. Execute the command by calling the ExecuteScalar, ExecuteReader, ExecuteXmlReader, or ExecuteNonQuery method, as appropriate for the type of SQL statement.
  5. Use the return value obtained by executing the command.
  6. Dispose the command object.
  7. Close the database connection.

The following example shows how to execute a SQL statement that updates employee by employee id (please, check this post for further information).
The SQL statement requires the following parameters: prmFirstName , prmLastName, prmEmail, prmPhoneNumber, prmHireDate, prmSalary,prmCommission and prmEmployeeId.

Fig 1. The application code.

Fig 2. Running the program.

Download example source code.

Implementing a RESTFul service with Windows Communication Foundation (WCF) and Oracle HR Schema

What Are RESTful Web Services?

REST stands for Representational State Transfer is an architectural style rather than a prescribed way of building Web services, some of the most important aspects of the REST environment are:

  • HTTP or HTTPS may be used as the transfer protocol.
  • URLs including query strings are used to address resources.
  • Representation formats supported range from HTML and XML to JSON and ATOM.
  • A Simple and intuitive programming interface is achieved by using HTTP verbs and status codes.
  • Statelessness in the interaction between clients and services.

REST is not concerned with the definition of messages and the design of methods, the key point here is that REST describes a stateless, hierarchical scheme for representing resources and business objects over a network.
The main components of this model are: resources and actions.
The action of the resource is determined by four main HTTP verbs: GET, PUT, DELETE and POST, and the action which can affect those resources are mainly CRUD (Create, Read, Update and Delete) methods, the success of the action is found by the HTTP status code.

The REST model relies on the application that accesses the data sending the appropriate HTTP verb as part of the request used to access the data.

  • GET is used exclusively to retrieve data and, therefore, the result can also be buffered.
  • POST is used to add new records.
  • PUT is used to add or change a resource.
  • DELETE is used for delete resources.

The data can be returned in a number of formats, but for portability the most common formats include XML (POX) and JSON.

WCF and REST

The REST architecture is becoming increasingly common, and WCF provides attributes, methods, and types with which you can build and access REST Web Services quickly and easily.

  • WebHttpBinding: An binding that uses the HTTP transport and text message encoder.
  • WebBehavior: This is an endpoint behavior that will modify the dispatch layer on all operations on a contract. The modifications cause messages to be dispatched to methods on your service based on URIs and HTTP verbs.
  • WebServiceHost: This is a ServiceHost-derived class that simplifies the configuration of a web-based service.
  • WebOperationContext: This is a new context object, which contains the state of the incoming request and ongoing response, and simplifies coding against HTTP using WCF.
  • WebGetAttribute/WebInvokeAttribute: Operation behaviors that are applied as attributes on a ServiceContract’s methods. WebGetAttribute is for GET verb and WebInvokeAttribute is for all the other verbs. It also tells the dispatcher how to match the methods to URIs and how to parse the URI into method parameters.

The following table shows the properties of both WebGetAttribute and WebInvokeAttribute.

Method The HTTP verb the method should respond to.
UriTemplate The definition of the URI the CLR method should respond to.
RequestFormat Enumeration that specifies the format for deserializing the request (Xml or Json).
ResponseFormat Enumeration that specifies the format for serializing the response (Xml or Json).
BodyStyle Enumeration that specifies whether the request and the response data should be wrapped in an element with the same name as the CLR method name. Bare is typically used with RESTful services.

The essential components to construct a REST Service with WFC can be found in System.ServiceModel.Web assembly.
However, the most important part of the process is designing the schema that you will use to provide access to the resources exposed by the service.
So the main idea behind REST is to design your URIs in a way that makes logical sense based on your resource set. The URIs should, if possible, make sense to the application that consumes the data.

Depending on the volume of data in the database, a query might retrieve a large number of items, therefore, it makes sense to provide additional query parameters that a user can specify to limit the number of items returned.

Implementing a simple RESTful Service Example with WCF and Oracle.

In this example, we will develop a WCF RESTful service by using Oracle HR Sample Schema, ODP.NET, ADO.NET and Visual Studio 2015.
You can learn about the HR Schema in this post, I have written to introduce you to this schema.
The following illustration shows the components in the Employee service that I have written for this post.

Fig 1. Components of our Employee RESTFul service.
Step 1:Write the POCO object.
Fig 2. Employee entity class.
Step 2: Write the following utility class.
Fig 3. Utility class.
Step 3: Write the following helper class adding the Oracle Data Provider for .NET.
Fig 4. DAC data access class.
Step 4:Define and write the service contract.
Fig 5. Service contract interface.
Step 5: Write the service implementation class.
Fig 6. Service implementation class.
Step 6: Write the following Employee.svc file. Use the WebServiceHost class, the WebServiceHost class inherits from ServiceHost and automatically assigns the correct binding and behavior to your endpoint. You no longer need to be concerned about the content of your configuration file.
Fig 7. EmployeeService.svc File.
Step 7: Write the configuration file and store the connection string for the Oracle HR schema.
Fig 8. Configuration File.

In this example a GET at http://localhost/WcfRest/EmployeeService.svc/Employees shows all the employees in the HR database.
Fig 9. Running the example, querying all the employees.

Here a GET at http://localhost/WcfRest/EmployeeService.svc/102 show only one employee with the ID 102.

Fig 10. Querying only one employee.
Also, a GET at http://localhost/WcfRest/EmployeeService.svc/110 show the employee with the ID 110.
Fig 11. Querying another employee.

Download example source code.

Oracle Recipe #4 How to retrieve the list of schema objects with Oracle Data Provider for .NET (ODP.NET)

In this post, I am going to introduce you one of the sample schemas that Oracle provides as we learn Oracle database: The HR Schema.
But before I introduce it specifically, we need to understand what is a schema.

I’ve found two definitions for the same term, a schema basically can be:

  • A logical container for data structures
  • A collection of objects associated with the database.

Oracle draws the distinction between logical and physical structures: structures that are visible at a disk level or operating system level such as data files, control files and redo log files are considered physical structures, on the contrary, objects like tablespaces, schemas, tables, views , and any database objects are considered logical structures.
A container in this context means that a single schema name can contain many different objects, these logical objects are known as schema objects, and they are made up of structures such as:

  • Table: A table is the basic logical storage unit in the Oracle database; composed of rows and columns.
  • Cluster: A cluster is a set of tables physically stored together as one table.
  • Index: An index is a structure created to help retrieve data more quickly and efficiently.
  • View: Logically represents subsets of data from one or more tables.
  • Store procedure: Stored procedures are predefined SQL queries stored in the data dictionary designed to allow more efficient queries.
  • Sequence: Numeric value generator.
  • Package: Named PL/SQL modules that group related stored procedures, functions, and identifiers.
  • Synonyms: Gives alternative names to objects.

The HR schema

The HR schema is a sample schema that Oracle makes available for learning purposes.
You can install sample schemas using DBCA (DataBase Configuration Assistant) or you can get it from the following link:

Fig 1. Entity Relationship Diagram for HR Schema.

Schemas present a layer of abstraction for your data structure and it helps to avoid a problem called name collision. Let me show you an example: if we don’t use schemas a user called Bob can create a table called Employees, and then another user called Alice cannot create a table called Employees on the same schema that Bob, but Alice can create a table in a different schema. Other users can access or execute objects within a user’s schema once the schema owner grants privileges.

List schema objects using .NET

The following code example uses Oracle Developer Tools for Visual Studio (ODT) to retrieve the list of schema objects that are available and then displaying them to the console.

Fig 2. Create and return an open OracleConnection
Fig 3. The utilities class
Fig 4. The main program
Fig 5. Running the program
Fig 6. Retrieving the list of schema objects of hr user.
Fig 7. Retrieving the list of schema objects of system user.
  • Note 1: You will find in many Oracle’s texts that some people using schema and user indistinctly.
  • Note 2: Oracle validates that the users have permissions to use the schema objects being accessed by theirs.

Download example source code.

Oracle Recipe #3: How to Execute a query that returns a Single Row with the method GetOracleValues.

if you want to obtain multiple values from a database, you can call the executeReader method once on a OracleCommand object, to execute a SQL statement that returns a collection of values in a single row result.

The ExecuteReader method returns an instance of a class that implements the IDataReader interface. Each of the data reader classes provided by .NET Framework has a GetValues method, which returns an array of column values for the current row.

To obtain a single row from a database.

  1. Open a OracleConnection.
  2. Create and initialize a OracleCommand object.
  3. Call the ExecuteReader method on the command object. Assign the return value from this method to a data reader variable.
  4. Call the Read method on the data reader object to move to the first(and only) row in the result set.
  5. Call the GetOracleValues method on the data reader object. Pass an object array as a parameter to retrieve the scalar results of the query.
  6. Convert each element in the array to an appropriate data type, if necessary.
  7. Close the OracleDataReader object.
  8. Dispose the OracleCommand object.
  9. Close the database connection.

The following example shows how to execute a query that returns a set of values.
The example place the results into an array named results.

Fig 1. Using the GetOracleValues of an OracleDataReader object.

Fig 2. Testing the program.

Download example source code.

Oracle Recipe #2: How to execute a query that returns a Scalar result with OracleCommand.

  1. Microsoft ADO.NET command objects have an ExecuteScalar method, which enables you to execute a query that returns a single result.
  2. Open a database connection.
  3. Create and initialize a command object.
  4. Call the ExecuteScalar method on the command object.
  5. Convert the return value from ExecuteScalar into an appropriate data type.
  6. Dispose the command object.
  7. Close the database connection.

The following example, show how to execute a query that determines the average salary from the table employees on the HR schema provided by Oracle Database XE.
The example assume that the query does not return a NULL result.

Fig 1. OracleCommand ExecuteScalar method code example.

Download source code