Granting CREATE SESSION Privileges

If you have been following along with your Oracle database and connecting as the user that you just created, you will find that you cannot. Even though you have created a user, that user will not be able to connect until you have granted the CREATE SESSION privilege. You can grant that privilege to the COIN_ADMIN user with the following command:

GRANT CREATE SESSION TO COIN_ADMIN;

After granting this privilege, you will be able to connect using the new user-id. Oracle has dozens of system privileges in addition to CREATE SESSION. You will learn more about them, as well as how to manage them, in the next module.

While the `GRANT CREATE SESSION TO COIN_ADMIN;` command grants the user permission to establish a connection to the Oracle database, it's not enough for complete functionality in most cases. Here's why:
Limitations of `CREATE SESSION`

• Only initiates connection: This privilege solely allows the user to connect to the database and start a session.
• Insufficient for accessing data: To perform any meaningful actions like querying or modifying data, additional privileges are needed depending on the desired activities.

Recommended Approach:

1. Identify User Needs: Determine the specific tasks COIN_ADMIN needs to perform within the database. Will they require read-only access, data manipulation, or administrative rights?
2. Grant Appropriate Privileges: Based on the identified needs, grant specific privileges tailored to the user's tasks. Here are some common examples:
   • Read-only access: Grant `SELECT` on relevant tables or views.
   • Data manipulation: Grant `INSERT`, `UPDATE`, or `DELETE` on specific tables.
   • Administrative tasks: Grant specific system privileges if needed, but use caution as these come with significant power.
3. Consider Roles: Instead of granting individual privileges directly, consider creating a role with the required permissions and assigning the COIN_ADMIN user to that role. This approach improves manageability and reduces the risk of granting excessive permissions.

Example: If COIN_ADMIN needs to view customer data but not modify it, you could grant:

GRANT SELECT ON customers TO COIN_ADMIN;

Remember, granting excessive privileges can be a security risk. **Follow the principle of least privilege** and grant only the minimum permissions necessary for the user's designated tasks.
Additional Notes

• For Oracle 19c, consider using the `DEFINER` clause when granting privileges to control the user under which the granted operation executes.
• Always audit and review granted privileges regularly to ensure they remain appropriate.

Once we have created a user, we probably want that user to be able to do something in our database. A created user has no privileges, they can not even connect to the database. In this section we will address this problem by learning how to give users the privileges they need to actually get some work done. First we will look at system privileges, followed by object privileges. We will then look at how you can use roles to make user administration much easier.

1. Oracle System Privileges: System privileges allow the user to perform system level activities. This might include such things as being able to actually connect to the system, or it might include the ability to do things like create objects in schemas other than your own schema. In the next sections we will discuss the grant command, which you use to grant system privileges. Also the revoke command is used to revoke privileges from users.
2. Granting Oracle System Level Privileges: The grant command is used to grant system level privileges. System level privileges are those privileges that you need to actually do something on the system. For example you grant system level privileges to a user so that they can:
   • Connect to the database (create session)
   • Create objects (create table, create index)
   • Perform DBA activities, like backup the database (SYSDBA, SYSOPER)
   • Alter session related parameters (alter session)
   Basically, even if you create a user account, it will not be able to do anything until you have granted it a basic set of privileges. Typically you would always grant the create session privilege so that the user can connect to the database.

The grant command is pretty simple to use, you just need to know what privilege you want to grant and who you want to grant that privilege to.

GRANT INDEX ON < table1> 
TO <user1>;