How to create a user in Oracle(90) database

Preface

In Oracle databases, creating users is a common task for database administrators (DBAs). Creating a user involves assigning a user name, password, and permissions to the user. Here are detailed steps and code examples to guide you on how to create and assign permissions to users.

Steps to create a user

• Connect to the database
• Create a user
• Assign permissions to users
• Allocate table space for users
• Set the user's account status

Detailed steps and code examples

1. Connect to the database

First, you need to be a user with administrator privileges (e.g.SYSorSYSTEM) Connect to Oracle database.

-- useSQL*PlusConnect to the database
sqlplus sys as sysdba
-- 或者use其他工具如SQL Developerconnect

2. Create a user

useCREATE USERThe statement creates a new user and specifies the default tablespace and temporary tablespace.

CREATE USER my_user 
IDENTIFIED BY my_password 
DEFAULT TABLESPACE users 
TEMPORARY TABLESPACE temp;

In the above statement:

• my_userIt is the user name of the new user.
• my_passwordIt is the password of the new user.
• usersIt is the default tablespace.
• tempIt is a temporary table space.

3. Assign permissions to users

useGRANTThe statement assigns the required permissions to the new user. Common permissions includeCONNECTandRESOURCERole, as well as specific system permissions and object permissions.

-- distributeCONNECTandRESOURCERole
GRANT CONNECT, RESOURCE TO my_user;

-- distribute特定的系统权限
GRANT CREATE SESSION TO my_user;
GRANT CREATE TABLE TO my_user;
GRANT CREATE VIEW TO my_user;
GRANT CREATE PROCEDURE TO my_user;
GRANT CREATE SEQUENCE TO my_user;

-- distribute特定的对象权限
GRANT SELECT, INSERT, UPDATE, DELETE ON my_schema.my_table TO my_user;

4. Allocate table space for users

If you need to allocate additional tablespace quotas for users, you can useALTER USERStatement.

ALTER USER my_user QUOTA 100M ON users;

In the above statement,100MIt is assigned to the userusersQuota for tablespace.

5. Set the user's account status

You can useALTER USERStatement to lock or unlock user accounts.

-- Lock user account
ALTER USER my_user ACCOUNT LOCK;

-- Unlock user account
ALTER USER my_user ACCOUNT UNLOCK;

Sample script

Here is a complete sample script showing how to create a user and assign permissions to them.

-- Connect to the database
-- sqlplus sys as sysdba

-- Create a user
CREATE USER my_user 
IDENTIFIED BY my_password 
DEFAULT TABLESPACE users 
TEMPORARY TABLESPACE temp;

-- distributeCONNECTandRESOURCERole
GRANT CONNECT, RESOURCE TO my_user;

-- distribute特定的系统权限
GRANT CREATE SESSION TO my_user;
GRANT CREATE TABLE TO my_user;
GRANT CREATE VIEW TO my_user;
GRANT CREATE PROCEDURE TO my_user;
GRANT CREATE SEQUENCE TO my_user;

-- distribute特定的对象权限
GRANT SELECT, INSERT, UPDATE, DELETE ON my_schema.my_table TO my_user;

-- 为用户distribute表空间配额
ALTER USER my_user QUOTA 100M ON users;

-- Unlock user account
ALTER USER my_user ACCOUNT UNLOCK;

-- 确认用户创建and权限distribute成功
SELECT username, account_status FROM dba_users WHERE username = 'MY_USER';

Verify the user

To verify that the newly created user can connect and operate the database normally, you can use the new user's credentials to connect to the database and perform some basic operations.

-- Connect to the database using a new user
sqlplus my_user/my_password@your_database

-- Create a table
CREATE TABLE test_table (id NUMBER, name VARCHAR2(50));

-- Insert data
INSERT INTO test_table (id, name) VALUES (1, 'Test Name');

-- Query data
SELECT * FROM test_table;

-- Delete table
DROP TABLE test_table;

Summarize

Creating a user is a basic operation in Oracle database management. With the above steps and code examples, you can systematically create users and assign them the required permissions and resources. Regular review and management of user permissions can ensure the security and efficient operation of the database system.