User Creation (learning notes), User Creation learning notes

User Creation (learning notes), User Creation learning notes

• Create a user:

Syntax:

Create user Username identified by password [default tablespace name] [temporary tablespace table name] [QUOTA number [K | M] unlimited on tablespace name QUOTA number [K | M] UNLIMITED ON tablespace name...] [PROFILE name | DEFAULT] [password expire] [account lock | UNLOCK]

As follows:

The [] section can omit the create user username identified by password: when creating a USER, you can set a password. However, the username and password cannot start with a number or be a reserved character in Oracle, if you want to set the number to a number, you need to use the "Declaration" such as ''000000'' default tablespace name: the user stores the TABLESPACE used by DEFAULT. When the user creates an object and does not set the TABLESPACE, the name of the temporary tablespace is the temporary tablespace used by the user. The QUOTA number [K | M] unlimited on tablespace Name: you can specify the quota for multiple tablespaces. If it is set to UNLIMITED, the limit PROFILE: PROFILE name | DEFAULT: resource file operated by the user is not set, if this parameter is not specified, use the default configuration resource file password expire: the user PASSWORD is invalid. In the first use, you must change the password account lock | U NLOCK: whether the user is locked. The default value is UNLOCK. The Administrator is required to create a user.

Create a user:

Create user te identified by te -- create user te password teDEFAULT TABLESPACE tbs_test -- default TABLESPACE tbs_testTEMPORARY TABLESPACE temp; -- temporary TABLESPACE temp;

Create another user

Create user oracletest -- create user oracletestIDENTIFIED BY oracletest -- password: oracletestDEFAULT TABLESPACE tbs_test -- default TABLESPACE: tbs_test temporary tablespace temp -- temporary tablespace tempQUOTA 8 m on tbs_test -- use up to 8 MQUOTA 20 m on users on the tbs_test TABLESPACE -- use up to 8 maccount unlock on the users TABLESPACE -- the user is active by default account, UNLOCK is active, LOCK is locked password expire; -- the user needs to force change the PASSWORD after the First Login

After the user is created, the following message is displayed:

ORA-01045: user te or oracletest lacks create session privilege; logon denied

Prompt that the user is not authorized to connect to the database

There are two ways to assign permissions:

Oracle pre-defines some roles:

 

 

NO	Predefined role	Description
1	EXP_FULL_DATABASE	Export Database Permissions
2	IMP_FULL_DATABASE	Database Import Permission
3	SELECT_CATALOG_ROLE	Permission to query data dictionaries
4	EXCUTE_CATALOG_ROLE	Data Dictionary execution permission
5	DELETE_CATALOG_ROLE	Delete permission on Data Dictionary
6	DBA	System Administrator Permissions
7	CONNECT	Grant users the most typical permission to connect
8	RESOURCE	Grant permissions to developers

 

CONNECT role permissions;

CREATE SESSION

Connect to database

 

RESOURCE role Permissions

CREATE TRIGGER	Create a trigger
CREATE SEQUENCE	Create Sequence
CREATE TABLE	Create a table
CLUSTER	Create a cluster
CREATE PROCEDURE	Create a stored procedure
CREATE TYPE	Creation type

 

 

Generally, developers grant the CONNECT and RESOURCE roles

 

• Assign a role to a user:

GRANT CONNECT,RESOURCE TO te;GRANT CONNECT,RESOURCE TO oracletest;

The created user can log on to and connect to the database.

• Maintenance User:

Alter user Username identified by new password

ALTER USER oracletest IDENTIFIED BY test

2. control user lock

 

Alter user Username account lock | UNLOCK

Set oracletest to locked

ALTER USER  oracletest ACCOUNT LOCK

At this time, oracle cannot log on to and connect to the database.

 

Set oracletest to unlock

ALTER USER  oracletest ACCOUNT UNLOCK

3. invalidate the password

ALTER Username PASSWORD EXPIRE

Invalidate the oracletest User Password

ALTER oracletest PASSWORD EXPIRE

At this time, the oracletest user will be prompted to change the password

4. Modify the tablespace quota

Alter user Username QUOTA number [K | M] unlimited on tablespace name

Modify the tablespace quota of oracletest

ALTER USER oracle QUOTA UNLIMITED ON tbs_test

Delete A User:

 

Drop user Username [CASCADE] CASCADE clause delete all objects in Mode

 

Delete An oracletest user

DROP USER oracletest

Authorize a role:

 

GRANT role name, role name,... TO user name

 

GRANT CONNECT,RESOURCE TO te;GRANT CONNECT,RESOURCE TO oracletest;

Revoke role:

 

REVOKE role name, role name... FROM user name

 

REVOKE CONNECT,RESOURCE TO te;REVOKE CONNECT,RESOURCE TO oracletest;

Create a role:

 

Create role name [not identified | identified by password]
Not identified no password
Identified by password

 

Create a common role:

CREATE ROLE NOTEST;

View the DBA_ROLES dictionary to check whether the role has been created successfully and its status.

SELECT * FROM dba_roles WHERE ROLE='NOTEST';

Role authorization:

Grant create session, create table to notest; GRANT the role permissions TO connect TO and CREATE tables.

Query the role_sys_privs data dictionary. What permissions are available for the status?

SELECT * FROM role_sys_privs
WHERE role = 'notest'
Order by role;

Find our role to view Permissions

Disable all roles in the current session

SET ROLE NONE;

Enable all roles in the current session

SET ROLE ALL;

Delete a role:

Drop role name;

Delete notest role

DROP ROLE NOTEST;