45 lines
1.5 KiB
SQL
45 lines
1.5 KiB
SQL
-- Oracle Common User Setup for Weather Connect
|
|
-- Run this as SYSTEM or SYSDBA user
|
|
-- This creates a common user that works across all containers
|
|
|
|
-- 1. Check current environment
|
|
COLUMN is_cdb FORMAT A10
|
|
SELECT DECODE(cdb, 'YES', 'CDB', 'Regular') as is_cdb FROM v$database;
|
|
SHOW CON_NAME;
|
|
|
|
-- 2. Create common user (prefixed with C##)
|
|
CREATE USER C##weather IDENTIFIED BY weather123
|
|
DEFAULT TABLESPACE USERS
|
|
TEMPORARY TABLESPACE TEMP
|
|
QUOTA UNLIMITED ON USERS
|
|
CONTAINER=ALL;
|
|
|
|
-- 3. Grant necessary privileges
|
|
GRANT CONNECT TO C##weather CONTAINER=ALL;
|
|
GRANT RESOURCE TO C##weather CONTAINER=ALL;
|
|
GRANT CREATE SESSION TO C##weather CONTAINER=ALL;
|
|
GRANT CREATE TABLE TO C##weather CONTAINER=ALL;
|
|
GRANT CREATE SEQUENCE TO C##weather CONTAINER=ALL;
|
|
GRANT CREATE VIEW TO C##weather CONTAINER=ALL;
|
|
-- Note: CREATE INDEX privilege is included in RESOURCE role
|
|
-- GRANT CREATE INDEX TO C##weather CONTAINER=ALL; -- This is redundant with RESOURCE
|
|
|
|
-- Optional: Grant additional privileges if needed
|
|
-- GRANT CREATE PROCEDURE TO C##weather CONTAINER=ALL;
|
|
-- GRANT CREATE TRIGGER TO C##weather CONTAINER=ALL;
|
|
|
|
COMMIT;
|
|
|
|
-- 4. Verify user creation
|
|
SELECT username, common, default_tablespace, temporary_tablespace, account_status
|
|
FROM dba_users
|
|
WHERE username = 'C##WEATHER';
|
|
|
|
-- 5. Display granted privileges
|
|
SELECT grantee, privilege
|
|
FROM dba_sys_privs
|
|
WHERE grantee = 'C##WEATHER'
|
|
ORDER BY privilege;
|
|
|
|
-- 6. Test connection as common user
|
|
-- CONNECT C##weather/weather123@pve-ora19c-1:1521/SHED; |