Oracle grant details)

Source: Internet
Author: User
Tags psql

After I log on with Scott/tiger and create a table, I will insert data, prompting that I have successfully inserted the table, but there is no record when querying the data, the reason is that my Scott user does not have the permission to insert this table. In this case, after logging on to the system, grant corresponding permissions to a table in scottr with Grant.

Grant
Name
Grant-grant access permissions to a user, a group, or all users

Grantprivilege [,...] on object [,...] to {public | group | username}
Input
Privilege
Possible permissions include:
Select
Access all columns/fields in the declared table/view.
Insert
Insert all column fields into the declared table.
Update
Update all declared columns/fields.
Delete
Delete all rows from the declared table.
Rule
Define rules on tables/views (see create Rule Statements ).
All
Grant all permissions.
Object
Name of the object to be granted permissions. Possible objects are:
Table)
View)
Sequence)
Index)
Public
Represents the abbreviation of all users.
Group
The group to be granted permissions. In the current version, the Group must be explicitly created using the following method.
Username
The username to be granted. Public is short for all users.
Output
Change
If yes, this message is returned.
Error: changeacl: Class "object" not found
If the declared object is unavailable or it is impossible to grant permissions to the declared group or user.
Description
Grant allows the Object Creator to grant certain permissions to a user or a group or all users (public. after an object is created, no one except the Creator grants the object permission.

Once a user has the permission of an object, he can use the permission. you do not need to grant (grant) the object permissions to the creator. The Creator automatically has all the permissions of the object, including the permission to delete it.

Note:
Currently, to grant only several columns of permissions to ipvs, you must create a view containing those columns and grant the permissions to those views.

Use the Psql/Z command to obtain more information about the existing object permissions:

Database = Lusitania + ---------------- + region + | relation | grant/revoke permissions | + ------------------ + region + | mytable | {"= RW", "Miriam = ARWR ", "Group todos = RW"} | + -------------------- + ------------------------------------------- + legend: uname = ARWR -- privileges granted to a user group gname = ARWR -- privileges granted to a group = ARWR -- privileges granted to public r -- select w -- Update/delete a -- insert r -- rule ARWR -- all
TIPS: currently, to create a group, you have to manually insert data to the pg_group table, such:

Insert into pg_group values ('todos '); create user Miriam in group Todos;
Refer to the revoke statement to reassign access permissions.

Usage
Insert record permissions to table films for all users:

Grantinsert on films to public;
Grant all permissions to the Manuel operation view kinds:

Grantall on kinds to Manuel;
Compatibility
Sql92
The sql92grant syntax allows you to set permissions for a single column/Field in a table, and allows you to set a permission to grant others the same permissions.

Grantprivilege [,...] on object [(column [,...])] [,...] to {public | username [,...]} [withgrantoption]
These fields are compatible with ipvs implementation, except for the following exceptions:

Privilege
Sql92 allows declaring additional permissions:
Select
 
References
Some or all columns/fields can be used in the consolidated constraints of a declared table.
Usage
A domain, Character Set, set, or transaction is allowed. If the declared object is not a table/view, privilege can only be declared as usage.
Object
[Table] Table
Sql92 allows an additional non-function keyword table.
Character Set
The declared character set is allowed.
Collation
The declared set sequence is allowed.
Translation
The declared Character Set conversion is allowed.
Domain
The declared domain is allowed.
Withgrantoption
Grant the same permissions to others

 

 

 

 

Revokename

Revoke -- delete the access permission.

Synopsis
REVOKE { { SELECT | INSERT | UPDATE | DELETE | RULE | REFERENCES | TRIGGER }    [,...] | ALL [ PRIVILEGES ] }    ON [ TABLE ]object[, ...]    FROM {username| GROUPgroupname| PUBLIC } [, ...]REVOKE { EXECUTE | ALL [ PRIVILEGES ] }    ON FUNCTIONfuncname([type, ...]) [, ...]    FROM {username| GROUPgroupname| PUBLIC } [, ...]REVOKE { USAGE | ALL [ PRIVILEGES ] }    ON LANGUAGElangname[, ...]    FROM {username| GROUPgroupname| PUBLIC } [, ...]
Description

RevokeAllows the creator of an object to revoke the permissions previously granted to (Others). It can be used to revoke the permissions of one or more users or a group of users. KeywordsPublicRepresents a group implicitly defined with all users.

Please note that any specific user will have the permissions directly granted to him/her, plus any group of him/her, and the permissions grantedPublicSo, for example, revokingPublicThe Select permission does not mean that all users lose the select permission on the object: those who directly obtain the permission and who obtain the permission through a group still have this permission.

Refer to the description of the grant command to get the meaning of the permission type.

Note:

Psql/ZThe command displays the permissions granted to an existing object. For details, see Grant to obtain information about the format.

Example

Revoke the public from the tableFilmsInsert permission on:

REVOKE INSERT ON films FROM PUBLIC;

 

Abolish userManuelViewKindsAll permissions:

REVOKE ALL PRIVILEGES ON kinds FROM manuel

After I log on with Scott/tiger and create a table, I will insert data, prompting that I have successfully inserted the table, but there is no record when querying the data, the reason is that my Scott user does not have the permission to insert this table. In this case, after logging on to the system, grant corresponding permissions to a table in scottr with Grant.

Grant
Name
Grant-grant access permissions to a user, a group, or all users

Grantprivilege [,...] on object [,...] to {public | group | username}
Input
Privilege
Possible permissions include:
Select
Access all columns/fields in the declared table/view.
Insert
Insert all column fields into the declared table.
Update
Update all declared columns/fields.
Delete
Delete all rows from the declared table.
Rule
Define rules on tables/views (see create Rule Statements ).
All
Grant all permissions.
Object
Name of the object to be granted permissions. Possible objects are:
Table)
View)
Sequence)
Index)
Public
Represents the abbreviation of all users.
Group
The group to be granted permissions. In the current version, the Group must be explicitly created using the following method.
Username
The username to be granted. Public is short for all users.
Output
Change
If yes, this message is returned.
Error: changeacl: Class "object" not found
If the declared object is unavailable or it is impossible to grant permissions to the declared group or user.
Description
Grant allows the Object Creator to grant certain permissions to a user or a group or all users (public. after an object is created, no one except the Creator grants the object permission.

Once a user has the permission of an object, he can use the permission. you do not need to grant (grant) the object permissions to the creator. The Creator automatically has all the permissions of the object, including the permission to delete it.

Note:
Currently, to grant only several columns of permissions to ipvs, you must create a view containing those columns and grant the permissions to those views.

Use the Psql/Z command to obtain more information about the existing object permissions:

Database = Lusitania + ---------------- + region + | relation | grant/revoke permissions | + ------------------ + region + | mytable | {"= RW", "Miriam = ARWR ", "Group todos = RW"} | + -------------------- + ------------------------------------------- + legend: uname = ARWR -- privileges granted to a user group gname = ARWR -- privileges granted to a group = ARWR -- privileges granted to public r -- select w -- Update/delete a -- insert r -- rule ARWR -- all
TIPS: currently, to create a group, you have to manually insert data to the pg_group table, such:

Insert into pg_group values ('todos '); create user Miriam in group Todos;
Refer to the revoke statement to reassign access permissions.

Usage
Insert record permissions to table films for all users:

Grantinsert on films to public;
Grant all permissions to the Manuel operation view kinds:

Grantall on kinds to Manuel;
Compatibility
Sql92
The sql92grant syntax allows you to set permissions for a single column/Field in a table, and allows you to set a permission to grant others the same permissions.

Grantprivilege [,...] on object [(column [,...])] [,...] to {public | username [,...]} [withgrantoption]
These fields are compatible with ipvs implementation, except for the following exceptions:

Privilege
Sql92 allows declaring additional permissions:
Select
 
References
Some or all columns/fields can be used in the consolidated constraints of a declared table.
Usage
A domain, Character Set, set, or transaction is allowed. If the declared object is not a table/view, privilege can only be declared as usage.
Object
[Table] Table
Sql92 allows an additional non-function keyword table.
Character Set
The declared character set is allowed.
Collation
The declared set sequence is allowed.
Translation
The declared Character Set conversion is allowed.
Domain
The declared domain is allowed.
Withgrantoption
Grant the same permissions to others

 

 

 

 

Revokename

Revoke -- delete the access permission.

Synopsis
REVOKE { { SELECT | INSERT | UPDATE | DELETE | RULE | REFERENCES | TRIGGER }    [,...] | ALL [ PRIVILEGES ] }    ON [ TABLE ]object[, ...]    FROM {username| GROUPgroupname| PUBLIC } [, ...]REVOKE { EXECUTE | ALL [ PRIVILEGES ] }    ON FUNCTIONfuncname([type, ...]) [, ...]    FROM {username| GROUPgroupname| PUBLIC } [, ...]REVOKE { USAGE | ALL [ PRIVILEGES ] }    ON LANGUAGElangname[, ...]    FROM {username| GROUPgroupname| PUBLIC } [, ...]
Description

RevokeAllows the creator of an object to revoke the permissions previously granted to (Others). It can be used to revoke the permissions of one or more users or a group of users. KeywordsPublicRepresents a group implicitly defined with all users.

Please note that any specific user will have the permissions directly granted to him/her, plus any group of him/her, and the permissions grantedPublicSo, for example, revokingPublicThe Select permission does not mean that all users lose the select permission on the object: those who directly obtain the permission and who obtain the permission through a group still have this permission.

Refer to the description of the grant command to get the meaning of the permission type.

Note:

Psql/ZThe command displays the permissions granted to an existing object. For details, see Grant to obtain information about the format.

Example

Revoke the public from the tableFilmsInsert permission on:

REVOKE INSERT ON films FROM PUBLIC;

 

Abolish userManuelViewKindsAll permissions:

REVOKE ALL PRIVILEGES ON kinds FROM manuel

After I log on with Scott/tiger and create a table, I will insert data, prompting that I have successfully inserted the table, but there is no record when querying the data, the reason is that my Scott user does not have the permission to insert this table. In this case, after logging on to the system, grant corresponding permissions to a table in scottr with Grant.

Grant
Name
Grant-grant access permissions to a user, a group, or all users

Grantprivilege [,...] on object [,...] to {public | group | username}
Input
Privilege
Possible permissions include:
Select
Access all columns/fields in the declared table/view.
Insert
Insert all column fields into the declared table.
Update
Update all declared columns/fields.
Delete
Delete all rows from the declared table.
Rule
Define rules on tables/views (see create Rule Statements ).
All
Grant all permissions.
Object
Name of the object to be granted permissions. Possible objects are:
Table)
View)
Sequence)
Index)
Public
Represents the abbreviation of all users.
Group
The group to be granted permissions. In the current version, the Group must be explicitly created using the following method.
Username
The username to be granted. Public is short for all users.
Output
Change
If yes, this message is returned.
Error: changeacl: Class "object" not found
If the declared object is unavailable or it is impossible to grant permissions to the declared group or user.
Description
Grant allows the Object Creator to grant certain permissions to a user or a group or all users (public. after an object is created, no one except the Creator grants the object permission.

Once a user has the permission of an object, he can use the permission. you do not need to grant (grant) the object permissions to the creator. The Creator automatically has all the permissions of the object, including the permission to delete it.

Note:
Currently, to grant only several columns of permissions to ipvs, you must create a view containing those columns and grant the permissions to those views.

Use the Psql/Z command to obtain more information about the existing object permissions:

Database = Lusitania + ---------------- + region + | relation | grant/revoke permissions | + ------------------ + region + | mytable | {"= RW", "Miriam = ARWR ", "Group todos = RW"} | + -------------------- + ------------------------------------------- + legend: uname = ARWR -- privileges granted to a user group gname = ARWR -- privileges granted to a group = ARWR -- privileges granted to public r -- select w -- Update/delete a -- insert r -- rule ARWR -- all
TIPS: currently, to create a group, you have to manually insert data to the pg_group table, such:

Insert into pg_group values ('todos '); create user Miriam in group Todos;
Refer to the revoke statement to reassign access permissions.

Usage
Insert record permissions to table films for all users:

Grantinsert on films to public;
Grant all permissions to the Manuel operation view kinds:

Grantall on kinds to Manuel;
Compatibility
Sql92
The sql92grant syntax allows you to set permissions for a single column/Field in a table, and allows you to set a permission to grant others the same permissions.

Grantprivilege [,...] on object [(column [,...])] [,...] to {public | username [,...]} [withgrantoption]
These fields are compatible with ipvs implementation, except for the following exceptions:

Privilege
Sql92 allows declaring additional permissions:
Select
 
References
Some or all columns/fields can be used in the consolidated constraints of a declared table.
Usage
A domain, Character Set, set, or transaction is allowed. If the declared object is not a table/view, privilege can only be declared as usage.
Object
[Table] Table
Sql92 allows an additional non-function keyword table.
Character Set
The declared character set is allowed.
Collation
The declared set sequence is allowed.
Translation
The declared Character Set conversion is allowed.
Domain
The declared domain is allowed.
Withgrantoption
Grant the same permissions to others

 

 

 

 

Revokename

Revoke -- delete the access permission.

Synopsis
REVOKE { { SELECT | INSERT | UPDATE | DELETE | RULE | REFERENCES | TRIGGER }    [,...] | ALL [ PRIVILEGES ] }    ON [ TABLE ]object[, ...]    FROM {username| GROUPgroupname| PUBLIC } [, ...]REVOKE { EXECUTE | ALL [ PRIVILEGES ] }    ON FUNCTIONfuncname([type, ...]) [, ...]    FROM {username| GROUPgroupname| PUBLIC } [, ...]REVOKE { USAGE | ALL [ PRIVILEGES ] }    ON LANGUAGElangname[, ...]    FROM {username| GROUPgroupname| PUBLIC } [, ...]
Description

RevokeAllows the creator of an object to revoke the permissions previously granted to (Others). It can be used to revoke the permissions of one or more users or a group of users. KeywordsPublicRepresents a group implicitly defined with all users.

Please note that any specific user will have the permissions directly granted to him/her, plus any group of him/her, and the permissions grantedPublicSo, for example, revokingPublicThe Select permission does not mean that all users lose the select permission on the object: those who directly obtain the permission and who obtain the permission through a group still have this permission.

Refer to the description of the grant command to get the meaning of the permission type.

Note:

Psql/ZThe command displays the permissions granted to an existing object. For details, see Grant to obtain information about the format.

Example

Revoke the public from the tableFilmsInsert permission on:

REVOKE INSERT ON films FROM PUBLIC;

 

Abolish userManuelViewKindsAll permissions:

REVOKE ALL PRIVILEGES ON kinds FROM manuel
Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.