How to grant the access on tables in Oracle
Contents
Grant Statement
Grant statement is used to provide the access to specific users and It allows the user to perform actions on database objects.
Types of privileges given by Grant
- Update data in a table.
- Delete records from a table.
- Select the records from a table,view, or a subset of columns in a table.
- Insert the data into the table.
- Create a trigger on a table.
- Run a specified function or procedure.
- Use a sequence generator or a user-defined type.
- Create a foreign key reference to a table.
Syntax of Grant Statement
|
1 2 3 |
GRANT <privilege_type> ON <Table_Name | View_Name> TO <oracle username or role name>; |
Here privilege type means SELECT,INSERT,UPDATE or DELETE access to the table. Then we need to specify the table name and the user name who wants the access for this table.
Example for Grant Statement
|
1 |
GRANT SELECT,UPDATE ON Banking_DB.Customers TO Bank_Admin; |
In this example, we are providing the SELECT and UPDATE access of the Table Customers to the user Bank_Admin.
Check User privileges in Oracle
|
1 2 3 4 5 |
SELECT * FROM USER_SYS_PRIVS WHERE USERNAME = 'Bank_Admin'; |
The above query will return the access details of the user Bank_Admin as below.
Recommended Articles
