oracle assign dba role to user

Jira Software

Project and issue tracking

Content collaboration

Jira Service Management

High-velocity ITSM

Visual project management

• View all products

Marketplace

Connect thousands of apps and integrations for all your Atlassian products

Developer Experience Platform

Jira Product Discovery

Prioritization and roadmapping

You might find helpful

Cloud Product Roadmap

Atlassian Migration Program

Work Management

Manage projects and align goals across all teams to achieve deliverables

IT Service Management

Enable dev, IT ops, and business teams to deliver great service at high velocity

Agile & DevOps

Run a world-class agile software organization from discovery to delivery and operations

BY TEAM SIZE

Small Business

BY TEAM FUNCTION

Software Development

BY INDUSTRY

Telecommunications

Professional Services

What's new

Atlassian together.

Get Atlassian work management products in one convenient package for enterprise teams.

Atlassian Trust & Security

Customer Case Studies

Atlassian University

Atlassian Playbook

Product Documentation

Developer Resources

Atlassian Community

Atlassian Support

Enterprise Services

Partner Support

Purchasing & Licensing

Work Life Blog

Support for Server products ends February 15, 2024

With end of support for our Server products fast approaching, create a winning plan for your Cloud migration with the Atlassian Migration Program.

Assess my options

Atlassian Presents: Unleash

Product updates, hands-on training, and technical demos – catch all that and more at our biggest agile & DevOps event.

Register now

• Atlassian.com

How to create a user and grant permissions in Oracle

Posted by: aj welch.

As always, begin by connecting to your server where Oracle is hosted, then connect to Oracle itself as the  SYSTEM  account.

The  SYSTEM  account is one of a handful of predefined administrative accounts generated automatically when Oracle is installed.  SYSTEM  is capable of most administrative tasks, but the task we’re particularly interested in is account management.

Creating a user

Once connected as  SYSTEM , simply issue the  CREATE USER  command to generate a new account.

Here we’re simply creating a  books_admin  account that is  IDENTIFIED  or authenticated by the specified  password .

The Grant statement

With our new  books_admin  account created, we can now begin adding privileges to the account using the  GRANT  statement.  GRANT  is a very powerful statement with many possible options, but the core functionality is to manage the privileges of both  users  and  roles  throughout the database.

Providing roles

Typically, you’ll first want to assign privileges to the user through attaching the account to various roles, starting with the  CONNECT  role:

In some cases to create a more powerful user, you may also consider adding the  RESOURCE  role (allowing the user to create named types for custom schemas) or even the  DBA  role, which allows the user to not only create custom named types but alter and destroy them as well.

Assigning privileges

Next you’ll want to ensure the user has privileges to actually connect to the database and create a session using  GRANT CREATE SESSION . We’ll also combine that with all privileges using  GRANT ANY PRIVILEGE .

We also need to ensure our new user has disk space allocated in the system to actually create or modify tables and data, so we’ll  GRANT TABLESPACE  like so:

Table privileges

While not typically necessary in newer versions of Oracle, some older installations may require that you manually specify the access rights the new user has to a specific schema and database tables.

For example, if we want our  books_admin  user to have the ability to perform  SELECT ,  UPDATE ,  INSERT , and  DELETE  capabilities on the  books  table, we might execute the following  GRANT  statement:

This ensures that  books_admin  can perform the four basic statements for the  books  table that is part of the  schema  schema.

Administering Roles

System privileges, object privileges, and roles provide a basic level of database security. They are designed to control user access to data and to limit the kinds of SQL statements that users can execute.

About Privileges and Roles

Roles are groupings of privileges that you can use to create different levels of database access. For example, you can create a role for application developers that enable users to create tables and programs.

You can grant privileges and roles to other users only when you possess the necessary privilege. The granting of roles and privileges starts at the administrator level. At database creation, the administrative user SYS is created and granted all system privileges and predefined Oracle roles. User SYS can then grant privileges and roles to other users and also grant those users the right to grant specific privileges to others.

Table 7-2 provides descriptions and examples of privileges and roles.

Table 7-2 Privileges and Roles

About Administrative Accounts

The following administrative accounts are automatically created when Oracle Database is installed:

When you create an Oracle database, the user SYS is automatically created and granted the DBA role.

All base tables and views for the database data dictionary are stored in the schema SYS . These base tables and views are critical for the operation of Oracle Database. To maintain the integrity of the data dictionary, tables in the SYS schema are manipulated only by the database. They should never be modified by any user or database administrator. Also, you should not create any tables in the schema of user SYS , although you can change the storage parameters of the data dictionary settings if necessary.

Ensure that most database users are never able to connect to Oracle Database with the SYS account.

When you create an Oracle Database, the user SYSTEM is also automatically created and granted the DBA role.

The SYSTEM user can create additional tables and views that display administrative information as well as internal tables and views used by various Oracle Database options and tools. Never use the SYSTEM schema to store tables of interest to nonadministrative users.

A predefined DBA role is automatically created with every Oracle Database installation. This role contains most database system privileges. Therefore, you should grant the DBA role only to actual database administrators. The DBA role does not include the SYSDBA or SYSOPER system privileges.

Administrative Privileges

SYSDBA and SYSOPER are administrative privileges required to perform basic database operations such as creating the database and instance startup and shutdown. Depending upon the level of authorization you require, you must have one of these privileges granted to you.

You can also think of the SYSDBA and SYSOPER privileges as types of connections that enable you to perform certain database operations for which privileges cannot be granted in any other way. For example, if you have the SYSDBA privilege, then you can connect to the database by specifying CONNECT AS SYSDBA .

Viewing Roles

You can use Enterprise Manager to view existing roles as follows:

In the Users & Privileges section of the Administration home page, click Roles .

Figure 7-1 Users & Privileges

The Roles page appears. From this page you can create, edit, view, or delete roles. The structure and functionality of the Roles page is similar to that of the Users page shown in Figure 7-2 .

Select the CONNECT role.

Click View .

The View page appears. In this page you can see all of the privileges and roles associated with the CONNECT role.

Creating Roles

You can create a secure role with the privileges necessary for application development. You can then grant the role to other roles or users depending on the level of data access required by the user.

In this exercise, you create an application developer role called APPDEV .

To create the APPDEV role:

The Roles page appears.

Click Create .

The Create Role General page appears.

In the Name field, enter the name of the new role. For instance, enter APPDEV to create a new role for application developers.

A page appears with a list of all roles, including the APPDEV role that you just created. You can now modify this new role by adding the required privileges.

Modifying Roles

You can add roles, privileges, and consumer groups to roles. In this exercise, you add the basic system privileges shown in Table 7-3 , which allow the creation of various objects, to the APPDEV role that you created previously. These objects are described in Chapter 8, "Managing Schema Objects" .

Table 7-3 APPDEV Privileges

To modify the APPDEV role:

From the list of roles, select APPDEV and click Edit .

Click System Privileges to navigate to the System Privileges property page.

The System Privilege column should display no items.

Click Edit List .

The Modify System Privileges page appears.

In the Available System Privileges list, double-click the privileges listed in Table 7-3 to add them to the Selected System Privileges list.

You are returned to the Edit Role: APPDEV page.

Click Apply .

A confirmation message should appear saying that the role has been modified successfully.

Dropping Roles

In this exercise, you drop to the APPDEV role that you created in "Dropping Roles" .

To drop the APPDEV role:

Select the APPDEV role and click Delete .

A confirmation page appears.

Click Yes .

A confirmation message indicates that the role has been deleted successfully.

Scripting on this page enhances content navigation, but does not change the content in any way.

Granting roles and privileges in Oracle DBA

• Oracle DBA Interview Questions
• Oracle DBA Tutorial
• Access to Network Services in Oracle Database - Oracle DBA
• Bigfile Tablespace in Oracle
• How to Create TableSpaces - Oracle DBA
• How to Create Profiles and Password Management in Oracle DBA
• Oracle Rename Tablespace - Dropping Table Space
• How to Specify nonstandard block sizes for tablespaces - Oracle DBA
• How to Tune and Setting the Undo Retention Period - Oracle DBA
• Listing privilege and role information in Oracle DBA
• Oracle Locally Managed Tablespace
• Control File in Oracle
• Managing the Redo Log - Oracle DBA
• Managing the sysaux tablespace - Oracle DBA
• Managing the undo tablespace - Oracle DBA
• Managing User Privileges and Roles - Oracle DBA
• Managing user roles - Oracle DBA
• Oracle Architecture and Components
• Oracle Database Startup and Shutdown Procedure
• Revoking user privileges and roles - Oracle DBA
• Steps to Install Oracle Database 11g on Linux
• Temporary Tablespaces - Oracle DBA
• Oracle transportable tablespaces - DBA
• Transporting tablespaces between databases. a procedure and example - Oracle DBA
• Undo Retention
• Using transportable tablespaces - Oracle DBA
• How to View Tablespace in Oracle
• Relational vs Non-Relational Databases
• Explore real-time issues getting addressed by experts
• Test and Explore your knowledge

What is a privilege in Oracle DBA?

Granting system privileges and roles.

Granting a role to a user

Identified by clause

With the admin option clause

Granting object privileges

With the grant option clause

Authorization is how the Oracle database discriminates between the administrators, the different users, and their levels of access. The access to the data in the databases is controlled by the database administrators by granting the users certain rights , i.e. the privileges or groups of them, i.e. the roles.

There are two primary processes included in authorization:

Permitting only a few particular users to be able to access, alter, or process the data applying limitations on user access and actions.

Placing limitations on the user s or removed might apply to tables, rows, and schemas. They might even apply to resources like connect, CPU, or even idle times.

Many tasks are involved in the administration of user roles, privileges, and profiles. Each Oracle database would have a list of valid database users and a user must run a database application to access the database and connect to a database instance.

• A privilege is a right allowing the user to run some particular types of SQL commands or access the object of another user. Some of the privileges that are given to users include the rights like connecting to a database or creating a table. There could also be rights to select the rows from the users of another table or execute the stored procedure of another user.
• Privileges are granted to users in order for them to accomplish the tasks needed for different jobs. Only those privileges should be granted to the user that would allow them to perform the necessary task. Security could be compromised if excessive or unnecessary privileges are granted to a user. Users can receive privileges in two of the following ways:
• Privileges can be granted to a user explicitly. Privileges can also be granted to a certain role, i.e. a named collection of privileges. In this way, a certain role can be granted to one user or more than one.
• Roles allow better and easier management of privileges. That is why, the general way to go should be to grant privileges to the roles, but not the individual users. This would make for a more sophisticated database with better controls and management.
• Below ones are six basic classes of privileges, though some of them have subcategories as well:
• System Privileges
• Schema Object Privileges
• Procedure Privileges
• View Privileges
• Table Privileges
• Type Privileges
• System Privileges - The system privileges offer the rights to perform actions on schema objects or some other actions. There are lots of different system privileges that can be granted under this category.
• Schema Object Privileges - The schema object privilege offers permission to execute a certain action on a specific schema object. The different object privileges are available for the different types of schema objects. It offers the privilege to delete roles. The schema objects that do not have associated object privileges are controlled through system privileges.
• Table Privileges - Security at the Data Definition Language (DDL) or Data Manipulation Language (DML) level is enabled by the schema object privileges for tables.
• View Privileges - A view is basically a presentation of the data accessed from one or more tables. The structure of the underlying tables is seen through a view. No actual data is contained in a view. It merely shows the data derived from the tables on which it is based. The data in a view can be updated or deleted and new data can be inserted. The tables are directly altered by the operations on which the views are based. The triggers and integrity constants of the base tables are taken into consideration.
• Procedure Privileges - The only schema object privilege for procedures is EXECUTE. Standalone procedures, packages, and functions are all included in these procedures. This privilege is to be granted only to those users who have to execute a procedure or compile another procedure that needs the desired procedure. For the creation and management of secure and effective usage of the procedure privileges, you need to know about the security domains.
• Type Privileges - The privileges for methods, types, and objects are defined by system privileges for named types, method execution model, and others.

The system privilege offers users the right to execute certain actions. It could be the actions of schema objects. The privileges that are needed to make tablespaces and delete rows of tables in the databases are all system privileges. More than 100 different system privileges are available.

How to grant and revoke the system privileges?

System privileges can be granted and revoked from users or roles. If system privileges are granted to roles, these roles can be used to manage the system roles. Roles offer the privileges the chance to be selectively available. In general, the system privileges should be granted only to the administrative team and the application developers. The end-users would not require these associated capabilities and must not be vested with them.

To revoke or grant the system privilege to the roles or users, one among the following can be used:

• The SQL REVOKE and GRANT statements
• The Oracle Enterprise Manager 10g Database Control

Who has the power to revoke or grant the system privileges?

There are only two kinds of users who are vested with the power of granting system privileges. They can grant them to the other users and even revoke them. They are:

• The users having a system privilege along with ADMIN option (or)
• The users having the GRANT ANY PRIVILEGE

Learn end-to-end Oracle DBA concepts through the Oracle DBA Course in Hyderabad to take your career to a whole new level!

Granting a role to a user in Oracle

• The GRANT command is used to grant the system privileges both to roles and users. The statement can even be used to grant roles to both roles and users. The privileges and roles could be global, external, or local. It can even offer object privileges for certain objects to roles, users, or the PUBLIC.
• Database users can be authorized in some other ways as well, where there would be no need to use the database or the GRANT statement. Most of the privileges in the Oracle database are often granted through the Java packages and Oracle PL/ SQL . Some of the operating systems offer facilities to let the users grant roles to the users of Oracle Database with the OS_ROLES initialization parameter.
• If you are granting roles to these users through the operating system, you would not be able to use the GRANT command in order to grant the roles to these users. However, it is possible to use the GRANT statement if you want to grant any system privilege to a user or system privilege or roles are to be granted to some other roles.

Prerequisites

If you want to grant the system privileges, they must be granted with the help of the ADMIN OPTION . You could also have GRANT ANY PRIVILEGE , which is a system privilege. In the same way, if you need to grant roles to a user, the role must have been offered the required ADMIN OPTION, if not the system privilege -  GRANT ANY ROLE.

The alternative is to create the role yourself. Granting any object privilege is possible only if the object is owned by you. Otherwise, the object owner must grant you the necessary object privileges and also GRANT OPTION . Another way out is for you to be granted with GRANT ANY OBJECT PRIVILEGE , which is another system privilege. 

You would be able to grant the object privilege through the GRANT ANY OBJECT PRIVILEGE provided that the owner of the object had the power to grant the same privilege. GRANTOR column in the DBA_TAB_PRIVS would display the owner of the object and not the person who had offered the GRANT command.

System privileges

Oracle RDBMS has added a few clauses in its semantics for better distribution of the privileges among the users and for better control of the administrators. A few clauses are important to grant the system privileges to users and roles. These clauses are crucial to the GRANT statement and help the database administrator or the users with roles and privileges exercise them with proper authentication.

Granting system privileges

It is needed to specify the privilege that you would like to grant. If a user is granted a privilege, the privilege is added to the privilege domain of the user by the database. The user would immediately be able to exercise that privilege. In the same way, if a role is granted a privilege, the privilege is added to the privilege domain section of that role by the database. If the users who had been granted the role have enabled it, the privilege can be exercised immediately. The role needs to be enabled, following which immediate exercise is allowed.

If a privilege is granted to PUBLIC, the privilege would be added to privilege domains each of the users possess and all the users would be immediately able to exercise the privilege. ALL PRIVILEGES is the shortcut that can be used to grant all system privileges offered by the Oracle Database. However, it does not have the privilege - SELECT ANY DICTIONARY .

Granting system privileges to users:

Granting system privileges to roles:.

The dw_manager role now has a privileged domain that contains system privileges that are related to materialized views.

In a similar way to the privileges, it is necessary to mention the role to be granted. The roles predefined in the Oracle Database as well as the user-defined roles can be granted. When a role is granted to some user, the database would make the role accessible for that particular user. It would make the user immediately able to enforce the privileges that a role has in its privilege domain.

Granting a role to a user: 

If a role is granted to some other role, the privilege domain that is the property of the role would be added to that privilege domain that is used by the grantee role. Thus, the users having the privileges of grantee role would be able to exercise the roles that are in the privilege domain of the granted role, along with the pre-existing ones of the grantee role. If a role is granted to PUBLIC, the role would be accessible to all the users. All the users would be immediately able to use the role as well as enforce the privileges that are present in the privilege domain.

Granting role to another role:

Identified by clause.

IDENTIFIED BY statement+1 finds use when a user is specifically identified by a password or a non-existing user is created. The clause would not be credible if the one who has been granted the role is a role itself or PUBLIC. The database would create a user with a given password, roles, and privileges provided in the clauses if the users provided in the grantee_clause are non-existent.

With Admin Option

The grantee must be specified with the clause - ‘ WITH ADMIN OPTIO N’ to offer a few rights. They are stated below.

• Grant this role to other roles or users, if the roles are not GLOBAL roles
• Revoke this role from other roles or users
• Alter the roles so that the authorization required for access can be modified
• Drop the roles altogether

If a user grants a role or system privilege to another user and do not specify WITH ADMIN OPTION , but however, the role or privilege is granted to that user subsequently with the ADMIN OPTION, the user would have ADMIN OPTION in that role or privilege. If you would like to revoke just the ADMIN option from users on a certain role or system privilege, you would need to revoke the role or privilege from those users altogether first. You would then need to grant that role or privilege to the users now again without using ADMIN OPTION.

Global privileges such as system privileges or roles are often granted WITH ADMIN OPTION. We also employ WITH GRANT OPTION for the table-specific privileges. Revoking a grant having WITH GRANT would cascade the privileges that had been offered by the original privileged user. However, if someone having WITH ADMIN OPTION is revoked, only the personal privileges would be revoked and all the granted users would remain intact.

When the system privileges pass on to others with a WITH ADMIN OPTION, there would be no cascade when system privileges are revoked from original users. If system privileges need to be revoked, direct action must be taken. This is quite unlike object privileges with WITH GRANT OPTION which is passed to others. Along with the privileges of the grantor, object privileges would be revoked as well. Only the object privileges would cascade when revoked, but the system privileges would not.

Sometimes, there is a need to pass on privileges to the users and also have them, in turn, be capable of granting privileges to the other users. WITH ADMIN OPTION is included in such a case in GRANT command. The user is allowed to pass on privileges to other users when this keyword is used. So this option is prohibited by many companies and the others make sure that all the user IDs are proper.

Granting a Role with the Admin Option:

Now she would be able to perform a few operations with the dw_manager:

granting and revoking the role to and from other users enabling the role and exercising any privileges that are present in the privilege domain of the role, and dropping the role

The object privileges that are to be granted under the GRANT command must be mentioned.  Let us look at how the object privileges are to be used, the restrictions on them, how to grant privileges to specific columns. The WITH GRANT OPTION would allow the grantee user to further pass on the object privileges.

• on_object_clause

The purpose of the on_object_clause is to identify the object for which privileges are being granted. The objects of resource schema and directory schema would be identified separately as they do not reside in the same namespaces. However, if the grant is made just because the user has the GRANT ANY OBJECT PRIVILEGE and he does not own the object, then from the grant’s effect, it would reflect that the user is following the wishes of the owner. The data dictionary *_TAB_PRIVS would show that the grant had been made by the object owner.

With Grant Option

The WITH GRANT OPTION is to be specified if the user would like the grantee to be able to pass object privileges on to the other roles or users. The Grant Option would allow you to offer the user you are assigning the privilege to pass it on to others. Only if WITH GRANT OPTION has not been mentioned in the statement, the schema that owns the object can grant such privileges. An example that describes  The Grant Option is as follows:

Restrictions on using object privileges

A privilege must not be appearing among the granted privileges more than a single time. If you would need to grant each and every privilege available for an object, the keyword to be used is ALL. The user in possession of the schema that contains the object would have all the privileges on that object with GRANT OPTION. The PRIVILEGES keyword is provided just to offer semantic clarity, so it is actually optional.

How to grant privileges to a specific column?

The view or table column is to be specified on which the privileges should be granted. If the columns are not specified, the grantee would then have the specified privileges on each of the columns in a view or table.

All these different levels of user privileges and authorization help sound management of the database. In the real world, there are different levels of employees in a company and not everyone should have the right to access or modify the content.

On top of that, there should be special privileges and powers that are to be vested only with the management or administration. The Oracle Database offers the concept of Authorization, which makes it possible to put this into effect. Authorization helps to put in place the mechanisms that are needed to keep checks on the users, using limitations individually or in groups.

Explore Oracle DBA Sample Resumes! Download & Edit, Get Noticed by Top Employers!

Stay updated with our newsletter, packed with Tutorials, Interview Questions, How-to's, Tips & Tricks, Latest Trends & Updates, and more ➤ Straight to your inbox!

Technical Content Writer

Copyright © 2013 - 2023 MindMajix Technologies

  Oracle Training   Oracle Tips   Oracle Forum   Class Catalog   Remote DBA   Oracle Tuning   Emergency 911   RAC Support   Apps Support   Analysis   Design   Implementation   Oracle Support

Copyright © 1996 -  2020

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.

• dbForge SQL Complete
• dbForge Studio for SQL Server
• dbForge SQL Tools
• dbForge Studio for MySQL
• Compare Bundle for MySQL
• Schema Compare for MySQL
• dbForge Studio for Oracle
• Compare Bundle for Oracle
• Data Compare for Oracle
• dbForge Studio for PostgreSQL
• Data Compare for PostgreSQL
• Multidatabase Solution
• dotConnect for Oracle
• dotConnect for PostgreSQL
• dotConnect for MySQL
• ODBC Driver for Salesforce
• ODBC Driver for Oracle
• ODBC Driver for MySQL
• SSIS Components for Salesforce
• SSIS Components for MySQL
• SSIS Integration Database Bundle
• Excel Add-in for SQL Server
• Excel Add-in for PostgreSQL
• Excel Add-in Database Pack
• Universal Data Access Components
• Oracle Data Access Components
• SQL Server Data Access Components
• SecureBridge
• Code Compare
• Review Assistant
• Code Review Bundle
• Entity Developer
• LinqConnect
• LINQ Insight
• Time Tracking App
• Data Integration Services
• Cloud to Cloud Backup
• Online SQL Tools
• Web API Server

In this article, we will talk about how to create a user in Oracle. You will learn how to add new database users, figure out which supplemental aspects this job involves: from the initial user creation to dropping it. Moreover, you will find some useful tips on working with IDENTIFY and TABLESPACE clauses, as well as learn how to GRANT roles and permissions in Oracle.

How to Create a New User in Oracle

Oracle create user syntax examples, how to create default users with default settings, create user identified by password clause, externally and globally clauses, default tablespace, temporary tablespace, password expire, account lock/account unlock, grant role to user, granting permission in oracle, grant command syntax, oracle user privileges, how to create and grant all privileges to oracle user, how to grant table privilege to user in oracle, create oracle users and grant permissions easily with dbforge studio for oracle, how to delete (drop) user in oracle.

Before we start, you need to check if you have the necessary system privilege to create users. If not, make sure to get them assigned to your account. After that, you can proceed to the practical tasks. The examples in this article relate to the create user Oracle 19c version, but the methods are the same for all Oracle versions in use (including Oracle 10g, 11g, 12c, etc.).

For starters, we will be looking into Oracle CREATE USER syntax. First, we will discuss how to create one with default settings. After that, we will move on to the different variations of the IDENTIFIED clause, tablespace clause, and other peculiarities of the CREATE USER syntax in Oracle.

It is always best to start with the basics. Thus, let us focus on the CREATE USER command by itself. As is, it will create a user with default attributes. Further in this article, we will look at how to configure users more finely and how it boosts the safety of the database in general.

Create User Identified by Clauses

The IDENTIFIED clause lets you indicate how the Oracle database authenticates a user. Let us take a closer look at different examples of the IDENTIFIED syntax in Oracle.

In the most straightforward case, we are creating a new local user under the username. The user will be required to enter the password to log into the system:

The username can be anything. However, the password must consist of single-byte characters from the database character set. If the character set also has multibyte characters, it does not change the password requirement – use only single-byte characters.

Besides identifying by password, you may use one of the two other means of user authentication. It will be configuring an external user or a global user. To do it, you need to include the EXTERNALLY or GLOBALLY clause in the CREATE USER Oracle command.

EXTERNALLY allows for creating an external user. In this case, the user is authenticated by an external system, such as the operating system. For instance, an Oracle database user is a Windows user. Thus, they can access the database after getting authenticated by Windows without entering other passwords. Working under the external user is a standard option for regular database users. But such users only have standard roles (CONNECT and RESOURCE), without administrator or database operator privileges.

To create an external user, we execute the below statement:

This way, we have made a new external user for our database. The name is external_user1. No additional password is needed. We assigned this user the default tablespace tbs_new_10 with a quota of 10 Mb. Other limitations are defined by the external_user_profile1 applied to this user.

As we mentioned earlier, different external systems can maintain and manage external users in the Oracle database. Using the capabilities of the operating system is the most common option. Thus, if we want to create an external database user accessible by the system account in the operating system, we only need to modify our statement slightly. We’ll add the ops$ prefix to the username:

GLOBALLY allows for creating global users. It means that their logins and passwords are stored on the Central Oracle Security Server instead of the specific database. Besides, roles assigned to global users on that central Server apply to this user in any database. It won’t be necessary to configure the user role in a separate database. Note that you need to enable the single sign-on option for global users.

To create a global database user, we use the following statement:

Now we have a new global database user under the name of global_user1 . We assigned USERS default tablespace to that user with a quote of 10M.

CREATE USER with Tablespace Clause

Now, let us review the basic Oracle create new user script. It is below:

As you see, the script includes several clauses that we should take into consideration:

This clause specifies the default tablespace for objects created by the user. Otherwise, such objects are stored in the default tablespace of the database. If there are not any default tablespaces specified for this particular database, the objects will get into the system tablespace.

Restriction: don’t specify the locally managed temporary tablespace (such as undo tablespace or dictionary-managed temporary tablespace) to be the Oracle create user default tablespace.

This clause specifies the tablespace/tablespace group meant to contain the temporary segments of the user. Without it, those users’ temporary segments are stored in the default temporary tablespace of the database of the system tablespace. When you specify the tablespace group including the tablespace_group_name value in the script, users’ temporary segments can be saved in any tablespace of that group.

Note : Make sure to specify the temporary tablespace with standard block size. It cannot be the undo tablespace or the tablespace with automatic segment-space management.

This clause specifies how much space this user can allocate in the tablespace. Multiple QUOTA clauses in one Oracle CREATE USER command can be present if you need to specify several tablespaces. The clause can include the UNLIMITED definition to allow this definite user to allocate the tablespace as much as needed, without bounds.

Restriction : the QUOTA clause does not apply to temporary tablespaces.

Create User Attributes

There are additional, optional Oracle CREATE USER attributes you can include in the syntax. Have a look at the following example:

Let us review these optional clauses.

This optional clause lets you limit the database resources for this specific user at once when the limitations are defined in the particular profile. Without this clause, a new user automatically comes under the default profile.

The clause is optional, but many database administrators set it for more effective security. If included, this clause will determine the forced change of the password on the user’s side. Usually, it happens when the user tries to log into the database for the first time.

You may use one of these clauses. With LOCK applied, Oracle creates the user account, but that account won’t have access to the database. If you apply the UNLOCK clause or don’t specify any of these two clauses, the account will be usable at once. The unlocked status is the default.

The CREATE USER statement with these additional parameters would be as follows:

Here, the statement creates a new Oracle database user named visitor , with the password migzw23ter . This user is assigned the default tablespace tbs_new_10 with a quota of 50Mb. This user is also allowed to use the temporary tablespace tbs_temp_10 .

The first step is the creation of a user. The next one is to set the user’s rights. A newly created user is not allowed to do anything, even to connect to the database.

Working with Oracle databases inevitably includes the task of creating database users. There are the system user accounts that Oracle creates itself – hr , OE , sys , etc. These accounts have predefined configurations with rights and limitations. However, daily work will always require other users. 

One of the DBA’s duties is to create additional database users. The job includes configuring the user accounts, setting privileges, and managing users according to the business goals.

By using the GRANT command, you can provide the users with certain privileges and configure their roles according to your needs. In Oracle, you can grant your permission to others so that they can manipulate and manage the data in your database. GRANT is a very powerful statement with many possible options, but the core functionality is to manage the privileges of both users and roles throughout the database.

The basic syntax of the query to grant certain privileges to the user is the following:

The GRANT command can give the users privileges to create, alter, drop and manage database objects. For instance, the privileges to create tablespaces and to delete the rows of any table in a database are system privileges.

Oracle has more than 100 system privileges that can be found in the SYSTEM_PRIVILEGE_MAP table.

Usually, the administrator of a database grants the privileges to the users. However, there are cases when the administrator needs to transfer their Oracle user privileges. This is when DBA privileges come in. If a DBA needs to provide system privilege to another person, it has to be done with the admin option:

Besides the Oracle system privileges, object privileges are granted upon database objects: tables, views, procedures, and so on.

First, we need to grant our users the system privilege to log into the database. We use the following statement for that:

There are many permissions the database administrator can provide to the user. But it is essential to stick to the primary concept of security, which is to give users the minimum of privileges necessary to do the job efficiently. That’s why it is not recommended to provide all privileges to the user.

You can apply other privileges one by one, each by a separate statement. Or, it is possible to combine these permissions into one, as shown below:

If this definite user is allowed to change tables, procedures, triggers, etc., the syntax to set the necessary privilege for each case is below. Again, be very careful when allowing the user to change any elements, as this permission is global.

To allow the user to delete elements, we use the below statements:

Before you set the privileges to the particular user, you should consider which tasks that person must perform in the database. The most common scenarios include creating tables, views, procedures, triggers. Some cases require the possibility to change or delete those elements. Depending on the situation, the administrator defines which system privileges to provide.

Let us take a closer look at how to grant CREATE TABLE privilege to a user in Oracle. If we are willing to allow our user – visitor – to create tables in the database, we will use the following query:

If you are working with Oracle databases on a daily basis and looking for a convenient all-in-one powerful IDE, your search ends here. dbForge Studio for Oracle helps developers to speed up PL/SQL coding, provides versatile data editing tools for managing in-database and external data.

In this article, we will be creating an Oracle user and granting DBA privileges using this multi-purpose script:

What we are doing in the provided script, is:

• creating a new user
• granting DBA privileges to the newly created user
• setting the newly created user as a default one for the current session

In Oracle, users and schemas are essentially the same thing. You can consider that a user is an account to connect to a database, and a schema is the set of objects that belong to that account. The newly created schema is empty and, therefore, will not be displayed in Database Explorer .

Let us create a departments table to make the new user appear in the list. To do that, feel free to use the following script:

Keep in mind that you do not have to open a different SQL document for this operation. dbForge Studio for Oracle allows you to execute the code only partially. Simply select the CREATE TABLE clause, make a right-click, and choose Execute Selection .

If you have the AutoComit feature ON, the previous step will be the last one. However, if it is off, you will need to commit the changes. Then, on refreshing Database Explorer, you will see username1 in the list.

There are instances when old sessions remain running on the server. They might interfere with the execution of new commands. In the code above, all the old sessions are automatically located and removed from the server. Should you require to restart your current session, you can disconnect or kill it using one of these queries:

• ALTER SYSTEM DISCONNECT SESSION
• ALTER SYSTEM KILL SESSION

In case you need to remove any user for any reason, you should use the DROP USER command with the following syntax:

In our test case, we are removing the user visitor created earlier:

However, there are several restrictions that you need to pay attention to before dropping the user:

• You can’t remove users without deleting all the related objects. Thus, you must drop all tables, views, procedures, etc. that this user created before proceeding to the DROP command.
• You can’t remove users that are connected to the database. First, you have to clear up all sessions that the user had. After that, you can drop the user itself.

There is a special command that allows for dropping the user with all its database objects in one shot:

Summing up, now you can use the Oracle SQL CREATE USER command to add new users, configure, and manage them. We used the examples for manual performance. However, the capabilities of the modern software solutions for Oracle databases can simplify this job, make it faster and more accurate. Feel free to give dbForge Studio a try with a free 30-day trial version .

Useful Links

• Setting Up a New User Account in Oracle
• Oracle Database Administration Tools
• Create a Database in Oracle
• Oracle Rename Table
• Oracle Alter Table
• dbForge studio for Oracle
• Oracle Tutorial

How to Connect to PostgreSQL in Delphi with Devart PgDAC

How to create a pivot table in sql server, understanding and implementing foreign keys in sql server.

Founded in 1997, Devart is currently one of the leading developers of database management software, ALM solutions and data providers for most popular database servers.

• SQL Server Tools
• MySQL Tools
• Oracle Tools
• PostgreSQL Tools
• Productivity Tools
• Discontinued Products
• ADO.NET Data Providers
• ORM Solutions
• ODBC Drivers
• SSIS Components
• Excel Add-ins
• Delphi Data Access Components
• dbExpress Drivers
• Submit Request
• View Forums
• Documentation Center
• Ordering FAQs
• Discount Programs
• Using Website
• SQL Server Tutorial
• MySQL Tutorial
• PostgreSQL Tutorial
• Success Stories

• Skip to content
• Accessibility Policy
• Oracle blogs
• Lorem ipsum dolor

How to Create Users, Grant Them Privileges, and Remove Them in Oracle Database

So, you’ve got your shiny, brand new Oracle Database up and running. It’s time to start creating users!

But how do you do this?

Ryan McGuire Gratisography

First you’ll need login as system or sys. Once you’re in, the basic create user command is:

So to create the user data_owner with the password Supersecurepassword!, use:

Now you’ve got your user. The next step is to connect to it. But try to do so and you’ll hit:

What’s going on?

The problem is you haven’t given the user any permissions! By default a database user has no privileges. Not even to connect.

Granting User Privileges

You give permissions with the grant command. For system privileges this takes the form:

To allow your user to login, you need to give it the create session privilege. Let’s do that:

There are a whole raft of other permissions you can give your users. And some rather powerful roles that grant them all.

So what should you enable?

At this point, keen to get developing, you may be tempted to give your user a bucket of powerful permissions.

Before you do, remember a key security concept:

The Principle of Least Privilege .

Only give your users the smallest set of privileges they need to do their job. For a basic data schema that’s simply create table:

This allows you to make tables. As well as indexes and constraints on them. But critically, not store data in them!

Which is could lead to embarrassing errors when deploy your brand new application:

To avoid this, you need to give your user a tablespace quota. You'll want to do this on their default tablespace. Which you can find with:

Assign the quota by altering the user, like so:

These privileges will get you far. But to build an application there are a few other privileges you’re likely to need:

• create view – Allows you to create views
• create procedure – Gives the ability to create procedures, functions and packages
• create sequence – The ability to make sequences

You can give many system privileges in one go. Grant these to data_owner by chaining them together like so:

Notice the lack of “drop <object type>” access. That’s because database users always have full privileges on their own objects. Meaning you can run any queries against your own tables. And insert, update, and delete rows however you like. And drop them!

Which brings a possible security loophole.

If your application connects to the database as the user which owns the tables, if you have any SQL injection vulnerabilities  you’re in trouble!

To avoid this, separate the connection user and the data schema. Ideally with a PL/SQL API between your tables and the users.

To learn more about protecting your database behind a PL/SQL API, head to the  SmartDB resource center .

So to secure your data, you need to create another user. The only system privilege you should give it is create session.

Great, another two statements you're thinking.

Luckily there’s a shortcut. You can create a user and grant it system privileges in one go!

Just add the identified by clause to grant:

If the user already exists this will grant the privileges. And reset the password. So take care when running this, or you may change their password!

Password Management

A brief note on password rules. By default the password will expire every 180 days. Which can lead to ORA-28002 errors on login. 

Not only is this kinda annoying, it goes against current password guidelines . You can get around this by changing the password_life_time for the user's profile.

So you’ve created your application user.

But you still need to assign it permissions on data_owner’s objects. For table level access, you can give access to query and change the rows with:

There is a "grant all" option for tables. But before you reach for this, be aware that not only does it include the DML permissions above, it also gives:

• on commit refresh
• query rewrite

Remember: only give out the exact permissions users need. No more!

If you have done the good thing and protected your data behind a PL/SQL API, grant execute to allow app_user to call it. Like so:

You can only grant permissions on one object at a time. So you’ll need to repeat this for each thing app_user needs access to.

To give these object privileges, you need to either:

• Own the object in question
• Have the grant any object privilege privilege
• Have been granted the permission using the with grant option

As a rule you should avoid giving out "any" privileges. So in most cases you should only grant object privileges when connected as the object owner.

But you may want to have a low-level admin user. You'll use this to grant permissions to other users. Such as the ability to query some of data_owner's tables for reporting. If you're feeling lazy, grant allows you to create many users in one go:

Now, to allow reporting_admin to give query privileges on data_owner's objects to report_user_1, you can:

• Connect to data_owner
• Grant query permissions with grant option
• Connect to reporting_admin to pass these permissions onto others

Note the grant of read instead of select. This is a new privilege in Oracle Database 12c . Granting select allows users to lock tables. Read doesn't. So you should give this privilege to read-only users instead of select.

So you've given your application users the smallest set of privileges they need.

You've locked the front door. But there’s still a backdoor!

Anyone with access to your network can connect as data_owner. At which point they’re free to wreak havoc in your database.

This is a tricky problem to avoid. You can stop people getting in by locking the account with:

But this brings a couple of issues.

First up, it’s easy to overlook this step. If you want to connect to data_owner, say to release some changes, you’ll need to unlock it. And remember to lock it again afterwards! A step easily forgotten when dealing with emergency releases.

But there’s another problem. It allows hackers to easily discover the names of your database users. When you try and connect to a locked account, you’ll get the following message:

If I’m phishing around your database, I now know it contains the user data_owner. Even though I don’t know the password!

Now, hopefully(!), your network security is good enough that hackers can’t scan through possible usernames to find the names of your accounts.

But this trick is a quick way for them to see if your database has Oracle supplied users installed. Things like Oracle Text or Oracle Spatial. If you have, this increases the options for a hacker to get in.

So what do you do?

Luckily Oracle Database 18c offers another way around this problem: schema-only accounts!

Schema vs. User

At this point it’s worth noting the difference between schemas and users. Officially a schema is a collection of tables. Whereas a user is an account you use to connect to the database. Some databases allow you to make a distinction between these with separate create schema and create user commands.

But in Oracle Database, there’s no difference between a schema and a user. All tables belong to one user.

While the  create schema command exists , you can only use it to create tables within an existing user.

So "schema-only" accounts are users which have no password. To create one, use the no authentication clause instead of identified by:

Now there is literally no way to login to this account. Any attempts to do so will hit:

So you no longer know if data_owner is a valid account.

Is the user missing? Or are they present, but you’ve got the password wrong? You don’t know.

So you’ve stopped hackers learning about your database. Great. But.

You’re probably thinking:

How do I connect to data_owner?

From time-to-time it’s likely you’ll want to connect to do things like run release scripts.

Sure, you can assign a temporary password with:

And remove it again when you’re done with:

But this is a repeat of the lock problem again. What if you forget to remove authentication when you’re done?

Luckily, there’s a solution: proxy users.

Proxy Users

Proxy users are low privilege accounts. With the ability to connect to higher powered users.

To use them, you need to create the user. And give it the power to connect through another account:

With this in place, you can now connect to proxy_user. But run with the privileges of data_owner. Do so with:

Using this method, you can leave your schema-only accounts with no password.

Removing Access

Over time applications get decommissioned. Or rewritten to access different information. But usually the data remains.

Leaving the user with access to unneeded data is a security risk. Stay on top of this and remove access when it’s no longer needed.

To do this, use the revoke command. This states what you’re removing from who. For system privileges this is:

For object privileges, include the thing you're removing access from:

Remember: if your release scripts have grants for existing objects you'll need to undo these if you have to rollback . So ensure you include the corresponding revoke in your rollback scripts!

Dropping Users

Getting rid of unwanted users is easy. Drop them with:

You can only do this if the user is not connected to the database. So ensure you clear up any sessions it has before you do so.

And there’s another step you need to watch for. Run this for data_owner and you’re likely to hit this error:

You can’t remove users that own objects!

So you need to go in and drop all its tables, views, etc. Or do it in one shot with:

This is an easy way to wipe all your data. So use with care!

Want to know more?

Read up on create user , drop user ,  grant , and revoke in the documentation. 

Learn the basics of SQL in Databases for Developers: Foundations .

Got any questions about creating users or managing their permissions?

Ask in the comments!

Chris Saxon

Developer advocate.

Chris Saxon is an Oracle Developer Advocate for SQL. His job is to help you get the best out of the Oracle Database and have fun with SQL!

To help you with this he blogs at All Things SQL . He also creates videos combining SQL and magic on YouTube at the The Magic of SQL .

If you have questions about working with Oracle Database technology, please reach out to him. You can do this via Twitter or on Ask Tom .

So, you&rsquo;ve got your shiny, brand new Oracle Database up and running. It&rsquo;s time to start creating users!

First you&rsquo;ll need login as system or sys. Once you&rsquo;re in, the basic create user command is:

Now you&rsquo;ve got your user. The next step is to connect to it. But try to do so and you&rsquo;ll hit:

What&rsquo;s going on?

The problem is you haven&rsquo;t given the user any permissions! By default a database user has no privileges. Not even to connect.

To allow your user to login, you need to give it the create session privilege. Let&rsquo;s do that:

Only give your users the smallest set of privileges they need to do their job. For a basic data schema that&rsquo;s simply create table:

To avoid this, you need to give your user a tablespace quota. You&#39;ll want to do this on their default tablespace. Which you can find with:

These privileges will get you far. But to build an application there are a few other privileges you&rsquo;re likely to need:

• create view &ndash; Allows you to create views
• create procedure &ndash; Gives the ability to create procedures, functions and packages
• create sequence &ndash; The ability to make sequences

Notice the lack of &ldquo;drop &lt;object type&gt;&rdquo; access. That&rsquo;s because database users always have full privileges on their own objects. Meaning you can run any queries against your own tables. And insert, update, and delete rows however you like. And drop them!

If your application connects to the database as the user which owns the tables, if you have any SQL injection vulnerabilities &nbsp;you&rsquo;re in trouble!

To learn more about protecting your database behind a PL/SQL API, head to the&nbsp; SmartDB resource center .

Great, another two statements you&#39;re thinking.

Luckily there&rsquo;s a shortcut. You can create a user and grant it system privileges in one go!

A brief note on password rules. By default the password will expire every 180 days. Which can lead to ORA-28002 errors on login.&nbsp;

Not only is this kinda annoying, it goes against current password guidelines . You can get around this by changing the password_life_time for the user&#39;s profile.

So you&rsquo;ve created your application user.

But you still need to assign it permissions on data_owner&rsquo;s objects. For table level access, you can give access to query and change the rows with:

There is a &quot;grant all&quot; option for tables. But before you reach for this, be aware that not only does it include the DML permissions above, it also gives:

You can only grant permissions on one object at a time. So you&rsquo;ll need to repeat this for each thing app_user needs access to.

As a rule you should avoid giving out &quot;any&quot; privileges. So in most cases you should only grant object privileges when connected as the object owner.

But you may want to have a low-level admin user. You&#39;ll use this to grant permissions to other users. Such as the ability to query some of data_owner&#39;s tables for reporting. If you&#39;re feeling lazy, grant allows you to create many users in one go:

Now, to allow&nbsp;reporting_admin to give query privileges on data_owner&#39;s objects to report_user_1, you can:

Note the grant of read instead of select. This is a new privilege in Oracle Database 12c . Granting select allows users to lock tables. Read doesn&#39;t. So you should give this privilege to read-only users instead of select.

So you&#39;ve given your application users the smallest set of privileges they need.

You&#39;ve locked the front door. But there&rsquo;s still a backdoor!

Anyone with access to your network can connect as data_owner. At which point they&rsquo;re free to wreak havoc in your database.

First up, it&rsquo;s easy to overlook this step. If you want to connect to data_owner, say to release some changes, you&rsquo;ll need to unlock it. And remember to lock it again afterwards! A step easily forgotten when dealing with emergency releases.

But there&rsquo;s another problem. It allows hackers to easily discover the names of your database users. When you try and connect to a locked account, you&rsquo;ll get the following message:

If I&rsquo;m phishing around your database, I now know it contains the user data_owner. Even though I don&rsquo;t know the password!

Now, hopefully(!), your network security is good enough that hackers can&rsquo;t scan through possible usernames to find the names of your accounts.

At this point it&rsquo;s worth noting the difference between schemas and users. Officially a schema is a collection of tables. Whereas a user is an account you use to connect to the database. Some databases allow you to make a distinction between these with separate create schema and create user commands.

But in Oracle Database, there&rsquo;s no difference between a schema and a user. All tables belong to one user.

While the&nbsp; create schema command exists , you can only use it to create tables within an existing user.

So &quot;schema-only&quot; accounts are users which have no password. To create one, use the no authentication clause instead of identified by:

Is the user missing? Or are they present, but you&rsquo;ve got the password wrong? You don&rsquo;t know.

So you&rsquo;ve stopped hackers learning about your database. Great. But.

You&rsquo;re probably thinking:

From time-to-time it&rsquo;s likely you&rsquo;ll want to connect to do things like run release scripts.

And remove it again when you&rsquo;re done with:

But this is a repeat of the lock problem again. What if you forget to remove authentication when you&rsquo;re done?

Luckily, there&rsquo;s a solution: proxy users.

Leaving the user with access to unneeded data is a security risk. Stay on top of this and remove access when it&rsquo;s no longer needed.

To do this, use the revoke command. This states what you&rsquo;re removing from who. For system privileges this is:

For object privileges, include the thing you&#39;re removing access from:

Remember: if your release scripts have grants for existing objects you&#39;ll need to undo these if you have to rollback . So ensure you include the corresponding revoke in your rollback scripts!

And there&rsquo;s another step you need to watch for. Run this for data_owner and you&rsquo;re likely to hit this error:

You can&rsquo;t remove users that own objects!

Read up on create user , drop user ,&nbsp; grant , and revoke in the documentation.&nbsp;

Previous Post

How to Fix ORA-28002 The Password Will Expire in 7 Days Errors

How to use create table, alter table, and drop table in oracle database, resources for.

• Analyst Reports
• Cloud Economics
• Corporate Responsibility
• Diversity and Inclusion
• Security Practices
• What is Customer Service?
• What is ERP?
• What is Marketing Automation?
• What is Procurement?
• What is Talent Management?
• What is VM?
• Try Oracle Cloud Free Tier
• Oracle Sustainability
• Oracle COVID-19 Response
• Oracle and SailGP
• Oracle and Premier League
• Oracle and Red Bull Racing Honda
• US Sales 1.800.633.0738
• How can we help?
• Subscribe to Oracle Content
• © 2022 Oracle
• Privacy / Do Not Sell My Info

DBA Genesis

Users, roles & profiles in oracle.

Users are the ultimate End-People who will be using Oracle database. Before a user can access the database, the DBA must create the user inside the database and grant necessary permissions.

Oracle User Management

Roles in oracle, profile management in oracle, find user permissions.

By just creating a new user will not make the new user access the database. There are necessary roles and privileges that must be assigned to the user

By just creating a new user will not make the new user access the database. There are necessary roles and privileges that must be assigned to the user.

To check all users inside database

To check current user

To Lock / Unlock user

To Create new user

To create new user by assigning a default tablespace

To change user password

Check Database Default Tablespace

When you create a new user without specifying a default tablespace, database default tablespace is assigned to the user. Use below command to find database default tablespace

Change User Default Tablespace

Use below command to change default tablespace of a user

Note: The objects created in the old tablespace remain unchanged even after changing a default tablespace for a user

Tablespace Quota

You can specify a limit onto how much tablespace quota (size) a user can use

Note: Allocating quota doesn’t represent reserving the space. If 2 or more users are sharing a tablespace, quota will be filled up in first come first serve basis

When you create a new user, you must at least assign CREATE SESSIONS privilege so the user can connect to the database

When you work in real-time, there are more than one permission which must be assigned to a user. Sometimes the list might be very big. For example, there is a manager who must be able to perform:

Insert into EMP & DEPT table

Update DEPT table

Delete from BONUS table

Instead of giving above privileges to the user one by one, we can create a role inside the database. We then assign all privileges to the role and then assign the role to a user. It makes your life easy!

Create New Role

Use below command to create new role inside the database

Grant Privileges to Role

Assign all the privileges to the role NOT THE USER

Grant Role to a User

Now that you have assigned all the necessary privileges to a role, its time to assign the role to a user

A profile is a way to control system resource that can be used by a database user. Profile management is of two types

Password management

Resource management

Password Management

The password management allows a DBA to have more control over user passwords. Some of the parameters you might be familiar in general like failed login attempts, password lock time etc

FAILED_LOGIN_ATTEMPTS: How many times a user can fail to login

PASSWORD_LOCK_TIME : Users who exceed failed login attempts, their password will be locked for specific time

PASSWORD_LIFE_TIME : Till when password is valid in days

PASSWORD_GRACE_TIME : Grace period for user to change password, else account will be locked

PASSWORD_REUSE_TIME : After how many days user can re-use same password

PASSWORD_REUSE_MAX : Specify how many times old password can be used

PASSWORD_VERIFY_FUNCTION : Defines rules for setting a new password

Resource Management

Resource management helps in limiting the database abuse a user can cause. For example, if a user connects to database and never runs a query then this ideal connection will take system resources like CPU. To restrict such kind of issues, we have resource management parameters

SESSIONS_PER_USER: How many concurrent sessions user can open

IDLE_TIME: Total time user can stay inside database without doing any activity

CONNECT_TIME: Total time user can stay inside database whether idle of active

Note : resource management parameters will take in effect only if RESOURCE_LIMIT parameter is set to TRUE.

Use below command to check the RESOURCE_LIMIT parameter

By default the parameter is set to FALSE. You can change it via below

To create a new user profile

Note: password lock time by default is for 1 day. You can specify it in minutes (n/1440) or even in seconds (n/86400)

To assign profile to a user

To check profiles assigned to a user

To check profile parameter values

To check system privileges granted to a user

To check object level privileges granted to a user or role

To check roles assigned to a user

To check permissions assigned to role

To check roles granted to another role

• Database Administration

Recent Posts

Install Oracle 19c with ASM

In this article we would be looking at installing Oracle 19c with ASM on Linux. This will allow us to create 19c databases on ASM disks. Prerequisites Setup ASM Disks Install Oracle 19c Grid Install O

Move Spfile to ASM

With ASM configured for RAC or NON-RAC systems, it is a good idea to move the spfile to ASM. The PFILE under $ORACLE_HOME/dbs location actually points to the SPFILE on ASM disk. Create PFILE from SPFI

Oracle Database 19c RPM-based Installation On Oracle Linux 7

Granting a Role to a User or to Another Role

You must register a role in the database before the role can be used in a GRANT statement. For more information, see CREATE ROLE statement .

• The privileges granted to the payables role
• The privileges granted to the petty_cash role
• The privileges granted individually to raoul
• The privileges granted to PUBLIC

If you attempt to grant a role to yourself, either directly or indirectly, the database server generates an error. (For an important exception to this rule, however, see the description of the DBSECADM Clause .)

The database server also generates an error if you include the WITH GRANT OPTION keywords in a GRANT statement that assigns a role to another role.

27 Oct Why granting the DBA role is a really bad idea!

Just like other user accounts, database users can get certain roles granted. Those roles define what they can do and what they can see. Best practice from a security perspective is the “Principle of Least Privilege” (also known as PoLP), granting only specific privileges the user really needs.

To make it easier to manage, Oracle has some pre-defined roles within the database which can be granted to users. One of those roles is the DBA role, containing a lot of privileges which should only be granted to the Database Administrator .

Defining the right privileges can be time consuming as it can change by time (new application features, new job role of the user, etc…). That’s one of the reasons why Oracle users are often just granted the DBA role. This way everything works as it should from a user’s perspective, and there’s no need to grant or revoke privileges after application changes/updates.

However, one should be aware of the danger granting the DBA roles to users (non-exhaustive list):

• When several or all users are granted the DBA role, hackers have more accounts to exploit, increasing the risk for data breaches;
• Users with the DBA role cannot only see their own data, but they can see ALL data within the database. Except for a privacy discussion, users/developers can also accidentally delete/modify other data;
• Last but not least, having the DBA role gives you the possibility to perform tasks which should be left in the hands of Database Administrators (DBA’s), like creating/deleting/blocking users, granting privileges to other users, create new or drop existing tables and indexes, etc…

Next to the bullets listed, the Oracle Patch Update of OCT 2020 shows how also a bug can help hackers to exploit users with the “DBA” role (see https://www.oracle.com/security-alerts/cpuoct2020.html#AppendixDB ):

The vulnerability exists due to a boundary error within the BZ2_decompress() function in decompress.c. A remote attacker can create a specially crafted archive, tricking the victim into opening it using the affected library, trigger out-of-bounds write and execute arbitrary code on the system.

What’s the difference between the DBA role and the SYSDBA system privilege?

The SYSDBA system privilege is the most powerfull administrative privilege to give to a user. With this system privilege the user can also start/stop a database and in worst case even remove the database. This role is also able to create a new spfile, change the archivelog-mode and allows the user to recover the database. Give this privilege only to the ones needed, or just leave it granted to the SYS user (which off-course has a complex password and is audited very precisely).

How can we find users with the DBA role?

After you’ve read the first sections, it should now be clear that giving the DBA role to the wrong users can be very very dangerous. You’ll now probably ask yourself how can we find which users already have this role in the database. Here is a query which will list all the users with the DBA role on your system:

select grantee from dba_role_privs where granted_role=’DBA’ and grantee not in (‘SYS’,’SYSTEM’) order by 1;

Now that you’ve got the list of all the users with the DBA role, you can start by listing the necessary privileges for each user. To make your life easy in this case, Oracle introduced the “Capture privilige” feature. This feature allows to capture real-time privileges and roles that were actually used on the system. You can then revoke unused grants and other changes to better reflect the access a user requires. Until Oracle 19c this functionality was part of the Oracle Database Vault option, so it should be only used with the appropriate license. From 19c onward this feature is now part of the Enterprise Edition and comes without the need of having a license for this option. Standard Edition users will need to user other techniques to find the necessary privileges.

Having troubles listing users with DBA role on your system? Want some help finding the necessary privileges? Need some help with the setup of the Oracle Capture Privilege feature? Our experts are here to help you. Want to go a step further and know how to increase security on your system? Our security assessment will provide you insights in what you could do better on database level.

Interested? Just ask us for help via phone: +32 3 450 67 89 or via e-mail: [email protected]

Some interesting links:

• Oracle Critical Patch Update Advisory – October 2020: https://www.oracle.com/security-alerts/cpuoct2020.html#AppendixDB