DB SEC – Once in a while exams suck
The operating system is one of the main access points to the database management system. No application including DBMS, regardless of how simple and small, or complex and large, can be used without the operating system.
When you want to access the database, you are authenticated by the operating system. Once you are authenticated, you can operate or access the database – depending on what roles and the set of privileges granted to you. Since the database resides on a machine operated by the operating system, the operating system becomes the first line of defense for any database security violations and infringements.
The primary reason for such a method is the assumption that once an intruder gets control of the operating system, it takes very little knowledge, or effort, to access the database. In addition, database architects and administrators may prefer to centralize the management of all user administration in one location by using operating system authentication only. But the user must be authenticated at each level: the network, the operating system, and the database system.
Advantages:
- User accounts and authentication are controlled by the database. There is no reliance on anything outside of the database.
- Oracle provides strong password management features to enhance security when using database authentication.
-Many network authentication services, such as Kerberos support single sign-on, enabling users to have fewer passwords to remember
- It is easier to administer when there are small user communities
Disadvantage:
With external authentication, your database relies on the underlying operating system or network authentication service to restrict access to database accounts – if the OS or network gets hacked, your database goes with it
A database password is not used for this type of login.
If your operating system or network service permits, then it can authenticate users before they can log in to the database and user can have access to data.
The members of setupadmin server role can manage linked servers (add or remove linked servers), manage replication, manage extended stored procedures, and execute some system stored procedures, such as sp_serveroption.
Implementation in SQL Server
Use application roles: Special roles that are activated at the time of authorization
- Require a password and cannot contain members
- Connect a user to the application role: overrules user’s privileges
- Create and drop application roles using the command line and the Enterprise Manager by executing the follow store procedures:
SP_ADDAPPROLE
SP_DROPAPPROLE
- Activate application roles using SP_SETAPPROLE
- Connect to database as the proxy user
- Validate the user name and password
- Retrieve the application role name
- Activate the application role
There are really two main purposes for using them:
- The security requirements of a security policy necessitate that data access be restricted at the row or column level (fine-grained access).
- One database schema serves multiple unrelated groups or entities.
For example:
We create a modified ‘VIEW’ from Employees table to restrict access per user. You add a column CTL_UPD_USER and use it to specifically indicate the user who owns the row. When the row is updated by the user, the user’s name populate/inserts in the column.
During an audit, the auditor can instantly know who was the last person to who updated the data.
Question with a question: what if the organization / database have 300 users? Management of the views for each users can be time consuming and cumbersome. There is no way a DB Admin is going to implement and use views for access-level control.
In SQL server, access-level can be controlled through built in Windows NT group login that has been granted access to SQL Server OR a SQL Server user login, that is maintained within SQL Server.
A lot of information is capture in SQL server including:
· Failed login attempts (optionally successful logins as well)
· Backup and Restore information
· Extended stored procedures DLL loading
· Server options being disabled/enabled (sp_configure)
· Database options being changed (sp_dboption)
· Some DBCC commands
· Error messages
Each audit activity depends on the objectives of the audit outlined. An audit assures that all company transactions comply with government laws and regulations. An audit can be conducted as a review of the enforcement of security policies and procedures – they all take place in an auditing environment which as follows:
Objectives—An audit without a set of objectives is useless. To conduct an audit you must know what the audited entity is to be measured against. What we want to audit?
Procedures—To conduct an audit, step-by-step instructions and tasks must be documented ahead of time. How the audit should be done?
People—Every auditing environment must have an auditor. Who is involved?
Audited entities—This includes people, documents, processes, systems, activities, or any operations that are being audited. What is being audited?
Row Level Trigger get fired once for each row in a table affected by the Insert, Update or Delete commands/statementes. If statement doesn’t affect any row, no trigger action happens. For example, if a deletion is defined as a trigger and a single DELETE command is issued that deletes five rows from a table, then the trigger will fire five times, once for each row.
Statement Level Trigger get fired once for each triggering statement. The trigger activates and performs its activities irrespective of number of rows affected due to SQL statement. In the same example as above, a single DELETE command deletes five rows and the trigger would fire only once.