Data Control Language (DCL)

When talking DCL, we are mainly talking about a portion of security in SQL Server. There are only two commands, GRANT and REVOKE, but remember, these commands can mean everything.

With great power comes great responsibility.

–Peter Parker

So, when trying to access or manipulate objects in SQL Server, we need a Login. A login by itself is nothing in SQL Server but it let’s you into to the server just to observe the server strucutre.

We can’t create, modify or manipulate objects with a login only. We also need a User.

This User may or may not be part in a Role. This Role or the user may have been granted accesses to few or all objects in the database.

DCL works a lot in association with DDL commands.

LOGIN

Creating Login Manually

LOGIN is created on the Server level.

SQL SERVER -> Security -> Logins

Right Click Logins -> New Login…

Let’s give a name for the login. Login should be per user, and the name should follow the username given to a user in the organization. In our case we will call it IvanManual.

We can also use Windows Atuthentication but that is only common for development. Generally we need to use SQL Server authentication and give a password for the login as well.

We will not use the Enforce password policy, but the password policy is important to use in any organization environment.

Let’s keep all the defaults besides enforce password and click OK.

Try to login to the server using the new login.

We can do that by clicking on Connect -> Database Engine

Find the SERVER name for Authentication use SQL Server Authentication use login and password we just created.

If you have any database available, try to do anything with it. In this case we have the Basics database.

If I try anything with that database I get this error.

Try to see and select objects in the master database.

Why can we see stuff in master?

When we create a login, we should create it using the master database as default. The master database is the primary configuration database. This database will have all information of objects in the database.

if you try to create a user with a different databse as default, you won’t be able to even login. Try it out yourself!

Creating Login with Script

USE master
GO


CREATE LOGIN IvanScript
WITH PASSWORD = '123456',
--MUST_CHANGE, CHECK_EXPIRATION = ON,

CHECK_POLICY = OFF,
DEFAULT_DATABASE = master, DEFAULT_LANGUAGE = us_english
GO

Again, we left CHECK_POLICY OFF. Try to login with this login. It may work or not. If it does not is probably because LOGIN is not enable, we just have to enable it.

ALTER LOGIN IvanScript ENABLE

USER

Now, we need to create a user and attach it to the login. This is the user that will be given permissions later on. Users are on the database level.

Creating USER Manually

Expand the Server -> Databases ->(Expand database where the user will be created. In Our case Basics) -> Security -> Users.

Right Click Users -> New User ->General

For user type choose SQL user with login.

For User name, you may choose any name, but I usually like to attach to the same login name.

For Login name choose the one we created for manual before, in this case IvanManual. Notice that a Login name must already exist.

For Default schema, leave it blank. It will default to dbo.

Press OK.

That should give access for use IvanManual, who is attached to login IvanManual, to view system objects related to dbo. We can’t see objects, like tables, created by other user. Not yet!

Creating USER with script

USE Basics
GO


CREATE USER IvanScript 
   FOR LOGIN IvanScript 
   WITH DEFAULT_SCHEMA = [dbo]
GO

Now, IvanScript also has access to view system objects in the database.

GRANT

We are going pretty good so far, but we still can’t do much. We are not even able to see tables created by other users. If we can’t see, we can’t select, insert, update or delete. You know, all those fun stuff.

Time to use our first DCL Command. GRANT!

Grant Manually

Go back to the database server. Expand the server -> Expand Databases -> Expand the database to Grant permissions. In our case is Basics -> Expand Security -> Expand users.

Double click on IvanManual. It will take you to the Securables page. Click Search…

You will see all those options. For our purpose we will choose Specific objects. Press OK.

Click Object Types and select Tables. Press OK!

Click on Browse… to select what objects should be granted to. Choose few tables you may have available.

Click OK and OK again

Now, another screen will show:

We can select all those GRANT options in Permissions. In our case, we will only do View definition for now.

Press OK.

Login with user IvanManual and try to view the databases granted. This is what you should see:

We can see that we are able to view the definition for dbo.Test1, but not for dbo.Test2. The reason is because we added the dbo.Test2 to the list of objects to be granted, however, we never granted that table in the last step.

We are only able to see the definition, but we are not able to SELECT or do anything else. You may try, it won’t work!

Grant with Script

GRANT VIEW DEFINITION ON dbo.Test1 TO IvanScript
GO

That is is to accomplish the exact same thing;

There are, as of now, 77 database permissions that can be given to a user.

If we have the right permissions, we can give to any users those permissions as well. We need to have in mind a concept called the principle of least privilege.

Priniple of least privilege means to only give the needed rights for the user. Sometimes, to speed up the process, database professionals, give more privileges than needed to developers. that can cause all kind of trouble.

So, have that in mind, learn who is your user and give only what is needed.

Maybe the user is a reporting user who only need to read data. In that case we can use something like:

GRANT SELECT ON dbo.Test1 TO IvanScript

If you now try to Select data in that table you will be able to see it.

The user may be a developer and needs access to INSERT, UPDATE and DELETE plus SELECT. We would need to create grand for the commands based on what the user really needs.

I have challenge for you. Try this command:

GRANT VIEW DEFINITION ON SCHEMA :: dbo TO IvanScript

What did that do? Let me know in the comments.

REVOKE

The process to REVOKE manually or by script is very similar. For that reason I will not attach screenshots but only mention and you can try yourself to take your own conclusions.

To do it manually you just need to double click on the user and uncheck the GRANT that must be revoked.

To use a script you just need to replace the word GRANT to REVOKE.

REVOKE SELECT ON dbo.Test1 TO IvanScript

Just try them by selecting records. What error message will you get?

ROLES

By now, you probably noticed that this can take sometime to do permissions objects by objects for user by user. What if we need to GRANT or REVOKE permissions to hundreds of people everyday.

That could be hard to manage and a lot of risks to take, specially if we forget to revoke one single permission to someone.

That is why we have roles.

Roles can be treadet like users, we can create a role and add users to that role. In this role, we can have the neede permissions so that when we add a user to that role, the user will automatically have all the needed permissions to perform their job or requests.

For example, if an employee is promoted to a different role in the organization, the database role can also be changed and from now on, that employee will have access to whatever the role allows it to.

If an employee leaves the company, we can just remove the user from the roles the employee was in. That will also remove all access to the privileges.

Now, instead of tracking all the permissions a user may have, we can only track the role the employee is in.

There are already some fixed database roles. You can check them here!

Add Member to a role manually

Expand the Server -> Expand Databases -> Expand Basics -> Expand Security.

Double Click IvanManual -> Membership ->check db_owner -> OK

If you open the databse with tha user you will be now able to see all tables and select from any of them.

the role db_owner, as the name say, is the owner of the database. It can do anything related to that database. It is bad practice to add a member to that role unless that user is a senior DBA.

We are using here only for demonstration purposes.

Add Member to a role with script

There are two ways to do that.

ALTER ROLE [db_owner] ADD MEMBER IvanScript
GO


EXEC sp_addrolemember 'db_owner', 'IvanScript'

Remove

To remove manually we just need to uncheck the db_owner membership.

To remove using scripts we just need to use one of the following:

ALTER ROLE [db_owner] DROP MEMBER IvanScript
GO


EXEC sp_droprolemember 'db_owner', 'IvanScript'

Notice that we only used DDL commands for roles. No DCL was used, but we can.

to finalize, we will create a role and assign users to the newly created role.

User defined roles

There are times where we need to customize privileges to be different from the ones already existing in the roles. If that is the case, we can create a role, give whatever access is needed for that role, and then assign users or other roles to it.

To start our exercize, leet’s remove all access given previously. You may use scripts or do it manually. Once you are done we can start.

Ready? Let’s go then.

Create Database role manually

Create Database role with script

CREATE ROLE StudentScript
GO


GRANT VIEW DEFINITION ON dbo.Test2 TO StudentScript
GO

GRANT SELECT ON dbo.Test2 TO StudentScript
GO


ALTER ROLE StudentScript ADD MEMBER IvanScript
GO

Treat role the same as user. A role can be assigned to a different role.

ALTER ROLE StudentScript DROP MEMBER IvanScript
GO

ALTER ROLE StudentScript ADD MEMBER StudentScriptRole
GO

ALTER ROLE StudentScriptRole ADD MEMBER IvanScript
GO