All the users we created in the previous scenarios cannot connect to the database let alone perform any other tasks as they do not have permissions to do so. In the previous steps, we created the user, specified the verification method, gave him the required share, and specified the profile to manage the password and resources. However, we are now waiting to give him permissions to work in the database. The permissions are granted through the database administrator or someone who has the permissions. There are two types of privileges:-

1- -System Privileges: This is to enable users to perform certain tasks on the database.

There are more than 700 System Privileges on the database, for example:

SYSDBA: This privilege allows the user to close and open the database.

TABLESPACE:CREATE allows the user to create a Tablespace.

In general, all System Privileges available to the database administrator can be viewed through the following query:

SELECT PRIVILEGE FROM DBA_SYS_PRIVS; 

Let's assume that we want to grant some System Privileges to the user TEST.

GRANT CREATE TABLESPACE TO TEST; 

This is how we give the user TEST the permission to create the tablespace.

The word ANY must be taken into account when granting permissions, and to clarify this meaning, we note the difference between permissions:

SELECT TABLE: Allows the user to perform a SELECT on tables owned by the granting user.

SELECT ANY TABLE: Allows the user to perform a SELECT query on tables owned by users.

There is an option when granting System Privileges to users, which is the option WITH ADMIN OPTION. This option means that the user, after having the authority, can grant it to other users.

GRANT ALTER SYSTEM TO TEST WITH ADMIN OPTION; 

The user TEST now has the ALTER SYSTEM privilege and can grant it to other users. There are some privileges that should not be granted to database administrators and confidentiality should be taken into account when granting, for example

(ALTER DATABASE & SESSION RESTRICTED & SYSOPER & SYSDBA) and other privileges that should only be for database administrators.

Of course, permissions can be revoked from users, let's assume we want to revoke the CREATE TABLESPACE permission from user TEST.

REVOKE CREATE TABLESPACE FROM TEST; 

Imagine this scenario that the database administrator granted the user TEST the CREATE TABLESPACE privilege via the WITH ADMIN OPTION option, the user TEST created a

Tablespace and then granted this privilege to another user named TEST1.

The database administrator wanted to revoke the CREATE TABLESPACE privilege from the user TEST.

First, the permission will be withdrawn from the user without affecting the tasks that he executed through this permission, which is here creating the Tablespace.

Second: User TEST1 is not affected by withdrawing permissions from user TEST.

This user can know what System Privileges permissions he has by querying:-

SELECT PRIVILEGE FROM USER_SYS_PRIVS; 

For inquiries:

DBA_ROLES

USER_ROLE_PRIVS

DBA_ROLE_PRIVS

ROLE_ROLE_PRIVS

ROLE_SYS_PRIVS

ROLE_TAB_PRIVS

SESSION_PRIVS

 2- Object Privileges: They enable users to access and interact with objects. Without these privileges, the user can only interact with the objects he owns. The user in the database has permissions of course on the objects that he owns, the user can grant permissions to another user to access the objects that he owns,

Likewise, the database administrator can grant permissions to users to access objects owned by other users.

Suppose user TEST owns a table named EXAMPLE, this user wants to grant SELECT privilege on this table to user TEST1.

GRANT SELECT ON EXAMPLE TO TEST1; 

What if the database administrator wanted to grant the SELECT privilege on the EXAMPLE table owned by user TEST to user TEST1.

GRANT SELECT ON TEST.EXAMPLE TO TEST1; 

The WITH GRANT OPTION option can also be used when granting Objects Privileges to indicate that this user, after having this privilege, can grant it to other users.

GRANT INSERT ON EXAMPLE TO TEST1 WITH GRANT OPTION; 

Now user TEST1 grants INSERT permission on table EXAMPLE owned by user TEST to other users.

What if the user TEST wanted to withdraw the SELECT privilege from the EXAMPLE table that he has from the user TEST1, noting that the user TEST1 granted this privilege to other users.

Let's assume that he is TEST2, the result is that the privilege will be withdrawn from the user TEST1 and also from all users granted the privilege through the user TEST1; here he is the user TEST2.

We will follow this scenario practically: -

SHOW USER; 

We are now working on user TEST.

User TEST owns one table EXAMPLE, now user TEST will grant SELECT privilege on table EXAMPLE which it owns to user TEST1.

GRANT SELECT ON EXAMPLE TO TEST١ WITH GRANT OPTION; 

By user TEST1 you can do SELECT on the above table.

User TEST1 can grant SELECT privilege on table EXAMPLE owned by user TEST to user TEST2.

GRANT SELECT ON TEST.EXAMPLE TO TEST2; 

User TEST2 can perform SELECT on table EXAMPLE.

SELECT * FROM TEST.EXAMPLE; 

We cancel the authorization of user TEST1.

REVOKE SELECT ON EXAMPLE FROM TEST1; 

Using user TEST1, we do a SELECT for the EXAMPLE table.

SELECT * FROM TEST.EXAMPLE; 

You will notice that user TEST1 now does not have permission.

What about user TEST2?

Also user TEST2 does not have permission to query table EXAMPLE.

Permission can be granted at column level, for example:

GRANT INSERT (CNO) ON EXAMPLE TO TEST1; 

Objects Privileges can be queried in several ways, including:

DBA_TAB_PRIVS

ALL_TAB_PRIVS

USER_TAB_PRIV

DBA_COL_PRIVS

ALL_COL_PRIVS

USER_COL_PRIVS

SESSION_PRIVS