An Oracle DBA’s Guide to Microsoft SQL Server Security

In today’s world, it is quite common for companies to use more than one type of relational database platform to host enterprise applications.  If you are an old-time Oracle DBA like me and are asked to administer Microsoft SQL Servers in addition to Oracle databases, the task can be pretty daunting from a SQL Server security perspective.  In this blog, I will try to explain the differences and similarities between the Oracle and SQL Server security models. 

The difference in security models between the two database platforms stems from the fact that Oracle was not built to run on the Microsoft Windows Operating System (OS).  Likewise, SQL was not built to run on operating systems other than Microsoft Windows.  While Microsoft has recently announced support for running SQL 2017 on Linux, I have yet to come across a company that has deployed SQL 2017 on Linux in production. 

Let us take a step back from security for a minute and understand the primary difference between Oracle and SQL Servers.  In the Oracle world, it is quite common to use the term instance and database synonymously.  The exceptions being Real Application Clusters (RAC) and the newer Container Database/Pluggable Database (CDB/PDB) scenarios.  In a single or stand-alone Oracle instance, there is a one-to-one relationship between instances and databases.  Conversely, in the case of RAC, there could be multiple instances associated with a single database.  This is not taking into account the ASM instance that Oracle automatically spins up if Oracle Automated Storage Management (ASM) is in use.  In the case of CDB/PDB, there is a primary container database, which houses one or more pluggable databases. 

For the most part, SQL Server follows the Oracle CDB/PDB model when it comes to database security.  SQL Server follows the concept of a SQL Instance running on a server and that instance can contain one or more databases.  Each instance contains certain standard databases that are considered to be part of the instance.  One such database is called the master database, which contains the instance related metadata.  I would consider the master database in the SQL Server to be similar to the container database or CDB in Oracle. 

After briefly focusing on the fundamental architectural differences between Oracle and SQL Servers, let us look at the difference in the security models between the two.

• In Oracle, when a user is created, the user has instance or database wide visibility or access.  The user also doubles up as the schema within the Oracle database.  In SQL Servers, there are concepts of both instance login and database login. For example, in order to grant database access to a Windows user named Bob, a login account needs to be created in the SQL Server instance for the Windows user Bob which gives Bob access to the SQL Server instance.  Then, depending on which database user Bob needs to have access to, a separate database account needs to be created in each database hosted on that instance.  In SQL Server 2012 and above, Microsoft introduced the concept of contained logins.  This new feature allows the users to be created within each database which implicitly grants them instance access without an instance level account dependency.  This simplifies the movement of databases between different SQL Server instances without having to worry about the instance level and database level users. 

• Since Oracle is operating system agnostic, the database security is self-contained and does not rely on the operating system security.  That is not to say that one cannot integrate Oracle database security with the operating system security (remember OPS$ user or “ops-dollar” user), which can be accomplished fairly easily, if necessary. While it was a complicated process to integrate Oracle with Microsoft Active Directory (AD) in the past, Oracle has made it easier to integrate with Microsoft AD in Oracle 12c and above. 

• With SQL Server, it is as easy as choosing an option from a drop-down list of different authentication modes. The login screen allows the users to switch between the native SQL Server, Windows (both local and AD), and Azure AD authentication as shown in Figure 1.

• I have to point out that there is one key difference between Oracle and SQL Server OS authenticated logins. That is, Oracle allows OS authenticated users to access the database with and without an additional password requirement. For example, if I have an operating system user called Bob that needs to access the database, then I can accomplish that in two ways:

1. CREATE USER ops$bob IDENTIFIED BY <password>; – user bob will have to specify the password to log into the database.  This option also allows the enforcement of the database level password policy independent of the operating system or Microsoft AD enforced policy.
2. CREATE USER ops$bobIDENTIFIED EXTERNALLY; – user bob will not be prompted for any additional passwords when logging into the database.  Password and associated policies are only enforced at the local OS or Microsoft AD level and not at the database level.

• SQL Server does not have any additional layer authentication at the SQL Server instance level if the user chooses Windows Authentication.  However, it should be noted that the Windows or OS users will have to be explicitly defined within the SQL Server instance.  SQL Server allows individual users or Microsoft AD groups to be defined as logins thus simplifying the process of user creation.  Oracle does not allow operating system groups to be defined within the database.  The exception to this rule is if Oracle is integrated with Microsoft AD.  As seen in Figure 2, SQL Server does not offer an option to modify the password and associated policies for a Microsoft AD user SBITSDHSmith using Windows authentication. 

• In Oracle and SQL servers, database access and activity are controlled through system and object privileges.  Both Oracle and SQL Servers use GRANT statements to assign privileges and REVOKE statements to remove privileges to a database object.  In addition, SQL Server supports the use of DENY statements to explicitly deny a privilege to a user.  This is useful when a local Windows or Microsoft AD group of users have access to a database. This is especially useful when there is a need to deny access to a certain database object to one or more users in those groups without impacting other users.  A denial of permissions to an object effectively overrides a grant of permissions to that object.  An exception to this rule is if the user is a member of the sysadmin fixed role, permissions are not checked further, therefore denials will not be enforced. 

• In SQL Server, the system catalog view sys.database_permissions and sys.fn_builtin_permissions can be used to query the list of permissions on objects and statements.  Equivalent Oracle views that encapsulate all the metadata are DBA_ , ALL_ , and USER_ views. 

• One of the most confusing aspects of SQL server security for an Oracle DBA is the concept of instance level permissions and database level permissions.  In SQL server, server-level or instance-level permissions can be granted to server logins via fixed server roles or used-defined server roles.  Database level permissions can be granted to database users via fixed database roles and user-defined database roles.  Furthermore, instance or server level permissions are granted to a login and database level permissions are granted to a user.  If you recall the point that I made earlier about the concept of contained logins, introduced in SQL Server 2012, the server login and database user can be the same user.  In Oracle, you have a user or a schema to which database level or instance-level permissions can be granted.  The only situation where Oracle comes close to the SQL Server security model is in the case of CDB and PDBs.  An Oracle container database can be loosely construed as a SQL server instance and an Oracle pluggable database can be compared to a database in SQL server.

• In Oracle, if I wanted to give a user the keys to the kingdom, I would grant the user with the infamous DBA role.  A similar fixed role in SQL Server is the sysadmin fixed role.  Back when I used to administer an Oracle Version 6 database, there were only three roles – DBA, CONNECT and RESOURCE) – and to this day, even though I am no longer a DBA, I still continue to use them.  The point I am trying to make is that the DBA role is a legacy role in Oracle and should not be used.  Starting in Oracle Version 7, Oracle introduced the SYSTEM and OBJECT level privileges.  I strongly recommend against using either the DBA role in Oracle or the SYSADMIN role in SQL Server.  Create your custom or user-defined roles and allocate only the minimum set of privileges to those roles following the concept of the least privileged role.

Last but not least, I would like to talk about the PUBLIC profile.  While it is clearly a profile in SQL Server, when it comes to Oracle it is a different story.  For the longest time, I thought about PUBLIC as a role in Oracle.  In actuality, PUBLIC is not strictly a role in Oracle. If you refer to the Oracle documentation, Oracle refers to it as a USER GROUP.  You will find that PUBLIC does not show up in the DBA_ROLES or any other dynamic view that lists the ROLES in Oracle.  Also, it is important to note that any privilege granted to the PUBLIC user group is automatically inherited by all the users defined in the database.  In fact, I strongly discourage anyone from granting any type of privilege to the user group PUBLIC in Oracle as it can be a  security risk.  If you refer to the DISA STIG vulnerability code V-61443, it explicitly states that all privileges granted to PUBLIC should be revoked.  In SQL Server, the role public exists at the server or instance level and also at the database level.  Figure 3 shows the privileges that are granted by default to the instance level public role.  The server level PUBLIC only has the ability to view any database as well as connect to various endpoints.  While I do not consider this to be a huge security risk, I will let you be the judge.

SELECT sp.state_desc, sp.permission_name, sp.class_desc, sp.major_id, sp.minor_id, e.name FROM sys.server_permissions AS sp
JOIN sys.server_principals AS l ON sp.grantee_principal_id = l.principal_id
LEFT JOIN sys.endpoints AS e ON sp.major_id = e.endpoint_id WHERE l.name = 'public';

Once you go from server level PUBLIC role to the database level PUBLIC role, that is where things start to get more interesting. Figure 4 shows the permissions that are automatically granted to the PUBLIC role at the database level.  The PUBLIC role at the database level has a lot more privileges than the PUBLIC role at the server level.   Since every login in SQL Server inherits the PUBLIC role automatically, please review and ensure that the permissions assigned to the PUBLIC role do not open up a security hole.

SELECT sp.state_desc, sp.permission_name, SCHEMA_NAME(ao.schema_id) AS 'Schema', ao.name FROM sys.database_permissions sp
  LEFT JOIN sys.all_objects ao
    ON sp.major_id = ao.object_id
  JOIN sys.database_principals dp
    ON sp.grantee_principal_id = dp.principal_id
WHERE dp.name = 'public' AND ao.name IS NOT NULL ORDER BY ao.name

While I was able to point out some major differences between Oracle and SQL Server security, this is by no means meant to be an exhaustive list.  Regardless of whether you are a seasoned Oracle or SQL Server DBA, STEALTHbits Data Access Governance solutions can assist you in identifying and reporting on all the permissions assigned to users and roles in your databases across the enterprise.  You will have the ability to collect, visualize and report on all the SQL Server and Oracle database enterprises as well as AD users & groups, and all files share from a single pane of glass.

To learn more about how STEALTHbits can help with evaluating database permissions, please visit our website: https://www.stealthbits.com/data-access-governance-solution.