User and Rights Management

Users and permissions are the most basic security management mechanisms for Oracle database systems.

When a user logs on to Oracle via Sql*plus or an application, a bulge, the user name and password, must be provided.
You must also have certain permissions to complete the operation after you log in.
What permissions the user has to complete the operation.

If the user wants to access the database, the database administrator should first assign it an account, and then assign it a certain permission so that the user can complete the operation.
The database administrator can also control the user's use of passwords and system resources through profile.

User Management

a user is a database object and is a generic term for a series of database objects and permissions .

user-owned operations are performed by default in their own mode , which is a collection of database objects owned by a user, each user has its own mode , and the user is one by one corresponding to the pattern, with the same name as the user name.
For example, the Scott user's mode is Scott,
In this schema, all database objects owned by the user Scott are included, including tables, views, indexes, stored programs, and so on.
The user's database objects and data are distributed in a table space, with each user having a default tablespace.

The default tablespace is specified when the user is created, and if not specified, the system tablespace is specified as the user's default tablespace.

Which users are in the database

There are three types of users in Oracle database, one is SYSDBA, the other is Sysoper, and the third is ordinary users .

which
The first two classes of users are called privileged users , and they have all the permissions on all database objects, including the database itself .

SYS users have both SYSDBA and sysoper two permissions, which are generated automatically when the database is created and do not need to be created manually.
A privileged user's password is stored in the database, on the other hand, in the password file.

If SYSDBA or Sysoper permissions are specified for a normal user, the user will also become a privileged user.
information about privileged users can be obtained from the dynamic performance view V$pwfile_users .
A user can log on to the database server as "as SYSDBA" or "as Sysoper" as long as they have both of these permissions.
For example:

Sqlplus Scott/tiger as Sysdba

If the user logs on to the database server as SYSDBA, he will become a SYS user.

If you log in as Sysoper, then he will be a public user.
The scope size of the two permissions is different, for example, if a user logs in as SYSDBA, he can access any user's data unconditionally, for example:

Sqlplus Scott/tiger as Sysdba

SELECT * from userl.tl;

If a user logs in as Sysoper, he cannot access other users ' data.

In a later section, the two permissions are compared in more detail.
Ordinary users are generally created by the SYS user, which is less privileged and generally limited to accessing database objects in their own schemas.

Ordinary users need to have the appropriate permissions if they want to make additional access to the database.

How to create a user

In the process of running the database system, it is often necessary to create the user according to the actual requirements and then assign the appropriate permissions to the user.
The operation of creating a user is generally done only by the SYS user, and if the ordinary user is also creating a user, it must have a system privilege, that is, create user.
The command to create a user is the Create user, which contains content that includes specifying user names, passwords, default tablespace, storage quotas, and so on for the user.
Where the user name is the identifier representing the user account, its naming rules are:
• Must start with a letter .
• Length is l ~ 30 characters .
• Starting with the second character, you can include characters such as uppercase and lowercase letters, numbers, one, $, and #.
. Uppercase and lowercase are the same .
The full language-pull format used to create the user command is:

CREATE user username identified by "password"
DEFAULT tablespace Table Space
Temporary tablespace temporary table space
QUOTA space quota size on table space
PASSWORD EXPIRE
Account LOCK | UNLOCK

Where the default tablespace is used to specify the user defaults for the table space, and if not specified, the system tablespace is specified as the user's default tablespace.
If not specifically specified, database objects such as tables, indexes, etc. created by the user are located in the default table space.
To improve database performance and to facilitate the management of data, Oracle recommends specifying a default tablespace for the user.
The temporary tablespace is used to specify a temporary tablespace for the user.

You can create more than one temporary tablespace in a database, and you can specify a different temporary table space for each user.
You can also organize multiple temporary table spaces into a table space group that serves as the default temporary tablespace for the entire database or for a user.
The quota clause is used to specify a space quota for the user on the tablespace.

Although a default tablespace is specified for the user, the user cannot create a database object on this tablespace because it does not have a discretionary storage space on the table space.
space quotas, in bytes, kilobytes, MB, and so on, can also be specified as unlimited, which is an unrestricted space quota .

If you want users to have unlimited space quotas on all tablespaces, simply specify unlimited tablespaces system permissions for them.
PASSWORD expire is used to specify that the user's password expires, and the user must modify his or her password the first time they log on to the database server.
When creating a user account, the user's initial password is specified by the administrator.

This will make the user's account more secure if the user is forced to modify their password in the form of a password expiration.
The account clause is used to specify the status of user accounts, and if unlock, this user is able to log on to the database server, which is the default setting.
If lock, then the lock state.
For example, the following create statement creates a user User1 and assigns it a related property:

CREATE USER user1 identified by "123"
DEFAULT tablespace Users
Temporary Tablespace Temp
QUOTA 100M on users
PASSWORD EXPIRE
Account LOCK;

How to create a user without specifying the quota size can be added later:

For example:

Alter user User1 quota 10M on users;
Alter user User1 quota unlimited on users;
Grant unlimited tablespace to User1;

After the user has been created, there is no permission or even login to the database, and it can access the database only if the SYS user has given it a certain permission.
information about the user can be obtained from the data dictionary view dba_users .

For example, the following SELECT statement is used to query for information such as the name, password, default tablespace, and account status of all users in the current database:

SELECT username, password, account_status, default_tablespace from dba_users WHERE username= ' USER1 ';

Among the listed users are privileged users, pre-created users of the database, and users that have just been manually created.

The password of the user is encrypted , in an earlier version of the database, the encrypted password can be displayed, and the password is not displayed in the current version.
If not specifically specified, the user's default tablespace is the system table space.

How to modify a user's information

To prevent criminals from stealing users ' passwords, users should always change their passwords.

The user's password should not be like 1234, Abed such a simple string, not to be designated as their own birthday and name, and do not designate as an English word, because such a password is easy to decipher.
a good password should include a mixed string of uppercase and lowercase letters, numbers, _, &,%, $, and so on.
Statistics show that the more complex the components contained in a password, the harder it is to decipher.
Modifying a user's password is generally done by the user himself, and the SYS user can unconditionally modify the password of any one user.
Ordinary users can only modify their own password, if you want to modify the other user's password, you must have alter any user this system permissions.
The command to modify the user's password is alter users.

The ALTER USER command format for modifying the username is:

ALTER user username identified by "New password";

For example, to change the password of the user user1 you just created to "book", the corresponding ALTER USER statement is:

ALTER USER user1 identified by ' book ';

In addition to the SQL command alter USER, Sql*plus also provides a password command that can be used to modify the user's password .
With this command of Sql*plus, SYS user can modify other user's password, the ordinary user can modify their own password, in the case of authorization can also modify other user's password.
A user must know his or her previous password when modifying his or her password .
This command has only one parameter, which is the user name, and if this parameter is defaulted, the password is changed.
Here's how the user Scott modifies his password:

PASSWORD Scott

The Al,ter user command, in addition to modifying the password of the users. You can also modify the user's default tablespace, storage quota, account status, and other information.
For example, the following statement is used to lock the user scott:

ALTER USER Scott account LOCK;

The following statement modifies the user user1 the default tablespace, temporary tablespace, and modifies the user's space quota on the table space users to unlimited:

ALTER USER Userl
DEFAULT tablespace TSL
Temporary tablespace TSL
QUOTA UNLIMITED on TSL;

How to delete a user

When a user no longer accesses the database system, the user should be removed from the database in a timely manner, otherwise there may be security implications.
A better way to do this is to lock the user down for a while and then delete the user if it is determined that it is no longer needed.
Actions to delete a user are typically done by the SYS user or by a user with the drop user permission.

After a user is deleted, the information of the user itself is deleted from the data dictionary in order to counter the information that it owns for the database object.
The command to delete a user is drop users.

The syntax format for this command is:

DROP user username;

For example, to delete a user USER2, you can execute the following drop USER statement:

DROP USER User2;

Note that if a user's schema already contains some database objects, then the user cannot be deleted directly, and when the user is deleted, the system will display a message similar to the following:

ORA-01922: Must specify CASCADE to remove ' USER1 '

The error message occurs because some database objects are included in the user USER1 mode and cannot be deleted directly.

If you want to force the deletion of a user, you can use the CASCADE option in the drop user command so that the user and the owning database objects will be deleted together.
For example, the delete user Scott can execute the following statement:

DROP USER user1 CASCADE;

Rights Management for users

The Oracle database has strict permissions on the user, and without permissions, the user will not be able to complete any operations or even log in to the database .

For example, the user User1 just created will see the following error message when attempting to log in to the database:

Ora-01045:user USER1 lacks CREATE SESSION privilege; Logon denied

That is, the user must have Create session permission to log in to the database .

There are two types of user rights, system permissions and object permissions.
System permissions mainly refers to the creation of database objects, delete, modify the permissions, create a database, backup, and other permissions, and object permissions mainly refers to the database objects in the data access rights.
System permissions are typically specified by the SYS user, or can be granted by another user with privileges.
Object permissions are typically granted to the user by the owner of the database object , either by the sys user or by another user who has permission to the object .

Management of System permissions

system permissions are the most important permissions related to database security, which are typically targeted at the database administrator .

the management of system permissions mainly includes the assignment of permissions, recovery and query .
Oracle 11g provides more than 200 system permissions, and the following table lists the most important system permissions that are relevant to the user.

For database objects, such as tables, views, sessions, users, triggers, the system permissions include the ability to create, delete, and modify them, and the associated commands are created, drop, and alter, respectively.
Objects such as tables, views, triggers, stored programs, and so on, are user-related and, by default, operate on these objects in the current user's own mode.
If you want to manipulate these types of objects in another user's mode, you need to have the corresponding any permission .

For example, to be able to create a table in another user's mode, the current user must have the Create any table system permission, and if you want to be able to delete the table in another user's mode, you must have the drop any table system permission, and so on.
System permissions generally have three kinds of grant, one is the SYS user, that is, the database administrator, this is the most important one of the grant, most of the system Rights management work is done by the SYS user.
The second is a privileged ordinary user , once the ordinary user has SYSDBA or sysoper privileges, you can also manage system permissions.
The third is a user who has been granted some kind of system permission , and the system allows it to grant other users the system permissions it has.
The syntax format for a grant command that grants permissions to a user is:

GRANT permission list to user list with ADMIN OPTION;

After the grant command executes, all specified users will get the specified permissions.

If you want to grant a permission to all users, you can use public instead of all user names .
The function of the option "with ADMIN option" is to allow the recipient of the permission to grant permissions to other users.
For example, when you just created user Userl, the user does not have any permissions.

If the Create session permission is granted to this user, then the user can log in to the database.
If the CREATE TABLE permission is granted to the user, then he can create the table in his own schema.
The statements that grant these two permissions are:

GRANT Create SESSION, create TABLE to User1;

Users should be able to create tables after logging into the database system, but when the user creates a table in their own schema,
You will encounter the following error:

ORA-01950: No permissions on tablespace ' USERS '

The reason for the error is that the current user does not have a space quota in his default tablespace, that is, there is no storage space available, so the table cannot be created.
You will need to specify a space quota for the user, or specify unlimited tablespace system permissions for the user.
So in order for a user to operate the database, at least three of these permissions should be granted to the new user.
For example, the following authorization statement makes unlimited disk space available to all users:

GRANT UNLIMITED tablespace to public;

If the ALTER user's system permissions are granted to a user, it can modify other users ' information.

Now examine the sequence of statements for the following permission actions:

Conn/as SYSDBA

GRANT ALTER USER to User1;

CONN user1/123

ALTER USER Scott identified by New_tiger;

If the above statement succeeds, then the user Scott's password will be modified to "New_tiger".

First log in with the privileged user sys and grant the user Userl the system permission alter user.
Then log in as user Userl, and the user can modify other users ' information.
the command to reclaim system permissions is revoke , which is typically performed by the SYS user.

If a normal user wants to reclaim the permissions of another user, he must have the "GRANT any PRIVILEGE" system permission.
If a user accepts a system permission in the form of "with ADMIN OPTION" and then grants the system permission to another user, he can also reclaim the system permissions from other users.
The syntax format for revoke is:

REVOKE system permissions list from user list;

if you want to reclaim a system right from all users, you can use public instead of all user names .

The system permissions here are also granted by public.

For example, to reclaim the system permissions just granted to the user User1 create session and create TABLE, you can execute the following revoke statement:

REVOKE Create SESSION, create TABLE from User1;

This way, the user USER1 cannot log on to the database, and cannot create a table.

It is important to note that system permissions can be delegated, but they cannot be recycled indirectly.

Assuming that there are three users, the first user grants a system privilege to the second user in the form of "with ADMIN OPTION", and the second user grants this permission to the third user, then when the first user reclaims the permission from the second user, the permission cannot be reclaimed from the third user at the same time. A third user still has this permission.
Now consider a sequence of statements for the following permission actions:

CONN as Sysdba
GRANT CREATE session,create TABLE to user1 with ADMIN OPTION;
CREATE USER User2 identified by "1234";
CONN user1/1234
GRANT CREATE session,create TABLE to User2;
CONN sys/1234 as Sysdba
REVOKE CREATE session,create TABLE from User1;
CONN user2/1234
SELECT * from user_sys_privs

User and Rights Management