This post will guide you on how to grant view privileges to users in Oracle.
How to Grant View Privileges to Users in Oracle
To grant view privileges to a user in Oracle follow the following steps:
- Login to Database
- Create a New User
- Grant Privileges Using Create View
- Grant Privileges Using Select
- Revoke Privileges
Step 1: Login to Database
To create a user, simply login to the database as “System Database Administrator” by typing the following command:
In the above command, the “SYS” user is logged in as “SYSDBA” (System Database Administrator) by using the “ROOT1234” password.
Output
The output displayed the successful login to the database.
Step 2: Create a New User
After the successful login, a new user can be created by using the “CREATE USER” command as shown in the given syntax:
In this syntax, the “CREATE” command is used to create a new database object, “USER” is used to specify the username, while the “IDENTIFIED BY” clause is used to specify the user’s password.
Let’s create the user “C##LINUXHINT” by using the password “VIEW1234”:
Output
The output proved that the user has been created.
Step 3: Grant Privileges Using CREATE VIEW
The CREATE VIEW privileges can be granted to a user using the “GRANT” keyword. The syntax is given below:
In this syntax, the “GRANT” keyword is used with the “CREATE VIEW” command to grant the view privileges to a user.
Let’s grant the view privileges to the “C##LINUXHINT” user by typing the following command:
Output
The output showed that the create view privileges had been granted to the “C##LINUXHINT” user.
Grant Privileges to Multiple Users
Oracle also provides a facility to grant privileges to multiple users through a single command. For this purpose, you need to separate the user names with a comma “,”.
For example, the following statement grants the view privilege to the users “C##LINUXHINT” and “C##MD”:
Output
The given screenshot showed that the privileges have been granted to multiple users.
Step 4: Grant Privileges Using SELECT
The “GRANT SELECT” grants the SELECT privilege to a user or role. The SELECT privileges allow the users to retrieve data from the specified table. The example is given below:
In the above example, SELECT privileges are granted using the GRANT keyword on the TABS table to the C##LINUXHINT user.
Output
The output displayed that the SELECT privileges had been granted to the “C##LINUXHINT” user.
Grant Privileges to Multiple Users
The “SELECT” Privileges can be granted to multiple users at the same time by using the following code:
Here the “TABS” represents a table and the “C##LINUXHINT” and “C##MD” represent the users.
Output
The output showed that the privileges have been granted to multiple users.
Step 5: Revoke Privileges
The privileges can be revoked using the “REVOKE” keyword, as shown in the provided syntax:
Let’s execute the following query to revoke the view privileges from the user “C##LINUXHINT”:
Output
The output showed that the privileges have been revoked from the specified user.
Conclusion
To grant the view privileges to users in Oracle, login to the Oracle database as System Database Administrator and create a user. After that, grant the view privileges to the user using the “GRANT” keyword with the “CREATE VIEW” command. The SELECT privileges can also be granted using the GRANT keyword. With the help of appropriate examples, this article explained how to grant view privileges to users in Oracle.
