Establishing and Modifying User Create User statements will establish a user. It must be verified when a user is connected to an Oracle database. There are three types in Oracle: Database External Global default is a database authentication. When the user is connected to the database, Oracle will detect if the user is a legitimate user of the database, and to provide the correct password.external verification, Oracle will only detect the user It is legal user, Password has been verified by the network or system. Global verification also only detects whether it is a legitimate user, Password is verified by OraclesECurity Server. Database verifies that the user account database verification account is a good default type, and is the most common type. Creating an account is Piyush, the password is the account of Welcome, just execute the following command:
Create Use Piyushi Identified by Welcome
Piyush can change the password to Saraswatt by the following statement:
Alter User Piyush Identified by Saraswati;
External verification User account user account can not provide passwords when entering the database, in which case the database identification password is the client operating system. External verification account is sometimes called an OPS $ account. When they initially introduced in Oracle6, the Oracle account has a keyword prefix OPS $, which is why INIT.ORA parameter OS_AUTHENT_PREFIX is OPS $ - the default feature is consistent with Oracle6. The string defined by the OS_AUTHENT_PREFIX must be preprocessed as an operating system account name for Oracle external identification account. Creating an operating system User Appl is:
Create User OPS $ Appl Identified Eaternally
But in general, Os_Authent_prefix will be set to empty, like this:
Create User Appl Identified Eaternally
This effect is the same, the keyword identified externally tells Oracle This is an external identification account. Global User Account Global Type User Account Database does not detect the password, but is detected by the X.509 directory server. The method of creating a user account for a Global type is:
Create User Scott Identified Globally AS "CN = Scott, Ou = DiSional, O = Sybex, C = US"
Keyword Identified Globally AS represents a user account for a global type. Create and change user account CREATE User to establish a user account and assign a value to the user account. ALTER USER is used to change user accounts and properties. But the CREATE User statement must include user names and passwords. Some qualifications are set with Creater User and ALTER User statements. The following is specific to these properties: Give User Assignment Default Table Space Table Spaces (TABLESPACE) is a placement table, an index, and a user object. If there is no table space in the CREATE User statement, then the default is the system table space.
Create User Piyush Identified by SaraswiaFAulte TableSpace User_Data; ALTER USER Manoj Default TableSpace DEV1_DATA;
Assign Up Time Table Space Temporary Table Spaces to users, as the name refer to user objects such as temporary storage tables, indexes. Establish a method
Create User Piyush Identified by Saraswatitemporary TableSpace User_Data; ALTER USER Manoj Temporary TableSpace DEV1_DATA; Using Quota for Using Quota Using Quota Using Quota Using Quota Using Quota Using Disks in Table Spaces. Quota can be set by bytes, kilobytes, megabytes or unlimited.
Create User Piyush Identified by SaraswiaFault TableSpace User_Dataquota Unlimited on User_Dataquota 20M on Tools; ALTER USER Manoj Quota 2500k on tools;
Assigning a brief watch for users to restrict resources that users consume during sessions. These resources include: Time, idle time, idle time, number of logical read data of each session, etc., default briefs are unlimited.
Create User Piyush Identified by SaraswatiProfile TableSpace User_Data; ALTER USER Manoj Temporary TableSpace dev1_data;
The specified role for the user can only be set by the ALTER USER statement, trying to return back to one exception to the CREATE User statement setting.
Alter User Manoj Default Role All Except Salary_ADM;
Set the expiration time for the user to change when the user is logged in, when the user's Password expires, when the next login will force the Password, Oracle prompts the user to enter the old Password, and enter the new Password. This feature is often used in new users, and you must modify Password immediately when new users use default Password logins.
Alter User Manoj Identified by Welcome; ALTER USER Manoj Password Expire;
Lock the account, is the user cannot log in
Alter User QL AC Count Lock
Unlock the account so that users can log in to the database
Alter User QL Account UNLOCK
Permissions and role permissions allow users to access objects or execution programs belonging to other users, and the Oracle system provides three permissions: Object Object-Level System System-Level Role Role level These permissions can be granted to users, special user public, or roles if grant a permission Special user "public" (user public is Oracle predefined, each user enjoys the permissions of this user), then it means that the permission will be granted to all users of the database. For administrative privileges, the role is a tool, permissions can be granted to a role, and the role can also be granted to another role or user. The user can inherit the permissions through the role, except for the management privilege, the role service has no other purpose. Permissions can be granted or canceled in the same way. Establishing and using the role as previously claimed, the purpose of the role is to make the management of the permissions easier. Establish a role using the Create Role statement, his syntax is as follows:
Create Role Role_name Identified by PasswordCreate Role Role_name Identified External Role Role_Name Identified Globally
The role established by default is not Password or other identification. If you use the Identified By clause, the role does not respond automatically and must be activated with SET ROLE.
Set role role_name Identified by PasswordExternal The role of the GLOBALLY type is verified by the operating system and Oracle Service Server. Usually the user needs permissions to modify data in the form used in the application, but only when the application is running rather than using the AD HOC tool, this context-sensitive security can be implemented by the role of Password. When the user connects the database inside the application, the code will execute the set role command, by security verification. So the user does not need to know the character's Password, nor does it need to enter the set role command yourself. Object rights object permissions refer to the right to perform special actions on tables, views, sequences, processes, functions, or packets. There are nine different types of permissions that can be granted to the user or role. As follows:
Permissions ALTERDELETEEXECUTEINDEXINSERTREAD REFERENCESELECT UPDATEDirectoryno no no no no yesnono nofunctionnonoyesno nononononoprocedureno noyesnonono nononopackagenonoyesnonononononoDB Objectno no yesnono nonononoLibary nonoyes nonono no nonoOperation no no yesno no no no no noSequenceyes no no no no no no no noTable yes yes noyesyes no yes yes yesTypeno no yes no no no no No no no no yes yes yes yes yes yes yes yes
Objects are made by more than one permissions, and the special permissions ALL can be granted or revoked. If a table's all permissions include: SELECT, INSERT, UPDATE, and DELETE, and INDEX, ALTER, and REFERENCE. How do I see this table With ALTER privileges as an example, Alter Permissions allow for ALTER TABLE and LOCK TABLE operations, Alter Table can do the following: Change the table name. Add or delete columns. Change the data type or size of the column. Table Change to the partition table ALTER permission on Sequence allows the ALTER SEQUENCE statement to be executed, re-assigning minimum values, increment, and buffer sizes to Sequence. System Permissions System Permissions need to be granted to have system-level activities, such as connecting to databases, changing user sessions, establish a table, or build a user, and so on. You can get complete system permissions on the Data Dictionary View System_PrivileGe_map. Object privileges and system permissions grants users or roles via the GRANT statement. It should be noted that the statement should be the WITH GRANT Option clause when granted object permission, but the statement is WITH Admin Option when granting the system power, so when you try to grant system permissions, use the statement with the grant option system to report an error. : Only Admin Option Can Be Specified. Pay special attention to this syntax and error message in the exam. Role and Role Permissions Role Permissions are granting a role that belongs to the user. Any permissions can be granted to one role. Granting system permissions Give awarder must use the use_admin_option clause, grant or revoke roles through the SET ROLE statement during the session. However, role privileges cannot rely on privileges stored in SQL. If functions, programs, packages, triggers, or methods use another plan, you must authorize the owner of the object, because the permissions will not change between sessions. Grant and revoking permissions to the user or role grant permission using the GRANT statement, the grammar of the GRANT statement is as follows:
Grant role (or system privilege) to user (optional) object permission is granted with Grant Option, Permissions, and Data Dictionary Data Dictionary is a place for Oracle storage about database structure information, the data itself is stored in other Place, data dictionary consists of tables and views. The most prone to data dictionary in the exam is: View that type of permissions have been granted. For example, DBA_TAB_PRIV contains information about object permissions to another user and whether it is with the WITHT OTPION substrs when granted. Note that DBA_TAB_PRIV contains not only the relationship of the permissions of the table, but he also includes the relationship between functions, packages, queues, etc. The following table lists all permissions and roles of data dictionary views: Table: Data Dictionary View of Permissions
View action all_col_privs represents the authorization on the column, the user, and public is authorized to be authorized by the author all_col_privs_made, the user is the authority and the authorization ALL_COL_RECD represents the authorization on the column, the user, and public is the authorized ALL_TAB_PRIVS represents the object. Authorization, the user is a public or awarder or user who is the owner ALL_TAB_PRIVS_MADE to represent the permissions on the object, the user is the author or the authority all_tab_privs_recd represents the permissions on the object, the user is all authorized DBA_ROLE_PRIVs on the PUBLIC or the DBA_ROLE_PRIVS on the Database column Displaying the Role DBA_SYS_PRIVS that has been granted to the user or other role has granted all permissions on the user or role DBA_TAB_RIVS database object Role_Role_Privs Display the granted user Role Role_sys_privs Display system permission Role_TAB_PRIVS displayed by the role Role to grant the user's object authority SESSION_PRIVS Displays the permissions on all system authority USER_COL_PRIVS display columns available now, the user is the author, the author, or the authorized user_col_privs_made display column, the user is the owner or awarder user_col_privs_recd display column has been granted Permissions, the user is the owner or awarder user_role_privs display all the roles of the authorized to the user. User_sys_privs displays all system permissions that have been granted to the user USER_TAB_PRIVS Display All object authority for the user has been granted to the user User_tab_privs_made display has been granted to other users Object privileges The user is the owner_tab_privs_recd display that the object authority has been granted to other users, and the user is granted.
