In MySQL 8, how do you distinguish between Roles and Users in table mysql.user?
Hi there, In MySQL 8, how can you figure out if an entry in the mysql.user table is a role or a user? With regards, Martijn Tonies Upscene Productions http://www.upscene.com Database Workbench - developer tool for Oracle, MS SQL Server, PostgreSQL, SQL Anywhere, MySQL, InterBase, NexusDB and Firebird.
In MySQL 8.0, how does one recognize roles?
Hi, In MySQL 8.0, if you use CREATE ROLE, it seems to create an entry in mysql.users But how does one distinguish between roles and users? With regards, Martijn Tonies Upscene Productions http://www.upscene.com Database Workbench - developer tool for Oracle, MS SQL Server, PostgreSQL, SQL Anywhere, MySQL, InterBase, NexusDB and Firebird.
RE: MySQL Roles and Groups
Hello Trimurthy, As far as I know, MySQL does not have such thing implemented as other database systems. -- Adrián Espinosa. Engineering Support, Wholesale Systems. Jazztel.com -Mensaje original- De: Trimurthy [mailto:trimur...@tulassi.com] Enviado el: viernes, 14 de diciembre de 2012 10:11 Para: mysql@lists.mysql.com Asunto: MySQL Roles and Groups Hi list, I want to know if there is a feature for creating roles or groups in MySQL? I want to create a user role Managers, where i want to add all the manager logins and then give specific permissions to Managers role on the tables. Can that be done in MySQL? Normal 0 false false false EN-US X-NONE AR-SA Thanks Kind Regards, TRIMURTHY Este mensaje es privado y CONFIDENCIAL y se dirige exclusivamente a su destinatario. Si usted ha recibido este mensaje por error, no debe revelar, copiar, distribuir o usarlo en ningún sentido. Le rogamos lo comunique al remitente y borre dicho mensaje y cualquier documento adjunto que pudiera contener. El correo electrónico via Internet no permite asegurar la confidencialidad de los mensajes que se transmiten ni su integridad o correcta recepción. JAZZTEL no asume responsabilidad por estas circunstancias. Si el destinatario de este mensaje no consintiera la utilización del correo electrónico via Internet y la grabación de los mensajes, rogamos lo ponga en nuestro conocimiento de forma inmediata.Cualquier opinión expresada en este mensaje pertenece únicamente al autor remitente, y no representa necesariamente la opinión de JAZZTEL, a no ser que expresamente se diga y el remitente esté autorizado para hacerlo. This message is private and CONFIDENTIAL and it is intended exclusively for its addressee. If you receive this message in error, you should not disclose, copy, distribute this e-mail or use it in any other way. Please inform the sender and delete the message and attachments from your system.Internet e-mail neither guarantees the confidentiality nor the integrity or proper receipt of the messages sent. JAZZTEL does not assume any liability for those circumstances. If the addressee of this message does not consent to the use of Internet e-mail and message recording, please notify us immediately.Any views or opinions contained in this message are solely those of the author, and do not necessarily represent those of JAZZTEL, unless otherwise specifically stated and the sender is authorised to do so. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: create roles/groups in MYSQL
There's nothing built in but if you want explore this it is an extension http://www.securich.com/ On 1 Aug 2012 21:56, Aastha aast...@gmail.com wrote: Hello, I have 10 different users and i have to give different accesses to different tables. Is it possible to create a groups with different set of access rights on different tables. I know there are ROLES and PROFILES in Oracle. Is there something similar in MySQL. Thanks, Aastha Gupta
Re: create roles/groups in MYSQL
There's nothing built in but if you want explore this it is an extension http://www.securich.com/ That seems like a nice extension. With regards, Martijn Tonies Upscene Productions http://www.upscene.com Download Database Workbench for Oracle, MS SQL Server, Sybase SQL Anywhere, MySQL, InterBase, NexusDB and Firebird! On 1 Aug 2012 21:56, Aastha aast...@gmail.com wrote: Hello, I have 10 different users and i have to give different accesses to different tables. Is it possible to create a groups with different set of access rights on different tables. I know there are ROLES and PROFILES in Oracle. Is there something similar in MySQL. Thanks, Aastha Gupta -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
create roles/groups in MYSQL
Hello, I have 10 different users and i have to give different accesses to different tables. Is it possible to create a groups with different set of access rights on different tables. I know there are ROLES and PROFILES in Oracle. Is there something similar in MySQL. Thanks, Aastha Gupta
RE: create roles/groups in MYSQL
Sorry, nothing like Roles or Profiles. Copy Paste. -Original Message- From: Aastha [mailto:aast...@gmail.com] Sent: Wednesday, August 01, 2012 1:56 PM To: mysql@lists.mysql.com Cc: Aastha Subject: create roles/groups in MYSQL Hello, I have 10 different users and i have to give different accesses to different tables. Is it possible to create a groups with different set of access rights on different tables. I know there are ROLES and PROFILES in Oracle. Is there something similar in MySQL. Thanks, Aastha Gupta -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
UPDATE: 2 MySQL DBA FULLTIME ROLES located in Westlake Village, CA and Miami Beach, FL
MySQL DBA We seek to hire a talented MySQL DBA to join our dynamic teams in California and Miami. Both roles are for onsite roles (no telecommuting). We are a well established multimedia/online dating organization focused primarily on the development of Internet media properties and is a leading global provider of original information, images and digital content that serves millions of customers around the globe. We have a corporate infrastructure on the cutting edge of technology. Additionally, we hold several patents and copyrights associated with our Internet properties and technology. DESCRIPTION Designs, analyzes, deploys, monitors, maintains and optimizes MySQL5 database servers. Analyzes requirements and produces optimal database schema. Maintains complex replication setup and manages data partitioning across multiple clusters/data centers. Refactors databases as needed to accommodate functionality changes. Works with application development to successfully implement new or modify existing features. REQUIREMENTS Key Functions: · Install, configure, manage and maintain multiple MySQL servers and databases using InnoDB MyISAM storage engines. · Data model design and recommendations: optimize DB schema, normalization, denormalization, query analysis and index optimization; database refactoring. · MySQL5 replication: multi-master, scripted role change (promotion, demotion, change master), utility scripts, mysql5 clustering; ability to work at binlog level. · Business Resumption Processes: automated failover and recovery. · Performance optimization at schema level, service level, OS level, hardware level. · Perform appropriate back-up, restoration and upgrades of database servers. · Create processes to ensure the data quality of the information by identifying potential data errors. · Capacity planning, health monitoring and diagnostics. · Act as lead on database systems. · Provide assistance to developers, recommending best practices. · Creation and development of ad hoc and customized reports. Supervisory Responsibilities: No direct reports, must be able to work effectively with all levels within the company. Mathematical Skills: Ability to apply mathematic skills for analysis of data, programming logic and implement mathematic functions as needed. Send resume to: sumak...@nerd.com or sumak...@globalpersonalsmedia.com or visit: Miami link to job posting: http://tbe.taleo.net/NA7/ats/careers/requisition.jsp?org=WORLDNETMEDIAcws=1rid=40 California link to job posting: http://tbe.taleo.net/NA7/ats/careers/requisition.jsp?org=WORLDNETMEDIAcws=1rid=81 -- Thank you, Sumaklos S. Rembert Vice President, Staffing Recruiting 705 Washington Ave Miami Beach, FL 33139 O: (305) 677-3384 F: (208) 275-7145 ICQ: 390080780 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
roles table design
HI, I'm developing a cms, I need some suggessions regarding database design. I'm creating role table in which role name will be unique, so my question is that should I create roleid(int, autoincreament, primary key )? Same question for users table. Note: I'll have user role mapping table. -- Sharique uddin Ahmed Farooqui (C++/C# Developer, IT Consultant) http://safknw.blogspot.com/ Peace is the Ultimate thing we want. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Does MySQL 5.2 include support for Roles Based Access Control?
I find some code about role management in mysql5.2 's source code \mysql-5.2.0-falcon-alpha\storage\falcon Does MySQL 5.2 include support for roles based access control now? Does it be supported by falcon engine?
MySQL and ROLES
Hey I'm fairly new to MYSQL (installed 4.0.18 yesterday, but have played with it in the past), and I must say, I'm really really impressed about the speed, and the easy of administrating the server and it's data! But, as I've looked through the documentation, I miss the prescence of 'ROLE' in MySQL Is it true, that at this point, there's no 'ROLE'-support in MYSQL, or did I just overlook it in the large documentation? Kindest regards, Bram
Re: MySQL and ROLES
Bram Mariën wrote: Hey I'm fairly new to MYSQL (installed 4.0.18 yesterday, but have played with it in the past), and I must say, I'm really really impressed about the speed, and the easy of administrating the server and it's data! But, as I've looked through the documentation, I miss the prescence of 'ROLE' in MySQL Is it true, that at this point, there's no 'ROLE'-support in MYSQL, or did I just overlook it in the large documentation? Kindest regards, Bram It is not supported -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL and ROLES
At 9:01 +0100 3/17/04, Bram Mariën wrote: Hey I'm fairly new to MYSQL (installed 4.0.18 yesterday, but have played with it in the past), and I must say, I'm really really impressed about the speed, and the easy of administrating the server and it's data! But, as I've looked through the documentation, I miss the prescence of 'ROLE' in MySQL Is it true, that at this point, there's no 'ROLE'-support in MYSQL, or did I just overlook it in the large documentation? You didn't overlook it. It's not there. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com MySQL Users Conference: April 14-16, 2004 http://www.mysql.com/uc2004/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Multiple Roles
In case that a user has multiple roles; for example, John Dole is both author and editor, 1. I should have two rows for John Dole? John Dole author John Dole editor or. I should have only one row and use comma ',' to separate the roles? John Dole author, editor 2. How do I create the table for the second case (see below)? create table user_roles ( user_name varchar(15) not null, role_name varchar(15) not null, varchar(15) null ); __ Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard http://antispam.yahoo.com/whatsnewfree -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Multiple Roles
Caroline Jen [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] In case that a user has multiple roles; for example, John Dole is both author and editor, 1. I should have two rows for John Dole? John Dole author John Dole editor or. I should have only one row and use comma ',' to separate the roles? John Dole author, editor 2. How do I create the table for the second case (see below)? create table user_roles ( user_name varchar(15) not null, role_name varchar(15) not null, varchar(15) null ); for the second option, you can use VARCHAR for roles_names, only make sure that you have enough space to define there all combinations of roles. you can also use BLOB for it (VARCHAR is up to 255 chars length). CREATE TABLE user_roles ( user_nameVARCHAR(15) NOT NULL, roles_names VARCHAR(31) NOT NULL ); roles_names is of length 31 cuz the comma is also a char. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Multiple Roles
Hi, In case that a user has multiple roles; for example, John Dole is both author and editor, 1. I should have two rows for John Dole? John Dole author John Dole editor or. I should have only one row and use comma ',' to separate the roles? John Dole author, editor 2. How do I create the table for the second case (see below)? create table user_roles ( user_name varchar(15) not null, role_name varchar(15) not null, varchar(15) null ); for the second option, you can use VARCHAR for roles_names, only make sure that you have enough space to define there all combinations of roles. you can also use BLOB for it (VARCHAR is up to 255 chars length). CREATE TABLE user_roles ( user_nameVARCHAR(15) NOT NULL, roles_names VARCHAR(31) NOT NULL ); roles_names is of length 31 cuz the comma is also a char. I would advise against this one. First of all: it breaks normal table design. Second: if you add more roles, you need to adjust your metadata (because of (1)). Third: you will run into problems when doing queries. Having a compound primary key which has multiple rows in the table for each role is the normal design. With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server. Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Multiple Roles
On Fri, 2 Jan 2004, Caroline Jen wrote: In case that a user has multiple roles; for example, John Dole is both author and editor, 1. I should have two rows for John Dole? John Dole author John Dole editor or. I should have only one row and use comma ',' to separate the roles? John Dole author, editor 2. How do I create the table for the second case (see below)? create table user_roles ( user_name varchar(15) not null, role_name varchar(15) not null, varchar(15) null ); If the roles will not be very dynamic and could be hardcoded you might be able to use the SET datatype which is described here: http://www.mysql.com/doc/en/SET.html If you will add/update/change/delete roles often, then you should go for one of the other methods suggested instead, but if the roles are static, this might work better for you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Multiple Roles
On Jan 2, 2004, at 4:28 AM, Caroline Jen wrote: 1. I should have two rows for John Dole? John Dole author John Dole editor or. I should have only one row and use comma ',' to separate the roles? John Dole author, editor 2. How do I create the table for the second case (see below)? create table user_roles ( user_name varchar(15) not null, role_name varchar(15) not null, varchar(15) null ); Both are poor solutions. You should have a person table and a role table, and join them using a third (typically called an allocation or assignment table, or simply a many-to-many table). This third table contains only the PKs of the person and their role. Typically it has only three columns (its own PK, person_FK and role_FK), but can optionally have additional columns if additional information about the relationship is needed. It is then a matter of joining the person table to the role table through the allocation table to get a list of all roles for a given person. Reversing the queries then gives you all people who have a given role. ___/ / __/ / / Ed Leafe Linux Love: unzip;strip;touch;finger;mount;fsck;more;yes;umount;sleep -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Multiple Roles
* Caroline Jen In case that a user has multiple roles; for example, John Dole is both author and editor, 1. I should have two rows for John Dole? John Dole author John Dole editor or. I should have only one row and use comma ',' to separate the roles? John Dole author, editor I see you allready got some relevant replies, I just wanted to add some comments and advise on normalization, which seems to be the core of this question. You should _never_ separate data with comma in a column. This violates the first normal form, called 1NF, which states that a column should contain a single value of the same type for each row. You should have _one_ row for John Dole in the users table, and two corresponding rows in a roles table. In addition you need a table to hold the combinations. 2. How do I create the table for the second case (see below)? create table user_roles ( user_name varchar(15) not null, role_name varchar(15) not null, varchar(15) null ); To normalize this fully, you need three tables: CREATE TABLE users ( uid int unsigned not null primary key auto_increment, name varchar(30) not null, unique(name) ); CREATE TABLE roles ( rid int unsigned not null primary key auto_increment, role varchar(30) not null, unique(role) ); CREATE TABLE user_roles ( uid int unsigned not null, rid int unsigned not null, primary key (uid,rid), unique (rid,uid) ); The primary keys are made as small/compact as possible, in this case 4 bytes for users and roles, and 8 bytes for the combination. You could make this even more compact, for instance using TINYINT for the rid column if you don't have more than 250 roles and SMALLINT for uid if you have less than 65000 users. This does not matter much for small tables, but when your data is considerably larger than the computer memory, these things become important. Note that if you need to change the spelling of an existing name or role, you just have to change it one place, in the 'users' or 'roles' table. The key (uid/rid) is unchanged, thus the rows in user_roles does not need to change. Also note that the data that consumes space (the VARCHAR columns) are stored only once for each value, and a smaller column, a 4 byte INTEGER is used as the key, representing the value stored in the VARCHAR. Now, if you had 50.000 users with an average of 100 roles each, that would be 5M rows in your user_roles table. With a non-normalized approach, you would store avg(length(name)) + avg(length(role)) bytes for each row, say 15 + 10 = 25 bytes = 125MB. With the normalized approach suggested above you store only 8 bytes for each row = 40MB in total, compacting further using TINYINT and SMALLINT you would store only 15MB. Inserting test data: INSERT INTO users SET name = 'John Dole'; SET @uid:=LAST_INSERT_ID(); INSERT INTO roles SET role = 'author'; INSERT INTO user_roles SET [EMAIL PROTECTED],rid=LAST_INSERT_ID(); INSERT INTO roles SET role = 'editor'; INSERT INTO user_roles SET [EMAIL PROTECTED],rid=LAST_INSERT_ID(); Now the tables looks like this: mysql select * from users; +-+---+ | uid | name | +-+---+ | 1 | John Dole | +-+---+ 1 row in set (0.01 sec) mysql select * from roles; +-++ | rid | role | +-++ | 1 | author | | 2 | editor | +-++ 2 rows in set (0.01 sec) mysql select * from user_roles; +-+-+ | uid | rid | +-+-+ | 1 | 1 | | 1 | 2 | +-+-+ 2 rows in set (0.00 sec) To select all roles for a user: SELECT role FROM roles NATURAL JOIN user_roles NATURAL JOIN users WHERE name = 'John Dole' To select all users of a role: SELECT name FROM users NATURAL JOIN user_roles NATURAL JOIN roles WHERE role = 'editor' To insert a user/role combination: 1. Get the key for the name: SELECT uid FROM users WHERE name = '$name' 2. If the name did not exist, create it: INSERT INTO users SET name = '$name'; Get the key: SELECT LAST_INSERT_ID() 3. Get the key for the role: SELECT rid FROM roles WHERE role = '$role' 4. If the role did not exist, create it: INSERT INTO roles SET role = '$role'; Get the key: SELECT LAST_INSERT_ID() 5. Insert the user_roles row: INSERT user_roles SET uid=$uid,rid=$rid; If the final INSERT fails, the user/role combination allready existed. If any of the other INSERTs fails you have a collision: two users are creating the same user or role at the same time. In that case you should redo the previous SELECT (step 1 or 3), or take the easy way out and just restart from step 1. -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Oracle roles in MySQL?
Does MySQL offer anything to take the place of Oracle's roles? I'm especially interested in the ability, when adding a new table (or a new view, in the future) to the database, to be able to grant the desired access to everyone who ought to have access, without having to enumerate those people. Specifically, GRANT SELECT, INSERT ON my_new_table TO data_tech; GRANT SELECT, INSERT, UPDATE, DELETE ON my_new_table TO research_assistant; lets you grant the necessary permissions without having to know who the research assistants are this semester, and who is on the data entry staff at this moment. Is there a similar facility with a different name in MySQL? If not, is it planned? Thanks. --Erv Young - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: equivalents to ROLES in SQL-Server in MySQL.
On Tue, Jan 22, 2002 at 12:13:10PM +0530, sreedhar wrote: Dear all, Is there any equivalents to ROLES in SQL-Server in MySQL. Not really, no. Have you already seen the documentation for MySQL's privilege system in the manual? Jeremy -- Jeremy D. Zawodny, [EMAIL PROTECTED] Technical Yahoo - Yahoo Finance Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 MySQL 3.23.41-max: up 22 days, processed 513,735,673 queries (266/sec. avg) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
equivalents to ROLES in SQL-Server in MySQL.
Dear all, Is there any equivalents to ROLES in SQL-Server in MySQL. regards, Sreedhar - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Roles
Date |Tue, 28 Aug 2001 09:41:02 -0700 From |John Meyer [EMAIL PROTECTED] Hello! JM In Oracle, I remember working with roles that I could assign to a user. Is JM there such a mechanism, or are there plans to implement such a mechanism, JM in MySQL. If you mean level based priveleges then no, mysql doesn't support it. More about mysql priveleges system you can read here: http://www.mysql.com/doc/P/r/Privilege_system.html ___ For technical support contracts, visit https://order.mysql.com/ This email is sponsored by SWSoft, http://www.asplinux.ru/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Grigory Bakunov [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB / SWSoft /_/ /_/\_, /___/\___\_\___/ ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Roles
In Oracle, I remember working with roles that I could assign to a user. Is there such a mechanism, or are there plans to implement such a mechanism, in MySQL. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Roles
In Oracle, I remember working with roles that I could assign to a user. Is there such a mechanism, or are there plans to implement such a mechanism, in MySQL. This sounds a lot link granting a user certain privileges. Here's the part of the documentation that talks about this: http://www.mysql.com/doc/U/s/User_Account_Management.html HTH, Kurt Hansen [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Roles
At 09:57 AM 8/28/01 -0700, you wrote: In Oracle, I remember working with roles that I could assign to a user. Is there such a mechanism, or are there plans to implement such a mechanism, in MySQL. This sounds a lot link granting a user certain privileges. Here's the part of the documentation that talks about this: Actually, you grant the permissions to a role (such as manager, secretary, etc), and then you grant the role to a specific user. It madkes user management a whole lot easier. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
