Chapter 2 User Authentication, Authorization, and Security (8): Create a database User mapped to the login name, authentication

Chapter 2 User Authentication, Authorization, and Security (8): Create a database User mapped to the login name, authentication
Source: Workshop

Without the consent of the author, no one shall be published in the form of "original" or used for commercial purposes. I am not responsible for any legal liability.

Previous Article: http://blog.csdn.net/dba_huangzj/article/details/38895357

 

Preface:

 

The login name is used to authorize and Access Server resources. To access the database, you need to map users in the database. A user is a database-level security subject, and the access to database resources is granted to the user, not the login name.

 

Implementation:

 

There are two ways to create a database user: the first is the attribute page of the server-level login name, and the other is implemented in the database's [security] → [user] node, the second method is used for Demonstration:

1. In SSMS, select the database node, expand the corresponding database, select Security, right-click User, and select new user]

2. on the general page, you can select the following user type:

 

Type description:

User Type	Description
SQL user with login (SQL user with login name)	Maps to an SQL logon user.
SQL user without login (SQL user without logon name)	Users who have not mapped to any server-level logon can use the execute as user command to simulate execution.
User mapped to a certificate/User mapped to an asypolicric key (ing to certificate/asymmetric key users)	Users created using a signature key can map to a remote object.
Windows user)	The user mapped to the login name for Windows authentication.

 

3. Enter the user name, which can be the same as the login name or use other permitted names, but cannot use keywords or reserved words. Select a login name (or enter the mapped certificate and key ). Select the default architecture. If no architecture is selected, SQL Server uses dbo as the default architecture. In addition to graphical operations, you can also achieve with the following T-SQL:

USE marketing; CREATE USER [Fred] FOR LOGIN [Fred];

 

4. to query created users, use the sys. database_pr \ incipals directory View:

SELECT dp.name as UserName, sp.name as LoginName, dp.default_language_name, dp.default_schema_name, dp.type_desc, dp.create_date FROM sys.database_principals dp JOIN sys.server_principals sp ON dp.sid = sp.sid WHERE dp.type IN ('S', 'U') AND dp.principal_id > 4;

 

1 ~ 4. principal_id is the preset user.

 

Principle:

 

You can create a user without a login name, which is mostly used in the test process or only for security context simulation (in chapter 3 ). To create a USER, you must have the alter any user permission ON the database level, which is equal to the fixed database role db_accessadmin. You can use grant alter on user :: [user name] to [specific login name to be managed. For example:

GRANT ALTER ON USER::[Fred] TO [Mary];

Indicates that the login name Mary has the right to manage the user Fred. However, no excessive permissions are granted to Mary.

 

Disabled users:

 

Unlike disabling a login name, if you do not have the alter user xxxx disable command, you can also see that this type of check option is not available in SSMS. In addition, some system users, such as guest and INFORMATION_SCHEMA, are disabled by default.

To disable a user, the CONNECT permission must be revoked:

USE marketing; REVOKE CONNECT TO [Fred];

 

More:

 

If a Windows account belongs to a Windows Group and is added to SQL Server as a logon user, you can not only create a database user to map to the entire group, you can also create a user to map to a separate Windows account. For example, if DOMAIN \ Fred is a member of the DOMAIN \ Developers group and the group has been defined as the SQL Server login name, But DOMAIN \ Fred login is not added, you can create a user separately and grant permissions:

CREATE USER [DOMAIN\Fred] FROM LOGIN [DOMAIN\Fred];

 

Who is dbo?

 

Dbo stands for the Database Owner. It is a special Database user mapped to the Database owner. When creating a Database, the login name used is granted as the Owner. You can use the following statement to query the Database:

SELECT SUSER_SNAME(owner_sid), name FROM sys.databases; -- or : SELECT SUSER_SNAME(sid) FROM sys.database_principals WHERE principal_id = USER_ID('dbo');

 

 

In this case, the login name is automatically mapped to dbo and all permissions of the entire database are granted. You can use the following statement to modify it:

ALTER AUTHORIZATION ON DATABASE::marketing TO sa;

 

Change the owner of the database marketing to sa. This method is useful when the database is moved to another server, and the owner's SID does not exist.

 

Dbo users cannot be renamed or deleted, nor can they be removed from the db_owner role. The database owner and sysadmin server role members are regarded as dbo users of the database. Logically, as the database owner, the database owner maps to dbo, And the sysadmin role members do not need to be mapped. In fact, even if the sysadmin member maps to a database, it is considered as dbo.

 

What is a guest user?

 

In each database, you will see a user named guest, which is a fixed database system user that cannot be removed, the purpose is to allow anonymous database access without a login name mapped to a user. However, it is disabled by default to prevent unnecessary access. From the security perspective, it is better to disable it. If you want to enable it, you can grant the CONNECT permission:

USE marketing; GRANT CONNECT TO guest;

 

You can use the following code to check whether the guest user is Enabled:

SELECT CAST(IIF(dp.state IN ('G', 'W'), 1, 0) AS bit) AS [HasDBAccess] FROM sys.database_principals u LEFT JOIN sys.database_permissions dp     ON dp.grantee_principal_id = u.principal_id and dp.type = 'CO' WHERE u.name = 'GUEST';

If the return value is 1, it is enabled. If the return value is 0, it is disabled. You can also use the following statement to view guest permissions:

SELECT permission_name, state_desc, OBJECT_NAME(major_id) as securable FROM sys.database_permissions WHERE grantee_principal_id = USER_ID('guest');

 

Use System functions to identify users and log on

You can use many system functions to obtain user and login information.

SYSTEM_USER and SUSER_SNAME (), which return the current logon information and contain the SID. Here, SUSER_SNAME () returns a SID from the login name or the current login. If there is no parameter, when the security context is changed (execute as), it returns a simulated login.

CURRENT_USER and SESSION_USER return the username of the current context of the current database, USER_ID () or DATABASE_PRINCIPAL_ID () return the principal_id of the database, and SUSER_SID () return the SID.

You can use the following statement to list all database names that can be accessed:

SELECT [Name] FROM sys.databases WHERE HAS_DBACCESS ([Name]) = 1   AND database_id > 4 ORDER BY [Name];

 

To query whether a user is a member of a Windows Group, use the IS_MEMBER () function:

SELECT IS_MEMBER('DOMAIN\developers');

The following describes the functions that can be used:

Function Name	Description
SYSTEM_USER	Returns the login name of the current server.
SUSER_SNAME ()	Returns the login name of the current server.
SUSER_ID ()	Returns the SID of the current server.
CURRENT_USER	Returns the current database user
SESSION_USER	Returns the current database user
USER_ID ()	Returns the current database principal_id.
DATABASE_PRINCIPAL_ID ()	Returns the current database principal_id.
ORIGINAL_LOGIN ()	Returns the original login name for money switching in the context.
HAS_DBACCESS ('database ')	1 indicates that the current login can access the database
IS_MEMBER ('group ')	1 indicates that the current logon is a Windows Member

Next article: http://blog.csdn.net/dba_huangzj/article/details/39003679

How to Write a table using SQL statements?

Drop table CELL_TCH;
Drop table ms;
Drop table msc;
Drop table bsc;
Drop table bts;
Drop table cell;
Drop table antenna;
Drop table data;
Drop table test;
Drop table neighbor;

Create table msc (
Mscid integer not null,
Mscname character (10 ),
Msccompany character (10 ),
Msclong1_decimal (9, 6 ),
Msclatitude decimal (8, 6 ),
Mscaltitude integer,
Primary key (MSCID ));

Create table bsc (
Bscid integer not null,
Bscname character (10 ),
Bsccompany character (10 ),
Longbench DECIMAL (9, 6 ),
Latitude decimal (8, 6 ),
Mscid integer,
Primary key (BSCID ),
Foreign key (MSCID) references msc (MSCID ));

Create table bts (
Btsname character (20) not null,
Bscid integer not null,
Longbench DECIMAL (9, 6 ),
Latitude decimal (8, 6 ),
Altitude integer,
Btscompany character (10 ),
Btspower decimal (2, 1 ),
Primary key (BTSNAME ),
Foreign key (BSCID) references bsc (BSCID ));

Create table cell (
Cellid integer not null,
Btsname character (20 ),
Areaname character (10 ),
Lac integer,
Longbench DECIMAL (9, 6 ),
Latitude decimal (8, 6 ),
Direction integer,
Radious integer,
Antnum integer,
Bcch integer,
Primary key (CELLID ),
Foreign key (BTSNAME) references bts (BTSNAME ));

CREATE TABLE MS (
Imei bigint not null,
Msisdn bigint,
... The remaining full text>

How can I set the server to support jsp?

Tomcat's www.apache.org/dist/jakarta/tomcat-4/
1. Install jdk. For details, refer to the java environment configuration in windows 2 K and redhat 8.0.

2. How to modify the port number

Use a text editor such as EditPlus to open the server. xml file and find
<! -- Define a non-SSL Coyote HTTP/1.1 Connector on port 8080 -->
<Connector className = "org. apache. coyote. tomcat4.CoyoteConnector"
Port = "8080" minProcessors = "5" maxProcessors = "75"
EnableLookups = "true" redirectPort = "8443"
AcceptCount = "100" debug = "0" connectionTimeout = "20000"
UseURIValidationHack = "false" disableUploadTimeout = "true"/>

Change 8080 to 80, restart tomcat, and access the local machine using localhost.
Note: For other versions, find the corresponding version 8080 and modify it.

3. Modify the release directory

For example, use the d: \ test directory as the directory for publishing your own documents, and specify mytest as the relative directory (localhost/mytest) for http access ).
Modify the server. xml file and find

<! -- Tomcat Root Context -->
<! --
<Context path = "" docBase = "ROOT" debug = "0"/>
-->
Remove the comment, or copy the blocked <Context path = "" docBase = "ROOT" debug = "0"/> to the blank area below, the copied text can be highlighted in color. Modify the text as follows:
<Context path = "/mytest" docBase = "d: \ test" debug = "0"/>
Then, repeat ...... the remaining full text>