Managing Roles   «Prev  Next»

Lesson 4 Granting system privileges
ObjectiveGrant system privileges to a user.

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:
The GRANT statement is used to grant privileges.
GRANT statement
  1. The GRANT statement is used to grant privileges.
  2. These are privileges that you are granting. They follow the GRANT keyword as a comma-delimited list.
  3. 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.


SQL Grant Statement

SQL Grant statement
GRANT CREATE SESSION, CREATE TABLE, 
CREATE INDEX, ALTER SESSION TO coin_admin;
This SQL command grants the `CREATE SESSION`, `CREATE TABLE`, `CREATE INDEX`, and `ALTER SESSION` privileges to the user `coin_admin` in an Oracle database.

  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.
Connected
After starting Security Manager and connecting to the database, you will come to this screen.
SQL*Plus Worksheet, a graphical user interface tool provided with older versions of Oracle Database for executing SQL queries and PL/SQL scripts.
The interface displays two sections:
  1. Upper Section:
    • This is the query input area where users can type SQL commands or PL/SQL blocks for execution.
  2. Lower Section:
    • This is the output area where the results or messages from the executed SQL statements are displayed.

The visible text in the lower section reads:
SQL*Plus: Release 8.1.5.0.0 - Production on Fri Sep 17 15:04:37 1999

(c) Copyright 1999 Oracle Corporation. All rights reserved.
Connected.

Breakdown:
  • SQL*Plus Version: The version of SQL*Plus is 8.1.5.0.0.
  • Release Date: It was part of Oracle's production release on September 17, 1999.
  • Copyright Information: Indicates ownership by Oracle Corporation as of 1999.
  • Status: The "Connected." message confirms that the user has successfully connected to the Oracle Database.

This interface was common in Oracle 8i and earlier database environments, providing a basic IDE for managing and querying databases.

GRANT CREATE SESSION, CREATE TABLE TO COIN_ADMIN
2) GRANT CREATE SESSION, CREATE TABLE TO COIN_ADMIN

GRANT succeeded
3) GRANT succeeded


  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.

Granting Privileges to All Users

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;

Granting System Privileges - Exercise

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

SEMrush Software