User Management : How to create user "ASHWANI" who has full access to all objects in a specific schema, "METALS", in your Oracle database.

 How to create user "ASHWANI" who has full access to all objects in a specific schema, "METALS", in your Oracle database.

-- 1. Create the user

CREATE USER ashwani IDENTIFIED BY ashwani
    DEFAULT TABLESPACE users
    TEMPORARY TABLESPACE temp
    QUOTA UNLIMITED ON users;

-- 2. Allow the user to connect

GRANT CREATE SESSION TO ashwani;
-- 3. Grant privileges on all existing objects in METALS schema
BEGIN
    FOR r IN (SELECT owner, object_name, object_type
              FROM all_objects
              WHERE owner = 'METALS'
                AND object_type IN ('TABLE','VIEW','SEQUENCE','PROCEDURE','FUNCTION','PACKAGE')) LOOP
        EXECUTE IMMEDIATE 'GRANT ALL ON ' || r.owner || '.' || r.object_name || ' TO ashwani';
    END LOOP;
END;
/
-- 4. Allow access to future objects in METALS schema

GRANT SELECT ANY TABLE TO ashwani;   -- optional for future read access

-- Note: To auto-grant all privileges for new objects, use schema triggers.

-- 5. (Optional) If you want ashwani to create objects in METALS schema

-- GRANT CREATE ANY TABLE TO ashwani;
-- GRANT CREATE ANY VIEW TO ashwani;