How to Manage Database Security?

Database Security in Oracle

With its many tools to protect data from unauthorised access and unintentional or deliberate destruction, Oracle Database security is an essential component of information management. A structured system of users, roles, and privileges is the fundamental way that Oracle manages data access. Regardless of the security of the underlying computer system, this method functions.

Users, Roles, and Privileges

Security in the Oracle database is controlled by giving or taking away particular rights to individuals or groups of users categorised into roles. Every Oracle user is given a distinct name and password, and they are the natural owners of any database objects they create, including tables and views.

In order to simplify privilege control in Oracle, roles are essential. A named set of privileges is basically what an Oracle role is. Administrators can offer roles to the right users by grouping similar privileges into one, rather than giving each user distinct privileges. Administration is streamlined by this method, particularly in settings with a large number of users and items.

A user’s individual access to the database and its contents is determined by their privileges. Oracle offers fine-grained control over which rows are accessible and which activities initiate auditing events because to its sophisticated degree of privilege support. Privileges fall into two major categories:

Database System Privileges: These allow users to execute specific sets of commands or perform system-wide operations. Examples include CREATE TABLE (to create tables), CREATE SESSION (to connect to the database), CREATE VIEW (to create views), and GRANT ANY PRIVILEGE (to grant any system privilege). Database administrators (DBAs) typically have the authority to grant any system privilege.
Database Object Privileges: These allow users to manipulate particular database items that they do not possess. For example, the DELETE privilege enables the removal of rows from tables and views, whereas the SELECT privilege enables the querying of data from sequences, materialised views, tables, and views. Other object privileges include INSERT, UPDATE, ALTER, DEBUG, and REFERENCES on tables. Views, which let users see only a limited amount of data from underlying tables, are effective tools that can be used to improve security.

DBAs are essential to maintaining secure and effective database operations because they manage users, roles, and privileges.

CREATE USER

The first step in implementing database security is to create user accounts. SYS and SYSTEM are pre-existing administrative users that Oracle comes with; SYSTEM is usually used to generate additional new user accounts.

The basic syntax for creating a new user is straightforward: CREATE USER username IDENTIFIED BY password;

The user is created in this way, but until the CREATE SESSION system privilege is specifically granted, the new account cannot really connect to the database.

GRANT CREATE SESSION TO Dora;

Along with setting storage quotas (space consumption restrictions), administrators can also set up specific properties when creating a user, such as the default tablespace (where their objects will be kept) and temporary tablespace (for sorting and other temporary operations). Non-administrative users should be assigned to non-SYSTEM tablespaces as a best practice. Within a designated tablespace, a user can use an infinite amount of space thanks to the QUOTA UNLIMITED ON tablespace_name clause.

For instance, a more comprehensive CREATE USER statement might look like this: CREATE USER "NEWUSER" PROFILE "DEFAULT" IDENTIFIED BY "newpassword" PASSWORD EXPIRE DEFAULT TABLESPACE "USERS" TEMPORARY TABLEPACE "TEMP" QUOTA UNLIMITED ON TEMP QUOTA UNLIMITED ON USERS ACCOUNT UNLOCK;

Profiles allow users to manage their passwords, enabling features like account lockdown after several unsuccessful login attempts, password expiration, and reuse prevention.

GRANT & REVOKE

Oracle access control management requires the GRANT and REVOKE commands.

GRANT is used to bestow privileges upon users or roles. The general syntax for granting object privileges is: GRANT { object_privilege | ALL [PRIVILEGES] } ON object_name TO { user | role } [WITH GRANT OPTION];

And for system privileges: GRANT system_privilege TO { user | role } [WITH ADMIN OPTION];

Granting an object privilege (SELECT) to a user: To allow a user named Judy to view data in Dora’s COMFORT table: GRANT SELECT ON Dora.COMFORT TO Judy; If Dora wanted Judy to also be able to grant this SELECT privilege to others, she would use WITH GRANT OPTION: GRANT SELECT ON Dora.COMFORT TO Judy WITH GRANT OPTION;
Granting a system privilege (CREATE TABLE) to a user: To allow a user named Bob to create tables: GRANT CREATE TABLE TO Bob; If Bob should be able to grant this CREATE TABLE privilege to other users, then WITH ADMIN OPTION would be used: GRANT CREATE TABLE TO Bob WITH ADMIN OPTION;

REVOKE is used to remove privileges from users or roles. The syntax mirrors the GRANT command: REVOKE { privilege | ALL } ON object_name FROM { user | role }; (for object privileges) REVOKE system_privilege FROM { user | role }; (for system privileges)

Revoking an object privilege: REVOKE SELECT ON Dora.COMFORT FROM Judy; After this, Judy would no longer be able to query the COMFORT table.
Revoking a system privilege: REVOKE CREATE TABLE FROM Bob;

System and object privilege grants and revokes are effective instantly. However, denying a user full privileges merely limits access; it does not erase the user account or the items the person owns. The command DROP USER username CASCADE; is used to delete a user and all of their objects.

CREATE ROLE

To define a new role that serves as a container for privileges, use the CREATE ROLE command. This makes privilege management more easier by enabling DBAs to create roles that match job duties (like CLERK and MANAGER) and provide these roles all the privileges they need.

The basic syntax for creating a role is: CREATE ROLE role_name;

As with granting privileges to users, the GRANT command can be used to grant privileges to a role after it has been formed.

Granting an object privilege to a role: To allow anyone assigned the CLERK role to query Dora’s COMFORT table: GRANT SELECT ON Dora.COMFORT TO CLERK;
Granting system privileges to a role: To allow users in the CLERK role to connect and users in the MANAGER role to connect and create views: GRANT CREATE SESSION TO CLERK; GRANT CREATE SESSION, CREATE VIEW TO MANAGER;

Permissions can be arranged hierarchically by granting roles to other roles. For example, all the privileges of the CLERK role might be passed down to the CLERK role: GRANT CLERK TO MANAGER;

Finally, a role is assigned to a user using the GRANT command: GRANT CLERK TO Bob;

Bob now has access to every capability that was given to the CLERK role, including the ability such as CREATE SESSION and SELECT on Dora.COMFORT. It’s crucial to remember that views, procedures, functions, packages, and foreign keys cannot be created using privileges granted through roles; instead, direct grants are needed for these activities.

In order to give the grantee control over the job, roles can also be granted with an ADMIN OPTION or password-protected. The granting or revocation of roles is detected when a user initiates a new database session or expressly issues a SET ROLE command.

Page Content

Tutorials