1. Creating Users
We will see in this article how to
create and configure a user database or an account that you can log in and
perform actions on the database functions of your rights will be allocated.
It is also good to know that unlike other databases, when you create a user on an Oracle database latter has no right to do this, refer to the section "Administration Roles and Privileges ".
Here are the steps that will be required to create an Oracle user:
It is also good to know that unlike other databases, when you create a user on an Oracle database latter has no right to do this, refer to the section "Administration Roles and Privileges ".
Here are the steps that will be required to create an Oracle user:
- Choose a username
- Choose an authentication method
- Select tablespaces that the user can use
- Set quotas on tablespaces EVERY
- Set the default tablespace of the user
- Create user
- Assign roles and privileges to the user
A schema is a collection (or set)
named objects such as tables, views, clusters, procedures and packages
associated with a specific user. When a user database is created, the schema is
automatically created. A user can then be associated with a single schema and
vice versa.
A user database will correspond to a
login that has received certain privileges. This user will be stored in the
data dictionary and have a storage space for objects that will be stored in the
schema.
In Oracle we can assimilate a user with its schema.
In Oracle we can assimilate a user with its schema.
The first thing to do to create a
new user will be to define a login. To avoid having too many problems when
adding new users, it is strongly recommended metttre naming strategy in place.
For example, all user names will be composed of the first 6 letters of their name, a "_" and the first letter of their first name.
For example:
Albert Durand give as durand_a login.
It is then necessary to know the limitations and naming rules to follow:
For example, all user names will be composed of the first 6 letters of their name, a "_" and the first letter of their first name.
For example:
Albert Durand give as durand_a login.
It is then necessary to know the limitations and naming rules to follow:
- Maximum size of 30 characters.
- Must contain only letters [az] and numbers [0-9]. All accented characters or other should be avoided. You can also use the symbols #, $, _.
- The login must start with a letter. If you want to use logins composed solely of numbers then you should surround your login between. "
Note: Care should be taken when
using the "Login as a Oracle will become sensitive.
"DURAND_D" is not the same as "durand_d."
"DURAND_D" is not the same as "durand_d."
To authenticate a user and define
the actions that will be able to perform on the database, the Oracle server
must be able to verify user access when it connects.
There are different types of authentication:
There are different types of authentication:
- The authentication database.
- Authentication by the operating system.
- Authentication by the network.
This mode, the most common is the
default mode. Clearly the user is authenticated with the password stored in the
database. This means that the database should be opened for are Features user
can connect.
To create a user authenticated by the database, you must use the IDENTIFIED BY clause <password>.
The password will default in cases begin with a letter, have a maximum length of 30 characters and can not be composed only of numbers, the alphabet, and the following symbols: #, _, $ (although Oracle does not recommend the use of # and $)
However, it is possible to override these conventions surrounding the password. "This will allow us to begin our passwords with numbers and use accented characters. It is good to know that Oracle recommends the Use single-byte character even if the database supports multi-byte characters.
Here is a simple example of creating a user authenticated by the database:
To create a user authenticated by the database, you must use the IDENTIFIED BY clause <password>.
The password will default in cases begin with a letter, have a maximum length of 30 characters and can not be composed only of numbers, the alphabet, and the following symbols: #, _, $ (although Oracle does not recommend the use of # and $)
However, it is possible to override these conventions surrounding the password. "This will allow us to begin our passwords with numbers and use accented characters. It is good to know that Oracle recommends the Use single-byte character even if the database supports multi-byte characters.
Here is a simple example of creating a user authenticated by the database:
CREATE USER scott IDENTIFIED BY tiger;
This command will then create a user
SCOTT with password tiger will. To connect it should use the following command
(after having given the necessary rights)
CONNECT scott / tiger @ <string <host;
This mode will allow Oracle to rely
on the user authentication by a third party or by the operating system. The
major advantage of this solution is that the user will then need to
authenticate once on its operating system.
However, this solution also brings security vulnerability because if the user forgets to log out of the machine, it will be very simple to connect to the database without having to provide a password. Oracle does not recommend using this authentication mode.
To create a user authenticated external way, you will use IDENTIFIED EXTERNALLY clause.
Warning: this command will require some configuration before Oracle parameters to function correctly.
We will take for the following examples:
However, this solution also brings security vulnerability because if the user forgets to log out of the machine, it will be very simple to connect to the database without having to provide a password. Oracle does not recommend using this authentication mode.
To create a user authenticated external way, you will use IDENTIFIED EXTERNALLY clause.
Warning: this command will require some configuration before Oracle parameters to function correctly.
We will take for the following examples:
- A local user Venkat
- A domain user Venkat galaxy
We will see how to configure the
server to allow authentication of the user by the operating system.
Then we will see how to configure the client.
Then we will see how to configure the client.
The first step is to set the
parameter value OS_AUTHENT_PREFIX (in the init.ora file). This parameter is
used to Oracle to be able to determine which users should be authenticated by a
third-party application.
We strongly advise to leave the default OPS $
We strongly advise to leave the default OPS $
Value
OS_AUTHENT_PREFIX
|
User
login
|
Remote
connection can
|
Os_
|
OS_DURAND_D
|
Not
|
No prefix
|
DURAND_D
|
Not
|
OPS $ (the default) or vacuum
|
OPS $ DURAND_D
|
Yes
|
The second step is to create a user
or domain local OS. For this please refer to the documentation for your
operating system.
The third step will depend on the type of user you want to allow system.
On Windows, if the user do not belong to a domain, you will need to add a value in your registry.
HKEY_LOCAL_MACHINE \ SOFTWARE \ ORACLE \ HOMEID (or id corresponds to the instance that will authenticate users using the system).
Add key OSAUTH_PREFIX_DOMAIN (type Expandable String Value) and give the value TRUE.
The fourth step is to check the value of the parameter in the sqlnet.ora file SQLNET.AUTHENTICATION_SERVICES. It must contain the value NTS.
The fifth step is to explicitly create the new user in the database.
It starts SQL * Plus
The third step will depend on the type of user you want to allow system.
On Windows, if the user do not belong to a domain, you will need to add a value in your registry.
HKEY_LOCAL_MACHINE \ SOFTWARE \ ORACLE \ HOMEID (or id corresponds to the instance that will authenticate users using the system).
Add key OSAUTH_PREFIX_DOMAIN (type Expandable String Value) and give the value TRUE.
The fourth step is to check the value of the parameter in the sqlnet.ora file SQLNET.AUTHENTICATION_SERVICES. It must contain the value NTS.
The fifth step is to explicitly create the new user in the database.
It starts SQL * Plus
SQLPLUS / nolog
Then performs the following actions
if the user is a local user OS:
- We connect as DBA
CONNECT system / <password>
- If the user is a local user on the command
launches
CREATE USER IDENTIFIED EXTERNALLY ops $ Venkat;
- He then gives the default rights
GRANT connect, resource TO ops $ Venkat;
If the user is an OS in a domain:
- We connect as DBA
CONNECT system / <password>
- If the user is a local user on the command
launches
CREATE USER "OPS $ GALAXY \ Venkat"
IDENTIFIED EXTERNALLY;
- He then gives the default rights
GRANT connect, resource TO "OPS $ GALAXY
\ Venkat";
Attention in the second case it will
be very important to the new user name in uppercase because we use quotes and
Oracle becomes sensitive. If you do not enter the login you should have
capitalized the error ORA-01017.
Over Unix, it is very important to prefix the $ a \ to prevent it from being interpreted as an environment variable.
Once these orders made the new user will be able to connect to the database using the command from the server.
Over Unix, it is very important to prefix the $ a \ to prevent it from being interpreted as an environment variable.
Once these orders made the new user will be able to connect to the database using the command from the server.
SQLPLUS /
As most of the time the average user
does not have direct access to the server. So here are the actions to take for
it to connect to the remote server while being authenticated by the OS.
The first thing to do will be to make the value of TRUE in the init.ora file REMOTE_OS_AUTHENT server. This setting will allow remote connections can connect to the server. If you forget to use this parameter to TRUE then you get the following errors:
The first thing to do will be to make the value of TRUE in the init.ora file REMOTE_OS_AUTHENT server. This setting will allow remote connections can connect to the server. If you forget to use this parameter to TRUE then you get the following errors:
ORA-01004: default username feature not
supported; logon denied
ORA-01988: remote os logon is not allowed
Then the rest of the configuration
is very simple as you just need to have a local account or a domain account on
the machine.
Check the value of the parameter in the sqlnet.ora file SQLNET.AUTHENTICATION_SERVICES. It must contain the value NTS.
Configure Net8 client to access the database
Login using the command:
Check the value of the parameter in the sqlnet.ora file SQLNET.AUTHENTICATION_SERVICES. It must contain the value NTS.
Configure Net8 client to access the database
Login using the command:
SQLPLUS / @ <string> connection>
He will now have to choose the field
of action of the new user.
Indeed, for obvious security reasons, we will restrict the scope of the user choosing the tablespaces that will be able to use.
Firstly, it is highly inadvisable to allow a user to use the SYSTEM tablespace, which must always remain dedicated to the data dictionary.
So we have to identify all the tablespaces required by the user, whether the data tablespaces, index, or temporary. There is no special syntax for this step.
Indeed, for obvious security reasons, we will restrict the scope of the user choosing the tablespaces that will be able to use.
Firstly, it is highly inadvisable to allow a user to use the SYSTEM tablespace, which must always remain dedicated to the data dictionary.
So we have to identify all the tablespaces required by the user, whether the data tablespaces, index, or temporary. There is no special syntax for this step.
Once the tablespaces identified, the
next step will be to define the space allocated to each user tablespaces.
Here are the options available for quotas:
Here are the options available for quotas:
- Size in K (KiloBytes) or M (MegaBytes)
- Unlimited
By default, the new user has a quota
on all tablespaces null.
Here is an example of assigning quotas on different TABLESPACES:
Here is an example of assigning quotas on different TABLESPACES:
...
QUOTA 5M ON QUOTA UNLIMITED ON TBSUSERS TMPUSERS
...
...
This step is absolutely essential,
it will define the data tablespace and temporary tablespace of the user. This
step is essential to avoid writing in the SYSTEM tablespace (which is assigned
if no default tablespace is not defined).
You must define these two tablespaces with DEFAULT TABLESPACE options for the tablespace and data TEMPORARY TABLESPACE for the temporary tablespace.
Here is an example of use:
You must define these two tablespaces with DEFAULT TABLESPACE options for the tablespace and data TEMPORARY TABLESPACE for the temporary tablespace.
Here is an example of use:
...
DEFAULT TEMPORARY TABLESPACE TABLESPACE
tbs_user tmp_user
...
Here is the complete structure of a
CREATE USER command:
 |
| Add caption |
Here is an explanation of all the keywords.
user:
Login future user (see section 1.2)
IDENTIFIED BY password:
Active authentication database with the specified password.
IDENTIFIED EXTERNALLY:
Enables authentication by the operating system
IDENTIFIED GLOBALLY AS 'external name':
Enables an LDAP external authentication.
DEFAULT TABLESPACE tablespace name:
TABLESPACE to assign a default data to the user.
TEMPORATY TABLESPACE tablespace name:
Can assign a default temporary tablespace for the user.
Options QUOTA ON tablespace name:
Sets the space quota assigned to the user on a specific tablespace.
PROFILE profile name:
Allows you to assign a profile limiting system resources for the user.
PASSWORD EXPIRE:
Allows you to expire the password of the user that it changes when the first connection.
ACCOUNT LOCK / UNLOCK:
Enable or disable a user account.
user:
Login future user (see section 1.2)
IDENTIFIED BY password:
Active authentication database with the specified password.
IDENTIFIED EXTERNALLY:
Enables authentication by the operating system
IDENTIFIED GLOBALLY AS 'external name':
Enables an LDAP external authentication.
DEFAULT TABLESPACE tablespace name:
TABLESPACE to assign a default data to the user.
TEMPORATY TABLESPACE tablespace name:
Can assign a default temporary tablespace for the user.
Options QUOTA ON tablespace name:
Sets the space quota assigned to the user on a specific tablespace.
PROFILE profile name:
Allows you to assign a profile limiting system resources for the user.
PASSWORD EXPIRE:
Allows you to expire the password of the user that it changes when the first connection.
ACCOUNT LOCK / UNLOCK:
Enable or disable a user account.
Here are some examples of creating
Oracle users:
CREATE USER Venkat
IDENTIFIED BY mypass1
DEFAULT TABLESPACE tbsusers
QUOTA 10M ON tbsusers
TEMPORARY TABLESPACE tmpusers
QUOTA 5M ON tmpusers
QUOTA 5M ON tools
PROFILE app_user
PASSWORD EXPIRE;
The user will have Venkat password mypass1.
Its default tablespace will tbsusers with a quota of 10MB, the temporary
tablespace is the tablespace tmpusers with a quota of 5M. It will also have the
opportunity to use the TABLESPACE tools with a quota of 5MB.
He must change his password at login and will receive app_user profile.
He must change his password at login and will receive app_user profile.
CREATE USER "OPS $ GALAXY \ Venkat"
IDENTIFIED BY mypass1
DEFAULT TABLESPACE tbsusers
QUOTA 10M ON tbsusers
TEMPORARY TABLESPACE tmpusers
QUOTA 5M ON tmpusers
QUOTA 5M ON tools
PROFILE app_user
PASSWORD EXPIRE;
The user will have the same
attributes as the previous example but it will be authenticated by the
operating system. In addition it is important to know that only the user's
domain GALAXY Venkat can connect with the new user and the local user Venkat
can not use this new account.
If you want to change the password
of a user here is the command that you should use.
ALTER USER <user login> IDENTIFIED BY
<new password>
This command will change the
password of a user. If you have used the verification passwords provided by
Oracle (UTLPWDMG.SQL script) or you make your own, you'll need to use an additional
option: REPLACE option with the old password so that it can be passed to the
verification password.
Example:
Example:
ALTER USER <user login> IDENTIFIED BY
<new password> REPLACE <old password>
For DBAs:
You can sometimes, as a DBA, being confronted with the type of problem:
Have to use another user's account without changing the password. (For example, this user has a procedure that can be initiated by the user because the condition was coded in the procedure).
Here is a method that will allow you to take the place of this user without changing his password.
You can sometimes, as a DBA, being confronted with the type of problem:
Have to use another user's account without changing the password. (For example, this user has a procedure that can be initiated by the user because the condition was coded in the procedure).
Here is a method that will allow you to take the place of this user without changing his password.
- It connects DBA
sqlplus system / <password> @
<connection string>
- It is an export text information that you
want the user to take the place
SPOOL c: \ saveuser.sql
SELECT 'ALTER USER' | | username | |
'IDENTIFIED BY VALUES''' | | password | |'' ';'
FROM dba_users
WHERE username = '<Login of user>';
SPOOL OFF
- We change the pass word
ALTER USER <login the user> IDENTIFIED BY
tata;
- It connects as another user
CONNECT <login the user> / tata
- We do our actions
...
- We execute the SQL contained in saveuser.sql
ALTER USER <login the user> IDENTIFIED BY
VALUES '<encrypted password>';
This method allows you to take up a
temporary user without knowing their password and without it being visible to
the user. The IDENTIFIED BY VALUES option is one of several hidden commands of
Oracle.
The syntax for changing a user's
quota is not different from the syntax used when creating the user.
However, it is important to note that if a user had 1MB quota on a tablespace and that he had used half with tables, and if you change it to 0MB its quota on this tablespace that does not imply that its tables are emptied or deleted, but its sinifiera tables will not be able to allocate additional extents.
Example:
However, it is important to note that if a user had 1MB quota on a tablespace and that he had used half with tables, and if you change it to 0MB its quota on this tablespace that does not imply that its tables are emptied or deleted, but its sinifiera tables will not be able to allocate additional extents.
Example:
ALTER USER scott QUOTA 15M ON QUOTA 0M ON tbsusers
tools;
The syntax is different from the
syntax for creating. This command allows you to change the default TABLESPACES
user. This will not move the objects TABLESPACES old.
Example:
Example:
ALTER USER scott DEFAULT TEMPORARY TABLESPACE
TABLESPACE tbsusers2 tmp2;
For safety reasons, it can sometimes
be useful to lock the user account to prevent it being used fraudulently. This
method can also be used to organize objects in a schema without that we can use
the user.
Here is an example of enabling and locking a user account:
Here is an example of enabling and locking a user account:
- Account Lockout
ALTER USER scott ACCOUNT LOCK;
- Account Activation
ALTER USER scott ACCOUNT UNLOCK;
Here is the complete structure of a
command ALTER USER:
 |
| usermanagement |
Here is an explanation of all the
keywords.
user:
Login future user
IDENTIFIED BY password:
Active authentication database with the specified password.
IDENTIFIED EXTERNALLY:
Enables authentication by the operating system
IDENTIFIED GLOBALLY AS 'external name':
Enables an LDAP external authentication.
DEFAULT TABLESPACE tablespace name:
TABLESPACE to assign a default data to the user.
TEMPORATY TABLESPACE tablespace name:
Can assign a default temporary tablespace for the user.
Options QUOTA ON tablespace name:
Sets the space quota assigned to the user on a specific tablespace.
PROFILE profile name:
Allows you to assign a profile limiting system resources for the user.
PASSWORD EXPIRE:
Allows you to expire the password of the user that it changes when the first connection.
ACCOUNT LOCK / UNLOCK:
Enable or disable a user account.
DEFAULT ROLE:
These options will allow to define when editing a user his or her default roles. These roles will be activated when the user login.
We will not discuss here the proxy clause.
user:
Login future user
IDENTIFIED BY password:
Active authentication database with the specified password.
IDENTIFIED EXTERNALLY:
Enables authentication by the operating system
IDENTIFIED GLOBALLY AS 'external name':
Enables an LDAP external authentication.
DEFAULT TABLESPACE tablespace name:
TABLESPACE to assign a default data to the user.
TEMPORATY TABLESPACE tablespace name:
Can assign a default temporary tablespace for the user.
Options QUOTA ON tablespace name:
Sets the space quota assigned to the user on a specific tablespace.
PROFILE profile name:
Allows you to assign a profile limiting system resources for the user.
PASSWORD EXPIRE:
Allows you to expire the password of the user that it changes when the first connection.
ACCOUNT LOCK / UNLOCK:
Enable or disable a user account.
DEFAULT ROLE:
These options will allow to define when editing a user his or her default roles. These roles will be activated when the user login.
We will not discuss here the proxy clause.
We will now see how to delete a user
and schema.
It is important to note that a user currently connected to the database will not be deleted.
For example:
It is important to note that a user currently connected to the database will not be deleted.
For example:
SQL> DROP USER scott CASCADE;
DROP USER scott CASCADE
*
ERROR at line 1:
ORA-01940: Can not delete a user who is logged
For this method we will just use the
DROP USER.
This method is the default method. You just check the user's schema is empty when running this command. Otherwise it will result in the following error:
This method is the default method. You just check the user's schema is empty when running this command. Otherwise it will result in the following error:
SQL> DROP USER scott;
DROP USER scott
*
ERROR at line 1:
ORA-01922: CASCADE to specify to remove
'SCOTT'
This error is due to the fact that
Oracle does not delete the objects within the schema of the user that you must
use the CASCADE option in the next chapter.
Here is an example of deleting a user with empty schema:
Here is an example of deleting a user with empty schema:
SQL> DROP USER test;
Deleted user.
3.2.
Deleting a user with a pattern
When you delete a user and all its
schema objects, you must use the CASCADE option.
This option will automatically delete all the objects in the user's schema.
If the schema contains tables, Oracle will automatically erase all the integrity constraints for tables and all integrity constraints in the patterns of other users who made references to UNIQUE and PRIMARY KEY constraints of the schema that is being deleted .
Oracle also remove all indexes associated with the table columns, as well as any triggers, data types (with the FORCE option).
Oracle invalidate but not delete objects in other schema but the schema references were deleted.
By cons, Oracle will not delete user-created roles.
Here is an example of deleting a user and the schema:
This option will automatically delete all the objects in the user's schema.
If the schema contains tables, Oracle will automatically erase all the integrity constraints for tables and all integrity constraints in the patterns of other users who made references to UNIQUE and PRIMARY KEY constraints of the schema that is being deleted .
Oracle also remove all indexes associated with the table columns, as well as any triggers, data types (with the FORCE option).
Oracle invalidate but not delete objects in other schema but the schema references were deleted.
By cons, Oracle will not delete user-created roles.
Here is an example of deleting a user and the schema:
SQL> DROP USER scott CASCADE;
Deleted user.
Here is the complete structure of a
DROP USER command:
Here is an explanation of all the keywords.
user:
User login to remove.
CASCADE:
Deletes the contents of the user's schema will be deleted.
user:
User login to remove.
CASCADE:
Deletes the contents of the user's schema will be deleted.
Here we will see the different views
that will be useful for obtaining information about users that we have created.
Views that will be useful are the views and DBA_USERS DBA_TS_QUOTAS (or views that contain information USER_USERS current user and USER_TS_QUOTAS that contain information about the current user quotas).
Views that will be useful are the views and DBA_USERS DBA_TS_QUOTAS (or views that contain information USER_USERS current user and USER_TS_QUOTAS that contain information about the current user quotas).
0 comments:
Post a Comment