Security in sql server

Security in sql

Sql server provides security with the help of two objects

Login

User

Login

Purpose of login is to allow the user to login to the server.

Login isof two types

Sql server authenticated

Windows authenticated

Creating sql server authenticated login

 To create a login use

Create login command that has the following syntax to create sql server authenticated login

Create login <loginname>

With password =’pwd’ [ ,]

[default_database=<dbname>] [ , ]

[check_expiration =ON|OFF [ ,]

[check_policy=ON | OFF] (Default is on for check_policy)

Default_database option is used to specify default database for the login, which will automatically come into use when you login in with that login.

Check_expiration:

Is used to specify whether or not the password will be expired and user will be forced to change the password (Default- off)

Check_policy

Is used to specify whether or not the password will be validated against password policy of sql server [Default- ON]

Example

The following example creates a sql server authenticated login with the name SSAe

create login ssae

with password ='ssae',

default_database=mydb,

check_policy=off

creating windows authentication login

to create a window authenticated login, first you have to create a user in windows operating system using user accounts options in control panel and then use the following syntax to create login command to  create a windows authenticated

syntax

create login [servername|username] from windows

example: create login [uday-PC\wa] from windows

in the above example wa is the user account creating in control panel .

the purpose of user is to allow the login to access a particular database

every login must be associated with a user, to access a database. Every login can be associated with only one user that also in anyone database available on the server.

To create the user for the login use the stored procedure

sp_grantdbaccess ‘loginname’,’username’

This procedural will create user for the login in the current database that is in the user. Hence while creating a user for the login using this stored procedure, you must make sure that the database in which you want to create the user is in use.

The following example create a user with name ssauser for the login ssae

sp_grantdbaccess 'ssae','ssauser'

the following example create a user with a name “Wauser” for the windows authenticated login wa.

sp_grantdbaccess [uday-PC\wa],'wauser'

Permissions :

Every login user requires permission to perform any operation against the server

Data base permission or system level permission

Object level permission

Database level

These are the permissions related to communicate like create,alter,drop

Database level permissions can be granted only by admistrator

Object level permission: these are the permissions related to particular object in the database like tables, view, store procedures and user defined functions

Related to table available permissions are select, insert, update, delete

Object level permission can be granted only by the user who creates that object

Granting database level permission

To grant permission , you have to use the grant command that has the following syntax

grant permission

to <userlist>

[with grant option]

The following example grants create table to the user ssauser

grant create table to ssauser

Granting object level permission

To grant object level permission use the following syntax of grant command

Syntax:

Grant <permissionlist>

On  <objectname>

To <userlist>

[with grant option]

The following example grant select, insert permission on emp table to the user ssauser

grant select,insert on emp to ssauser

The following example grants update permission on the column ename,hiredate and deptno of emp table to the user ssauser

update EMP set ename='uday' where EMPNO=1001

note: we cannot grant permission for insert, delete on specified columns (i.e only select & update permissions can be granted on specific columns and not on insert, delete & references.

The following example grants all permissions on table dept to the user ssauser

grant all ondept to ssauser

the permission all is deprecated and it will be eliminated in future versions and hence it is not recommended to use the permission all

With grant option     

While granting permission , the user who is granting permission is called as Grantor and the use who is getting permission is called Grantee

Whenever grantor wants to allow the grantee to grant the permission to other user, than he must specify option while granting the permission.

The following example grants the select permission on the table emp to the user ssauser and wauser using grant option

grant selecton emp tossauser with grantoption

getting list of permissions the current user has

to get list of permission the current user has use the function “fn_my_permission” which is a table valued function and it has the following syntax

select * fromfn_my_permissions ('db/objectname','database/object')

this function takes two arguments

1 dbobjectname and the string database or string object

When the first argument is a db name than second argument must be string db.

When the first argument is an object name, than second argument must be string object.

The following example list the db level permission that the current user has

select * fromfn_my_permissions ('mydb','database')

The following example list the permissions the current user has on the table emp

select * fromfn_my_permissions ('emp','object')

Revoking database level permissions

The permission you granted to other users can be revoked when you don’t want to allow those users to perform those operations or access your objects and for this Revoke command is used.

Revoke command the following syntax to revoke DataBase level permission

Revoke <permission list>

From <user list>

[cascade]

The following example revoke create table and create view permissions from the user ssauser and wauser

1 verify permission first   

2 Revoke

Revoke create table, create view from ssauser, wauser

Revoking Object Level Permissions

To revoke object level permissions revoke command has the following syntax

Revoke <permissionlist>

on <objectName>

from <userlist>

[cascade]

The following example revoke select & Insert Permission on Emp Table from the users ssauser and wauser

Revoke select user on

Emp

From ssauser,wauser

The following example revokes update permissions on the columns hiredate and deptno of emp table from the user ssauser and wauser

Revoke update on

Emp

From ssauser, wauser

Revoke update(hiredate,DeptNo)  in Emp

From ssauser,wauser

The following example revokes all permissions on table dept from the user ssauser and wauser

Revoke all on dept

From ssauser,wauser

Cascade

To revoke the permission granted with grant option, you must specify the option cascade

When you revoke the permission using cascade option than permission will be revoked not only from the user from whom you are revoking but also from every user who get those permissions either directly or indirectly from that user.

Example:

The following example revokes select permission on table studies from the user ssauser

Revoke select on

Studies from ssauser

Cascade

Roles:

There are situation in Real time where you have to grant different permissions on different object to different users at different times. In this case Roles will make the task easy.

Role will make the task easy.

A role is the set of permission and main purpose of a role is to make it easy to manage the permissions to be granted to other users.

A role can contain any no of permissions and a single role can contain both database level and object level permissions.

Creating a Role:

To create a role use the stored procedure that has the following syntax

Sp_addrole ‘rolename’

The following example creates a role with a name MyRole

Sp_addrole ‘myrole’

Granting permission to Revoking Permission from a Role

Granting permission to a role or Revoking permissions from a Role is same as granting and revoking permissions to and from user except that in the place of username you have to write Role Name

The following example grants different permissions on the object emp ,dept,salgrade,studies and programmer to the role myrole

Grant select,insert on emp to myrole

Grant select, update on dept to myrole

Grant select on salgrade to myrole

Grant select, delete on studies to myrole

Grant insert on programmer to myrole

Adding Members to a Role

When you want to grant all the permissions available in the role to the user, you have to add that use as a member to the role and for this use the stored procedure

Sp_addrolemember ‘rolename’,’username’

The following example adds the user ssauser as a member to the role “myrole”

Sp_addmember ‘myrole’ ,’ssauser’

Getting list of Roles

To get the list of roles available in the database use the stored procedure

sp_helprole with no arguments

eg: sp_helprole

Getting list of member of a Role

To get list of members of a role, use the stored procedure

sp_helprolemember

syntax:

sp_helprolemember ‘rolename’

the following example list the members of the role my role

sp_helprolemember ‘myrole’

Removing the member from the Role

When you want to revoke all permissions from a user that are granted to the user through a role , than you have to remove that user as a member from the role, and for this use the stored  procedure

sp_droprolemember ‘rolename’,’username’

The following example revokes the user ssauser as a member from the role “myrole”

Sp_droprolemember ‘myrole’,’ssauser’

Deleting a Role:

To delete a role it  must not have any members

After deleting all the members from a role use the stored procedure

sp_droprole ‘role name’

The following example deletes the role myrole

sp_droprole ‘myrole’

While working with roles, there are situations where you want to add a user as a member to the role but you don’t want to grant one or more permissions available in the role to that user. In this case you have to user the deny command

The permissions granted to a role cannot be revoked with revoke command

Syntax:

Deny <permission list>

[on <object name>]

To <user list>]

[cascade]

The following example deny select and update permissions on table dept to the user ssauser

Deny select, update on

Dept to

Ssauser

Fixed Roles

sql server provides a set of predefined roles which are collectively called as fixed roles and they are classified into

fixed database roles

fixed server roles

Fixed database roles

Fixed db rules are used to grant permission on a particular db and fixed db rules available

In sql server are as follows

db_datareader

Members of this roles will get select  permissions on all tables in database that may be created by any user.

Db_datawriter

Member of this role will get insert, update, and delete permissions on all tables in the database that may be created by any user.

Dd_ddladmin

Members of this will get permissions on all ddl commands within that particular database

Db_owner

Members of this roles will become owner of the database and they will have completely rights on the database

To add a member of this role use the stored procedure

Sp_addrolemember and to delete a member from this use the stored procedure

Sp_droprolemember

 Both of these stored procedures take two arguments where the first argument is RoleName and the second argument is UserName

Fixed Server Roles

Are used to grant permission on server, to a login. Fixed server roles available in database as follows

Dbcreator: members of this role will get permission to create alter and drop database

Security Admin

Member of this role will get permission to manage logins and they will also have the permission to grant, revoke, or deny server level permissions

Sysadmin

Members of this role will become administrator for the server and they will have complete rights on the server.

To add a member to the fixed server roles use the stored procedure

Sp_addsrvrolemember and to delete a member from this role use the stored procedure

Sp_dropsrvrolemember

Both of these stored procedure takes two arguments where the first argument is login Name and second argument is RoleName

Sp_addsrvrolemember ‘loginname’,’rolename’

Sp_dropsrvrolemember ‘loginname’,’rolename’

To get the list  of permission you granted to a particular user or list of permissions you got from any user or list of permission available in a role

Use the stored procedur

Sp_helprotect ‘object/dbaname’ ‘username’ ,’grantorname’,’0’/’s’/’o s’

The first argument for this procedure is object Name or database Name on which you want to get the list of permissions

Second argument the username permission of which you want to list

Third argument the grantorname permission granted by whom you want to list and object level permission or system level permissions or both.

Only single p in helprotect

The following example lists the permissions granted by the use dbo to the user “ssauser”

Sp_helprotect null,’ssauser’,dbo’

The following example list the permission granted by the user dbo to all other users

Sp_helpprotect null,null,’dbo’

To get all permission we need to specify null.

The following example list all the permission the  role myrole has

Sp_helptext null,’myrole’

TCL IN SQL SERVER

Transaction control language

Related to transaction management sql server provides a set of commands which are collectively called as tcl and those commands are as follows

Begin

Commit

Explicit transaction

A transaction is a unit of work, mainly consists of DML operational which must be made permanent as a whole and must be under as a whole

Sql server runs in 3 types of transaction modes

Auto commit

Implicit transaction

Explicit transaction

Auto commit

In auto commit mode, the transactions are started with “DML operations

After executing DML command the transactions end automatically with commit

In auto commit mode, user can’t control transaction

Implicit transaction

In implicit transaction mode, a transaction starts with DML operation

A transaction end with Commit/Rollback

Are the transactions that will be automatically created when the first sql statement is executed without Begin Transaction command.

By default implicit transaction are set to off . To make them On use the set statement as follows

set implicit_transactionson/off

Explicit Transaction Mode

Each transaction is explicitly started with the Begin Transaction statement and explicitly ended with a commit or rollback statement

 By default sql server uses Auto commit Transactions i.e. after executing each statement it will automatically commit it.

Note:

If we want to use the Explicit Transactions before executing the statements we need to start with a “Begin Transaction” statement and then decide whether it has to be commit or Rollback , until the transactions ends the record get locked.

Begin Transaction

This command is used to create a transaction and once you create a transaction and then make any changes to the table then, the changes will be accessible only for the current user and the tables for which changes are made will be luck for the current user and they will not be accessible to other users until the transaction ends.

After creating a transaction and making changes to the table, if any interruption occurs like the system restarts because  of any power failure or any other reason  before the transaction is committed then the transaction will be automatically rollback.

Commit

This command is used to make all the changes in the current transaction permanent and end the transaction.

Rollback Transaction

This command is used to cancel, all the changes in the current transaction and end the transaction.

Examples

Begin Transaction

Delete from EMP where empno=1014

Commit

Rollback

Begin transaction

Delete from EMP where empno=1014

Rollback

Roll of log File in Transaction Management

When we create a transaction and then make changes to tables then every change you make will be recorded in log file.

When you create a transaction with Begin Transaction command then a unique Id will be assigned to the transaction and an entry will be returned to log file indicating the transaction is started along with transaction id.

From here onwards every change you make to a table will write two entries to the log file, before image and after image of the table along with transaction_id

               

When you commit the transaction, then first it will write one entry to the log file indicating the transaction is committed along with transaction id and then uses after image of every change in the transaction to commit the transaction.

During commit changes in the transaction are executed from top to bottom.

When you roll back the transaction , then first it will write one entry to the log file indicating the transaction is roll back along with transaction id  and then uses before image of every change in the transaction to roll back the transaction. During roll back changes in the transaction are executed from bottom to top.

Save points

Save points are used to divide the transactions into two parts and allow the user to commit one part , while the other part is RollBack.

To Create a save point, use save transaction command that has the following syntax

Save transaction <savepointname>

save transactiont1

When you roll back the transaction by specifying save point name then all the changes in the transaction prior to the save point will be committed and next to it will be roll back

Note:

With in a single transaction you can create multiple save points.But you can RollBack the transaction to only one save point, when you roll back a transaction to a particular save point, then all remaining save points in the transaction will be ignored and all the changes in the transaction prior to that save point will be comitted and all the changes Next to that save point will be RollBack.

Implicit Transaction

Setting  Implicit transaction to On and create a transaction explicity will have the followingg problem

If you create  a transactions explicity when implicity transaction is set to ON. Then when you execute the first sql statement after creating a transaction explicity , then an implicity transaction will be created and every change you make to tables will be recorded twice in the log file, one for the explicit transaction, and  one for the implicit transaction. When you commit the transaction then the implicit transaction wil be comitted, and the explicit transactions which is not committed are not roll back will be roll back  next time the sql server starts , that is the same changes that are committed by implicit transactions will be roll back. Now hence whenever you are creating a transaction explicity make sure implicit transaction is set to off.

INDEXED VIEWS

A view whose query result is stored in database is called indexed view.

Index view are created mainly for two reasons

To improve performance of aggregate operations

To create a local copy for remote database

View	Indexed view
When view is created, the query is stored in database	When indexed view is created  , the query result is stored in database
A view does not contain any data	Index view contains data

Follow steps while creating index view.

Create a view with schema binding

Create index on that view

create view indv1 with schemabinding

as

select deptno,SUM(isnull(sal,0)) sumsal, COUNT_BIG(*) emps from dbo.EMP group by DEPTNO

step2:

create unique clustered index on group by column that is deptno.

create unique clustered index ind1  on indv1(deptno)

select *from indv1

update EMP setSAL=SAL+1000 where DEPTNO=10

after creating indexed view, if we make any changes to base table the changes are reflected to index view automatically

Stored Procedure and Function

Stored procedure	Function
A procedure need not return a value	Function must return a value
Returns values using out parameters	Function returns values using return expression
They supports optional parameters	they does not support optional parameters
You can call a function from stored procedure	You cannot call a stored procedure from a function
Procedure can’t be called in select statement	Function can be called in select statement
Try and catch statements can be used	Try and catch statements cannot be used.

What is the role of the "new" operator in the creation of an object ?

What is the role of the "new" operator in the creation of an object ?

Ans: When u use the "new" operator for creating the object of the class it will internally perform the following:

   -Reads the Classes

   -Calls the Constructors

   -Allocates the memory required for the object

-What is meant by reading the class ?

Ans: Reading the class in the sense it will recongnize each and every member which was defined under the class.