oracle assign dba role to user

DBA Genesis

Start your DBA Career Today

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

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

Oracle REGEXP Functions

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.