Chapter 3 Protecting the Data (1): Understanding permissions, chapterprotecting

Chapter 3 Protecting the Data (1): Understanding permissions, chapterprotecting
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/39496517

 

Preface:

 

Relational database management systems (RDBMS), such as SQL Server, Oracle, and Mysql, are not only responsible for data access, but also for ensuring data consistency and security. Similar to other server systems, data access is controlled by authorizing a user and maintaining the user's session. SQL Server checks these permissions every time it attempts to read and write data. First, I want to understand some concepts:

Permissions: permission, which is authorized at the security subject level. These security subjects include the login name, database user, and role. All these objects can be granted the grant/deny/revok permission. GDR (GRANT, DENY, REVOKE) for short ). The subject that receives the permission is called Grantee. The account that sets the permission becomes the Grantor ).

 

Permissions can be granted using the GRANT option, which belongs to the Data Control Language (DCL) command and is independent of DML/DDL. The basic syntax is as follows:

 

<GRANT | REVOKE | DENY> ON <class of securable>::<securable> TO <principal>;

 

The three statuses of permissions are:

• GRANT: the permission is allowed.
• DENY: the permission is explicitly denied (the priority is higher than GRANT)
• REVOKE: removes the influence of GRANT/DENY, which is equal to removing the permission.

 

Implementation:

 

You can use the following statement to view the permissions that can be granted:

-Full list of all returned built-in permissions
SELECT * FROM sys.fn_builtin_permissions (DEFAULT);
--Return permissions for specific categories, such as Schema:
SELECT * FROM sys.fn_builtin_permissions ('SCHEMA');

The local result is as follows:

The first column is the security subject category, that is, permissions are applied to this category. Permission_name and type are used to describe permissions. type is usually short for permissions. Covering_permission_name column. If it is not NULL, the permission name of this class is used (Other permissions of this class are implicitly ). For example, in the first row, the create table permission requires the alter database permission. Parent_class_desc (

If it is not NULL, It is the name of the parent class containing the current class .) And Parent_covering_permission_name (if not NULL, It is the permission name of the parent class (all other permissions of this class are implicitly ).), For more information, see books online.

 

Here are some examples of GRANT permissions:

 

-Authorize the database user bill to have SELECT permission on the Accounting.Account table
GRANT SELECT ON object :: Accounting.Account TO Bill;
-Authorized to bill this database user, can execute all stored procedures and scalar functions in the Accounting architecture
GRANT EXECUTE ON schema :: Accounting TO bill;
-Authorize the user to the custom role AvailabilityManager, so that it has the right to modify the availability group on the server
GRANT ALTER ANY AVAILABILITY GROUP TO AvailabilityManager;

To REVOKE permissions, run the REVOKE command:

REVOKE SELECT ON object::Accounting.Account TO Fred;

If you need to explicitly disable the feature, you can use the DENY command:

DENY SELECT ON object::Accounting.Account TO Fred;

After deny, if the user needs to run the deny operation, a 229 error will be reported, which indicates that the permission on the object is disabled, however, due to information exposed (for example, xx objects are forbidden, which means xx objects exist), attackers are given information of interest. Error 208 indicates an attempt to access a nonexistent object.

 

Principle:

 

The following are the most common permissions:

Permission name	Description
ALTER	Modify Object definition Permissions
CONNECT	Access database or connection endpoint Permissions
DELETE	Delete object permissions
EXECUTE	Permission to execute stored procedures or functions
IMPERSONATE	Equivalent to the execute as command
INSERT	Insert data to a table or view
REFERENCES	Define the foreign key or use the permission to apply the object in with schemabinding in the view
SELECT	Able to execute the SELECT command on objects or columns
TAKE OWNERSHIP	Permission to become the owner of an object
UPDATE	Data Update permission
VIEW DEFINITION	View object-defined Permissions

 

Example:

DENY SELECT ON OBJECT :: dbo.Contact TO Fred;-Forbid Fred to query the data in the dbo.contact table
DENY UPDATE ON OBJECT :: dbo.Contact TO Fred;-Fred updates the data in the dbo.contact table
GRANT SELECT ON OBJECT :: dbo.Contact TO Fred;-Authorize Fred to query the data in the dbo.contact table

 

Note: Although grant all exists, it will be discarded in the future. The REVOKE command can remove the GRANT effect. Do not use the DENY command to remove the GRANT effect unless you are sure that this user does not need to access this object. The DENY command overwrites all GRANT commands.

The login name can be granted or DENY at the schema or object level, for example

DENY SELECT ON Accounting.Account TO dbo;

The CONTROL permission includes other permissions on the security entity. If DENY has the SELECT permission and then GRANT the CONTROL permission, the deny permission will be revoked. The SQL Server permission check algorithm and complexity make it best to keep the Permission Policy simple. SQL Server Object permission hierarchy and transmission:

 

More:

 

If you need to test the permissions of the current user, use the HAS_PERMS_BY_NAME function maliciously. If 1 is returned, the permission is granted:

-Do you have select permission on the dbo architecture?
SELECT HAS_PERMS_BY_NAME ('dbo', 'SCHEMA', 'SELECT');
-Check all permissions at the server level
SELECT HAS_PERMS_BY_NAME (null, null, 'VIEW SERVER STATE');

Use the following statement to obtain the list of granted permissions:

-What permissions does the current user have on the dbo architecture?
SELECT * FROM sys.fn_my_permissions ('dbo', 'SCHEMA');
--Server all permissions
SELECT * FROM sys.fn_my_permissions (null, null);

Server permissions are recorded in the sys. server_permissions System View. Database permissions are stored in the sys. database_permissions System View of each database. You can view the permission set on the dbo. prospect table:

SELECT  grantee.name AS grantee , 
        grantor.name , 
        dp.permission_name AS permission , 
        dp.state_desc AS state 
FROM    sys.database_permissions dp 
        JOIN sys.database_principals grantee ON dp.grantee_principal_id = grantee.principal_id 
        JOIN sys.database_principals grantor ON dp.grantor_principal_id = grantor.principal_id 
WHERE   dp.major_id = OBJECT_ID('dbo.prospect');

 

Working principle of with grant option:

 

This means that the authorized subject can grant the same or less than the current permission to other subjects. For example

GRANT SELECT ON OBJECT::dbo.contact TO fred WITH GRANT OPTION;

This grants Fred two permissions: SELECT, grant select, and revoke other permissions except SELECT:

REVOKE SELECT ON OBJECT::dbo.contact TO fred CASCADE; GRANT SELECT ON OBJECT::dbo.contact TO fred;

The two statements are to revoke all permissions first, and then grant only SELECT permissions. The meaning of CASCADE is to revoke the SELECT permissions that Fred has granted to other subjects.

 

 

REFERENCE permission:

 

This permission not only applies to the scope table, but also to the database, architecture, and other entities. REFERENCE allows you to create foreign key constraints in a table. In a view, it can be used to REFERENCE tables affected by the with schemabinding operation.

Next article:

Shenzhen English Oxford edition chapter 13 single-choice special exercises

Mid-term Review (3) Select Chapters 1--4
1. --- Do you know when Mr. Green will come? (C 1-2)
--- Sorry, I have no idea. But if he _____, I will call you.
A. come B. comes C. will come D. has come
2. --- Why was the old man so angry _____ his wife?
--- Because his wife never visited him _____ hospital.
A. at; at B. with; in C. at; in D. with;
3. --- My television ______ wrong. What shall I do with it?
--- I suggest _____ it retries red.
A. goes; to get B. becomes; getting C. goes; getting D. gets; to get
4. If you invite me, I ______ to your party.
A. will go B. wowould go C. go D. goes
5. --- Why didn't you hand in your exercise book, Tom?
--- I remember _____ it to you just now.
A. give B. gave C. giving D. to give
6. --- He is an amazing runner.
--- Yes, I was _____ to see how fast he ran.
A. amaze B. amazing C. amazed D. to amaze
7. --- Sorry, I am late, Anne, _____ I couldn't find your tape.
--- ______, Millie. I have got another one.
A. but; That's all right B. and; It's nothing C. so; That's all D. but; All right
8. If you boil water, it ____ steam.
A. will become B. becomes C. become D. became
9. You won't succeed _____ you work hard.
A. unless B. if C. when D. because
10. --- I'm tired of the life here. I want to try _____.
--- Just do it. But you shoshould think twice before you act.
A. something strange B. strange something
C. something new D. new something
11. --- When I returned, I found the door half _____ ...... the remaining full text>

Oxford English 1, 2, 3, 4, 9Areading Translation

Oxford English 9AReading translation reward points: 35-solution time: yo ah Text Translation !!!! What are 6 English letters ????? # ^ () ^ # Chapter 1 Protecting the innocent · Text Translation Reading Chapter 1 Protecting innocent Pan xi wants to write an article about detectives for the school newspaper. In order to obtain information, she interviewed Ken. Pan xi: You said you like to be a detective. Why? Detective Ken: Let me tell you about one of my recent cases, Panxi. Mr. Li is a rich man. He lives alone and enjoys his favorite interests. He spent 2 million on a vase and showed it to his friends, gill and Jenny. Then he locked it in the safe deposit box in the house, but that night, some people stole the vase. Li reported the theft and I went to him to find a clue. Panxi: What have you found? Detective Ken: Yes. I saw a pair of black pearl earrings near the safe. Outside the window opened in the room, I found a lot of mud on the wet ground, and the carpet in the room was spotless. Panxi: What did you do next? Detective Ken: I interrogated Gill and Jenny. They both denied stealing the vase, but I noticed that gill was wearing a black pearl necklace, but there were no earrings. Panxi: Is Gill a thief? Detective Ken: No. I need evidence. A good detective never draws conclusions easily. I showed Gill the earrings. She admitted it was her. She said someone stole it from the room a month ago. I have verified this. It is true. Panxi: nagir is no longer a suspect. But what about the mud in the wetland outside the window? What about the spotless carpet in the room? How is it? Detective Ken: That tells me there may be no thieves in Lee's room. Therefore, I asked Li. Finally, he admitted he had stolen his vase. He purchased insurance for the vase. If the vase was stolen, the insurance company would pay him 2 million yuan in compensation. Panxi: Who Stole Gill's earrings? Detective Ken: Li, too. He tried to put Gill in jail instead of him. Panxi: So Li is in jail now. Detective Ken: Yes. And Gill is free. My job is to protect innocent people and find criminals. That's why I like to be a detective. One day, Tony received a student's note: "My grandfather often tells stories to us. I have attached a story, so you may post it in your newspaper ." The story is as follows: one night, Grandpa said, "I once had a friend named Dai. His name is Henry, but everyone calls him Mr. Happy. Mr. Happy makes a living. He has been to many places. When he came to a place he liked, he started a pet shop and began selling African fish. According to him, this is the only hidden fish in the world. They are very good pets because they do not need food. They only need water. Female fish produce a single year of fish eggs. After spawning, if the surroundings are absolutely quiet, she will immediately show up. But in the twinkling of an eye, she disappeared again. If you see her, you are extremely lucky, because she is the most beautiful creature in the world. When Mr. Happy told his customers the story, his whole tank of fish sold very fast. When all these fish are sold out, he usually leaves this place to another town ." "Is it because his customers are not satisfied ?" Let's ask. "Not at all ." Grandpa said. "His customers are completely satisfied. He has hardly received any complaints. In fact, many people tell him that they get happy from watching invisible fish. They feel very relaxed. Some may not want Peter to invite friends to enjoy fish. Some people even claim that they have seen female fish, which is indeed unforgettable. Yes, Mr. Happy is a smart person ." "You can tell us