无法在SQL中添加用户：Oracle Apex SQL Workshop操作受阻求助

Hey there! Let's walk through how to resolve issues when adding a user via Oracle Apex's SQL Workshop (SQL Commands). I've helped many folks troubleshoot this exact scenario, so here's what you need to know:

第一步：确认你拥有足够的权限 #

The most common roadblock here is lacking the right permissions. To create a user, your current Apex session user needs the CREATE USER privilege, plus the ability to grant necessary roles/permissions to the new user. If you hit an ORA-01031: insufficient privileges error, reach out to your DBA to assign these permissions to your account.

正确的用户创建SQL语句 #

Start with the basic user creation command, making sure to follow Oracle's rules:

-- 创建用户，注意密码要符合复杂度要求
CREATE USER new_user IDENTIFIED BY "StrongPass_123";

Important notes about passwords:

• Must meet Oracle's password policy (usually at least 8 characters, mix of uppercase, lowercase, numbers, and special characters)
• Avoid Oracle reserved words as passwords
• Wrap the password in double quotes if it includes special characters or mixed case that you want to preserve

If you need to specify a tablespace and quota (to avoid "no privileges on tablespace" errors), use this extended version:

CREATE USER new_user IDENTIFIED BY "StrongPass_123"
DEFAULT TABLESPACE USERS
QUOTA UNLIMITED ON USERS;

为用户分配必要的权限 #

A user can't do much without basic permissions. At minimum, grant them the ability to connect to the database:

GRANT CREATE SESSION TO new_user;

If the user needs to use Apex applications or SQL Workshop tools, add relevant roles/permissions:

-- 赋予Apex用户基础角色
GRANT APEX_USER_ROLE TO new_user;
-- 如果需要创建表、视图等，添加RESOURCE角色或具体权限
GRANT RESOURCE, CREATE TABLE, CREATE VIEW TO new_user;

常见错误排查 #

• User already exists: If you see ORA-01920: user name 'NEW_USER' conflicts with another user or role name, pick a unique username (Oracle usernames are case-insensitive unless quoted).
• Password policy violation: Errors like ORA-00922: missing or invalid option or password-related warnings mean your password doesn't meet the database's security rules. Adjust the password to fit the requirements.
• Tablespace quota issue: ORA-01950: no privileges on tablespace 'USERS' means the user doesn't have access to the default tablespace. Fix this by specifying the tablespace and quota in the CREATE USER command (as shown earlier).

验证用户创建成功 #

After running the commands, confirm the user exists with this query:

SELECT username, account_status FROM dba_users WHERE username = 'NEW_USER';

You should see the new user listed with an OPEN account status.

内容的提问来源于stack exchange，提问作者Alish Manandhar