ORACLE: Grant Privs and dba_roles

SQL> DESC DBA_OBJECTS
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ------------------
 OWNER                                                          VARCHAR2(30)
 OBJECT_NAME                                                    VARCHAR2(128)
 SUBOBJECT_NAME                                                 VARCHAR2(30)
 OBJECT_ID                                                      NUMBER
 DATA_OBJECT_ID                                                 NUMBER
 OBJECT_TYPE                                                    VARCHAR2(19)
 CREATED                                                        DATE
 LAST_DDL_TIME                                                  DATE
 TIMESTAMP                                                      VARCHAR2(19)
 STATUS                                                         VARCHAR2(7)
 TEMPORARY                                                      VARCHAR2(1)
 GENERATED                                                      VARCHAR2(1)
 SECONDARY                                                      VARCHAR2(1)

SQL> COLUMN OBJECT_NAME FORMAT A11
SQL> SELECT OWNER, OBJECT_NAME FROM DBA_OBJECTS WHERE OWNER = 'SCOTT';

OWNER                          OBJECT_NAME
------------------------------ -----------
SCOTT                          PK_DEPT
SCOTT                          DEPT
SCOTT                          EMP
SCOTT                          PK_EMP
SCOTT                          BONUS
SCOTT                          SALGRADE

6 rows selected.

SQL> CREATE USER NEWUSER IDENTIFIED BY NEWUSER;

User created.

SQL> ALTER USER NEWUSER DEFAULT TABLESPACE USERS
 TEMPORARY TABLESPACE TEMP;
SQL> GRANT CREATE SESSION TO NEWUSER;
Grant succeeded.
SQL> SELECT 'GRANT INSERT, UPDATE, DELETE ON '||OWNER||'.'||OBJECT_NAME||' TO NEWUSER;' FROM DBA_OBJECTS
  2  WHERE OWNER= 'SCOTT'
  3  AND (OBJECT_TYPE ='TABLE' OR OBJECT_TYPE='VIEW');

'GRANTINSERT,UPDATE,DELETEON'||OWNER||'.'||OBJECT_NAME||'TONEWUSER;'
--------------------------------------------------------------------------------
GRANT INSERT, UPDATE, DELETE ON SCOTT.DEPT TO NEWUSER;
GRANT INSERT, UPDATE, DELETE ON SCOTT.EMP TO NEWUSER;
GRANT INSERT, UPDATE, DELETE ON SCOTT.BONUS TO NEWUSER;
GRANT INSERT, UPDATE, DELETE ON SCOTT.SALGRADE TO NEWUSER;

From here you can copy paste the query to .sql file and run it from there or you can spool the file, edit
the spooled file and then run it as follows. We are naming our file as newuser.sql, LOCATED IN C:\HARRY
FOLDER.
SQL> @c:\harry\newuser.sql

Grant succeeded.

Grant succeeded.

Grant succeeded.

Grant succeeded.
=======================================
NOW,
SQL> DESC DBA_TAB_PRIVS
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- -------------
 GRANTEE                                               NOT NULL VARCHAR2(30)
 OWNER                                                 NOT NULL VARCHAR2(30)
 TABLE_NAME                                            NOT NULL VARCHAR2(30)
 GRANTOR                                               NOT NULL VARCHAR2(30)
 PRIVILEGE                                             NOT NULL VARCHAR2(40)
 GRANTABLE                                                      VARCHAR2(3)
 HIERARCHY                                                      VARCHAR2(3)
SQL> COLUMN OWNER FORMAT A10
SQL> COLUMN TABLE_NAME FORMAT A10
SQL> COLUMN PRIVILEGE FORMAT A10
SQL> COLUMN GRANTEE FORMAT A10

SQL> SELECT ROWNUM, GRANTEE, OWNER, TABLE_NAME, GRANTOR, PRIVILEGE FROM  DBA_TAB_PRIVS WHERE OWNER = 'SCOTT';

    ROWNUM GRANTEE    OWNER      TABLE_NAME GRANTOR    PRIVILEGE
---------- ---------- ---------- ---------- ---------- ----------
         1 HELLO      SCOTT      DEPT       SCOTT      DELETE
         2 HELLO      SCOTT      DEPT       SCOTT      INSERT
         3 HELLO      SCOTT      DEPT       SCOTT      SELECT
         4 HELLO      SCOTT      DEPT       SCOTT      UPDATE
         5 HELLO      SCOTT      EMP        SCOTT      DELETE
         6 HELLO      SCOTT      EMP        SCOTT      INSERT
         7 HELLO      SCOTT      EMP        SCOTT      SELECT
         8 HELLO      SCOTT      EMP        SCOTT      UPDATE
         9 HELLO      SCOTT      BONUS      SCOTT      DELETE
        10 HELLO      SCOTT      BONUS      SCOTT      INSERT
        11 HELLO      SCOTT      BONUS      SCOTT      SELECT
        12 HELLO      SCOTT      BONUS      SCOTT      UPDATE
        13 HELLO      SCOTT      SALGRADE   SCOTT      DELETE
        14 HELLO      SCOTT      SALGRADE   SCOTT      INSERT
        15 HELLO      SCOTT      SALGRADE   SCOTT      SELECT
        16 HELLO      SCOTT      SALGRADE   SCOTT      UPDATE
        17 NEWUSER    SCOTT      DEPT       SCOTT      DELETE
        18 NEWUSER    SCOTT      DEPT       SCOTT      INSERT
        19 NEWUSER    SCOTT      DEPT       SCOTT      UPDATE
        20 NEWUSER    SCOTT      EMP        SCOTT      DELETE
        21 NEWUSER    SCOTT      EMP        SCOTT      INSERT
        22 NEWUSER    SCOTT      EMP        SCOTT      UPDATE
        23 NEWUSER    SCOTT      BONUS      SCOTT      DELETE
        24 NEWUSER    SCOTT      BONUS      SCOTT      INSERT
        25 NEWUSER    SCOTT      BONUS      SCOTT      UPDATE
        26 NEWUSER    SCOTT      SALGRADE   SCOTT      DELETE
        27 NEWUSER    SCOTT      SALGRADE   SCOTT      INSERT
        28 NEWUSER    SCOTT      SALGRADE   SCOTT      UPDATE

28 rows selected.

SQL> SELECT ROWNUM, GRANTEE, OWNER, TABLE_NAME, GRANTOR, PRIVILEGE,
ROW_NUMBER() OVER(PARTITION BY GRANTEE,TABLE_NAME ORDER BY PRIVILEGE)RNK_PART 
FROM DBA_TAB_PRIVS WHERE OWNER = 'SCOTT'

    ROWNUM GRANTEE    OWNER      TABLE_NAME GRANTOR    PRIVILEGE    RNK_PART
---------- ---------- ---------- ---------- ---------- ---------- ----------
         9 HELLO      SCOTT      BONUS      SCOTT      DELETE              1
        10 HELLO      SCOTT      BONUS      SCOTT      INSERT              2
        11 HELLO      SCOTT      BONUS      SCOTT      SELECT              3
        12 HELLO      SCOTT      BONUS      SCOTT      UPDATE              4
         1 HELLO      SCOTT      DEPT       SCOTT      DELETE              1
         2 HELLO      SCOTT      DEPT       SCOTT      INSERT              2
         3 HELLO      SCOTT      DEPT       SCOTT      SELECT              3
         4 HELLO      SCOTT      DEPT       SCOTT      UPDATE              4
         5 HELLO      SCOTT      EMP        SCOTT      DELETE              1
         6 HELLO      SCOTT      EMP        SCOTT      INSERT              2
         7 HELLO      SCOTT      EMP        SCOTT      SELECT              3
         8 HELLO      SCOTT      EMP        SCOTT      UPDATE              4
        13 HELLO      SCOTT      SALGRADE   SCOTT      DELETE              1
        14 HELLO      SCOTT      SALGRADE   SCOTT      INSERT              2
        15 HELLO      SCOTT      SALGRADE   SCOTT      SELECT              3
        16 HELLO      SCOTT      SALGRADE   SCOTT      UPDATE              4
        23 NEWUSER    SCOTT      BONUS      SCOTT      DELETE              1
        24 NEWUSER    SCOTT      BONUS      SCOTT      INSERT              2
        25 NEWUSER    SCOTT      BONUS      SCOTT      UPDATE              3
        17 NEWUSER    SCOTT      DEPT       SCOTT      DELETE              1
        18 NEWUSER    SCOTT      DEPT       SCOTT      INSERT              2
        19 NEWUSER    SCOTT      DEPT       SCOTT      UPDATE              3
        20 NEWUSER    SCOTT      EMP        SCOTT      DELETE              1
        21 NEWUSER    SCOTT      EMP        SCOTT      INSERT              2
        22 NEWUSER    SCOTT      EMP        SCOTT      UPDATE              3
        26 NEWUSER    SCOTT      SALGRADE   SCOTT      DELETE              1
        27 NEWUSER    SCOTT      SALGRADE   SCOTT      INSERT              2
        28 NEWUSER    SCOTT      SALGRADE   SCOTT      UPDATE              3

28 rows selected.
=======================================================
Now say you have user that is locked OR NEEDS TO BE LOCKED, then use use
SQL> ALTER USER USERNAME ACCOUNT UNLOCK/LOCK;
To create a user,
SQL> CREATE USER USERNAMME IDENTIFIED BY PASSWORD;
SQL> GRANT CREATE SESSION, RESOURCE TO USERNAME;
SQL> ALTER USER USERNAME
       DEFAULT TABLESPACE USERS;
SQL> ALTER USER USERNAME
       DEFAULT TEMPORARY TABLESPACE TEMP;
SQL> ALTER USER USERNAME
       QUOTA UNLIMITED ON USERS;
SQL> ALTER USER USERNAME
        QUOTA 50M ON USERS;

ORACLE: DBA_ROLES

SQL> SELECT ROWNUM, ROLE FROM DBA_ROLES;

    ROWNUM ROLE
---------- ------------------------------
         1 CONNECT 2 RESOURCE 3 DBA
         4 SELECT_CATALOG_ROLE
         5 EXECUTE_CATALOG_ROLE
         6 DELETE_CATALOG_ROLE
         7 EXP_FULL_DATABASE
         8 IMP_FULL_DATABASE
         9 RECOVERY_CATALOG_OWNER
        10 GATHER_SYSTEM_STATISTICS
        11 LOGSTDBY_ADMINISTRATOR
        12 AQ_ADMINISTRATOR_ROLE
        13 AQ_USER_ROLE
        14 GLOBAL_AQ_USER_ROLE
        15 SCHEDULER_ADMIN
        16 HS_ADMIN_ROLE
        17 AUTHENTICATEDUSER
        18 OEM_ADVISOR
        19 OEM_MONITOR
        20 WM_ADMIN_ROLE
        21 JAVAUSERPRIV
        22 JAVAIDPRIV
        23 JAVASYSPRIV
        24 JAVADEBUGPRIV
        25 EJBCLIENT
        26 JAVA_ADMIN
        27 JAVA_DEPLOY
        28 CTXAPP
        29 XDBADMIN
        30 XDBWEBSERVICES
        31 OLAP_DBA
        32 OLAP_USER
        33 MGMT_USER

33 rows selected.

Comments

Popular posts from this blog

Query for Tablespace usage with Autoextend

How to configure multiple Oracle listeners

sheel script - automatic tablespace addition in oracle 11g