Oracle Permissions and data types

Oracle Create User:

CREATE user username identified by password [account lock| UNLOCK]

"Note" lock| Unlock is locked when the user is created, and is locked by default. A locked user cannot log on properly for database operation.

For example, create a user tom, the password is AAA, the default is non-locking, the format is:

Sql>create USER Tom

2 identified by AAA

3 account UNLOCK;

Note: Oracle commands in Sql*plus are terminated with a semicolon, the command is completed and executed, the command is saved in the cache, only the most recently executed commands are saved in the cache, and if the commands in the cache are re-executed, the slash character is used directly. If the command does not end with a semicolon, the command simply saves the write cache but does not execute.

Although the user was successfully created, the Oracle database system could not be logged on properly because the user does not have any permissions. If the user is able to log in properly, at least create SESSION system permission is required.

Oracle user rights to database management or object operations are divided into system permissions and database object permissions. System permissions such as: CREATE session,create table and so on. A user with system privileges that allows the appropriate system operation. Database object permissions, such as the deletion of data in the table, and so on, users who have database object permissions can do the corresponding operation of the object.

Database role, a database role is a collection of several system permissions, and several commonly used roles are as follows:

• Connect role: The primary application is for temporary users, especially those who do not need to build a table, and usually only give them connect role. Connect is a simple permission to use Oracle, a user with the connect role that can establish a connection session with the server (session, a client-to-server connection, called a conversation).
• Resource role: A more reliable and formal database user can authorize resource role. Resource provides users with additional permissions to create their own tables, sequences, procedures, triggers, indexes, and so on.
• DBA role: DBA roles has all system permissions, including unlimited space limits and the ability to authorize other users. The user system has a DBA role.

In general, a normal user with connect and resource two roles can perform regular database development work.

A permission can be granted to a role, and permissions and roles can be granted to a user. System permissions can only be authorized by the DBA user, the object permissions are authorized by the user who owns the object, and the authorization syntax is:

GRANT Role | Permissions to User (role)

The syntax for reclaiming permissions is:

REVOKE Roles | Permissions from user (role)

The syntax for modifying a user's password is:

ALTER user username identified by new password

To modify a user in a locked (non-locked) state:

ALTER User Username Account lock| UNLOCK

Oracle data type:

• CHAR (length): Stores a fixed-length string. The length of the argument is specified as long, and if the stored character is less than length, it is padded with spaces. The default length is 1 and the maximum is 2000 bytes.
• VARCHAR2 (length): Stores variable-length strings. length specifies the maximum size of the string. The default length is 1 and the maximum is 4000 characters.
• varchar (length): There is no difference between varchar and VARCHAR2, but the future version of Oracle does not support varchar type, if you want a new version of the database compatible, do not use varchar, Do not use VARCHAR2 if you want to be compatible with other databases.
• Number (P,s): You can store either floating-point numbers or integers, p represents the maximum number of digits (if it is a decimal part and a fractional part and a decimal point, p is 38 bits by default), and S refers to the decimal digits.
• Date: Stores the day and time, storage era, 4-bit year, month, day, hour, minute, second, and storage time from January 1, 4712 in front of the park to A.D. December 31, 4712.
• TIMESTAMP: Not only the date of the month and day, the time of the second, and the second after 6 bits, including the timezone.
• CLOB: Stores large text, such as storing unstructured XML documents.
• BLOB: Stores binary objects, shapes, videos, sounds, and so on.

Example of a number type:

Format	The number entered	The actual storage
Number	1234.567	1234.56
Number (6,2)	123.4526	123.45
Number (4,2)	12345.67	The number entered exceeds the specified precision and the database cannot be stored

For date types, you can use the sysdate built-in function to get the current system date and time, return a date type, and use the Systimestamp function to return the current date, time, and time zone.

SELECT Sysdate,systimestamp from DUAL

In queries for Oracle, you must use the Select column ... The complete syntax of the from table, when querying a single-line function, from behind using the dual table, the dual table has only one row in the system, and the table is used for Select...from syntax integrity when outputting a single-line function.

Oracle Permissions and data types