Object security

If a user creates an object such as a table, in the SQL Server, the user becomes the owner of that object. Only the owner can access the object unless privileges are explicitly granted to other users. Other users can only access the object if they have been granted privileges to do so. In an LN environment where many users access the same tables in the SQL Server database, a mechanism has been developed to allow users to share these tables.

To allow different LN users to share the same SQL Server table, a group concept is used. An LN group maps users to a database in SQL Server and ensures that members of the LN group have sufficient privileges to access data in the LN group’s tables.

The LN MSQL driver uses a SQL Server role to implement the LN group concept. Whenever a new table is created by the LN group user, select, insert, delete, and update privileges are granted to the SQL Server role. Any user associated with the SQL Server role automatically inherits these privileges and can individually perform these operations on the LN group table.

When new users are added, they only need to be associated with the SQL Server role. New users automatically inherit all privileges currently granted to the role without the need to grant privileges on every object in the database to the user. When the user is dropped from the role, these privileges are revoked. The user no longer has access to tables within that SQL Server group. If the privileges to operate on the tables were explicitly granted to the user, then they must also be explicitly revoked when the user is dropped from the LN group. The overhead of adding users is greatly reduced by granting privileges to the role and simply associating users with that role. The use of this method provides flexibility and ease of maintenance.

In the DDL statements generated by the driver, object names are not qualified by the owner name. Ownership is determined by the session (group or user) the create table is executed in. When creating objects identified as belonging to the group, the user creating the object logs into the SQL Server as the group user (the database driver handles this transparently).

In this case the table will be owned by the group and permissions will be granted to allow all group-users access. When accessing objects, users connect to the SQL Server with their own login.