{"id":2617,"date":"2019-03-12T13:20:14","date_gmt":"2019-03-12T10:20:14","guid":{"rendered":"https:\/\/sysdba.org\/?p=2617"},"modified":"2025-07-02T06:01:07","modified_gmt":"2025-07-02T06:01:07","slug":"user-roles-privileges","status":"publish","type":"post","link":"https:\/\/sysdba.org\/en\/user-roles-privileges\/","title":{"rendered":"User, Roles & Privileges"},"content":{"rendered":"

User Management<\/p>\n

 <\/p>\n

Creating users
\n[crayon]
\nCREATE USER omer IDENTIFIED BY sifreniz<\/p>\n

DEFAULT TABLESPACE users<\/p>\n

TEMPORARY TABLESPACE temp<\/p>\n

QUOTA UNLIMITED ON users;
\n[\/crayon]
\nGranting privileges
\n[crayon]
\nGRANT connect TO omer;<\/p>\n

GRANT create table, create procedure TO omer;
\n[\/crayon]
\nLocking and Unlocking User Accounts
\n[crayon]
\nalter user user_name account lock;<\/p>\n

alter user user_name account unlock;
\n[\/crayon]
\nSpecifying passwords
\n[crayon]
\nalter user user_name identified by new_password;<\/p>\n

ALTER USER user_name IDENTIFIED BY new_password ACCOUNT UNLOCK;
\n[\/crayon]
\nConfiguring an account lock after several failed login attempts
\n[crayon]
\nALTER PROFILE “DEFAULT” LIMIT FAILED_LOGIN_ATTEMPTS DEFAULT<\/span><\/p>\n

ALTER PROFILE “DEFAULT” LIMIT FAILED_LOGIN_ATTEMPTS 9
\n[\/crayon]
\nConfiguring the DBMS to end a user session after being idle for a number of minutes.
\n[crayon]
\nALTER PROFILE “DEFAULT” LIMIT IDLE_TIME 15 –(resource_limit = “False” by default, isn’t active until the value is set to “True”.)
\n[\/crayon]
\nListing the users
\n[crayon]
\nset pages 999 lines 100<\/p>\n

col username format a20<\/p>\n

col status format a8<\/p>\n

col tablespace format a20<\/p>\n

col temp_ts format a20<\/p>\n

select username<\/p>\n

, account_status status<\/p>\n

, created<\/p>\n

, default_tablespace tablespace<\/p>\n

, temporary_tablespace temp_ts<\/p>\n

from dba_users<\/p>\n

order by username
\n[\/crayon]
\nRoles
\n[crayon]
\nselect *<\/p>\n

from dba_roles<\/p>\n

where role like ‘%role’
\n[\/crayon]
\nGranting roles to users
\n[crayon]
\nselect grantee<\/p>\n

, granted_role<\/p>\n

, admin_option<\/p>\n

from dba_role_privs<\/p>\n

where grantee like upper(‘%username’)<\/p>\n

\/
\n[\/crayon]
\nPrivileges granted to specific roles
\n[crayon]
\nselect privilege<\/p>\n

, admin_option<\/p>\n

from role_sys_privs<\/p>\n

where role like ‘%role’
\n[\/crayon]
\nThe table rights given to specific roles
\n[crayon]
\nselect owner || ‘.’ || table_name “TABLE”<\/p>\n

, column_name<\/p>\n

, privilege<\/p>\n

, grantable<\/p>\n

from role_tab_privs<\/p>\n

where role like ‘%role’
\n[\/crayon]
\nThe roles and privileges assigned to a specific user
\n[crayon]
\nSET SERVEROUTPUT ON<\/p>\n

SET VERIFY OFF<\/p>\n

SELECT a.granted_role “Role”,<\/p>\n

a.admin_option “Adm”<\/p>\n

FROM user_role_privs a;
\n[\/crayon]<\/p>\n

[crayon]
\nSELECT a.privilege “Privilege”,<\/p>\n

a.admin_option “Adm”<\/p>\n

FROM user_sys_privs a;
\n[\/crayon]
\nSET VERIFY ON<\/p>\n","protected":false},"excerpt":{"rendered":"

User Management, creating users…<\/p>\n","protected":false},"author":1,"featured_media":3448,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[383,338],"tags":[345],"class_list":["post-2617","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-dba_i","category-oracle-tr","tag-oracle"],"_links":{"self":[{"href":"https:\/\/sysdba.org\/en\/wp-json\/wp\/v2\/posts\/2617","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/sysdba.org\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/sysdba.org\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/sysdba.org\/en\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/sysdba.org\/en\/wp-json\/wp\/v2\/comments?post=2617"}],"version-history":[{"count":1,"href":"https:\/\/sysdba.org\/en\/wp-json\/wp\/v2\/posts\/2617\/revisions"}],"predecessor-version":[{"id":4820,"href":"https:\/\/sysdba.org\/en\/wp-json\/wp\/v2\/posts\/2617\/revisions\/4820"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/sysdba.org\/en\/wp-json\/"}],"wp:attachment":[{"href":"https:\/\/sysdba.org\/en\/wp-json\/wp\/v2\/media?parent=2617"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sysdba.org\/en\/wp-json\/wp\/v2\/categories?post=2617"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sysdba.org\/en\/wp-json\/wp\/v2\/tags?post=2617"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}