The permission system of Oracle database is divided into system permissions and object permissions. System permissions (Database system privilege) allows users to perform a specific set of commands. For example, the CREATE TABLE permission allows a user to create a table, and grant any privilege permission allows the user to grant any system permissions. Object permissions (Database object privilege) allow users to perform certain operations on individual objects. For example, delete permission allows a user to delete a row of a table or view, and the SELECT permission allows the user to query information from a table, view, sequence (sequences), or snapshot (snapshots) through a select.
Each Oracle user has a name and password and has some tables, views, and other resources created by it. An Oracle role is a set of permissions (privilege) (or the type of access each user needs depending on their status and conditions). The user can grant or give the role the specified permissions, and then assign the role to the appropriate user. A user can also authorize other users directly.
First, create users
Oracle creates the user's syntax:
Oracle Create user (password authenticated user), you can use the Create user command.
CREATE USER username identified by password
OR identified exeternally
OR identified globally as ' Cn=user '
[DEFAULT tablespace tablespace]
[Temporary tablespace temptablespace]
[QUOTA [Integer k[m]] [unlimited]] On tablespace
[, QUOTA [Integer k[m]] [unlimited]] On tablespace
[PROFILES Profile_name]
[PASSWORD EXPIRE]
[Account LOCK or Account UNLOCK]
which
CREATE user username: User name, typically alphanumeric and "#" and "_" symbols.
Identified by password: User password, typically alphanumeric and "#" and "_" symbols.
Identified exeternally: Indicates that the user name is validated under the operating system and that the user name must be the same as the user name defined in the operating system.
Identified globally as ' Cn=user ': User name is validated by Oracle Security Domain Center Server, and the CN name represents the user's external name.
[Default tablespace tablespace]: Table space for defaults.
[Temporary tablespace tablespace]: The default temporary tablespace.
[QUOTA [Integer k[m]] [unlimited]] On tablespace: The number of bytes in the table space that the user can use.
[PROFILES Profile_name]: The name of the resource file.
[PASSWORD EXPIRE]: Immediately set the password to an expired state, the user must modify the password before logging on.
[Account LOCK or UNLOCK]: The user is locked out and is not locked by default.
There are two built users within Oracle: System and sys. Users can log on to the system user directly to create other users, because system has the right to create another user. When Oracle is installed, the user or system administrator can first establish a user for themselves. e.g.
Copy Code code as follows:
Create user User01 identified by u01;
This command can also be used to set additional permissions, as detailed in the self-study material. To change a password, you can use the ALTER USER command:
Copy Code code as follows:
Alter user User01 identified by Usr01;
Now User01 's password has been changed from "U01" to "Usr01".
In addition to the alter USER command, users can also use the password command. If you use the password command, the new password entered by the user will not appear on the screen. A user with DBA authority can change the password of any other user through the password command, and other users can only change their password.
When the user enters the password command, the user is prompted for the old password and the new password, as follows:
Password
changing password for User01
Old Password:
New Password:
Retype new Password:
When a password is successfully modified, the user receives the following feedback:
Password changed
Second, delete users
To delete a user, you can use the drop user command as follows:
Copy Code code as follows:
If the user owns the object, it cannot be deleted directly, or an error value is returned. Specifies the keyword cascade, which deletes all the user's objects and then deletes the user. The following examples are used to delete users and their objects:
Copy Code code as follows:
Drop user User01 cascade;
three, 3 standard roles
Qracle provides three standard roles for compatibility with previous versions: Connect, resource, and DBA.
1. Connect role (Connection roles)
Temporary users, especially those who do not need to build tables, usually only give them connectrole. Connect is a simple privilege to use Oracle, which makes sense only if you have access to other users ' tables, including SELECT, INSERT, UPDATE, and delete. Users who have connect role can also create tables, views, sequences (sequence), clusters (cluster), synonyms (synonym), sessions (session), and chains to other databases (link).
2. Resource role (Resource roles)
More reliable and formal database users can grant resource role. Resource provides users with additional permissions to create their own tables, sequences, procedures (procedure), triggers (trigger), indexes (index), and clusters (cluster).
3. DBA roles (database Administrator role)
The DBA role has all the system privileges----including unlimited space limits and the ability to grant various permissions to other users. System is owned by the DBA user. Here are some typical privileges that DBAs often use.
(1) Grant (authorization) Order
The following is a USER01 authorization for the user you just created, with the following command:
Grant Connect, resource to User01;
(2) REVOKE (REVOKE) permissions
The permissions that have been granted can be undone. For example, to undo the authorization in (1), the command is as follows:
Copy Code code as follows:
Revoke connect, resource from User01;
A user with a DBA role can revoke other permissions from Connect, resource, and DBA for any other user or even another DBA. Of course, this is dangerous, so, unless really needed, DBA authority should not be arbitrarily granted to ordinary users who are not important. Revoking all of the permissions of a user does not mean that the user is removed from Oracle or that any tables created by the user are not destroyed, but simply prevents access to those tables. Other users who want to access these tables can access them as before.
Iv. Creating Roles
In addition to the three system roles mentioned earlier----connect, resource, and DBA, users can also create their own role in Oracle. User-created role can consist of table or system permissions or a combination of both. In order to create role, the user must have the Create role system permission. An example of the Create role command is given below:
Copy Code code as follows:
This command creates a role named student.
Once a role is created, the user can authorize it. The syntax for grant commands for role authorization is the same as for the user. When granting role authorization, use the name of role in the to clause of the grant command, as follows:
Copy Code code as follows:
Grant select on class to student;
Now, all users who have the student role have SELECT permissions on the Class table.
v. Delete Roles
To remove a role, you can use the drop Roles command, as follows:
Copy Code code as follows:
The specified role, along with the permissions associated with it, is removed from the database.
Vi. Delete Table considerations
When you delete all of the data in a table, you must use the
SQL code
Copy Code code as follows:
Because the table space occupied by the drop Table,delete * from table name is not released, the tablespace space on the tablespace is depleted after repeated several drop,delete operations.
Author "Technical Summary"
