This is a simple reminder of the entire SQL Security process for everyone struggling with their new role as a DBA. Protecting your SQL server database is the first step towards becoming a good DBA from an accidental DBA.
How is SQL Server Securitydifferent from trusted authentication?
Allremote DBA experts must already know this — all Microsoft SQL Servers have two branches of security. The SQL Server Securityand the Trusted Authentication (we most commonly refer to as Windows Authentication). SQL server has a standard login and password combination as a security measure. The trusted authentication, on the other hand, assumes that the device that is attempting to connect to the SQL database has already been authenticated through domain authentication and that is passed on to the SQL server instance.
Hence, all the users attempting to login to the SQL server through the SQL Server Securitygateway will require an authenticated login id and password. These need to be created on the particular SQL server. Only a validated login that matches with the one on the SQL server will grant an attempted user access to the database.
Why do DBAs need prior knowledge to get to the heart of SQL security?
There are usually two levels of access into a SQL server. These are usually the server logins and the database users. If you have spent a couple of days as a DBA you must already know that these server logins allow an outside user to connect to the SQL server. As we have discussed above, this usually involves a login and password for all SQL based security logins.
Now, as far as the DBA dynamicity allows, there are two different ways to create these logins. A DBA can create and monitor these logins via Transact-SQL or he can create it through the GUI. The documentation of the former procedure is quite extensive and it is best served through a link to the SQL server’s official documentation.
The GUI path is quite simple and well guided. To start creating a new login a user can go to Object Explorer > SSMS > Logins > New Logins.
You will be guided to a general tab for the create login process where you will be guided to set the following fields and options –
- Login name – this is your unique user name that will be later used for authentication under the SQL Server Security.
- Password – if you are using a SQL Server authentication the text box for password will be enabled and you will be prompted to give both the login id/name and the corresponding password.
- Default database –during the completion of the establishment of a connection with a SQL Server a couple of things happen in sequence. Your authentication is verified and the validated login is associated with a user object inside the default database.
There are a few other elements inside the general tab that can be customized and optimized, like – password policy and expiration settings and certificates, keys and credentials.
What defines server roles in SQL database?
Our next concern is Server Roles. This page allows you to assume any roles inside the SQL server for the new login. It is essentially a collection of securables that are collected together so you don’t have to assign them rights individually every time use call for them.Every login is a member of the public role by default and that affords a basic connection to the SQL server. With the current structures of the SQL Servers, the securables exist in two different levels. They can be found in both database and server levels, and are usually granted in two different situations –
- When a pre-defined role is too broad to a login and affords too many rights.
- When a pre-defined role is not encompassingall the rights required for an authenticated login.
What are Schemas and Roles in a SQL Server?
In case of SQL, both Schemas and Roles represent collections. While Roles are a collection of rights within the given database, Schemas have two different types of default categories: sys and dbo. Sys is the owner of all the system objects in the Microsoft SQL server and the dbo is assigned for all users if no default schema is assigned for them.
With reference to this bit of information, we’d suggest that, it is in the best interest of the user and the remote DBMS to use fully-qualified names only. When we say that, we mean, using the database name, schema name and object name instead of just the object name.
As far as roles are concerned, all pre-defined roles are included with Microsoft SQL Servers at both the database and server layers. You may even create your own role at both the levels since customizing these roles allow you to restrict access to only the predefined ones. Hence, it not only saves you time, it also prevents instances of inconsistency when multiple users and logins are pushed together at one level.
It is indeed impossible to enlist all the roles at the database and the server layers. But all in all, the evolution of these roles and logins has converged with the security layers of Microsoft SQL server. All the current versions of SQL Servers use role-based security and they allow you, as a DBA, to assign permissions to a role, a group of users or even the conventional individual user before the commencement of an operation.
Author Bio: Amanda Stein is a leader, thinker and creator who have made new records in the world of remote database management. She has set a new benchmark for all remote Database experts who strive to perform better each day. She has been working as a DBA for over a decade and the prowess of the minutest details of Microsoft SQL server database makes her one of the most sought after experts in seminars, webinars and RDBMS presentations.
