Auditing IT components using CAATs

Auditing SQL Server Permissions using CAATs

Before we get into auditing SQL Server permissions a reminder of a few definitions might be helpful.

In SQL Server anything that can be granted a right to perform an activity is called a principal. So fundamentally principals are logins, users, roles, etc.  Principals can also be separated into server principals and database principals.  These reflect the scope of the principal.  See (http://msdn.microsoft.com/en-us/library/ms181127(v=sql.90).aspx for further details).

Permissions are what allow principals to perform these activities in SQL Server.  Depending on the scope, the permission granted is either a server permission or a database permission.  

Roles are used to group together permissions or other roles. They are a means of facilitating the granting of multiple permissions or roles to users.

SQL Server provides some predefined roles to help in database administration.   

Fixed Server Roles (see http://msdn.microsoft.com/en-us/library/ms175892(v=sql.90)) are defined at the server level and have server level permissions. They cannot be added, removed, or changed. Each member of a fixed server role can add other users to the role.  A more detailed description of Fixed Server Roles can be seen at http://www.mssqltips.com/sqlservertip/1887/understanding-sql-server-fixed-server-roles/

Fixed Database Roles (see http://msdn.microsoft.com/en-us/library/ms189612(v=sql.90)) are defined at the database level and exist in each database. Members of the db_owner and db_securityadmin database roles can manage fixed database role membership; however, only members of the db_owner database role can add members to the db_owner fixed database role. A more detailed description of fixed database roles can be seen at http://www.mssqltips.com/sqlservertip/1900/understanding-sql-server-fixed-database-roles/

User Defined (or Flexible) Database Roles allow you to create your own roles. After you create a role you can configure the database permissions of the role by using grant, deny, and revoke etc.

From SQL Server 2012 you can also create User Defined Server Roles (see http://sequelinfo.wordpress.com/2012/02/25/sql-server-2012-user-defined-server-roles/) 

To get a list of Fixed Server Roles use the sys.server_principals view (see http://msdn.microsoft.com/en-us/library/ms188786(v=sql.90) )

Spool the view (and all subsequent views mentioned in this post) to a CSV type file by following the instructions in the following document http://www.isaca.org/Groups/Professional-English/sql-servers/GroupDocuments/SQL%20Server%20-%20Output%20to%20CSV.doc

and substituting in the following SQL

We will also need the users that we previously imported (see http://www.isaca.org/Blogs/273340/Lists/Posts/ViewPost.aspx?ID=13)

The users that have been allocated Fixed Server Roles can be seen in the sys.server_role_members view (see http://msdn.microsoft.com/en-us/library/ms190331(v=sql.90).aspx)

 
Once each of the above are in your CAATs tool you can use the server_role_members view to join to roles (using the “Server Principal ID of the role” and users (using “Server Principal ID of the member”).  It is then a matter of reviewing the allocated roles for appropriateness.  Special attention should be paid to the SYSADMIN and PUBLIC Fixed Server Roles.

We can also view a list of individual server permissions using the sys.server_permissions view (see http://msdn.microsoft.com/en-US/library/ms186260(v=sql.90).aspx

 
As above this can be joined to users (using “grantee principal idandgrantor principal id”).  This will then list all server permissions with the grantee and grantor details.  This should again be reviewed for appropriateness.

To get a list of Database Roles (both fixed and user defined) use the sys.database_principals view (see http://msdn.microsoft.com/en-us/library/ms187328(v=sql.90).aspx)

For each database under review

USE <database name>

(Note the column IS_FIXED_ROLE=1 for Fixed Server Roles)

Get a list of the database users and roles

Get a list of the role members from the view database_role_members (see http://msdn.microsoft.com/en-US/library/ms189780(v=sql.90).aspx)

Once each of the above are in your CAATs tool you can use the database_role_members view to join to roles (using the “Database Principal ID of the role” and users (using “Database Principal ID of the member”).  It is then a matter of reviewing the allocated roles for appropriateness.  Note that PUBLIC is a database role, while GUEST is a database user.  Also don’t forget to review for adequate separation of duties.

We can also view a list of individual database permissions using the sys.database_permissions view (see http://msdn.microsoft.com/en-us/library/ms188367(v=sql.90) )


In your CAATs tool you can use the database_principals view to see who has been granted these permissions (join using the “Grantee_principal_id)”.  Note that negative numbers are system objects.

It is also possible to see what objects permissions have been granted on using the sys.objects view


And join on major_id from database_permissions with object_id

System objects can be further broken down in other views if required e.g.  sys.tables, sys.triggers, sys.procedures.

The company you are auditing should have a policy on what permissions may be granted to users.  If so import them to your CAATs tool (using the method above from a “model” database that has the correct permissions set).  Compare the two databases, questioning any differences with the DBA. 

One of the most important (in my opinion) SQL Server security areas to consider is “Application Roles”.  If your application is defined without a proxy user, that is, all the application users are also defined as individual users on the SQL Server database, it may be possible for them to access the database via ODBC. 

This means that they could use common tools such as Microsoft Access or Microsoft Excel to access the database directly.  They are now connected to the database with the permissions defined for them but critically any controls defined by the application can be bypassed.  For example, if your application is written in Visual Basic and ensures that a particular field has particular limits this may be overridden by updating the database table directly.

Further information on Application Roles may be seen at http://msdn.microsoft.com/en-us/library/ms190998(v=sql.90).aspx

Fundamentally, an application role is a role that is only enabled by an application executing the set application role stored procedure with a password known only to the application.

To check if Application Roles are in use

And discuss the roles with the system administrator

You must sign in to rate content.
(1 ratings)

Comments

RE: Auditing SQL Server Permissions using CAATs

You raise an important point about the risk of application controls being bypassed using tools such as Microsoft Access and Excel where users are permitted to make direct connections to the database.

The obvious security impacts are:
- confidentiality breaches - if users have read access to whole database tables they could potentially copy entire extracts whereas using the application they would have constrained access
- integrity breaches - bypassing validation controls by directly changing data, also bypassing the application's audit trail
- availability breaches - deleting data and/or affecting system availability by causing uncontrolled database locks

I would also point out that there are potentially serious performance risks from allowing direct connections to databases. If Microsoft Access is used to create queries across multiple external linked tables, then many separate ODBC connections will be opened per external linked table with all of the data being pulled back over the network and query processing being performed "client-side". This is disastrous both for network performance and for database performance.

Given the severity of these risks I would recommend adding to your recommended audit procedure the following 2 checks:

1. Verify existence of suitable network and desktop controls to prevent direct access to database servers by end users. 
- If a suitable hardware firewall is not available to enforce a "tiered architecture" then at least software firewalls on the database server and on the desktop machines could be used to restrict access to the database to application servers. 
- The application servers should not have tools such as Microsoft Access installed on them.
- End users should not be allowed Remote Desktop Access to application servers

2. Verify the applications that are being used to make database connections using simple SQL queries (whilst logged on with full audit rights):

e.g. SELECT DISTINCT program_name from sys.sysprocesses;

This should establish if any unauthorised applications are being used and should identify the "name_of_authorised_application" for use in the next query.

e.g. SELECT DISTINCT hostname, nt_username from sys.sysprocesses WHERE program_name <> "name_of_authorised_application";

This should help to identify unauthorised database access methods and provide a basis for improving the network and desktop controls.

Although the information shown in sysprocesses will depend on network conditions, for a full list of potential fields see:
http://technet.microsoft.com/en-us/library/ms179881.aspx

Trusting that this information is useful to someone.

TimDWilliams at 1/1/2013 8:59 AM
You must sign in to rate content.
(Unrated)

RE: Auditing SQL Server Permissions using CAATs

Another important point is the authentication method used as it has a big potential impact on security. Many legacy applications use SQL logins, - with a username and password. Once this SQL login information is known, (for example by inspecting application configuration files), then it could be reused elsewhere for example connecting via Excel or Access, or passed to other people to use. There are many inherit problems with SQL logins including: * Login information is often passed over the network in cleartext * Difficulties in regularly rotating the SQL login password (changing SQL based logins in each application differs) * The password is often easily accessible from within application configuration files * Alternatively, developers may attempt to obscure this login information by hard-coding it into applications, making it very difficult to audit and change passwords. * A client application may be accessed by an unprivileged user by using the privileged user's desktop (either remotely, or by interactive login) The alternative is to use Windows Authentication based logins. These rely on passing an existing (already authenticated) windows security principle to the SQL server and provide many benefits, including: * Login information is hashed via NTLM or Kerberos so passwords are never sent in cleartext over the network * Rotating passwords does not involve changing login settings on the SQL server * It's much harder to impersonate an Application's Service account using Windows Auth login to SQL server than a SQL login (if using the proxy style access to SQL). * Applications can use Windows Delegation to pass through the user's login on behalf of the application's service account so that per-user role granular access to SQL is maintained. If not using the proxy style access to SQL, then SQL Database Application Roles come in to play best when using Windows Authentication based logins. The Windows Logins (of the users) only need LOGIN rights to the SQL server without specific access to the databases. The application then uses application roles to get access to the database objects as needed. Advantages: * Management of SQL server is simple because users can be granted access to application via an Active Directory Group * Users cannot use Excel/Access/ODBC to access databases as they do not know Application Role passwords * Users can be separately granted access to specific DB objects for Reporting Services etc as appropriate * Auditing and performance management on SQL server is better because we see actual users rather than a proxy account. Ultimately these decisions depend on what influence you have over the application. Application controls should exist at the database, server and client levels, but that doesn't always happen....
Chris.Harrison at 1/6/2013 5:26 AM
You must sign in to rate content.
(Unrated)

RE: Auditing SQL Server Permissions using CAATs

quote: each member of a fixed server role can add other users to the role. I think it's quite confusing, as they have securityadmin role for that. why would they allow that? please explain I'd the benefits outweigh the risk
Junior Lazuardi at 11/12/2012 10:17 AM
You must sign in to rate content.
(Unrated)

RE: Auditing SQL Server Permissions using CAATs

quote: each member of a fixed server role can add other users to the role. I think it's quite confusing, as they have securityadmin role for that. why would they allow that? please explain I'd the benefits outweigh the risk
Junior Lazuardi at 11/12/2012 10:17 AM
You must sign in to rate content.
(Unrated)

RE: Auditing SQL Server Permissions using CAATs

Another important point is the authentication method used as it has a big potential impact on security. Many legacy applications use SQL logins, - with a username and password. Once this SQL login information is known, (for example by inspecting application configuration files), then it could be reused elsewhere for example connecting via Excel or Access, or passed to other people to use. There are many inherit problems with SQL logins including: * Login information is often passed over the network in cleartext * Difficulties in regularly rotating the SQL login password (changing SQL based logins in each application differs) * The password is often easily accessible from within application configuration files * Alternatively, developers may attempt to obscure this login information by hard-coding it into applications, making it very difficult to audit and change passwords. * A client application may be accessed by an unprivileged user by using the privileged user's desktop (either remotely, or by interactive login) The alternative is to use Windows Authentication based logins. These rely on passing an existing (already authenticated) windows security principle to the SQL server and provide many benefits, including: * Login information is hashed via NTLM or Kerberos so passwords are never sent in cleartext over the network * Rotating passwords does not involve changing login settings on the SQL server * It's much harder to impersonate an Application's Service account using Windows Auth login to SQL server than a SQL login (if using the proxy style access to SQL). * Applications can use Windows Delegation to pass through the user's login on behalf of the application's service account so that per-user role granular access to SQL is maintained. If not using the proxy style access to SQL, then SQL Database Application Roles come in to play best when using Windows Authentication based logins. The Windows Logins (of the users) only need LOGIN rights to the SQL server without specific access to the databases. The application then uses application roles to get access to the database objects as needed. Advantages: * Management of SQL server is simple because users can be granted access to application via an Active Directory Group * Users cannot use Excel/Access/ODBC to access databases as they do not know Application Role passwords * Users can be separately granted access to specific DB objects for Reporting Services etc as appropriate * Auditing and performance management on SQL server is better because we see actual users rather than a proxy account. Ultimately these decisions depend on what influence you have over the application. Application controls should exist at the database, server and client levels, but that doesn't always happen....
Chris.Harrison at 1/6/2013 5:26 AM
You must sign in to rate content.
(Unrated)

RE: Auditing SQL Server Permissions using CAATs

You raise an important point about the risk of application controls being bypassed using tools such as Microsoft Access and Excel where users are permitted to make direct connections to the database.

The obvious security impacts are:
- confidentiality breaches - if users have read access to whole database tables they could potentially copy entire extracts whereas using the application they would have constrained access
- integrity breaches - bypassing validation controls by directly changing data, also bypassing the application's audit trail
- availability breaches - deleting data and/or affecting system availability by causing uncontrolled database locks

I would also point out that there are potentially serious performance risks from allowing direct connections to databases. If Microsoft Access is used to create queries across multiple external linked tables, then many separate ODBC connections will be opened per external linked table with all of the data being pulled back over the network and query processing being performed "client-side". This is disastrous both for network performance and for database performance.

Given the severity of these risks I would recommend adding to your recommended audit procedure the following 2 checks:

1. Verify existence of suitable network and desktop controls to prevent direct access to database servers by end users. 
- If a suitable hardware firewall is not available to enforce a "tiered architecture" then at least software firewalls on the database server and on the desktop machines could be used to restrict access to the database to application servers. 
- The application servers should not have tools such as Microsoft Access installed on them.
- End users should not be allowed Remote Desktop Access to application servers

2. Verify the applications that are being used to make database connections using simple SQL queries (whilst logged on with full audit rights):

e.g. SELECT DISTINCT program_name from sys.sysprocesses;

This should establish if any unauthorised applications are being used and should identify the "name_of_authorised_application" for use in the next query.

e.g. SELECT DISTINCT hostname, nt_username from sys.sysprocesses WHERE program_name <> "name_of_authorised_application";

This should help to identify unauthorised database access methods and provide a basis for improving the network and desktop controls.

Although the information shown in sysprocesses will depend on network conditions, for a full list of potential fields see:
http://technet.microsoft.com/en-us/library/ms179881.aspx

Trusting that this information is useful to someone.

TimDWilliams at 1/1/2013 8:59 AM
You must sign in to rate content.
(Unrated)

RE: Auditing SQL Server Permissions using CAATs

quote: each member of a fixed server role can add other users to the role. I think it's quite confusing, as they have securityadmin role for that. why would they allow that? please explain I'd the benefits outweigh the risk
Junior Lazuardi at 11/12/2012 10:17 AM
You must sign in to rate content.
(Unrated)

RE: Auditing SQL Server Permissions using CAATs

You raise an important point about the risk of application controls being bypassed using tools such as Microsoft Access and Excel where users are permitted to make direct connections to the database.

The obvious security impacts are:
- confidentiality breaches - if users have read access to whole database tables they could potentially copy entire extracts whereas using the application they would have constrained access
- integrity breaches - bypassing validation controls by directly changing data, also bypassing the application's audit trail
- availability breaches - deleting data and/or affecting system availability by causing uncontrolled database locks

I would also point out that there are potentially serious performance risks from allowing direct connections to databases. If Microsoft Access is used to create queries across multiple external linked tables, then many separate ODBC connections will be opened per external linked table with all of the data being pulled back over the network and query processing being performed "client-side". This is disastrous both for network performance and for database performance.

Given the severity of these risks I would recommend adding to your recommended audit procedure the following 2 checks:

1. Verify existence of suitable network and desktop controls to prevent direct access to database servers by end users. 
- If a suitable hardware firewall is not available to enforce a "tiered architecture" then at least software firewalls on the database server and on the desktop machines could be used to restrict access to the database to application servers. 
- The application servers should not have tools such as Microsoft Access installed on them.
- End users should not be allowed Remote Desktop Access to application servers

2. Verify the applications that are being used to make database connections using simple SQL queries (whilst logged on with full audit rights):

e.g. SELECT DISTINCT program_name from sys.sysprocesses;

This should establish if any unauthorised applications are being used and should identify the "name_of_authorised_application" for use in the next query.

e.g. SELECT DISTINCT hostname, nt_username from sys.sysprocesses WHERE program_name <> "name_of_authorised_application";

This should help to identify unauthorised database access methods and provide a basis for improving the network and desktop controls.

Although the information shown in sysprocesses will depend on network conditions, for a full list of potential fields see:
http://technet.microsoft.com/en-us/library/ms179881.aspx

Trusting that this information is useful to someone.

TimDWilliams at 1/1/2013 8:59 AM
You must sign in to rate content.
(Unrated)

RE: Auditing SQL Server Permissions using CAATs

Another important point is the authentication method used as it has a big potential impact on security. Many legacy applications use SQL logins, - with a username and password. Once this SQL login information is known, (for example by inspecting application configuration files), then it could be reused elsewhere for example connecting via Excel or Access, or passed to other people to use. There are many inherit problems with SQL logins including: * Login information is often passed over the network in cleartext * Difficulties in regularly rotating the SQL login password (changing SQL based logins in each application differs) * The password is often easily accessible from within application configuration files * Alternatively, developers may attempt to obscure this login information by hard-coding it into applications, making it very difficult to audit and change passwords. * A client application may be accessed by an unprivileged user by using the privileged user's desktop (either remotely, or by interactive login) The alternative is to use Windows Authentication based logins. These rely on passing an existing (already authenticated) windows security principle to the SQL server and provide many benefits, including: * Login information is hashed via NTLM or Kerberos so passwords are never sent in cleartext over the network * Rotating passwords does not involve changing login settings on the SQL server * It's much harder to impersonate an Application's Service account using Windows Auth login to SQL server than a SQL login (if using the proxy style access to SQL). * Applications can use Windows Delegation to pass through the user's login on behalf of the application's service account so that per-user role granular access to SQL is maintained. If not using the proxy style access to SQL, then SQL Database Application Roles come in to play best when using Windows Authentication based logins. The Windows Logins (of the users) only need LOGIN rights to the SQL server without specific access to the databases. The application then uses application roles to get access to the database objects as needed. Advantages: * Management of SQL server is simple because users can be granted access to application via an Active Directory Group * Users cannot use Excel/Access/ODBC to access databases as they do not know Application Role passwords * Users can be separately granted access to specific DB objects for Reporting Services etc as appropriate * Auditing and performance management on SQL server is better because we see actual users rather than a proxy account. Ultimately these decisions depend on what influence you have over the application. Application controls should exist at the database, server and client levels, but that doesn't always happen....
Chris.Harrison at 1/6/2013 5:26 AM
You must sign in to rate content.
(Unrated)

Leave a Comment

You must be logged in to post a comment.