Infrastructure at your Service

Security Archives - Blog dbi services

Stéphane Haby

SQL Server 2016: Does Dynamic Data Masking works with INSERT INTO and SELECT INTO commands?

By | Database Administration & Monitoring | No Comments

I wonder how works Dynamic Data Masking (DDM) with these two commands INSERT INTO  and SELECT INTO. First, I create a table and add some “sensitive data”: USE [DDM_TEST] GO CREATE TABLE [dbo].[Confidential]( [ID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED, [Name] [nvarchar](70)NULL, [CreditCard] [nvarchar](16)NULL, [Salary] [int] NULL, [Email] [nvarchar](60)NULL) insert into [dbo].[Confidential]([Name],[CreditCard],[Salary],[Email]) values (N’Stephane’,N’3546748598467584′,113459,N’sts@dbi-services.com’) insert into [dbo].[Confidential]([Name],[CreditCard],[Salary],[Email]) values (N’David’,N’3546746598450989′,143576,’dab@dbi-services.com’) insert into [dbo].[Confidential]([Name],[CreditCard],[Salary],[Email]) values (N’Nathan’,N’3890098321457893′,118900,’nac@dbi-services.com’) insert into [dbo].[Confidential]([Name],[CreditCard],[Salary],[Email]) values (N’Olivier’,N’3564890234785612′,98000,’olt@dbi-services.com’) insert into [dbo].[Confidential]([Name],[CreditCard],[Salary],[Email]) values (N’Alain’,N’9897436900989342′,85900,’ala@dbi-services.com’)…

 
Read More
Stéphane Haby

SQL Server 2016: Does Dynamic Data Masking work with Temporal Table?

By | Database Administration & Monitoring | No Comments

In the last IT Tagen 2016, I presented the Dynamic Data Masking (DDM) and how it worked. To add a little fun, I applied the DDM to a temporal table to see if the history table inherits also from DDM’s rules. In this blog, I explain all the different steps to reproduce my last demo. Step 1: Create the table and the temporal table in the database DDM_TEST USE [DDM_TEST] GO CREATE TABLE [dbo].[Confidential]( [ID]…

 
Read More
Stéphane Savorgnano

SQL Server 2016: Dynamic Data Masking and database role

By | Database Administration & Monitoring, Technology Survey | 3 Comments

Last week, dbi services organized an event named “SQL Server 2016: what’s new?” in Lausanne, Basel and Zurich. I would take the opportunity to say again a big thank you to everyone which joined us. During my session some questions concerning the new functionality Dynamic Data Masking were asked. In fact data are masked for some roles and not for some others. Let’s try to clarify that. I will use the same script I used…

 
Read More
Daniel Westermann

Securing your connections to PostgreSQL by using SSL

By | Database Administration & Monitoring | 3 Comments

Security is a big topic today and in the news almost every day. As the database usually holds sensitive data this data must be well protected. In most cases this is done by encrypting critical data inside the database and decrypt only when requested. But this is not all: When a client reads the data it is decrypted inside the database and then send back over the network unencrypted. What do you win with such…

 
Read More
Stéphane Savorgnano

SQL Server 2016: Always Encrypted – part 2

By | Database Administration & Monitoring, Technology Survey | No Comments

In my last blog post about SQL Server 2016 Always Encrypted, here, I showed how to use this new functionality but also that you have to separate the different execution context with an Application server, a database server and a security server to avoid that certificate will be available for all users and break the segregation. Let’s see how to build those environment. In my security server named SQL2016-2, I first create a Self-signed certificate…

 
Read More
Stéphane Savorgnano

SQL Server 2016: Always Encrypted

By | Database Administration & Monitoring, Technology Survey | One Comment

One of the top new features of SQL Server 2016 is the Always Encrypted functionality. Always Encrypted provides that data, store in a database, remains encrypted the all times there are in the database. There is a complete separation between persons who own the data and person who manage it. Only persons who own the data can see plain text data and person like DBAs, sys admins or privilege logins cannot have access to the…

 
Read More
Franck Pachot

SYS password on Oracle Cloud Service managed database

By | Database Administration & Monitoring | No Comments

When you create a DBaaS on the Oracle Cloud services you have to provide an administration password in the database configuration form. You do not need a password to connect to the VM. You use SSH key for it: on creation you provide the public key that will allow you to connect as the oracle user or the opc user (which can ‘sudo su’). But for the database you need to provide a password which…

 
Read More
Clemens Bleile

Database security: Where is my critical data?

By | Database Administration & Monitoring, Database management | One Comment

Last week I visited the Oracle University Event “Expert Summit 2016″ in Berlin, Germany. Besides the nice location in the Adlon Kempinski hotel, close to the Brandenburger Tor, I saw very interesting presentations from Jonathan Lewis (Database Troubleshooting and Tuning) and Pete Finnigan (Oracle Database Security Audit Training). The training from Pete was about how to do a database security audit. On the first day we learned a lot about potential security holes (vulnerabilities) in…

 
Read More
Stéphane Haby

SQL Server Tips: Find sql logins when “windows authentication only” is enabled

By | Database Administration & Monitoring | No Comments

A customer asks me to find all sql logins enabled on servers where windows authentication only is enabled. The goal is to clean sql logins on all servers through the CMS (central management server). In some cases, sql logins are created even if the authentication is set to “Windows authentication only mode”. I think that this query is interesting and I will just share it with you. DECLARE @value INT USE [master] EXEC xp_instance_regread N’HKEY_LOCAL_MACHINE’,…

 
Read More