Advertisements

TechOnTheNet Logo

  • Oracle / PLSQL
  • Web Development
  • Color Picker
  • Programming
  • Techie Humor

clear filter

Oracle Basics

  • AND & OR
  • COMPARISON OPERATORS
  • IS NOT NULL
  • REGEXP_LIKE

down caret

Oracle Advanced

  • Alter Table
  • Alter Tablespace
  • Change Password
  • Check Constraints
  • Comments in SQL
  • Create Schema
  • Create Schema Statement
  • Create Table
  • Create Table As
  • Create Tablespace
  • Create User
  • Declare Variables
  • Drop Tablespace
  • Error Messages
  • Find Default Tablespace
  • Find Users Logged In
  • Find Version Information
  • Global Temporary
  • Grant/Revoke Privileges
  • Local Temporary
  • Primary Keys
  • Set Default Tablespace
  • System Tables
  • Unique Constraints

Oracle Cursors

  • Close Cursor
  • Cursor Attributes
  • Declare Cursor
  • Fetch Cursor
  • Open Cursor
  • Select For Update
  • Where Current Of

Oracle Exception Handling

  • Named Programmer-Defined Exception
  • Named System Exception
  • WHEN OTHERS Clause

Oracle Foreign Keys

  • Disable Foreign Key
  • Drop Foreign Key
  • Enable Foreign Key
  • Foreign Key
  • Foreign Key (cascade delete)
  • Foreign Key (set null delete)

Oracle Loops/Conditionals

  • CURSOR FOR LOOP
  • IF-THEN-ELSE
  • REPEAT UNTIL LOOP

Oracle Transactions

  • Commit Transaction
  • Rollback Transaction
  • Set Transaction

Oracle Triggers

  • After Delete Trigger
  • After Insert Trigger
  • After Update Trigger
  • Before Delete Trigger
  • Before Insert Trigger
  • Before Update Trigger
  • Disable All Triggers
  • Disable Trigger
  • Drop Trigger
  • Enable All Triggers
  • Enable Trigger

String/Char Functions

  • Concat with ||
  • REGEXP_INSTR
  • REGEXP_REPLACE
  • REGEXP_SUBSTR

Numeric/Math Functions

  • REGEXP_COUNT
  • ROUND (numbers)
  • TRUNC (numbers)

Date/Time Functions

  • CURRENT_DATE
  • CURRENT_TIMESTAMP
  • LOCALTIMESTAMP
  • MONTHS_BETWEEN
  • ROUND (dates)
  • SESSIONTIMEZONE
  • SYSTIMESTAMP
  • TRUNC (dates)

Conversion Functions

  • CHARTOROWID
  • NUMTODSINTERVAL
  • NUMTOYMINTERVAL
  • TO_DSINTERVAL
  • TO_MULTI_BYTE
  • TO_SINGLE_BYTE
  • TO_TIMESTAMP
  • TO_TIMESTAMP_TZ
  • TO_YMINTERVAL

Analytic Functions

  • FIRST_VALUE

Advanced Functions

  • CARDINALITY
  • SYS_CONTEXT

totn Oracle / PLSQL

Oracle / PLSQL: Roles

This Oracle tutorial explains how to create roles, grant/revoke privileges to roles, enable/disable roles, set roles as the default, and drop roles in Oracle with syntax and examples.

Description

A role is a set or group of privileges that can be granted to users or another role. This is a great way for database administrators to save time and effort.

Create Role

You may wish to create a role so that you can logically group the users' permissions. Please note that to create a role, you must have CREATE ROLE system privileges.

The syntax for creating a role in Oracle is:

  • If both NOT IDENTIFIED and IDENTIFIED are omitted in the CREATE ROLE statement, the role will be created as a NOT IDENTIFIED role.

Let's look at an example of how to create a role in Oracle.

For example:

This first example creates a role called test_role .

This second example creates the same role called test_role , but now it is password protected with the password of test123.

Grant TABLE Privileges to Role

Once you have created the role in Oracle, your next step is to grant privileges to that role.

Just as you granted privileges to users , you can grant privileges to a role. Let's start with granting table privileges to a role. Table privileges can be any combination of SELECT, INSERT, UPDATE, DELETE, REFERENCES, ALTER, INDEX, or ALL.

The syntax for granting table privileges to a role in Oracle is:

The privileges to assign to the role. It can be any of the following values:

Let's look at some examples of how to grant table privileges to a role in Oracle.

For example, if you wanted to grant SELECT, INSERT, UPDATE, and DELETE privileges on a table called suppliers to a role named test_role , you would run the following GRANT statement:

You can also use the ALL keyword to indicate that you wish all permissions to be granted. For example:

Revoke Table Privileges from Role

Once you have granted table privileges to a role, you may need to revoke some or all of these privileges. To do this, you can execute a revoke command. You can revoke any combination of SELECT, INSERT, UPDATE, DELETE, REFERENCES, ALTER, INDEX, or ALL.

The syntax for revoking table privileges from a role in Oracle is:

The privileges to revoke from the role. It can be any of the following values:

Let's look at some examples of how to revoke table privileges from a role in Oracle.

For example, if you wanted to revoke DELETE privileges on a table called suppliers from a role named test_role , you would run the following REVOKE statement:

If you wanted to revoke ALL privileges on the table called suppliers from a role named test_role , you could use the ALL keyword. For example:

Grant Function/Procedure Privileges to Role

When dealing with functions and procedures, you can grant a role the ability to EXECUTE these functions and procedures.

The syntax for granting EXECUTE privileges on a function/procedure to a role in Oracle is:

Let's look at an example of how to grant EXECUTE privileges on a function or procedure to a role in Oracle.

For example, if you had a function called Find_Value and you wanted to grant EXECUTE access to the role named test_role , you would run the following GRANT statement:

Revoke Function/Procedure Privileges from Role

Once you have granted EXECUTE privileges on a function or procedure to a role, you may need to revoke these privileges from that role. To do this, you can execute a REVOKE command.

The syntax for the revoking privileges on a function or procedure from a role in Oracle is:

If you wanted to revoke EXECUTE privileges on a function called Find_Value from a role named test_role , you would run the following REVOKE statement:

Grant Role to User

Now, that you've created the role and assigned the privileges to the role, you'll need to grant the role to specific users.

The syntax to grant a role to a user in Oracle is:

Let's look at an example of how to grant a role to a user in Oracle:

This example would grant the role called test_role to the user named smithj .

Enable/Disable Role (Set Role Statement)

To enable or disable a role for a current session, you can use the SET ROLE statement.

When a user logs into Oracle, all default roles are enabled, but non-default roles must be enabled with the SET ROLE statement.

The syntax for the SET ROLE statement in Oracle is:

Let's look at an example of how to enable a role in Oracle.

This example would enable the role called test_role with a password of test123.

Set role as DEFAULT Role

A default role means that the role is always enabled for the current session at logon. It is not necessary to issue the SET ROLE statement. To set a role as a DEFAULT ROLE, you need to issue the ALTER USER statement.

The syntax for setting a role as a DEFAULT ROLE in Oracle is:

Let's look at an example of how to set a role as a DEFAULT ROLE in Oracle.

This example would set the role called test_role as a DEFAULT role for the user named smithj .

This example would set all roles assigned to smithj as DEFAULT.

This example would set all roles assigned to smithj as DEFAULT, except for the role called test_role .

Once a role has been created in Oracle, you might at some point need to drop the role.

The syntax to drop a role in Oracle is:

Let's look at an example of how to drop a role in Oracle.

This DROP statement would drop the role called test_role that we defined earlier.

previous

Home | About Us | Contact Us | Testimonials | Donate

While using this site, you agree to have read and accepted our Terms of Service and Privacy Policy .

Copyright © 2003-2024 TechOnTheNet.com. All rights reserved.

oracle database assign role to user

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

Related Posts

oracle database assign role to user

Add paragraph text. Click “Edit Text” to customize this theme across your site. You can update and reuse text themes.

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

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

oracle database assign role to user

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?

Gorilla hands typing on a laptop with a banana next to them

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.

An app connecting to the database as a user which has execute privileges on a PL/SQL function in another schema

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 and rows within them. 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!

Four metal doors on a red wall

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.

The back of a security guard going down an escalator

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 .

UPDATE 3 Jan 2024 : Adding "...and rows within them" to "Granting select allows users to lock tables"; HT Dan Morgan.

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 and rows within them. 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;

UPDATE 3 Jan 2024 : Adding &quot;...and rows within them&quot; to &quot;Granting select allows users to lock tables&quot;; HT Dan Morgan.

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

Home » Oracle Database Administration » Oracle ALTER USER

Oracle ALTER USER

Summary : in this tutorial, you will learn how to use the Oracle ALTER USER statement to modify the authentication or database resource of a database user.

The ALTER USER statement allows you to change the authentication or database resource characteristics of a database user.

Generally speaking, to execute the ALTER USER statement, your account needs to have the ALTER USER system privilege. However,  you can change your own password using the ALTER USER statement without having the ALTER USER system privilege.

Let’s create a user named dolphin and grant the CREATE SESSION system privilege to dolphin :

1) Using Oracle ALTER USER statement to change the password for a user

The following example uses the ALTER USER statement to change the password for the user dolphin :

Log in to the Oracle Database using the dolphin user:

The user dolphin should be able to authenticate to the Oracle Database using the new password xyz123

2) Using Oracle ALTER USER statement to lock/unlock a user

This example uses the ALTER USER statement to lock the user dolphin :

If you use the user dolphin to log in to the Oracle Database, you should see a message indicating that the user is locked:

To unlock the user dolphin , you use the following statement:

Now, the user dolphin should be able to log in to the Oracle Database.

3) Using Oracle ALTER USER statement to set the user’s password expired

To set the password of the user dolphin expired, you use the following statement:

When you use the user dolphin to log in to the database, Oracle issues a message indicating that the password has expired and requests for the password change as follows:

4) Using Oracle ALTER USER statement to set the default profile for a user

This statement returns the profile of the user dolphin :

When you create a new user without specifying a profile, Oracle will assign the DEFAULT profile to the user.

Let’s create a new user profile called ocean :

and assign it to the user dolphin :

Now, the default profile of the user dolphin is ocean .

5) Using Oracle ALTER USER statement to set default roles for a user

Currently, the user dolphin has no assigned roles as shown in the output of the following query when executing from the dolphin’s session:

First, create a new role  called rescue from the user OT ‘s session:

Second, grant this role to dolphin :

Third, use the user dolphin to log in to the Oracle Database. The default role of the user dolphin is rescue now.

Here is the output:

Fourth, create another role called super and grant all privileges to this role:

Fifth, grant the role super to the user dolphin :

Sixth, set the default role of the user dolphin to super :

Seventh, disconnect the current session of the user dolphin and log in to the Oracle Database again. The default role of the user dolphin should be super as shown in the output of the following query:

The following shows the output:

In this tutorial, you have learned how to use the Oracle ALTER USER to change the authentication or database resource of a database user.

  • Reset Password
  • SQL DBA Jobs

IT Tutorial IT Tutorial | Oracle DBA | SQL Server, Goldengate, Exadata, Big Data, Data ScienceTutorial

Oracle roles & privileges.

Mehmet Salih Deveci June 9, 2021 Leave a comment

I will explain Oracle Roles & Privileges in this post.

Oracle Roles

If lots of types of users are using the database, you need to classfied the users with the Roles. Because If you use the Roles, then you won’t grant the users one by one, you will only grant the roles not users. When you change the privilige of Role, all users using this role will be effected.

Oracle Roles are used to localize the administration of objects. Oracle roles are most helpful when large numbers of users will need the same system and object privileges

The syntax for creating a role in Oracle is as follows.

The following examples are Oracle-defined roles:

  • CONNECT is a role that Enterprise Manager automatically grants to a user when you create a user as shown in “Creating Users”. This role has the CREATE SESSION privilege.
  • RESOURCE extends the privileges of a user beyond those granted by the CONNECT role. It includes CREATE PROCEDURE, CREATE TRIGGER, and other system privileges.
  • DBA is the standard role that can be granted by an administrator to another administrator. It includes all system privileges and should only be granted to the most trusted and qualified of users. Assigning this role to a user enables the user to administer the database.

The syntax for granting table privileges to a role in Oracle is:

The syntax for revoking table privileges from a role in Oracle is:

Roles are created and managed by DBAs .

A definition of privilige that can be assigned to users or other roles .

It facilitates compliance and reporting of rules on who , what and how .

Role Creation in Oracle

Now let’s create a role for developers :

oracle database assign role to user

Our role is now ready to grant :

Give the priviliges to the role as follows.

oracle database assign role to user

Now let’s give this role to the user named mdrn that we created before and after that mdrn user will have all grants or privileges to be given to developer_role .

oracle database assign role to user

For example; You want to create the read_only_users role and all reporting users are granted with this role.

You can create this role and grant any privilige to this role as follows.

If you want to learn more details about Grant & Priviliges in Oracle, read the following post.

How to Grant and Revoke Privileges | Create and Drop any User in Oracle Database

Do you want to learn Oracle Database for Beginners, then Click and read the following articles.

Oracle Database Tutorials for Beginners ( Junior Oracle DBA )

About Mehmet Salih Deveci

oracle database assign role to user

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Managing Users and Roles Using the Identity Console

This tutorial covers how to manage users and roles using the Identity Console.

Time to Complete

Approximately 40 minutes

Introduction

The Oracle Cloud is based on the Oracle Identity Manager product. This enables you to add and remove users, grant them permissions related to administration and usage of the service, and connect credentials across multiple instances you have purchased.

In this tutorial, you review your own user profile, change your password, set your security questions and review your current roles. In addition, you create a new user and assign the user to a variety of roles at different times to see the differences between them.

Prerequisites

Before starting this tutorial, you should have performed the following tutorials:

  • Signing Up for a Database Cloud Service
  • Installing an Application into Your Database Cloud Service

Accessing the Identity Console

In order to access the Identity Console for your service, login to your Identity Domain. Perform the following steps:

Open a browser and enter the following URL:

http://cloud.oracle.com

Click Sign In .

oracle database assign role to user

Make sure the correct Data Center for your service is selected and click Sign In to My Services .

oracle database assign role to user

If you receive the login window, enter your User ID, Password and the Identity Domain name you created when you created the service. Then click Sign In .

oracle database assign role to user

Your service is displayed. In the next section, you login to the Identity Console and review your profile. Click Identity Console .

oracle database assign role to user

Your need to login to the Identity Console the first time. Enter your credentials and click Sign In .

Updating Your User Profile

In this section, you review your user profile in the Identity Console. Perform the following steps:

Review your information. To change your password, expand the Change Password section..

oracle database assign role to user

Enter your existing password and then a new password for the fields presented based on the policy rules and click Apply .

oracle database assign role to user

Your password was changed successfully. Click OK .

oracle database assign role to user

It is important to setup challenge questions so that you can identify yourself if you forget your password. Expand the Challenge Questions section and select one of the questions from the drop down.

oracle database assign role to user

Once you have all the questions and answers specified, click Apply .

oracle database assign role to user

The Challenge questions have been saved. Click OK .

oracle database assign role to user

Reviewing Your Roles

Roles give users privileges within a service. In this section, you review the roles that have been assigned to you. Perform the following steps:

Under the My Profile option, select My Roles .

oracle database assign role to user

To see all the roles available, enter * in the search area and click Search .

oracle database assign role to user

A list of roles is displayed.

oracle database assign role to user

On this page, you can change the columns that are displayed, and you can also reorder the columns. In the next section, you will create a new user.

oracle database assign role to user

Creating a New User

You are now ready to create a new user. Note only Identity domain Administrators can create users. Both Identity domain Administrators and Service Administrators can assign or revoke roles. Perform the following steps:

Under Administration, select Manage Users .

oracle database assign role to user

Click Create .

oracle database assign role to user

Enter the information about the user you want to create and click Create .

oracle database assign role to user

The user was created successfully. Click OK .

oracle database assign role to user

To see a list of all the users in this sevice, enter ' * ' in the search area and click Search .

oracle database assign role to user

A list of users in this service are displayed. Select the Last Name of the user you just created.

oracle database assign role to user

The User Details are displayed. Notice that there are no roles specified. In the next section, you assign a role to the user that will allow them to manage the service. Click Close .

oracle database assign role to user

Assigning Roles to Users

To assign a role to a user, perform the following steps:

Under Administration, select Manage Roles .

oracle database assign role to user

To see all the available roles, enter ' * ' in the search area and click Search .

oracle database assign role to user

Review the list of Roles. The Identity Domain Administrator will see all roles for all services in the identity domain, including the TenantAdminGroup role (which is not service specific). The Service Administrator will see all roles related to the services the Service Administrator manages in the identity domain (so the TenantAdminGroup role is not visible).

Select database Database Administrator from the list and click Assign .

oracle database assign role to user

Enter your new user name or a portion of the name in the search area and click Search .

oracle database assign role to user

Select your user from the list and click Assign .

oracle database assign role to user

The role was assigned to the user successfully. Click OK . In the next section, you review the roles assigned to a user.

oracle database assign role to user

Launching a Service as the New User

Now that you have setup your user in the Identity Console, you want to login as the new user and launch the service. Perform the following steps:

Navigate to the Oracle Public Cloud Home page and click Sign In .

Note: you may need to delete your browser cache/cookies to correctly login as the new user.

This time you want to enter the new User ID and password of the user you just created with the same Identity Domain name as before and click Sign In .

oracle database assign role to user

The first time you login as the new user, you will be prompted to change your password and enter challenge questions. Enter the information and click Submit .

oracle database assign role to user

Notice that you see your service in the list. Click the Launch Service icon.

oracle database assign role to user

You are now logged in to Application Express for your Service.

oracle database assign role to user

Accessing the Identity Console as the New User

The new user can view and change their information in the Identity Console but because they do not have the TenantAdminGroup role, they will not be able to add or modify other users in this service. Perform the following steps:

In the My Services window, click Identity Console .

oracle database assign role to user

You are automatically logged into the Identity Console as the same user which is the new user you created. Select the Manage Users link.

oracle database assign role to user

Enter ' * ' in the search area and click Search .

oracle database assign role to user

The list of users is displayed. Notice that the Create/Modify buttons are not displayed because you are not allowed to add or modify users.

Select Sign Out . In the next section, you change the role of the user to db_developer to see the differences between the roles.

oracle database assign role to user

Changing the Role to a Developer

You want to change the role of the new user from db_administrator to db_developer to see what effect it has. Perform the following steps:

Login with the user name and password you used to create the Identity Domain and click Sign In .

Under Administration, select the Manage Roles link.

oracle database assign role to user

You need to revoke the database Database Administrator role and then assign the database Database Developer role. Select the database Database Administrator role from the list and click Revoke .

oracle database assign role to user

Enter ' Nancy ' in the search area and click Search .

oracle database assign role to user

Select the user you added from the list and click Revoke .

oracle database assign role to user

The database Database Administrator role was revoked successfully. Click OK .

oracle database assign role to user

Now you can assign the database Database Developer role. Select database Database Developer from the list of roles and click Assign .

oracle database assign role to user

Enter Nancy in the search area and click Search .

oracle database assign role to user

Select the user you added from the list and click Assign .

oracle database assign role to user

The database Database Developer role was assigned successfully. Click OK . In the next section, you again access the service as the new user but with the database Database Developer role.

oracle database assign role to user

Launching a Service as a Developer

This time, you want to launch the service as a Database Developer. Perform the following steps:

Enter the user name and password of the user you just assigned the database Database Developer role and click Sign In .

This time the service is not listed. Note that for General Availability a user with database Database Developer role will see the service and be able to launch it from this interface.

oracle database assign role to user

To access the Database Cloud Service directly, enter the URL as follows:

http://<service-name>-<identity-domain>.db.cloud.oracle.com/apex

where <service-name> is the name of the Database Cloud Service you created and <identity-domain> is the Identity Domain the service is a part of

You receive the Sign In window. Enter the information for the user you just assigned the db_developer role and click Sign In .

The Application Express Home page is displayed. At this point, applications may be developed and run. In the next section you change the role for the user you created from db_developer to db_user to see the differences.

oracle database assign role to user

Changing the Role to an End User

You want to change the role of the new user from db_developer to db_user to see what affect it has. Perform the following steps:

Enter your User ID and password and the same Identity Domain name as before and click Sign In .

Click Identity Console .

oracle database assign role to user

Enter * in the search area and click Search .

oracle database assign role to user

You need to revoke the database Database Developer role and then assign the database Database User role. Select the database Database Developer role from the list and click Revoke .

oracle database assign role to user

Enter ' Nancy ' or the username in the search area and click Search .

oracle database assign role to user

Select the user and click Revoke .

oracle database assign role to user

The database Database Developerrole was revoked successfully. Click OK .

oracle database assign role to user

Now you can assign the database Database User role. Select database Database User from the list of roles and click Assign .

oracle database assign role to user

The database Database User role was assigned successfully. Click OK . In the next section, you again access the service as the new user but with the database Database User role.

oracle database assign role to user

Running an Application as an End User

As an End User, you will only be able to run the applications developed by the administrator or developers. Perform the following steps:

Enter the following URL to access an installed packaged application or a developed database application or websheet, the developer would need to give you the specific URL. It would be something similar to the following:

http://<service-name>-<identity-domain>.db.cloud.oracle.com/apex//f?p=<appid>

You can find the URL by following the instructions in the Finding Direct URLs to Give to Various Users section.

oracle database assign role to user

If an end user tries to access the Application Express Development Environment as you did when you had the Database Developer role, the result would be as follows. The URL would be something like the following:

oracle database assign role to user

Finding Direct URLs to Give to Various Users

In this section, you learn how to find the URL you need to give to your developers to develop in Application Express and also how to find the URL you can give to end users so they can access the applications you have built and installed in your Database Cloud Service. Perform the following steps:

Navigate to the Oracle Public Cloud Home Page and select My Services .

oracle database assign role to user

Sign in as a user who has the database Database Administrator role.

oracle database assign role to user

Click the Details icon.

oracle database assign role to user

The value for the host is what you want to send to your developers so they can access Application Express in your database cloud service.

oracle database assign role to user

In addition, you can find the URL for packaged applications that you have installed by clicking the Applications tab.

oracle database assign role to user

Right click the Run button and select Copy link address to get the URL to run this application in a separate window. This URL is what you would give to an end user to run this application.

oracle database assign role to user

In this tutorial, you have learned how to:

  • Access the identity console
  • Update your user profile
  • Review your roles
  • Create a new user
  • Assign roles to users
  • Review roles assigned to a user
  • Launch a service as the new user
  • Access the identity console as the new user
  • Change the role to a developer
  • Launch a service as the developer
  • Change the role to an end user
  • Run an application as an end user
  • Find direct URLS to give to various users
  • Oracle Cloud Home Page
  • To learn more about Oracle Application Express, refer to additional OBEs in the Oracle Learning Library

To help navigate this Oracle by Example, note the following: Hiding Header Buttons: Click the Title to hide the buttons in the header. To show the buttons again, simply click the Title again. Topic List Button: A list of all the topics. Click one of the topics to navigate to that section. Expand/Collapse All Topics: To show/hide all the detail for all the sections. By default, all topics are collapsed Show/Hide All Images: To show/hide all the screenshots. By default, all images are displayed. Print: To print the content. The content currently displayed or hidden will be printed.

To navigate to a particular section in this tutorial, select the topic from the list.

Oracle DB: Create New Users, Assign Roles, Privileges, and Table Spaces

How To Create New Users, Assign Tables, And Grant Privileges on Oracle Database

1 minute read

Thilina Ashen Gamage

Thilina Ashen Gamage

  • --> Colombo, Sri Lanka -->