Voir les rôles/droits assignés à un user

C'est une demande récurrente. Pouvoir voir les rôles/droits accordés à un user. Si la base de données est bien gérée, tous les droits devraient être accordés via des rôles correctement nommés.

La vue dba_role_privs

Cette vue permet de données tous les rôles accordés et à qui ils ont été accordés. Les colonnes intéressantes sont celles-ci:

• grantee : Celui à qui on accorde le rôle
• granted_role : Le rôle autorisé

La vue dba_tab_privs

Mais comme dans la plupart des bases de données on trouve des droits accordés en dehors des rôles, il faut aller sur la vue dba_tab_privs. Cette vue permet de récupérer tous les droits accordés à un user sur un objet de la base. Les colonnes intéressantes sont celles-ci :

• grantee : Celui à qui on accorde des droits
• owner : Le propriétaire de l'objet sur lequel des droits ont été accordés
• table_name : Ce nom est ambigu, il s'agit bien de nom de l'objet (qui peut être une table ou pas)
• grantor : Celui qui a donné le droit
• privilege : Le droit accordé

Les vues role_*_privs

Si les rôles n'ont pas été correctement nommés et si la base n'est pas correctement documentée, il est possible d'oublier quels droits sont donnés par un rôle. C'est là que les vues role_role_privs, role_sys_privs et role_tab_privs peuvent vous aider. Malheureusement, le SQL ne permet pas de récursivité pour récupérer l'intégralité des rôles grantés à un autre rôle. Voir le script proposés après.

Tous les droits pour un user

La problématique est la même que pour les rôles. Il n'est pas possible de faire du récursif en SQL. Or, c'est nécessaire dans ce cas car on peut attribuer un rôle à un autre rôle un paquet de fois! Pour corriger ce problème, je vous conseille d'aller faire un tour sur le site de Pete Finnigan qui, même s'il a un sacré problème dans le choix des couleurs de son blog, a écrit une procédure stockée en PL/SQL qui peut déterminer l'ensemble des droits d'un rôle ou d'un user. http://www.petefinnigan.com/weblog/archives/00001243.htm

Pour en savoir plus

Vous pouvez regarder la définition des vues sur le livre Database Reference Oracle. En 11G : http://docs.oracle.com/cd/E11882_01/server.112/e40402/toc.htm

Jouer avec les Wallet

Oracle permet de stocker les mots de passe dans des "wallets" sécurisés ce qui permet par la suit de se connecter sans saisir ni login ni mot de passe.

Création du Wallet

On commence par créer le wallet et son mot de passe. (Il faut que le mot de passe satisfasse les conditions de sécurité Oracle)

mkstore -wrl "dir" -create

dir est le répertoire où vous allez stocker tous les fichiers du Wallet

Ajout d'un alias

On ajout un alias au Wallet ainsi :

mkstore -wrl dir -createCredential ALIAS user psswd

Au prompt, il faut donner le mot de passe du wallet.

Il faut ensuite rajouter une entrée dans le tnsnames qui comporte l'alias donné au Wallet.

Liste des alias

Pour lister les alias déjà connus :

mkstore -wrl dir -listCredential

Au prompt, il faut donner le mot de passe du wallet.

Supprimer un alias

C'est tout simplement :

mkstore -wrl dir -deleteCredential ALIAS

Au prompt, il faut donner le mot de passe du wallet.

Créer un user en lecture seule sur un autre schéma

C'est une demande fréquente : les développeurs souhaitent avoir un user en lecture seule sur les objets d'un autre schéma...

Le rôle

Encore et toujours, on commence par créer le rôle correspondant à ces droits :

CREATE ROLE role_name NOT IDENTIFIED;
GRANT CONNECT TO role_name;
GRANT CREATE SESSION TO role_name;
GRANT RESOURCE TO role_name;
GRANT CREATE SYNONYM TO role_name;
GRANT UNLIMITED TABLESPACE TO role_name;

Il faut ensuite récupérer les GRANT SELECT à effectuer, c'est possible grâce à ces requêtes :

SELECT 'GRANT SELECT ON schema.'||TABLE_NAME||' TO role_name;' FROM dba_tables WHERE owner='owner';
SELECT 'GRANT SELECT ON schema.'||VIEW_NAME||'TO role_name;' FROM dba_views WHERE owner='owner';

Le user

On crée alors le user :

CREATE USER user_name IDENTIFIED BY pswd DEFAULT TABLESPACE tbs_name;Et on le grant :

GRANT role TO user;
GRANT UNLIMITED TABLESPACE TO user;

Synonymes

la création de synonymes n'est pas obligatoire mais facilite la vie du user... Autant le faire ! Il faut exécuter le résultat de ces deux requêtes :

SELECT 'CREATE SYNONYM ' ||TABLE_NAME|| ' FOR schema.' ||TABLE_NAME||';' FROM ALL_TABLES WHERE OWNER='owner';
SELECT 'CREATE SYNONYM ' ||VIEW_NAME|| ' FOR schema.' ||VIEW_NAME||';' FROM ALL_VIEWS WHERE OWNER='owner';

Et voilà!

Gestion des users "standards" sous Oracle

Tu es content, tu as fini d'installer et de configurer ta plateforme Oracle... Et là, on te demande un user standard avec tous les droits sur son schéma.
Comme d'habitude tu ne fais que de la migration, tu ne sais pas trop comment faire...
Respire, ça va bien se passer!

Les rôles, pensez-y!

Les rôles ont été créés par Oracle pour simplifier l'attribution des mêmes droits à plusieurs utilisateurs. Et même si aujourd'hui on ne te demande qu'un seul utilisateur, tu auras l'air malin si dans 2 ans on t'en demande un autre avec les mêmes droits!

Voilà les droits à "granter" pour obtenir un rôle permettant un accès total à tous les objets de son propre schéma :

CREATE ROLE role_name NOT IDENTIFIED;
GRANT CONNECT TO role_name;
GRANT CREATE SESSION TO role_name;
GRANT RESOURCE TO role_name;
GRANT CREATE CLUSTER TO role_name;
GRANT CREATE INDEXTYPE TO role_name;
GRANT CREATE OPERATOR TO role_name;
GRANT CREATE PROCEDURE TO role_name;
GRANT CREATE SEQUENCE TO role_name;
GRANT CREATE TABLE TO role_name;
GRANT CREATE TRIGGER TO role_name;
GRANT CREATE TYPE TO role_name;
GRANT UNLIMITED TABLESPACE TO role_name;

Et les tablespaces ???

Ne pas oublier de créer des tablespaces pour mettre les données du user. Cette étape est facultative, mais recommandée.

CREATE TABLESPACE tbs__dbo_indx01 DATAFILE '+_INDX' SIZE your_size AUTOEXTEND ON NEXT your_next_extend_size MAXSIZE your maxsize_or_unlimited;

CREATE TABLESPACE your_tbs_name DATAFILE 'your_datafile_path' SIZE your_datafile_size AUTOEXTEND ON  NEXT your_next_extend_size MAXSIZE your_max_size_or_unlimited;

Enfin, le user

Il n'y a plus qu'à créer le user

CREATE USER user_name IDENTIFIED BY your_psswd DEFAULT TABLESPACE your_tbs_name ;

Et lui donner les droits :

GRANT your_role TO user;
GRANT UNLIMITED TABLESPACE TO user;