36 | How to Create New User & Grant Privileges and Managing Roles | Oracle PL/SQL Programming

Описание: CREATE USER
Purpose

Use the CREATE USER statement to create and configure a database user, which is an account through which you can log in to the database, and to establish the means by which Oracle Database permits access by the user.

You can enable a user to connect to the database through a proxy application or application server. For syntax and discussion, refer to ALTER USER.

Prerequisites

You must have the CREATE USER system privilege. When you create a user with the CREATE USER statement, the user's privilege domain is empty. To log on to Oracle Database, a user must have the CREATE SESSION system privilege. Therefore, after creating a user, you should grant the user at least the CREATE SESSION system privilege. Please refer to GRANT for more information.

Syntax

create_user::=
Semantics

user

Specify the name of the user to be created. This name can contain only characters from your database character set and must follow the rules described in the section "Schema Object Naming Rules". Oracle recommends that the user name contain at least one single-byte character regardless of whether the database character set also contains multibyte characters.

IDENTIFIED Clause

The IDENTIFIED clause lets you indicate how Oracle Database authenticates the user.

BY password

The BY password clause lets you creates a local user and indicates that the user must specify password to log on to the database. Passwords can contain only single-byte characters from your database character set regardless of whether the character set also contains multibyte characters.

Passwords must follow the rules described in the section "Schema Object Naming Rules", unless you are using the Oracle Database password complexity verification routine. That routine requires a more complex combination of characters than the normal naming rules permit. You implement this routine with the UTLPWDMG.SQL script, which is further described in Oracle Database Security Guide.

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

create user username identified by "password";
So to create the user data_owner with the password Supersecurepassword!, use:

create user data_owner identified by "Supersecurepassword!";
Now you’ve got your user. The next step is to connect to it. But try to do so and you’ll hit:

conn data_owner/Supersecurepassword!

ORA-01045: user DATA_OWNER lacks CREATE SESSION privilege; logon denied
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:

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

grant create session to data_owner;
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:

grant create table to data_owner;
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:

conn data_owner /Supersecurepassword!

36 | How to Create New User & Provide Privileges and Roles in ORACLE SQL | Oracle PL/SQL Programming

36 | How to Create New User & Grant Privileges and Managing Roles | Oracle PL/SQL Programming

#create_new_user #grant_privileges #managing_role