C# language introduced some new syntax—the ?? operator, which is called the Null Coalescing Operator.

Let’s take a look at this operator in action. The expression z = x ?? y is roughly equivalent to the following code:

string tempX = x;     // Ensures thread safety by
                           // using a local variable.
if (tempX != null)
z = tempX;
z = y;

Note that the coalescing operator is thread-safe—it copies the reference to a local, temporary variable before making the null comparison. If the coalescing operator didn’t perform this step, it would be possible for another thread to modify the original reference between the comparison and the assignment.

In this post we are going to discuss about creating RBAC applications . Before that , we will have a brief overview of RBAC.

What does R B A C mean .

R-Role

B-Based

A-Access

C- Control

So summing up , its all about implementing role based access control system in application . Now , we will see how to implement

this system and make use of it in our application . As , we all are familiar with Membership , Role , Profile Providers being available

in .Net 2.0 . Those role providers does provide the same functionality , but there are requirements some time to get more of them

so we will achieve those targets by using RBAC .

Say for Example , we are having roles being implemented in our application , like (admin , Hr , Senior HR , Trainee HR etc) ,

now in our application , we do want to implement such a functionality , where a role defined can perform certain access like

Senior HR can edit , update or delete the records while Junior HR can only ADD records and view records . Even going more deep , lets assume a scenario where on a certain form /view we want that a user with specific permission (access rights ) can only view a specific part of form while others cannot , that is a form is being displayed in a different-different way depending on the access rights of the user . So how to attain this .

We will attain this using RBAC Implementation .

For this , first of all we are going to create / define roles .

a. Super Admin

b. Admin

C. Manager

d .Operator

Now as , we do have a predefined set of roles who will be using our application . the next step is to identify objects of our application ex :

a. Invoice Master

b. Product Master

c. Customer Master

and so on …..

Now once object are being identified , the next step is to identify unique  actions ( activities)  which can be performed on all the objects ex:

a. Add

b. update

c. Delete

d. Moderate

e. Publish

and so on ……

The next step is to link these actions to the objects , that is making applicable pairs of objects and activities ex:

A. product master

1. Add
2. Update

B. Invoice Master

1. Add
2. Update
3. Publish

and so on….

Here after next step is to define Permissions which is the most important step here , that is assigning the object –activity pair to roles ex:

A. Super Admin

All object – Activity Pair

B. Operator

1. View Product
2. Add Invoice

and so on ….

Save these role –object –activity pairs in a database table .

Now , Assign the roles to each user , ex;

John Smith

1. Manager

Tim Uzzanti

1. Operator

Jacob Sebastian

1. Super Admin

and so on ….

So till this step , we do have a clear definition of user , his role and his role permissions . and most important the Object of Application

Now , when you authenticate the user , load its role – object –activity pair data into session, hash table , static object (depending on whatever frontend you use , web , desktop , mobile etc and what ever the best method to load this information as per the performance of your application ).

Here After you are ready to apply RBAC .

Now whenever an object of your application instantiates , just before that give a lookup to the role – object –activity pair corresponding to the object and fetch the permissions and apply them . ex:

The user logged in does not have Add Permission on Invoice Object , disable the Add Button .

btnAdd.Visible=User.HasPermission(Invoice.Add) ;

And it is done , RBAC is implemented .

I will be soon , posting the running code for this implementation  using Asp.net , Sql server 2005 and c# . Keep watching this post .

For any specific queries /suggestions ping me up and i will respond to them.

Unfortunately , it’s an all-too-common practice to connect to the database as the sa SQL Server user account during development. While this option is usually chosen for the sake of convenience, this potentially disastrous setting has a habit of finding its way into the  production site.

Connecting as the sa user violates the security principle of least privilege, which states that, generally speaking, your web application shouldn’t require permissions to drop a database or perform other system administration operations. A bug in the application that allowed an SQL injection attack, for instance, would be disastrous if the sa user was in use in production —
in that case, your system would lack a safety net of database permissions that could to prevent the attack.

I’ve even seen clients use the sa account with no password on production web sites. A double no-no!

Table Valued User Defined functions introduced in SQL Server 2008.. a real new stuff from Sql Server 2008.. as few of technology guys recommend this feature and they say use of Table Valued Function (TVF) instead of Stored Procedure (SP).

Table Valued Functions :

User-defined functions that return a table data type can be powerful alternatives to views. These functions are referred to as table-valued functions. A table-valued user-defined function can be used where table or view expressions are allowed in Transact-SQL queries. While views are limited to a single SELECT statement, user-defined functions can contain additional statements that allow more powerful logic than is possible in views.

A table-valued user-defined function can also replace stored procedures that return a single result set. The table returned by a user-defined function can be referenced in the FROM clause of a Transact-SQL statement, but stored procedures that return result sets cannot.

Components of a Table-Valued User-defined Function

In a table-valued user-defined function:

• The RETURNS clause defines a local return variable name for the table returned by the function. The RETURNS clause also defines the format of the table. The scope of the local return variable name is local within the function.
• The Transact-SQL statements in the function body build and insert rows into the return variable defined by the RETURNS clause.
• When a RETURN statement is executed, the rows inserted into the variable are returned as the tabular output of the function. The RETURN statement cannot have an argument.

No Transact-SQL statements in a table-valued function can return a result set directly to a user. The only information the function can return to the user is the table returned by the function.

Note:
The text in row table option is automatically set to 256 for a table returned by a user-defined function. This cannot be changed. The READTEXT, WRITETEXT, and UPDATETEXT statements cannot be used to read or write parts of any text, ntext, or image columns in the table. For more information, see In-Row Data.

Yesterday i faced a problem in SQL. I was not able to modify my column datatype, i was getting error “cannot rename the table because it is published for replication” i searched it and found the solution, i needed to drop subscription and then drop the related article database and then readd the subscription and article.

Because i got this kind of problem first time so i dug this in the detail . I know most of the people also got the same kind of error and they are already known about this error and even known about the solution. But i thought i should post this in detail.

So the problem was, i am using third part tool and I have replicated some tables of my DB into that tool so that the tool could get all information about all kind of transaction or operations that are going on these tables  and capture the data related to this transactions/operations.

Sometimes the schema of a replicated table needs altering, possibly the datatype has been incorrectly chosen, or a default is missing, or we want to rename a column as i wanted. And to change the table schema directly will result in the error “cannot rename the table because it is published for replication” and now we have question how to change an existing column without breaking replication?

And i did like this

‘tEmployees” is table name.

exec sp_dropsubscription   @publication =  'tTestFNames'�
     ,  @article =  'tEmployees'�
     ,  @subscriber =  'RSCOMPUTER'
     ,  @destination_db =  'testrep' 

  exec sp_droparticle  @publication =  'tTestFNames'
     ,  @article =  'tEmployees'

  alter table tEmployees alter column Forename varchar(100) null

  exec sp_addarticle  @publication =  'tTestFNames'�
     ,  @article =  'tEmployees'�
     ,  @source_table =  'tEmployees' 

  exec sp_addsubscription  @publication =  'tTestFNames'
     ,  @article =  'tEmployees'
     ,  @subscriber =  'RSCOMPUTER'
     ,  @destination_db =  'testrep'

http://msdn.microsoft.com/en-us/library/ms184385.aspx
http://msdn.microsoft.com/en-us/library/ms173832.aspx

So this was the solution for my problem but i want to tell more about Replication.

What is Replication ?
SQL Replication term describes a group of technologies allowing information distribution and mirroring between different databases. SQL replication allows not only for copying data between databases, but also copying any database objects as well. Essentially replication performs synchronization between databases. By utilizing SQL replication, you can distribute data to as many remote network locations you need, and you can do that over different types of networks including LAN, WAN, and Internet.

Why use SQL Replication ?
There are many reasons why SQL replication exists. In many scenarios replication is done to ensure redundancy. For example if a business runs a mission-critical database, it may be wise to have a replication of this database on a separate physical database server, which can take over in case of failure in the primary database (software corruption, hardware failure, etc.). Another valid reason to use SQL replication is load balancing. By using replication you can share the access load between several database server having identical databases. There are many other reason to use SQL replication, however we won’t discuss them here.