\n \n\t 请先使用 sqlplus 使用 system 或 sys 账号连接 oracle 服务。\n

\n\n\n### 6.1.登录 Oracle 服务\n\n```bash\n-- 使用 SYSTEM 或 SYS 用户连接\nsqlplus system/oracle@localhost:1521/xe\n\n-- 或者使用 SYS 用户（需要以 SYSDBA 身份连接）\nsqlplus sys/oracle@localhost:1521/xe as sysdba\n\n-- 或者缺省，本地连接（推荐，权限更高）\nsqlplus sys/password as sysdba\n```\n\n### 6.2.基本用户创建\n\n```sql\n-- 创建最简单的用户\nCREATE USER dev_user IDENTIFIED BY dev_password;\n\n-- 创建用户并指定默认表空间\nCREATE USER app_user IDENTIFIED BY app_password\nDEFAULT TABLESPACE users\nTEMPORARY TABLESPACE temp;\n\n-- 创建用户并设置配额\nCREATE USER report_user IDENTIFIED BY report_password\nDEFAULT TABLESPACE users\nTEMPORARY TABLESPACE temp\nQUOTA 100M ON users;\n```\n\n### 6.3.完整的用户创建模板\n\n```sql\n-- 完整用户创建示例\nCREATE USER airline_app IDENTIFIED BY \"Airline@2025\"\nDEFAULT TABLESPACE users\nTEMPORARY TABLESPACE temp\nQUOTA 500M ON users\nQUOTA 100M ON temp\nPROFILE default\nACCOUNT UNLOCK\nPASSWORD EXPIRE;\n\n-- 立即解锁账户（如果创建时锁定了）\nALTER USER airline_app ACCOUNT UNLOCK;\n```\n\n### 6.4.为用户授权\n\n#### 6.4.1.基本权限授予\n\n```sql\n-- 授予连接权限\nGRANT CREATE SESSION TO airline_app;\n\n-- 授予资源权限（创建表、序列等）\nGRANT CONNECT, RESOURCE TO airline_app;\n\n-- 授予特定对象权限\nGRANT SELECT, INSERT, UPDATE, DELETE ON system.customers TO airline_app;\n\n-- 授予系统权限\nGRANT CREATE TABLE, CREATE VIEW, CREATE SEQUENCE TO airline_app;\n```\n\n#### 6.4.2.角色权限管理\n\n```sql\n-- 创建自定义角色\nCREATE ROLE airline_developer;\nCREATE ROLE airline_readonly;\n\n-- 为角色授予权限\nGRANT SELECT ANY TABLE, CREATE SESSION TO airline_readonly;\nGRANT CONNECT, RESOURCE, CREATE VIEW TO airline_developer;\n\n-- 将角色授予用户\nGRANT airline_developer TO airline_app;\n\n-- 查看用户权限\nSELECT * FROM user_role_privs WHERE username = 'AIRLINE_APP';\nSELECT * FROM user_sys_privs WHERE username = 'AIRLINE_APP';\n```\n\n### 6.5.开发人员权限用户创建案例\n\n#### 6.5.1.创建用户\n```sql\n-- 创建开发用户\nCREATE USER JSGBJD IDENTIFIED BY \"gbjd123\"\nDEFAULT TABLESPACE users\nTEMPORARY TABLESPACE temp\nQUOTA 500M ON users\nPROFILE default\nACCOUNT UNLOCK\nPASSWORD EXPIRE;\n\n-- 立即解锁并设置密码（如果创建时锁定了）\nALTER USER JSGBJD ACCOUNT UNLOCK;\n```\n\n#### 6.5.2.创建开发角色\n\n```sql\n-- 创建开发角色\nCREATE ROLE dev_role;\n\n-- 授予基本的会话权限\nGRANT CREATE SESSION TO dev_role;\n\n-- 授予对象创建权限\nGRANT CREATE TABLE, CREATE VIEW, CREATE SEQUENCE, CREATE PROCEDURE, CREATE TRIGGER, CREATE TYPE, CREATE SYNONYM TO dev_role;\n\n-- 授予对象管理权限\nGRANT ALTER ANY TABLE, DROP ANY TABLE TO dev_role;\nGRANT ALTER ANY SEQUENCE, DROP ANY SEQUENCE TO dev_role;\nGRANT ALTER ANY PROCEDURE, DROP ANY PROCEDURE TO dev_role;\n\n-- 授予数据操作权限\nGRANT SELECT ANY TABLE, INSERT ANY TABLE, UPDATE ANY TABLE, DELETE ANY TABLE TO dev_role;\n\n-- 授予调试和性能监控权限\nGRANT DEBUG CONNECT SESSION, DEBUG ANY PROCEDURE TO dev_role;\nGRANT SELECT ANY DICTIONARY TO dev_role;\n\n-- 授予索引和约束权限\nGRANT CREATE ANY INDEX, ALTER ANY INDEX, DROP ANY INDEX TO dev_role;\n```\n\n#### 6.5.3.授予特定开发工具需要的权限\n\n```sql\n-- 为常见开发工具授予权限\nGRANT UNLIMITED TABLESPACE TO dev_role;\n\n-- 授予执行 DBMS 包的权限（需要 sysdba 权限执行）\nGRANT EXECUTE ON DBMS_LOCK TO dev_role;\nGRANT EXECUTE ON DBMS_OUTPUT TO dev_role;\nGRANT EXECUTE ON DBMS_SQL TO dev_role;\nGRANT EXECUTE ON DBMS_UTILITY TO dev_role;\nGRANT EXECUTE ON UTL_FILE TO dev_role;\n\n-- 授予统计信息权限（用于性能调优）\nGRANT ANALYZE ANY TO dev_role;\n\n-- 授予物化视图权限（如果使用）\nGRANT CREATE MATERIALIZED VIEW, ALTER ANY MATERIALIZED VIEW, \n DROP ANY MATERIALIZED VIEW TO dev_role;\n```\n\n#### 6.5.4.将角色授予开发用户\n\n```sql\n-- 将开发角色授予用户\nGRANT dev_role TO JSGBJD;\n\n-- 授予 CONNECT 角色（包含基本权限）\nGRANT CONNECT TO JSGBJD;\n\n-- 授予 RESOURCE 角色（包含资源创建权限）\nGRANT RESOURCE TO JSGBJD;\n\n-- 设置默认角色\nALTER USER JSGBJD DEFAULT ROLE dev_role, CONNECT, RESOURCE;\n```\n\n#### 6.5.5.创建开发专用的表空间（可选）\n\n

\n \n 表空间创建失败，可能是因为 Oracle 容器对应目录操作权限没有，请使用 SSH 登录 Oracle 服务容器进行授权。\n

\n\n\n授予容器创建文件的权限：\n\n```bash\nchmod -R 777 /opt/oracle/oradata/XE/\n```\n\n```sql\n-- 为开发环境创建专用表空间\nCREATE TABLESPACE dev_data\nDATAFILE '/opt/oracle/oradata/XE/dev_data01.dbf'\nSIZE 500M\nAUTOEXTEND ON NEXT 100M MAXSIZE 2G;\n\nCREATE TEMPORARY TABLESPACE dev_temp\nTEMPFILE '/opt/oracle/oradata/XE/dev_temp01.dbf'\nSIZE 200M\nAUTOEXTEND ON NEXT 50M MAXSIZE 1G;\n\n-- 修改用户使用开发表空间\nALTER USER JSGBJD \nDEFAULT TABLESPACE dev_data\nTEMPORARY TABLESPACE dev_temp\nQUOTA UNLIMITED ON dev_data;\n```","has_starred","hub_user","immutable_tags_settings",{"_75":11,"_76":77},"enabled","rules",[78],".*","is_automated","permissions",{"_82":17,"_83":11,"_84":11},"read","write","admin","user","storage_size","orgData","recentTags",[90],{"_91":45,"_92":93,"_94":95,"_96":97,"_98":99,"_100":101,"_51":117,"_118":93,"_119":29,"_120":57,"_39":121,"_122":123,"_124":108,"_125":117,"_126":65,"_127":17},"content_type","creator",11661199,"digest","sha256:c2546bdff797e5a318937817bba568ed91490ea99d3de78a59a1bed9393cc6a9","full_size",1450451257,"id",991694093,"images",[102],{"_103":104,"_94":95,"_105":106,"_107":108,"_109":110,"_111":112,"_113":106,"_114":-5,"_115":97,"_63":65,"_116":-5},"architecture","amd64","features","","last_pulled","2026-09-19T21:53:06.71312521Z","last_pushed","2025-10-15T06:06:39.981293071Z","os","linux","os_features","os_version","size","variant","2025-10-15T06:06:40.771926Z","last_updater","last_updater_username","media_type","11.2.0.2.0","repository",28842674,"tag_last_pulled","tag_last_pushed","tag_status","v2","sbxKitMetadata","isPotentialSpam","isHubSbxKitsEnabled","showSbxLogoOverride","sbxTrademarkText","displaySecurityMetadata","tagAttestations","tagsHaveAttestations",[],"routes/_layout.r.$namespace.$reponame.tags",{"_34":139,"_147":148,"_135":157,"_158":11},{"_36":140,"_43":142,"_46":47,"_48":49,"_50":11,"_51":52,"_53":54,"_55":143,"_39":31,"_28":29,"_58":59,"_60":45,"_61":62,"_63":62,"_64":65,"_66":-5,"_67":68,"_69":70,"_71":11,"_72":29,"_73":144,"_79":11,"_80":146,"_85":29,"_86":-5},[141],{"_39":40,"_41":42},[45],[57],{"_75":11,"_76":145},[78],{"_82":17,"_83":11,"_84":11},"tags",{"_149":62,"_150":-5,"_151":-5,"_152":153},"count","next","previous","results",[154],{"_91":45,"_92":93,"_94":95,"_96":97,"_98":99,"_100":155,"_51":117,"_118":93,"_119":29,"_120":57,"_39":121,"_122":123,"_124":108,"_125":117,"_126":65,"_127":17},[156],{"_103":104,"_94":95,"_105":106,"_107":108,"_109":110,"_111":112,"_113":106,"_114":-5,"_115":97,"_63":65,"_116":-5},[],"canDeleteTags","actionData","errors"]

caixb/oracle

Tags for caixb/oracle