There is no EDW (Enterprise Data Warehouse) system allowing only one user to access and operate with its data 🙂 There as in any other system Oracle will have to deal with different types of users and restrict their access. A multi-user environment requires strict and thought-out management or you will run into trouble sooner rather than later. In this article, I will introduce you to users, privileges, roles and profiles management.
In Oracle, you can create, alter and delete users. Every user is associated with a password and that allows him (not only this – read further 🙂 ) to connect to the database.
The CREATE USER is a very powerful command and therefore is limited to DBAs and SYSADMINs only.
Here is an example of creating a user:
CREATE USER oracle_world IDENTIFIED BY mySecretPassword DEFAULT TABLESPACE common_tablespace QUOTA 10M ON common_tablespace TEMPORARY TABLESPACE temp QUOTA 5M ON system PROFILE dba_user PASSWORD EXPIRE;
Let me elaborate on every single line:
- followed by your desired username
- username cannot be the same as the role name
- you can use such user as a proxy user as well
- followed by your password (password policies are set within profiles – see below)
- followed by your defined tablespace (list from dba_tablespaces)
- this is a space where users will have their objects stored by default (default is SYSTEM but it’s highly recommended to properly structure tablespaces)
- this defines how much data can user store in a particular tablespace (defined in M as megabytes or G as gigabytes)
- you cannot set a quota on the temporary tablespace
- by default, there is no quota applied
- you can revoke the user rights to store anything by setting the quota to 0
- you can set unlimited tablespace on the user by executing this command:
GRANT UNLIMITED TABLESPACE TO oracle_world
- this defines where temporary objects will be stored
- whenever user issues any SQL statement – its data has to be stored in TEMP tablespace (check out this article about physical storage structures)
- followed by a profile name (dba_profiles)
- this is a set of restrictions and limits on database resources (find more below)
You can change user settings by issuing ALTER USER (all parameters allowed by creation are allowed here as well):
ALTER USER oracle_world DEFAULT TABLESPACE another_tablespace PROFILE dev_user;
With the same command, you can change the password
ALTER USER oracle_world IDENTIFIED BY myNewPassword; -- no single quotes !! -- or eventually ALTER USER oracle_world IDENTIFIED BY myNewPassword REPLACE mySecretPassword;
Or eventually, (un)lock the account
ALTER USER oracle_world ACCOUNT LOCK; -- to unlock ALTER USER oracle_world ACCOUNT UNLOCK;
Lastly, you can drop the user easily with DROP USER command
-- simple user drop DROP USER oracle_world; -- more complex (will check all associated user's object and foreign keys dependent on user's schema .. and drop them all as well) DROP USER oracle_world CASCADE;ontrol c
Before you issue this command, check if you can safely drop a user.
There are two types of privileges:
- SYSTEM – allow users to perform database operations; they can be very powerful so you (as a DBA) should be very mindful when granting those
- OBJECT – allow users to access and manipulate objects in the database
For more details about privileges check this article about Permissions.
Due to the complexity and a huge number of objects inside any database, it’s advisable to group object privileges into roles and grant those to users. Not only you can assign object privileges to a role but also you can nest other roles as well.
I strongly suggest not to go too deep with nested roles – don’t forget the KISS (Keep It Simple, Stupid) principle. It works 🙂
The following commands can be used to maintain roles: CREATE or DROP roles and GRANT them to users or roles. Column-level access can be controlled as well, as you can see in an example below.
--create roles CREATE ROLE access_data; CREATE ROLE read_data; -- grant role to a role GRANT read_data TO access_data; -- grant role to a user GRANT access_data TO oracle_world; -- yes this user has both roles now -- revoke role REVOKE access_data FROM oracle_world; -- drop DROP ROLE read_data; -- column level control GRANT INSERT (name, salary) ON employees TO oracle_world;
You can also control row-level access with VPD (Virtual Private Database). However, since this might be another article easily 🙂 please google it and check to documentation for more details.
There is more thing you can do with granting roles. You can grant them with extra permissions 🙂 Sounds catchy? Read on!
There are two types of extra privileges
- GRANT role TO user WITH GRANT OPTION
- GRANT role TO user WITH ADMIN OPTION
With Grant Option:
- applicable on object privileges only (not system privileges)
- only the user who granted it can revoke it
- you can use CASCADE again to revoke many subsequent grants
- can be granted to a role
With Admin Option:
- applicable on system privileges only (not object privileges)
- cannot be granted to a role
Check this script out to see how to List all privileges for a particular user.
Same as users, you can CREATE, ALTER and DROP profiles.
CREATE PROFILE dev_user LIMIT SESSIONS_PER_USER UNLIMITED CPU_PER_SESSION UNLIMITED CPU_PER_CALL 3000 CONNECT_TIME 45 LOGICAL_READS_PER_SESSION DEFAULT LOGICAL_READS_PER_CALL 1000 PRIVATE_SGA 15K COMPOSITE_LIMIT 5000000 FAILED_LOGIN_ATTEMPTS 5 PASSWORD_LIFE_TIME 60 PASSWORD_REUSE_TIME 60 PASSWORD_REUSE_MAX 5 PASSWORD_VERIFY_FUNCTION verify_function PASSWORD_LOCK_TIME 1/24 PASSWORD_GRACE_TIME 10;
Because all of them are self-explanatory I am not gonna describe every single entry there 🙂
You can also assign a profile to a user, which will limit him according to profile settings.
ALTER USER oracle_world PROFILE dev_user;
As you can with cascade the related objects when dropping a user, you can CASCADE the action too. When you run the
DROP PROFILE dev_user CASCADE;
all users connected to the database with this profile will be automatically assigned to a default profile.
Here are some most useful dba catalogs regarding user management.
- DBA_USERS – list of users and their details
- DBA_TS_QUOTAS – quota for users and their usage
- DBA_PROFILES – list of profiles and their settings
- PROXY_USERS – list of proxy users and users allowed to use them
There is one last catch I would like to point out. It is a “feature” (which is rather a bug IMO) that restricts you from using PL/SQL objects (procedures, packages, … ) when you grant an object to a role that is assigned to you. Somehow, you have to grant it directly to a user as well to work properly. Don’t ask me why 🙂
Hope this article helped you to understand the basics of what does it take to manage users and setup your environment accordingly.