The syntax for creating a user is as follows:
CREATE USER username IDENTIFIED BY password -- CREATE a user and set the password at the same time, but the user name and password cannot make the reserved words of oracle (such as DROP, CREATE, etc.) start with a number (if you want to set it as a number, you need to declare the number in double quotation marks, such as "9977")
[DEFAULT TABLESPACE tablespace name] - the user stores the DEFAULT TABLESPACE. When the user creates an object without a tablespace, it will be saved under the tablespace specified here, which can be distinguished from the system tablespace
[TEMPORARY TABLESPACE tablespace name] - TEMPORARY TABLESPACE used by the user
[quot number [K | M] UNLIMITED on tablespace name -- the user's usage quota on the tablespace. You can specify the quota of multiple tablespaces. If it is set to 'UNLIMITED', it means that the quota is not set
Quot number [K | M]UNLIMITEND ON tablespace name...]
[PROFILE profile name | DEFAULT] - resource file for user operation. If it is not specified, the DEFAULT configuration resource file will be used
[PASSWORD EXPIRE] - if the user password is invalid, the password must be modified when it is used for the first time
[ACCOUNT LOCK | UNLOCK] - whether the user is locked. It is "UNLOCK" by default
ALTER USER username IDENTIFIED BY new password -- modify password
ALTER USER username ACCOUNT LOCK -- modify the user to the locked state
ALTER USER username ACCOUNT UNLOCK -- modify the user to unlock status
ALTER USER username PASSWORD EXPIRE -- invalidate user password
drop user user name-- delete user
drop user username cascade-- If the user owns the object, it cannot be deleted directly, otherwise an error value will be returned. Specify the keyword cascade to delete all objects of the user, and then delete the user.
Authorization role: oracle is compatible with previous versions and provides three standard roles: connect/resource and dba
-
Connect role
– temporary users, especially users who do not need to create tables, are usually given only connect role
– connect is a simple oracle permission. This permission only has access to other users' tables, including select/insert/update and delete.
– users with the connect role can also create tables, views, sequence s, cluster s, synonyms, session s, and other data link s -
Resource role
– more reliable and formal database users can grant resource role s.
– resource gives users additional permissions to create their own tables, sequences, procedures, triggers, indexes, and clusters. -
DBA role (database administrator role)
– dba role has all system permissions
– includes unlimited space quotas and the ability to grant various permissions to other users. The system is owned by dba users
grant connect, resource to user name-- Authorization command
revoke connect, resource from user name-- Revoke permissions
In addition to the three system roles mentioned earlier - connect, resource and dba, users can also create their own roles in oracle. User created roles can consist of tables, system permissions, or a combination of both.
In order to create a role, the user must have create role system permission.
1> Create role: create role name;
2> Authorization role: grant select on class to role name-- Note: now, all users with the testRole role role have select query permission on the class table
3> Delete role: drop role name-- Note: all permissions related to the testRole role role will be deleted from the database
Authorize system permissions for users: grant permissions,... to user name | role name [public] [with admin option]
To -- set the user and role granted permission, or use public to set this permission as public permission
with admin option -- continue granting permissions granted by the user to other users
Revoke system permission: revoke permission,... from user name
grant object permission: grant object permission,... ALL on object name to user name | role name [public] [with admin option]
All -- indicates that all object permissions are set
on -- name of the object to grant the object
Remove object permission: revoke permission,... on object name from user name
Create role: create role name [not identified | identified by password] - role is a group of permissions
Authorization for role: grant permission,... to role name
Delete role: drop role role name
select * from role_sys_privs where role in ('role name ') -- view the permissions of the role
Role password modification: alter role role name identified by password
Cancel role password: alter role role name not identified
Predefined roles:
exp_full_database export database permissions
imp_full_database import database permissions
select_catalog_role query data dictionary permission
execute_ catalog_ Execute permissions on role data dictionary
delete_ catalog_ Delete permission on role data dictionary
Related permissions of dba system management
connect grants users the most typical permissions
resource grants permissions to developers
grant execute on AGE to LIS; -- Grant the stored procedure age permission to the LIS user
grant select on CARD_TA_MEMO to LIS;– Add table card_ TA_ The memo authority is granted to LIS users
example:
-- Create the user create user MKJK identified by ""; -- Grant/Revoke object privileges grant select on HISDB.EXAM_TA_BILL to MKJK; grant select on HISDB.EXAM_TA_BOOK to MKJK; grant select on HISDB.EXAM_TA_REP to MKJK; grant select on HISDB.INP_TA_BOOK to MKJK; grant select on HISDB.INP_TA_DIAG to MKJK; grant select on HISDB.INP_TA_ORDER to MKJK; grant select on HISDB.INP_TA_PRESC_D to MKJK; grant select on HISDB.INP_TA_PRESC_M to MKJK; grant select on HISDB.INP_TA_TRAN to MKJK; grant select on HISDB.INP_VD_FEE_CLINIC to MKJK; grant select on HISDB.LAB_RESULT to MKJK; grant select on HISDB.LAB_TA_BILL to MKJK; grant select on HISDB.LAB_TA_BOOK to MKJK; grant select on HISDB.MED_TD_CODE to MKJK; grant select on HISDB.MED_TD_ORGI to MKJK; grant select on HISDB.MED_TD_SUPP to MKJK; grant select on HISDB.OPE_MAS_BILL to MKJK; grant select on HISDB.OPE_TA_BILL to MKJK; grant select on HISDB.OPE_TA_MASTER to MKJK; grant select on HISDB.OUT_TA_BOOK to MKJK; grant select on HISDB.OUT_TA_DIAG to MKJK; grant select on HISDB.OUT_TA_ORDER to MKJK; grant select on HISDB.OUT_TA_ORDER_BILL to MKJK; grant select on HISDB.OUT_TA_PRESC_D to MKJK; grant select on HISDB.OUT_TA_PRESC_M to MKJK; grant select on HISDB.PAT_TA_BINFO to MKJK; grant select on HISDB.PUB_TA_LIMIT_DRUG to MKJK; grant select on HISDB.PUB_TA_USER to MKJK; grant select on HISDB.PUB_TD_CLINIC to MKJK; grant select on HISDB.PUB_TD_CODE to MKJK; grant select on HISDB.PUB_TD_DIAG to MKJK; grant select on HISDB.PUB_TD_DOCTOR_GROUP to MKJK; grant select on HISDB.PUB_TD_FEE to MKJK; grant select on HISDB.PUB_TD_OPER to MKJK; grant select on HISDB.PUB_TD_ORGI to MKJK; grant select on HISDB.PUB_TR_FEE_CLINIC to MKJK; grant select on HISDB.V_INP_TA_ORDER to MKJK; grant select on INP.INP_TA_BILL to MKJK; grant select on MED.MED_TA_IMP_M to MKJK; -- Grant/Revoke role privileges grant connect to MKJK; grant resource to MKJK; -- Grant/Revoke system privileges grant create any view to MKJK; grant unlimited tablespace to MKJK;