Tips on Protecting Your SQL Database with Security Roles
Microsoft SQL Server
Microsoft’s SQL Server is the most powerful relational database in the world. With the ability to deliver millions of transactions per second, this database server must be secure. Microsoft has engineered SQL with capabilities to cluster with other servers so that information can be delivered to a user within seconds. This complex server has to be secured so that data and assets will not fall in the hands of malicious users or untrained users. SQL allows for thousands of tables, scripts and reports to be compiled and delivered to a user within seconds. Because of the speed of this server, the data can be stolen in seconds or the data can be changed and corrupt within seconds.
Defining Roles
Security Roles are used in all versions of Microsoft SQL Servers. With Analysis Services (Multidimensional Data), Roles are used to manage security for objects and data (Analysis Services). The roles a database administrator defines manages these aforesaid objects and data.
Every user defined in a workstation or server has a SID. SIDs are security identifiers of these individual users (or even groups). In layman terms, SIDs are the DNA of the user. These identifiers, when attached to objects (all for specific access), are what defines permissions and rights. In other words, when you give ‘John’ access to read-only or full control, it’s not just ‘John’, it’s his SID which attaches (not visible to the administrator) to the object.
There are two primary roles that are provided with Analysis Services. The server role which is a fixed role that gives the administrator access to any instance of Analysis Services and the database role that are defined by the SQL administrator. The database role is the role assigned to a user (non-administrator) by the SQL server administrator.
Roles are groups of users who use the SQL server and data. Specific permissions for individual objects are given by the administrator. These members can be added or removed from specific roles depending on their individual need for database access. When roles are defined, SQL Server administrators are ’looking’ at the needs (uses) of individual users.
Conclusion
In conclusion, individual roles can directly be defined by the user’s role on the Microsoft SQL Server. Individual database administrators should not give administrative privileges to end users of the database unless that person truly needs these permissions. Defining roles should be analyzed by the database administrator and too much ‘power’ can be given to users if the administrator does not take into consideration of what the user will be doing. This role based security is not only for the protection of theft but is used for the integrity of the data. These roles play a critical step in the securing of your data.
