Granting System Privileges and assign Privileges to a User in Oracle 19c

Granting system privileges and assigning them to a user in Oracle 19c involves several steps. Below is a detailed explanation of the process:
Steps to Grant System Privileges and Assign Privileges to a User:

1. Log in to the Database as a User with Administrative Privileges
   • Use a privileged account like SYS or SYSTEM to connect to the database.

   sqlplus sys@<dbname> as sysdba
   

   or
   

   sqlplus system@<dbname>
   

2. Create a New User (if necessary)
   • If the user doesn't already exist, you can create a new user:
     

     CREATE USER username IDENTIFIED BY password;
     

   • Example:
     

     CREATE USER my_user IDENTIFIED BY my_password;
     

3. Grant the User Necessary Roles and Privileges
   • You need to grant roles or privileges for the user to log in and perform actions. Common examples include:
   • Grant CREATE SESSION Privilege (to allow the user to log in):
     

     GRANT CREATE SESSION TO username;
     

   • Grant Additional System Privileges (e.g., CREATE TABLE, CREATE VIEW, etc.):
     

     GRANT privilege_name TO username;
     

   • Example:
     

     GRANT CREATE TABLE, CREATE VIEW, CREATE PROCEDURE TO my_user;
     

4. Assign Specific Object Privileges (if necessary)
   • If the user requires access to specific database objects, grant object privileges such as SELECT, INSERT, UPDATE, etc.:

   GRANT privilege ON object_name TO username;
         

   • Example:

   GRANT SELECT ON employees TO my_user;
         

5. Check Privileges Granted to the User
   • You can verify the system privileges granted to a user by querying the USER_SYS_PRIVS or DBA_SYS_PRIVS views:

   SELECT * FROM USER_SYS_PRIVS WHERE USERNAME = 'username';
         

   • Example:

   SELECT * FROM DBA_SYS_PRIVS WHERE GRANTEE = 'MY_USER';
         

6. Revoke Privileges (if necessary)
   • If you need to remove specific privileges, use the REVOKE statement:

   REVOKE privilege_name FROM username;
         

   • Example:

   REVOKE CREATE TABLE FROM my_user;
         

Common System Privileges
Here are some commonly used system privileges in Oracle:

• CREATE SESSION: Allows the user to log in to the database.
• CREATE TABLE: Allows the user to create tables.
• CREATE VIEW: Allows the user to create views.
• CREATE PROCEDURE: Allows the user to create stored procedures.
• CREATE USER: Allows the user to create new database users.
• ALTER USER: Allows the user to modify existing database users.
• DROP USER: Allows the user to drop database users.
• GRANT ANY PRIVILEGE: Allows the user to grant any privilege to other users.
• SELECT ANY TABLE: Allows the user to query any table in the database.

Examples

1. Create a User and Grant Basic Privileges:

   CREATE USER john IDENTIFIED BY welcome123;
   GRANT CREATE SESSION, CREATE TABLE TO john;
       

2. Grant All Privileges to a User:

   GRANT ALL PRIVILEGES TO john;
       

3. Revoke a Specific Privilege:

   REVOKE CREATE TABLE FROM john;
       

These steps and commands help manage user privileges effectively in Oracle 19c. Let me know if you need assistance with specific scenarios!

The SQL GRANT command controls what privileges a user will have. You can issue the GRANT command from SQL*Plus or SQL Developer. View the code below to see what the syntax looks like.

GRANT privilege[, privilege...] TO username 
[WITH ADMIN OPTION];

The username is the user to which you are granting the privileges. The WITH ADMIN OPTION allows that user to further grant the privileges to other users.
WITH ADMIN OPTION is described later in this module.
The following diagram shows a typical example of a grant:

1. GRANT: The GRANT statement is used to grant privileges.
2. CREATE SESSION, CREATE TABLE, CREATE INDEX, ALTER SESSION: These are privileges that you are granting. They follow the GRANT keyword as a comma-delimited list.
3. TO coin_admin: The TO clause indicates the user to whom you are granting the privileges. In this case, they are being granted to the user named coin_admin.

---

The following series of images lets you practice this using the SQL*Plus worksheet.

1. Go ahead and type in the command to give the coin_admin user the CREATE SESSION and CREATE TABLE privileges. The CONNECT command will be replaced by what you type.
2. Now, execute the command by clicking the lightning-bolt on the left side of the screen.

Connect system/ ****@coin

Your GRANT succeeded, and a message to that effect is displayed in the bottom half of the window. This is the end of the simulation. Click the Exit button.
Grant System Privileges

1. After starting Security Manager and connecting to the database, you will come to this screen. Go ahead and type in the command to give the coin_admin user the CREATE SESSION and CREATE TABLE privileges. The highlighted CONNECT command will be replaced by what you type.
2. Now, execute the command by clicking the lightning-bolt on the left side of the screen.
3. Your GRANT succeeded, and a message to that effect is displayed in the bottom half of the window.

You might find yourself in a situation where a privilege would be more aptly granted to all users as opposed to individual users. This is rare, and you should be very cautious when considering granting a particular privilege to all users. To assign this privilege, simply use the command PUBLIC. For example, the following command gives everyone the CREATE SESSION privilege:

GRANT CREATE TABLE TO PUBLIC;

Before you move on to the next lesson, click the Exercise link to practice writing GRANT statements.
Granting System Privileges - Exercise