Managing database users using Management Studio. Creating database roles

Hi all! Now we will look at examples creating and deleting users in Microsoft DBMS SQL Server both using Transact-SQL statements and using the environment Management Studio.

The process of creating users in MS SQL Server includes two stages:

Creating a SQL Server login. This name is required to allow the user to connect to the SQL Server instance;
Creating a database user. IN in this case we already grant the user permissions on database objects.

Note! As SQL server and for my example, I will use the Microsoft SQL Server 2012 Express version. A test database Test has been created on this SQL server.

Creating a Login on MS SQL Server

Before you start creating a login for the SQL server, you need to decide on the authentication method. There are two options:

Examination Windows authenticity – this is when the login name can identify the user as an account Windows entry or as a member Windows groups (including domain Accounts, and groups);
SQL Server Authentication. In this case, the login only exists in SQL Server.

Let's look at a couple of examples of creating a login on a SQL server. We'll do this first using SQL Server Management Studio and then using Transact-SQL.

Creating a Login Using SQL Server Management Studio

We launch Management Studio, then in the object browser we find the item “ Safety", open it with a plus sign, click right click mouse on the item " Logins" and select the item " Create Login».

Next, as an example, let's create a test login with SQL Server authentication. We provide a login name, create a password and confirm it. We can also check several options, such as using password policy, default database, default language and others.

Then click on the button “ OK", after which the login TestLogin will be created. Default given name login will be enabled and will have the rights of the "public" server role.

Creating a login using Transact-SQL

To create a login in Transact-SQL, you need to open the query editor in Management Studio and run the following instructions (it does exactly the same thing as our actions above in graphical interface Management Studio).

CREATE LOGIN WITH PASSWORD=N"Pa$$w0rd", DEFAULT_DATABASE=, DEFAULT_LANGUAGE=[Russian], CHECK_EXPIRATION=OFF, CHECK_POLICY=ON GO

In other words, to create a login in the SQL server, the instruction is used CREATE LOGIN.

Creating a SQL Server Login with Windows Authentication

To create a Windows Authenticated login, do the following: SQL statement:

CREATE LOGIN FROM WINDOWS WITH DEFAULT_DATABASE=, DEFAULT_LANGUAGE=[Russian];

GO
ComputerName\NameUser is the Computer Name\User Name;
FROM WINDOWS – specifies that Windows authentication will be used;
WITH DEFAULT_DATABASE= – default database;

DEFAULT_LANGUAGE=[Russian] – default language.

Disabling and enabling logins in MS SQL Server

If necessary, you can temporarily disable the login in order to block the user from accessing the server.

Disable ALTER LOGIN TestLogin DISABLE;

--Enable ALTER LOGIN TestLogin ENABLE;

Creating a database user in MS SQL Server Once the login has been created, you can proceed to create a database user, i.e. mapping a user to a login. Let's create a TestLogin user also in two ways i.e. With

using Management

Studio and T-SQL language. Safety Creating a Database User Using Management Studio Open Management Studio, find the desired database in the object browser and open it with the plus sign. Then also use the plus sign to open the item “" and click on the folder " Users».

"right-click and select " Create a user (Next, enter the username and login name that matches to this user in my case the names are the same).

), and also indicate the default scheme ( if not specified, the dbo scheme will be assigned Let's also immediately note the database role that this user will have. On the page " Membership"I checked the box next to the role OK».

db_datareader

, i.e. the user will have rights to read data from user tables. Click " Creating a Database User Using Transact-SQL Next T-SQL statement creates a database user (

USE Test GO CREATE USER FOR LOGIN WITH DEFAULT_SCHEMA= GO ALTER ROLE ADD MEMBER ;

GO Thus, the instructions CREATE USER

used to create a database user.

Removing Database User and Login in MS SQL Server

In order to delete a database user, you can write a simple SQL statement, for example

DROP USER Testlogin; Or use graphic tool Management Studio, i.e. in the Object Explorer, in the required database data select " Security -> Users " and right-click on the user you want to delete and select "».

Note! Delete

Users who own securable objects cannot be removed from the database. You can also use the graphical tool Management Studio ( those. “Security -> Logins” right-click on the name, and then click on “Delete”

) and Transact-SQL statement i.e.

Note! DROP LOGIN TestLogin; The current login cannot be deleted, nor can the login that owns any server-level securable object or SQL Server Agent job. Also, the login cannot be deleted if the this moment

the user is connected to the system. It is possible to delete a login without deleting the associated database user, but this will result in users who are no longer associated with their accounts.

That's all I hope, the material was useful to you, bye! In this article I will talk about how to add new or existing existing bases 8.3 (data to the 1C:Enterprise server 8.1 for other versions of the platform - 8.2 And actions are similar). Options for adding an information base will be considered both from and through the 1C:Enterprise server administration program (in the OS Windows family

). Some issues of administering information bases in a 1C:Enterprise server cluster are also touched upon.

1. Adding an infobase from the 1C:Enterprise launch window Let's create a new database on the 1C:Enterprise server from a standard configuration. To do this, launch “1C:Enterprise” and in the launch window click “ Add…

» to add an information base. The wizard for adding an information base will start, select the item “ Creation of a new information base " by setting the appropriate switch and clicking "».

Further On the list installed templates " by setting the appropriate switch and clicking "».

configurations, select the template we need and click “ Let's enter the name of the database as it will be displayed in the list of infobases, specify the location type " On the 1C:Enterprise server " by setting the appropriate switch and clicking "».

" and click " next page it is necessary to specify the parameters of the created information base, namely:

(IN in this example parameters were selected in accordance with the 1C:Enterprise server installation parameters adopted in the article)

Server cluster name 1C:Enterprise— as a rule, it matches the network name of the computer where the 1C:Enterprise server is installed (the central cluster of the server);
Name created base in a cluster— the name by which the information base will be accessed. Must be unique within a given cluster;
Secure connection- disabled by default;
Type of DBMS on which the database will be stored— in this example, MS SQL Server;
Database server name— as a rule, it consists of the network name of the computer where the database server is installed and the name of the server instance (if any), separated by the “\” sign;
Name of the database on the database server— for convenience, we will adhere to the rule that the name of the database must match the name of the infobase in the cluster. In addition, in the case of using MS SQL Server, the first character in the database name can only be a letter of the Latin alphabet or the symbol “_”, subsequent characters can only be a letter of the Latin alphabet, a number or the symbols “_” and “&”, the name must be unique within a given database server instance and not exceed 63 characters. If the database already exists on the server, the current database will be used, if not and the flag " Create a database if it doesn't exist", will be added new base to the database server.
Database user— a DBMS user who will become the owner of the database on the server if a new database is created or who has rights to work with an existing one;
User password— the password of the user on whose behalf the database will be accessed;
Date offset— 0 or 2000. This parameter determines the number of years that will be added to dates when they are saved in the database Microsoft data SQL Server and subtracted when retrieving them. The fact is that the DATATIME type used by Microsoft SQL Server allows you to store dates in the range from January 1, 1753 to December 31, 9999. If, when working with an infobase, there may be a need to store dates preceding lower limit of this range, the date offset should be selected as 2000. In addition, if the application solution uses accumulation registers or accounting registers (and most likely this will be the case), the date offset field must also be set to 2000.
Set blocking routine tasks — setting the flag allows you to prohibit the execution of scheduled tasks on the server for this information base. It should be installed in the case of creating test information bases, where the implementation of routine tasks does not carry any practical load.

Having set all the information base parameters, click “ " by setting the appropriate switch and clicking "».

And finally, specify the launch parameters for the database being created and click “ Ready» to start the process of creating a new information base. This will create a new information base on the 1C:Enterprise server, if necessary, a new database is created on the database server, and data from the configuration template will also be loaded.

If all of the above actions are completed successfully, the wizard will complete its work, and we will see the newly created database in the list of infobases in the 1C:Enterprise launch window.

2. Adding an information base from the 1C:Enterprise server administration console

Now let’s add another infobase to the server cluster, but through the “ Administration of 1C:Enterprise servers"(previously). You can find it:

Well, in any case, the snap-in can be launched by executing the file “ 1CV8 Servers.msc"located in the 1C:Enterprise installation directory in the subdirectory " common».

If the equipment " "runs on the same computer where the 1C:Enterprise server is installed, then in the tree on the left, in the branch with the network name current computer, we should see this server cluster called " Local cluster" Expanding the tab " Information bases"we will see all the infobases in this server cluster (for example, the database created through the 1C:Enterprise launch window in the previous step). To add a new information base, right-click on this tab and select “ Create» — « Information base».

The parameters window for the created infobase will open. The list of parameters is the same as that described above in paragraph 1 of this instruction. After filling out all the parameters, click “ OK» to start the process of creating a new information base. In this case, a new information base will be created on the 1C:Enterprise server and, if necessary, a new database will be created on the database server.

If all of the above actions are completed successfully, the parameters window will close, and we will see the newly created database in the list of infobases of the current cluster.

And if you go to the “Wednesday” program SQL Server Management Studio" and connect to the current instance of MS SQL Server, you can see the ones created on previous steps Database.

3. Properties of the infobase

To view or change the parameters of a specific infobase, you need to use the " Administration of 1C Enterprise servers", in the list of infobases, right-click on it and select " Properties" To authenticate in the administration console, you must use administrators in the corresponding infobases. In other words this check is similar to authentication when connecting to an infobase through the 1C:Enterprise client.

As you can see, the following have been added to the list of parameters already familiar to us:

Session start blocking is enabled— a flag that allows you to enable blocking of the start of sessions with the infobase; if the flag is set, then:
- Existing sessions can continue running, establish new connections, and also run background jobs;
- Establishing new sessions and connections to the infobase is prohibited.
Start for other versions of the platform - End— duration of session blocking;
Message— text that will be part of the error message when trying to establish a connection with a blocked infobase;
Permission code— the string that should be added to the parameter /UC when starting 1C:Enterprise to connect to the infobase despite being blocked;
Blocking options— arbitrary text that can be used in configurations for various purposes;
External session management— a string describing the parameters of the Web service external control sessions;
Mandatory use of external control— if the flag is set, then if the external session management Web service is unavailable, an error occurs and establishing a connection to the infobase is impossible;
Security Profile— if you specify a profile name, the application solution begins to work taking into account the restrictions imposed by specified profile security;
Security Profile safe mode - the same as the security profile, but restrictions will be imposed on fragments application solution operating in safe mode.

By changing required parameters click " Apply" to save changes or " OK» to save and close the infobase properties window.

4. Adding an existing infobase to the list of infobases in the 1C:Enterprise launch window

And finally, all we have to do is add the one created using the “ Administration of 1C Enterprise servers» infobase to the list of infobases in the 1C:Enterprise launch window. Why in this window click “ Let's create a new database on the 1C:Enterprise server from a standard configuration. To do this, launch “1C:Enterprise” and in the launch window click “" and in the wizard that starts adding an infobase/group, select the appropriate item and click " " by setting the appropriate switch and clicking "».

Enter the name of the infobase as it will be displayed in the list, select the type of infobase location " On the 1C:Enterprise server" and press " again " by setting the appropriate switch and clicking "».

Enter the address of the 1C:Enterprise server cluster and the name of the infobase, as it is defined in this cluster. Click " " by setting the appropriate switch and clicking "».

And finally, set the parameters for launching the infobase and click “ Ready» to complete the wizard.

Our database has appeared in the list of information databases. You need to understand that this is an empty (clean) database, and then you should load data into it from a template, or from a pre-loaded data file.

Did this article help you?

There are quite a lot of similar articles, but I primarily wrote this one for myself, focusing on the notes that describe possible problems. I hope the article will be useful to others.
1. Install the 1C platform
2. Install MS SQL server 2008. During installation, specify the database user. (Which is SA).

After installation, open the administration panel for 1C enterprise servers and see that it is empty.
You need to create a server: Open console root->Central 1C: Enterprise 8.2 servers. Right-click on it and select new. In the drop-down menu, select Central Server 1C Enterprise 8.2. A window with 4 fields will open in front of us:
Protocol- protocol through which data will be transmitted
Name- the name of the computer on the network on which the server is located
IP port- port on which the server is accessible
Description-description. not necessary.

Note:
If the 1C platform was installed on a computer, and then the computer was renamed, then you will not be able to reach it, because the 1C platform is a very smart platform and records the computer name in certain files during installation, but then, when the computer name changes, the platform no longer will rewrite These files are needed for the operation of the RAGENT 1C service (it can be found in running services, through the administration panel windows server). This all suggests that in order to rename these files, you need to stop the RAGENT service. The files themselves are located in the following places:
C:\Program Files (x86)\1cv82\srvinfo\srvribrg
C:\Program Files (x86)\1cv82\srvinfo\reg_1541\1CV8Reg
We open these files with notepad and change the past name of the machine to the present one manually. Save and launch RAGENT.

Let's return to the settings:
After the window with the fields is filled in, click OK and if everything is done correctly, then we have a server named by the name of the machine on which it is located.

So. The server is running and now we need to create a database on MYSQL server and connect it to north 1C. There are several ways - here I will describe the simplest:
On the 1C enterprise server, open our new created server by clicking on the + next to the server name and on the “INFORMATION BASES” item, right-click and select New->Information Base
A window will open in front of us with the following fields:

Name-the name of our database on the 1C server (As a rule, many people write it the same as in the database field, so as not to get confused)
Description-description
Secure connection-disabled by default. you can enable it, but then the load on the server will increase
Database server-if the server is on the same server, then indicate (local) exactly like this in brackets, if not on this server, then indicate the server ip
DBMS type-Select MS SQL type
Database-name of the database on the MS SQL server. If there is no database, you can tick one of the checkboxes and it will be created
Database server user-Specify either the user you created during installation, or create individual user in MS SQL, set its rights and register it here.
Database server user password-password
Allow the issuance of licenses by the 1C Enterprise server-choose yes
A country-Choose a country
Date offset-set to 0
Checkbox “Create a database in case of absence”-the same checkbox for creating a database if it doesn’t exist
Checkbox “Set blocking of scheduled tasks”- do not tick the box

Click OK and see that the servers are configured and an information base appears in the “Information Bases” tab under the name we gave it.

To set up Backup we need to open Microsoft SQL MANAGEMENT STUDIO.
Enter your login and connect to the server.
Before us is the administrative console. IN Object explorer open a tab Management and in it we see Maintenance plans. Here we will create the BackUP we need. As usual, right click on Maintance plans->new maintance plan. The subplan tab will appear in the main window, and under Object Explorer another window will appear ToolBox in which is nested Maintance Plans Tasks. In it we will choose Back Up DataBase Task by clicking on it 2 times. It will be transferred to the main window. We click on it 2 times and a window appears in front of us, again with fields where we can choose which Back Up to do, which BackUp base to use, and where to save it. After completing the settings, click Ok.

Note:
Saving Back Up to any network folder(by the way, you will have to register the path manually, because the directory selection window only sees local resources) keep track of the access rights, and at the same time keep track of what authentication you have on MySql server because if the authentication is set not for Windows accounts, but for the internal DBMS user, and if at the same time you have an AD server running, then BackUp will throw an error when trying to execute, since it will do this on behalf of the internal DBMS user and AD will not let it through anywhere except local computer.

After you have configured the path, base and BackUp type, you need to configure the schedule. To do this, there is a sign in the main window above the Task you created SubPlan. At the end of the sign (on the right) there is a calendar icon. By clicking on it you will be taken to the schedule settings. By checking the checkboxes of the days and setting the time, you will set up a schedule. By double-clicking on the field called SubPlan you can change the name of the Task. Having configured everything, go to File-> Save All. After saving, a Task with your name that you gave to BackUp will appear in Maintance plans.

After completing the setup, be sure to check the operation. For this Right key mouse click on the created Task and execute Exicute.

Note:
If Exicute runs with an error, read the errors that Studio will give you, and first of all, check if you are running SQL server agent. It is he who carries out the tasks and the Exicute function turns to him to complete the tasks. If it is not running, the execution attempt will fail. To see whether the agent is working or not in Studio in Object Explorer, go to the tab SQL Server Agent. If the bullet icon has a red circle with a cross, it means the agent has been stopped. You can start it by right-clicking on it and selecting the START option from the context menu.

The security system adopted in SQL language. The general rules for access control are outlined. Describes authentication modes and components of the security structure (users, database roles), administration of the security system (creating and managing accounts, managing users and roles). A definition of user rights to access database objects is given. Implicit rights, issues of denying access and implicit denial of access, as well as access conflicts are considered.

Database User Management

Stable system user management – required condition data security, stored in any relational DBMS. In SQL there is no single standard command, intended for creating users databases - each implementation does this differently. In some implementations these special teams have certain similarities, while in others their syntax has significant differences. However, regardless of the specific implementation, all the basic principles are the same.

User management in MS SQL Server environment

Let's consider the question creating users in MS SQL Server environment.

After design logical structure database, relationships between tables, integrity constraints and other structures it is necessary to determine the circle of users who will have access to the database.

The SQL server system has a two-level configuration of data access restrictions. At the first level it is necessary to create the so-called user account(login), which allows him to connect to the server itself, but does not automatically give him access to the databases. At the second level, a user record must be created for each SQL server database based on the account. Based on the rights granted to the user as a database user (user), his login name (login) gains access to the corresponding database. In different databases, the login of the same user may have the same or different names user with different access rights. In other words, with the help user account a connection is made to the SQL server, after which its access levels are determined for each database separately.

In the SQL server system, there are additional objects - roles, which determine the level of access to SQL server objects. They are divided into two groups: those assigned to user accounts servers and used to restrict access to database objects.

So, at the server level, the security system operates with the following concepts:

authentication ;
Account ;
built-in server roles.

At the database level the following concepts apply;

database user;
fixed database role;
custom role of the database.

Authentication Modes

SQL Server offers two modes user authentication:

authentication mode using Windows NT/2000;
mixed mode authentication (Windows NT Authentication and SQL Server Authentication).

Security system administration

For creating a user in MS SQL Server environment should be taken next steps:

Create in database user account by specifying a password and default database name (sp_addlogin).
Add this user to all necessary bases data (procedure sp_adduser).
Grant it the appropriate privileges in each database (GRANT command).

Create a new account can be done using a system stored procedure:

sp_addlogin [@login=] "account" [, [@password=] "password"] [, [@defdb=] "default_database"]

After completing authentication and receiving login ID user is considered registered and is granted access to the server. For each database whose objects he intends to access, user account(login) is associated with the user (user) of a specific database, which is done through the procedure:

sp_adduser [@loginame=] "account" [, [@name_in_db=] "username"] [, [@grpname=] "rolename"]

The stored procedure allows you to map a Windows NT account to a username:

sp_grantdbaccess [@login=] 'account' [, [@name_in_db=]'username']

The user who creates an object in the database (table, stored procedure, view) becomes its owner. Object owner(database object owner dbo) has all access rights to the object it created. In order for a user to create an object, the database owner (dbo) must grant the user the appropriate rights. Full name created object includes the name of the user who created it.

Object owner doesn't have special password or special access rights. He implicitly has full control, but must explicitly grant access to other users.

SQL Server allows you to transfer ownership from one user to another using the procedure:

sp_changeobjectowner [@objname=] 'object_name' [@newowner=] 'owner_name'

A role allows you to combine users who perform the same functions into one group.

SQL Server implements two types of standard roles: at the server level and at the database level. At SQL installation Server, fixed server roles are created (for example, sysadmin with the right to perform any SQL server functions) and fixed database roles (for example, db_owner with the right full access to the database or db_accessadmin with the right to add and delete users). Among the fixed database roles, there is a public role, which has special appointment because its members are all users who have access to the database.

You can include any SQL Server (login) account or Windows NT account in any server role.

Database roles allow you to combine users into one administrative unit and work with it as regular user. You can assign access rights to database objects to a specific role, which automatically grants all members of that role the same rights.

The database role can include SQL Server users, SQL Server roles, and Windows NT users.

Various actions in relation to the role are carried out using special procedures:

Creation new role :
sp_addrole [@rolename=] "role_name" [, [@ownername=] "owner_name"]
adding a user to a role:
sp_addrolemember [@rolename=] "role_name", [@membername=] "user_name"
removing a user from a role:
sp_droprolemember [@rolename=] "role_name", [@membername=] "user_name"
removing a role:
sp_droprole [@rolename=] "role_name"

Data access control

Defining privileges in a language standard

Every DBMS must support a mechanism to ensure that only those users who have the appropriate permission can access the database. The SQL language includes GRANT and REVOKE statements designed to protect tables in a database. The protection mechanism is based on the use user IDs, ownership rights and privileges granted to them.

User ID is a common SQL identifier used to identify a database user. Each user must be assigned their own ID, assigned by the database administrator. For obvious safety reasons user ID usually associated with some password. Each SQL statement executed by the DBMS is executed on behalf of a user. User ID determines which database objects a user can reference and what operations on those objects he is authorized to perform.

Each created in SQL environment an object has its owner, who is initially the only person aware of its existence of this object and has the right to perform any operations with it.

Privileges, or rights, are actions that a user has the right to perform in relation to a given database table or view. The SQL standard defines the following set of privileges:

SELECT – the right to select data from the table;
INSERT – the right to insert new rows into the table;
UPDATE – the right to change data in the table;
DELETE – the right to delete rows from the table;
REFERENCES – the right to refer to columns of the specified table in descriptions of data integrity support requirements;
USAGE – the right to use domains, validations and character sets.

INSERT and UPDATE privileges can be limited to specific columns in a table, in which case the user is only allowed to modify the values of those columns. Similarly, the REFERENCES privilege can be applied exclusively to individual columns of a table, allowing their names to be used in data integrity statements such as CHECK and FOREIGN KEY clauses included in the definition of other tables, while other columns will be prohibited from being used for similar purposes.

When a user creates a new table using the CREATE TABLE statement, he automatically becomes its owner and has control over it. full set privileges that other users do not initially have. To grant them access, the owner must explicitly grant the necessary rights, which is done by using the GRANT statement.

By creating a view using the CREATE VIEW statement, the user automatically becomes the owner of that view and also has full rights. To create a view, the user only needs to have the SELECT privilege on all tables included in it and the REFERENCES privilege on all columns referenced in the definition of this view. The user will receive INSERT, UPDATE, and DELETE privileges on the created view only if he has the corresponding privileges on all tables used in the view.

Granting privileges to users

The GRANT statement is used for granting privileges against named database objects to specified users. It is usually used by the table owner to providing access to other users. The GRANT statement has next format:

<предоставление_привилегий>::= GRANT (<привилегия>[,...n] | ALL PRIVILEGES) ON object_name TO (<идентификатор_пользователя>[,...n]| PUBLIC) [WITH GRANT OPTION]

Parameter<привилегия>represents:

For reasons of simplicity, you can specify in the GRANT statement keyword ALL PRIVILEGES , which will grant the specified user all existing privileges without having to list them. Additionally, this statement may include the PUBLIC keyword, meaning Providing access of the specified type not only to all existing users, but also to all those who will be defined in the database subsequently.

The object_name parameter can be used as the name of a database table, view, domain, character set, check.

With the WITH GRANT OPTION, users specified in the GRANT statement have the right to transfer all provided them in relation to the specified object of privilege to other users, who, in turn, will be endowed with exactly the same right to transfer their powers. If this parameter will not be specified, the recipient of the privilege will not be able to transfer his rights to other users. This way, the object owner can clearly control who has been granted access to the object and what permissions are granted to them.

Revoking privileges granted to users

In SQL language for revoking privileges, provided to users via the GRANT statement, the REVOKE statement is used. Using this operator, all or some of the privileges previously obtained by the specified user can be revoked. The REVOKE statement has the following format:

<отмена_привилегий>::= REVOKE (<привилегия>[,...n] | ALL PRIVILEGES) ON object_name FROM (<идентификатор_пользователя>[,...n]| PUBLIC)

The ALL PRIVILEGES keyword means that all privileges for the specified user are revoked. provided him earlier by the user who entered this operator. The optional GRANT OPTION FOR clause allows all privileges passed in the original GRANT statement by WITH GRANT OPTION to be overridden, regardless of the privileges themselves.

If the RESTRICT keyword is specified in the statement, the REVOKE command can succeed only if the privileges listed in the statement would not cause any other users to have so-called "retained" privileges. The CASCADE option removes any privileges that might otherwise remain with other users.

"Retained" privileges are those that remain with the user to whom they were once granted using the GRANT OPTION parameter.

Since the presence of a privilege is necessary to create certain objects, along with its deletion, you can lose the right through the use of which this or that object was created (such objects are called “abandoned”). If the REVOKE statement may result in abandoned objects (such as views), the right will be revoked unless the CASCADE keyword is specified. If the CASCADE keyword is present in the statement, then DROP statements will be automatically issued for any dropped objects that occur when the original REVOKE statement is executed.

Privileges that have been granted to the specified user by other users cannot be affected by the REVOKE statement. Therefore, if another user also granted a given user a removable privilege, then the specified user will retain the right to access the corresponding table. For example, suppose user A and user E had INSERT permission on the Product table. User A granted INSERT privilege to user B for the Product table, specifying WITH GRANT OPTION (step 1). User B transferred this privilege to User C (step 2). Then user C received it from user E (step 3). Next, user C granted the said privilege to user D (step 4). When user A revokes privilege INSERT for user B, it cannot be reversed for user C because he has already received it from user E. If user E had not granted this privilege to user C, then removing user B's privilege would have the effect of cascadingly removing privileges for users C and D (see Table 17.1).

Implementation of access rights to database objects in the MS SQL Server environment

Categories of rights in the MS SQL Server environment

When connecting to SQL Server everything possible actions users are defined by the rights (privileges, permissions) granted to their account, group or role in which they belong.

Rights can be divided into three categories:

rights to access objects;
rights to execute commands;
implicit rights.

Table 17.1.

User A	User B	User C	User D	User E
GRANT INSERT ON Product TO B WITH GRANT OPTION	Obtaining the right
		Obtaining rights from B. Obtaining the right from E		GRANT INSERT ON Product TO C WITH GRANT OPTION
		GRANT INSERT ON Item TO D	Obtaining the right
REVOKE INSERT ON Product TO B CASCADE	Cancellation of rights	Reservation of rights	Reservation of rights	Reservation of rights

Working with data and executing stored procedures requires an access class called rights to access objects databases. Objects mean tables, table columns, views, stored procedures.

For various objects different sets of access rights apply to them:

SELECT, INSERT, UPDATE, DELETE, REFERENCES – for a table or view;
SELECT , UPDATE – for a specific column of a table or view;
EXECUTE – for stored procedures and functions.

The INSERT right allows you to insert new rows into a table or view and is granted only at the table or view level; it cannot be issued at the column level.

The UPDATE right is issued either at the table level, which allows you to change all the data in it, or at the level of an individual column, which allows you to change the data only within its limits.

The DELETE right allows you to delete rows from a table or view; it is granted only at the table or view level, but cannot be granted at the column level.

The SELECT right allows data selection and can be issued both at the table level and at the level of an individual column.

The REFERENCES right provides the ability to refer to specified object. In relation to tables, allows creating foreign keys, referencing the primary key or unique column of this table.

Granting rights

For driving user permission to access objects database command is used:

Parameter<привилегия>

The WITH GRANT OPTION option will help the user to whom you grant rights to grant access rights to the object to other users. Its use requires special care, since the owner loses control over granting rights for access to other users. It is best to limit the circle of users who have the ability manage rights assignment.

Optional parameter AS (group_name | role_name) allows you to specify the user's participation in a role that provides granting rights other users.

The only access right that can be granted to a stored procedure is the right to execute it (EXECUTE). Naturally, in addition to this, the owner of the stored procedure can view and change its code.

You can grant a function the right to execute it, and also grant the REFERENCES right, which will make it possible to associate the function with the objects it refers to. Such binding will prevent changes to the structure of objects that could lead to disruption of the function.

Permissions to execute SQL commands

This class of rights controls the ability to create objects in the database, the database itself, and execute procedures Reserve copy. Can be used next command to provide rights to execute commands SQL:

<предоставление_права_выполнения>::= GRANT (ALL |<команда>

Parameter<команда>is the following structure:

Thus, you can grant the right to create database, table, view, default, rule, stored procedure, backup copy database and transaction log or grant all of the above rights at once.

Implicit rights

Some actions do not require explicit permission and are available by default. These actions can only be performed by members of server roles or owners of objects in the database.

Implicit rights are not directly granted to the user; they are granted only under certain circumstances. For example, a user can become the owner of a database object only if he creates the object himself or if someone else transfers ownership of their object to him. Thus, the owner of the object will automatically receive rights to perform any actions with the object, including Providing access to the object to other users. These rights are not specified anywhere; only the fact of owning the object allows you to perform any actions.

Denying access

SQL Server security is hierarchical, so database roles include Windows NT accounts and groups, SQL Server users, and roles. The user, in turn, can participate in several roles and simultaneously have different access rights for different roles. When one of the user's roles has permission to access data, the user automatically has the same rights. However, if the need arises, the user can be denied access to data or commands, then all access permissions granted to him at any level of the hierarchy are revoked. This ensures that access remains denied regardless of permissions granted at a higher level.

For access denial

The CASCADE parameter allows you to revoke rights not only from a specific user, but also from all those to whom he has granted similar rights.

To prohibit the execution of SQL commands, use the following operator:

<запрещение_выполнения>::= DENY (ALL |<команда>[,...n]) TO (username | groupname | rolename) [,...n]

Implicit access denial

Implicit Rejection like access denial with the difference that it operates only at the level at which it is defined. If the user is at a certain level access is implicitly denied, he can still obtain it at another level of the hierarchy through membership in a role that has view rights. By default, user data access is implicitly denied. For implicit access denial The command used for database objects is:

For implicit deviationpermissions to execute commands The SQL command used is:

<неявное_отклонение_разрешения>::= REVOKE (ALL |<команда>[,...n]) FROM (username | groupname | rolename)[,...n]

The meaning of the parameters is similar to the parameters of the GRANT and DENY commands. The GRANT OPTION FOR parameter is used when it is necessary to revoke the right, provided with the WITH GRANT OPTION parameter of the GRANT command. The user retains permission to access the object, but loses the ability to grant that permission to other users.

Access conflicts

Permissions, provided roles or groups are inherited by their members. Although the user may access granted Through membership in one role, a role at another level may be prohibited from acting on an object. In this case there arises access conflict.

When resolved access conflicts SQL Server is guided by the following principle: permission to Providing access has the lowest priority, and access denial- highest. This means that data can only be accessed explicitly. providing Without access denial at any other level of the security hierarchy. If access is not explicitly granted, the user will not be able to work with the data.

Example 17.1. Create a new database, a new user for this database, giving him all rights.

Creation by the administrator of a new -- database CREATE DATABASE basa_user -- creation of a new user with -- name UserA and password '123' -- the default database for -- user UserA will be a database -- named basa_user. sp_addlogin "UserA","123","basa_user" -- go to the database basa_user USE basa_user -- add to the current database -- (basa_user) a user named -- userA sp_adduser "UserA" -- provide the user with userA -- in the database basa_user all rights GRANT ALL TO UserA Example 17.1.

Creating a new database, a new user for this database, granting him all rights. Example 17.2.

Using roles.

Let's create the stud role and include two users user1 and user2 in this role:

sp_addrole "stud" sp_addrolemember "stud","user1" sp_addrolemember "stud","user2"

Let's grant rights to the role stud and directly to the user user2:

GRANT SELECT, INSERT ON Product TO stud GRANT SELECT, INSERT ON Product TO user2

After executing these commands, users user1 and user2 can execute the commands to select and add a record to the Product table.

Let's suspend the right to insert into the Product table for the stud role:

REVOKE INSERT ON Product TO stud

After executing the previous command, user1 loses the right to insert a record, and user2 retains this right, since the insert right is explicitly granted to him.

Let's execute the command

DENY INSERT ON Product TO stud.