Hi
DBA-mates,
As we all know, creating a Schema or DB User is very easy
and we all do it in DBA’s life. Here, How to create database user and grant privileges in oracle.
However, there is some important points, which we need to
take care.
There is some points such as how to create User’s and
Grant them the Privileges.
How
to create User and grant the user:
1. Create DB User or Schema:
Note: Here username is ora-data.
$sqlplus '/as sysdba'
Before creating user, we should check either user is
existing or not.
SQL>select USERNAME from DBA_USERS where USERNAME like
'%ORA-DATA%';
Query:
CREATE USER user_name IDENTIFIED BY 'passsword' DEFAULT TABLESPACE
tablespace_name TEMPORARY TABLESPACE tablespace_name PROFILE DEFAULT ACCOUNT
UNLOCK;
Example:
SQL>CREATE USER ora-data IDENTIFIED BY ora-data
DEFAULT TABLESPACE user TEMPORARY TABLESPACE TEMP PROFILE DEFAULT ACCOUNT
UNLOCK;
Cross
check:
SELECT USERNAME, ACCOUNT_STATUS, DEFAULT_TABLESPACE, TEMPORARY_TABLESPACE,
CREATED from dba_users where USERNAME='ora-data';
Providing
GRANTS:
SQL>GRANT CONNECT, RESOURCE TO ora-data;
SQL>GRANT CREATE SESSION TO ora-data;
SQL>GRANT CONNECT TO ora-data;
SQL>GRANT SELECT ANY DICTIONARY to ora-data;
SQL>GRANT SELECT ANY TABLE to ora-data;
SQL>GRANT UNLIMITED TABLESPACE to ora-data;
SQL>ALTER USER ora-data ENABLE EDITIONS; (In R12.2
specially)
If you want to Grant only on any particular Table (tb1)
then find the below details:
SQL>GRANT SELECT ON tb1 to ora-data;
Suppose, if you want to GRANT for any particular OWNER
for particular TABLE then follow below steps:
Example:
OWNER= xxabc, TABLE= tb1, USERNAME=ora-data
SQL>conn xxabc/password
SQL>show user
xxabc
SQL> GRANT SELECT ON tb1 TO ora-data;
How
to Check the GRANT details:
1. DBA_SYS_PRIVS: To show information about user's
privileges related to System (DBA).
2. DBA_TAB_PRIVS: To show information about user's
privileges related to Tables.
3. DBA_ROLE_PRIVS: To show information about user's
privileges related to Roles.
To
know details:
SQL>desc DBA_SYS_PRIVS;
Some Column Name and details:
GRANTEE: It
is the name, role, or user that was assigned the privilege.
PRIVILEGE: It
is the privilege that is assigned.
ADMIN_OPTION: It
indicates if the granted privilege also includes the ADMIN option.
SQL>desc DBA_TAB_PRIVS;
GRANTEE: It
is the name of the user with granted access.
TABLE_NAME: It
is the name of the object (table, index, sequence, etc).
PRIVILEGE: It
is the privilege assigned to the GRANTEE for the associated object.
Hope this may useful and helpful. If there is any suggestions or corrections, please let us know either in comment box or contact us @https://ora-data.blogspot.in/
Some more useful links:
Regards,
ora-data Team
