Authorization: Grant and withdrawal
GRANT <jurisdiction>[,<jurisdiction>]... ON <object type> <Object name>[,<object type> <Object name>]... TO <user>[,<user>]... [WITH GRANT OPTION];
The with grant option clause is used to:
Users with grant option can continue to grant permissions
Users without this clause cannot continue to pass permissions.
[Example 4.1] Put query Student Table permissions granted to users U1
GRANT SELECT ON TABLE Student TO U1;
SQL server has some differences in t-sql syntax
GRANT SELECT ON Student TO U1;
Put right Student Table and Course All permissions on the table are granted to the user U2 and U3
Grant all PRIVILIGES on Student,course to u2,u3;
This is because on the one hand, T-SQL does not need (can not have) privileges, on the other hand, T-SQL needs to be given permissions separately, and multiple permissions cannot be given at one time.
Grant all PRIVILEGES on Student to u2,u3; Grant all on SC to u2,u3;
Put query Student The authority to form and modify student numbers is granted to users U4
GRANT UPDATE(Sno), SELECT ON Student TO U4
Set your watch SC of INSERT Permission granting U5 And allow him to grant this permission to other users
GRANT INSERT ON TABLE SC TO U5 WITH GRANT OPTION;
The reason for this is the with grant option clause
If you log in with u5 user at this time, you can grant other users the insert permission of SC table through this user, but if there is no tag sentence, you can't. when I do the experiment, I have all permissions because I use the root account.
REVOKE <jurisdiction>[,<jurisdiction>]... ON <object type> <Object name>[,<object type><Object name>]... FROM <user>[,<user>]...[CASCADE | RESTRICT];
Put users U4 Withdrawal of permission to modify student ID
REVOKE UPDATE(Sno) ON Student FROM U4;
Recall all user statements SC Query permissions for
REVOKE SELECT ON SC FROM PUBLIC;
Put users U5 yes SC Tabular INSERT Permission withdrawal
REVOKE INSERT ON SC FROM U5 CASCADE ;
Will user U5 of INSERT Should be used when permission is withdrawn CASCADE，Otherwise, the statement is rejected Strange times, this time T-SQl Support again casade Yes In recovery u5 All permissions from u5 The permissions of users who have obtained permissions here will also be withdrawn
A role is a collection of permissions. You can create a role for a group of users with the same permissions.
CREATE ROLE <Role name>
GRANT <jurisdiction>[,<jurisdiction>]... ON <object type>Object name TO <role>[,<role>]...
Grant a role to another role or user
GRANT <Role 1>[,<Role 2>]... TO <Role 3>[,<User 1>]... [WITH ADMIN OPTION]
This statement grants a role to a user or to another role
The grantor is the creator of the role or has the ADMIN OPTION on the role
If WITH ADMIN OPTION is specified, the role or user who obtains certain permission can also grant this permission to other roles
Here is to distinguish roles from users. I don't know why I think roles feel like views.
Withdrawal of role permissions
REVOKE <jurisdiction>[,<jurisdiction>]... ON <object type> <Object name> FROM <role>[,<role>]...
Of course, the premise of this operation is that you have the permission to operate this role. For example, you are his creator or get his permission through some methods
Create a role R1
CREATE ROLE R1;
Then use GRANT Statement to make the role R1 have Student Tabular SELECT,UPDATE,INSERT jurisdiction
GRANT SELECT, UPDATE, INSERT ON Student TO R1;
Grant this role to U1，U2. Give them a role R1 All permissions contained
GRANT R1 TO U1,U2;
There's a mistake here!
EXEC sp_addrolemember 'R1','U1' EXEC sp_addrolemember 'R1','U2'
Here is another small tips
Withdraw all permissions of U1 at one time:
REVOKE R1 FROM U1;
EXEC sp_droprolemember 'R1','Wang Ping'
Permission modification of role
GRANT DELETE ON Student TO R1;
At this time, R1 is also authorized to modify
To revoke a permission, use revoke
I have to say that during this experiment, t-sql's own characteristics emerge one after another.. It reminds me that when playing games in the past, different versions are also different. You need to have a clear mind to skillfully use different sql languages.